Sum the Records based on Records from Another Table

mysamza

So I am trying to sum the Base and Amount of VAT Entry Table.
But for only those records that have a particular Global Dimension.

So from the GL Entry Table I can get all the Document No. filtered on the Global Dimension 1 I want.
Then I want to filter the VAT Entry table with those Document No. found from GL Entry table and Sum their Base and Amount.

Here is what and not getting the expected result for the sake of this example we will call the Global Dimension 1 as 'AB'
On my report's (which has VAT Entry as its dataitem) onaftergetrecord()

GLEntry.SETFILTER("Global Dimension 1 Code", 'AB');
"VAT Entry".SETFILTER("Document No.", GLEntry."Document No.");
"VAT Entry".SETFILTER("Type", 'Sale');
"VAT Entry".SETFILTER("VAT Prod. Posting Group", '5% VAT');
IF "VAT Entry".FINDSET THEN BEGIN
REPEAT
TotalPS := TotalPS + "VAT Entry".Base;
TotalVATAmountPS := TotalVATAmountPS + "VAT Entry".Amount;
UNTIL "VAT Entry".NEXT=0;
END;

The result I get tells me GLEntry.SETFILTER("Global Dimension 1 Code", 'AB'); didn't work.

Any hint would be highly appreciated thanks in advance.

aacnsilva

You're applying the filter to the GLEntry variable but you're not doing anything with it...
You must add a dataItem for the G/L Entry table and set the filter on the OnPreDataItem trigger and then link the VAT Entry dataItem with "Document No." = "Document No." on the dataItem property DataItemLink.

You can also just have the G/L Entry dataItem and do the calculation with your code applying the same filters on the onaftergetrecord of the G/L Entry dataItem.

Be aware that the G/L Entry has many records with the same Document No. so you'll have to control this, since you don't want to calculate more than once the same document.

You also check the keys in the VAT Entry table and select or add a new key with document no. in which the SumIndexFields contain the Base and Amount fields and you won't have to the the iteration on that table. You'll just have to VATEntry.CALCSUMS(xxx, xxx);