Hello everyone in the community,
I am working in the Acumatica Report Designer customizing the Accounts Payable report AP632500.rpx (AP Vendor Summary / Details). I need some help to solve a total accumulation issue with USD amounts in the group footers.
Technical Context & What We've Done So Far:
-
Currency filtering (
CuryID): We modified the detail cells for the Monto USD and Saldo USD columns with a conditional so they only display data when the document is in USD and remain empty (Null) for peso (MXN) vendors, avoiding visual duplication. -
Sign inversion: We applied logic to subtract documents of type
ADR,CRM, andPPM. -
Current formulas in detail cells:
-
Monto USD:
=IIf([APRegister.CuryID] = 'USD', IIf([APRegister.DocType]='ADR' Or [APRegister.DocType]='CRM' Or [APRegister.DocType]='PPM', -1 * [APRegister.CuryOrigDocAmt], [APRegister.CuryOrigDocAmt]), Null) -
Saldo USD:
=IIf([APRegister.CuryID] = 'USD', IIf([APRegister.DocType]='ADR' Or [APRegister.DocType]='CRM' Or [APRegister.DocType]='PPM', -1 * [APRegister.CuryDocBal], [APRegister.CuryDocBal]), Null)
-
The Problem:
At the detail level, the individual rows sum up to the exact real amount (for example, summing the independent balances of a USD vendor gives a real total of 45,483.52 USD). However, when trying to display the accumulated total in the vendor footer (groupFooterSection8), the sum is incorrect and gets inflated.
This happens because the report performs a join with the APAdjust table (payment applications), which duplicates the lines and causes any direct sum function (=Sum(...)) to overstate the value in the footer.
What I Need Help With:
How can I structure the expression, variable, or function in the vendor footer within the Acumatica Report Designer so that the sum respects the USD filter and takes only the net balance of the master document, without being altered or duplicated by the APAdjust table rows?
Thank you very much in advance for your help!