When a Sales invoice is paid it is matched against the receipt. A Sales invoice can also be matched against a credit note but there is no distinguishing flag. I want the final report to only show the Paid invoices. I am currently Grouping by Customer, then Matching Letter, then document, so at this level you can see if the match is with a receipt or with credit notes. I output the Group footer of the document giving me a list of documents, which for a customer payment will start with the Receipt, followed by one or more invoices totaling the value of the receipt:
GF4 Customer A Match BC REC101009798 GBP240.00 GF4 Customer A Match BC INV101059389 ‘new field’ GBP120.00 GF4 Customer A Match BC INV101059390 ‘new field’ GBP120.00
If the matching is as a result of a customer payment, the Receipt will always be on the first line in the group output.
How do I create a ’new field’ on the Invoice lines that will say “Paid” , it doesn’t matter if the new field also appears on the Receipt line?
I have tried to use a formula on the group footer referencing the document type but of course when the document type changes, so does the result of the formula. Having a success on the first line I have been concentrating on trying to copy the result on to the successive lines so thought a fresh pair of eyes might help.
Many thanks
Thank you Pals, I have solved this with a colleague. We revised the formula on the Group Footer. First we created a formula that assigned a 1 to a Receipt and 0 to all other docments: IF {GACCDUDATE.TYP_0} = "REC01" THEN 1 ELSE 0. Next we created a SUM formula: IF Sum({@rowIsPaid}, {GACCENTRYD.MTC_0}) > 0 THEN "Paid" and put this on the Group footer.