Using Formula SUM in Table Builder

Rhodienat

May 16, 2019 09:53

None

I am writing a report that requires a link of the Vendor Ledger Entry and the VAT entry table. This creates a one to many link using the transaction no as the link.

I am attempting to simply create a list with the sum total of Base and Amount of all the VAT lines that correspond with the Vendor Ledger Entry.

However I am having trouble getting this table formula to work.

Using Table Builder I created the following Link:

=NL("Link","VAT Entry",,"Transaction No.","=Transaction No.")

I then added the prerequisite filters and fields I need from both tables.

Do I use formula within Table builder to add the Base and Amount with the following:

NL("Sum","Vat Entry","Base")

NL("Sum","Vat Entry","Amount")

Would this result in the report totalling the lines within the VAT Entry? I am getting issues attempting to select ok so it seems to be unhappy with he formula somehow.

Help Appreciated.

Date Votes

2 comments

0
Harry Lewis May 16, 2019 14:34
Hello -

Here is a simple example of the syntax needed to include a sum in an NL(Table) function.

You need to use both the the LINKSUM syntax (to get the sum) and the LINK= syntax to define how to connect to that other table.
```
=NL("Table","Customer",{"No.","Name","LinkSum([Cust. Ledger Entry],[Amount])"},"Link=","Cust. Ledger Entry","Customer No.","=No.")
```
Does that help get you pointed in the right direction?
Comment actions Permalink

Rhodienat May 16, 2019 15:03

Thank you for your reply. To advise what I am trying to do.
I have entered Vendor ledger at the primary layer, and then added Vat Entry as the secondary layer.
Within the vat entry table I am pulling off these fields:

Document No.
External No.
Document Type
Purchase (LCY)

I then wish to look up the sum of the Base within the VAT Entry Table.  

The Vendor has a single line, whilst the related vat entry table has multiple records linked on the "Transaction No."

I am only wanting the system to sum the BASE amount for that VLE transaction, for each Vat Product posting type grouping.

However I cannot get the Sum to work.

I tried a number of ways including entering an Add Formula

LinkSum([VAT Entry],[Base])

Stuck - hope you can help.

Please sign in to leave a comment.