0

Sum GL Entries - Filter From Different Table

CJH

August 12, 2020 19:32

None

Follow

Hi Everyone,

I've done some research on this forum to no avail. I was hoping someone could tell me if it is possible to sum the G/L Entry "Amount" field based on a filter from a different table?

I want to sum all G/L Entry "amounts" for all posted sales invoices based on a specific GL account, posting date, and salesperson code.

The kicker is, I need to filter based on the salesperson code from the Sales Invoice Header table, not the G/L Entry table. 

This formula works great to sum exactly what I need, but it uses the "Salesperson Code" field from the G/L Entry table instead of the Sales Invoice Header table.

=-NL("Sum","G/L Entry","Amount","G/L Account No.","3000","Salesperson Code",C261,"Posting Date","8/12/2020")

Can anyone help me out?

 

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Yuko Saito August 21, 2020 08:12

  Hi, you should be able to using Link= function.

  =-NL("Sum","G/L Entry","Amount","Posting Date","8/12/2020","G/L Account No.","3000","link=","Sales Invoice Header","No.","=Document No.","Salesperson Code",C261)

  https://support.jetglobal.com/hc/en-us/articles/219402627-Using-Link-

• 0
  
  Marc August 21, 2020 09:02

  Hi. It should be simple-enough to link to the second table. You will need a field in each table (G/L Entry and Sales Invoice Header table) which return the same result (e.g. Salesperson Code, but you'll need to be sure to find the correct name of the field).

   

  Instead of : "Salesperson Code",C261

  Try: "Link=","Sales Invoice Header","<A>","=<B>","Salesperson Code [or whatever it's called in the Sales Invoice Header table]",C261

  * <A> is the name of field in Sales Invoice Header table which will link to G/L Entry table

  * <B> is the name of field in G/L Entry table which will link to Sales Invoice Header table

   

  M

• 0
  
  CJH August 21, 2020 20:09

  Great, thank you both for your help.

  
  Yuko, I tried your suggestion first and it worked perfectly. Thanks so much.