Exchangerate adjustment on account balance

[Florian Haase]

Hi

we had in Business OnPrem modules for adjusting the accounting balance for used currencies by the actual currencyrate on given accounts and costunits. For that we need the Outgoing balance for a given period from the accountingtransactions.

That worked by something about that (not exactly):

SELECT SUM(Amount in Currency), CurrencyNo, AccountNo, CostUnits1-12 FROM GeneralLedgerTransaction GROUP BY CurrencyNo, AccountNo, CostUnit1-12No

WHERE Period < YYYYMM

We try to do about the same in BNxt but I can't find a good way to do so:

1) We can get all transactions from the GeneralLedger first by filtering on Period<yyyymm - that can result in several millions of lines for distinct customers. But than we can group and summerize and all we want after that. Good solution? I don't think so

2) We can use the aggregate endpoint by setting filter on each combination of accountNumber, CostunitNumber and CurrencyNumber. (for 50 accounts, 10 currencies and 50 costUnits = 25000 API calls). Good solution?

Any better suggestions?

Florian