Customer Trial Balance PDF versus Query

Hi, 

For what you wish to do, it could be good to look at the customer balance history functionality. 

Assuming this has not been used, you first create a category.  Then assign the company(s) to that category.   This is a onetime set up. 

Then in the balance history, create a new record for example August 13 2025.  Really any date. 

IFS then assesses the invoices, payments, payment dates and other to get the true open balance as of August 13.  Then see the details. 

A SQL: search is really tough because the “true” open balance or balances is dependent on many factors, such as invoice dates, payment dates, and activity.   If your logic in the SQL is flawed / incomplete, it will not match. 

Best regards, 

Thomas

Also, I would add that if you have business reporter, this is a very simple report. But it is also possible something other than invoices are being posted to the IFS standard TB you are trying to tie back to.

Hi Thomas, hi Richard,
yes it seems it is a complex issue and I wonder why IFS never tried to have an alternative to the PDF way!
Meanwhile my customer provided me with more information and showed me the Voucher Row Analysis:

The two figures in my query coming with minus are in the sum of the Debit Amount.
so we have to turn SeriesID = OP from minus to plus. :-)
Looking forward to learn more from that field.
All the best

Michael
 

Did you look at the balance history? 

Hi experts, 
we did far more tests and the first FACT_CustomerTrialBalance looks very good.
Some “aliens” but we will fix the as well.

Coming down to AccumulatedDebit and AccCredit.
There we have mismatches!
 

My DWH looks the following:

When we summ up data from the FACT_GeneralLedgerVoucherRow (and copy to Excel to have a better look) we will see:

So my question is:
Could it be that the Accumulated Debit and Credit is without the period used in the CustomerTrialBalance?

So, only accumulated up to 08/2025 but no vouchers from 09/2025.

Thank you for your opion and input.

BR

Michael