0

Filter on sum - different table

Jet Reports Historic Posts

November 17, 2011 22:24

None

Follow

Hi all,

I want to filter the customer table based on a sum of the amount field in the customer ledger entry table. How do I do this?

I'm using Jet 2010 R2 with Nav.

Thanks in advance!

Charlene

• Facebook
• Twitter
• LinkedIn

4 comments

• 
  Jet Reports Historic Posts November 21, 2011 15:31 Official comment

  Hi Charlene,
  
  So first, we can tell Jet to treat the calculated values as numeric by changing your actual filter to "Number&<0". As far as the posting date itself, generally you should use ".." rather than "<" with dates. So if you had a date filter of "..11/01/2011" in cell C2, your formula would look like this:
  
  =NL("Rows","Customer Bank Account",,"Code",E3,"Customer No.",E2,"PAP Withdrawal","Yes","=nl(""Sum"",""Cust. Ledger Entry"",""Remaining Amount"",""Customer No."",nf(,""Customer No.""),""Posting Date"","""&$C$2&""",""Open"",""Yes"")","Number&<0")
  Note that the "Number&" syntax only works when the filter field is calculated as in this example. If it was just a normal filter field, there would be no need to use "Number&" nor would it work to do so. Does this work for you?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts November 18, 2011 00:29

  Hi Charlene,
  
  I should preface this by saying that the "Balance" or "Balance ($)" flow fields on the Customer table goes to the Amount field on the Cust. Ledger Entry, so you could just sort by the value of those flow fields. I'm assuming that you know this, but you want to filter by more on the Cust. Ledger Entry than just the posting dates or something so you need more flexibility. Basically, the simple version would look something like this:
  
  =NL("Rows","Customer",,"-=NL(""Sum"",""Cust. Ledger Entry"",""Amount"",""Customer No."",NF(,""No.""))","*")
  This function will return record keys for customers sorted in descending order by the sum of their amounts on the Cust. Ledger Entry table. The sort direction is indicated by the - at the beginning of the calculated formula. The NF with the blank key is what connects the NL function in calculated filter field with the main function. Basically for each record from the customer, it is running that nested NL function through Excel (and then back through Jet) to get the sum from the Cust. Ledger Entry where the value of the Customer No. field is equal to the value of the No. field from the original formula.
  
  Calc filter fields like this are not necessarily very fast, but it should work. Is that what you are trying to do?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts November 18, 2011 14:37

  Thank you, Hughes. That was a big help. I'm having an issue with it though. This is my formula:
  
  =NL("Rows","Customer Bank Account",,"Code",E3,"Customer No.",E2,"PAP Withdrawal","Yes","=nl(""Sum"",""Cust. Ledger Entry"",""Remaining Amount"",""Customer No."",nf(,""Customer No.""),""Posting Date"",""<11/01/2011"",""Open"",""Yes"")","<0")
  I'm working with a small set of test data. The formula won't work with the "<0" as the last argument (I want to only see customer bank accounts for customers with balances less that zero). However, if I put the exact amount that I know is in my test data ("=-224.87") the formula will work.
  
  Also, I'm having difficulty with the date filter in the nested NL. It works if I hard code the date as I have above, but not if I reference a cell. I'm not famiiar with the syntax for this type of formula.
  
  Thanks!

• 0
  
  Jet Reports Historic Posts November 29, 2011 22:25

  Thank you, that works perfectly now.