0

Calculated Filter

Judith Stettler

March 07, 2019 13:06

None

Follow

Hi

I'm struggling to find a way to use calculated filters with values from different tables. Actually, I have no idea if this is even possible. 

The idea is to generate an item list with nl(rows). The item list is generated with criteria from the item table and the value entries table. Additionally I would like to set a filter which calculates if the valid purchase price in the purchase price table is the same price as it is in the last purchase line.

I'm familiar with calculated filters within the primary table. Does anybody know, if there is a way to us calculated filters with fields from different tables?

Thank you very much for your help!

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Harry Lewis March 07, 2019 18:41

  Hi Judith -

  I definitely get the feeling that this can be done with NL(Filter), LINK=, or even just a calculated NL(Last) function.

  Before I could have any certainty, I need to clarify a few things.

  When you say "...is the same as it is in the last purchase line"... from what table is that last purchase line coming?  Value Entry?

  How do you define "last" ?  By the Entry No. field?  By some date in the record?

  Is "Valid Purchase Price" the name of a field in your database?  (my Purchase Price table does not include that).

   

• 0
  
  Judith Stettler March 08, 2019 06:47

  Hi Harry,

  To be more precise I will give you a practical example. Hopefully I manage to name all the fields correctly, as my Jet Report is in German.

  First the formula to get all the items with a purchase and stock:

  =NL("rows";"Value Entry";"No.";"Posting Date";"02.02.19..05.02.19";"Ledger Entry Type";"Purchase";"Invoiced Amount";">0";"=NL(""Sum"";""Item Ledger Entry"";""Amount"";""Posting Date"";""01.01.00..05.02.19"";""No."";NF(;""No.""))";"<>0")

  To this formula I would like to add a filter which gives me all Item numbers where price 1 – price 2 <> 0. Here the formulae to retrieve the prices (for this example I took the item number 12345):

  Price 1: =NL("-1";"Purchase Price";"Purchase Price";"No.";"12345";"Priority";"1";"+Start Date";"<=05.02.19")

  Price 2: =NL(;"Purchase Line";"Purchase Price";"No.";"12345";"Link=";"Value Entry";"Document Number.";"=Document Number";"No.";"12345";"Posting Date";"02.02.19..05.02.19";"Invoiced Amount";">0";"Ledger Entry Type";"Purchase")

  In the formula for price 2 I assume that there is only one Purchase Price to retrieve or that all the Purchase Prices have the same value. It is important for me to get the Purchase Price in the Purchase Line table, as the Purchase Price in the Value Entries is not in the original currency (at least in my NAV).

  Does this give you a better idea of what I would like to do?

• 0
  
  Judith Stettler March 08, 2019 12:31

  I ended up doing this:

  =NL("rows";"Value Entry";"No.";"Posting Date";"02.02.19..05.02.19";"Ledger Entry Type";"Purchase";"Invoiced Amount";">0";"=NL(""Sum"";""Ledger Entry"";""Amount"";""Posting Date"";""01.01.00..05.02.19"";""No."";NF(;""No."")";"<>0";"=NL(;""Purchase Line"";""Purchase Price"";""No."";NF(;""No."");""Link="";""Value Entry"";""Document Number"";""=Document Number"";""No."";NF(;""No."");""Posting Date"";""02.02.19..05.02.19"";""Invoiced Amount"";"">0"";""Ledger Entry Type"";""Purchase"")-NL(""-1"";""Purchase Price"";""Purchase Price"";""No."";NF(;""No."");""Priority"";""1"";""+Start Date"";""<=05.02.19"")";"<>0")

  But there is a Syntax Error somewhere, as Jet Report warns me.