Hi @arlinewelty93 ,

There’s no generic inquiry behind this screen; it’s implemented in C# and uses multiple data access classes and logic. There is a way to simulate roughly the same thing using an inquiry; this is a challenge I’ve had to solve when working on ARM and building Velixo Reports as well. 

I just created a simplified version for you that I think will be well adapted to your needs. The XML of the inquiry is attached to this message, import it to you system

The ReferenceFinPeriodID is the column you need to filter on to get the balances as of a specific period. 

The FinPeriodID column is the most recent period with activity for this ledger/branch/account/subaccount combination

Notes:

• GLHistoryLastActivity is a “projection” DAC in Acumatica, and not an actual table; it does a full join between GLHIstory and FinPeriod to find the most recent period for each ledger/branch/account/subaccount combination. You can see the full query that’s been executed if you go to Help->Trace, it’s quite long.
• For income/revenue accounts, we limit to the current financial year only (based on the ReferenceFinPeriodID you filtered on)
• This inquiry performs well under SQL Server, even when you have a large GLHistory table. if you need to run it on MySql with a large DB you will notice it is very slow -- GLHistoryLastActivity does not perform well in GIs under MySql in versions of Acumatica before 2020 R2.  I have an optimized version of this inquiry that works very quickly across SQL Server and MySql now, but the query is complex to understand and I wanted to emphasize readability over performance here.

Thank you for your generous answer @Gabriel Michaud - I’ll give this a try.

@Gabriel Michaud 

this doesn’t seem to return the correct value for beginning balance if the account doesn’t have transactions in the current period… 

@matthewbeebe GLHistory table only contains rows for periods that have activity. The inquiry above should already take care of returning the period with the most recent activity; if FinPeriodID<>ReferenceFinPeriodID, it means there was no activity in that period and that the record returned is from the most recent period. The balance you need to use as beginning balance is the closing balance of that prior period.

but that beginning balance needs to be from the last prior period with activity in it, not necessarily the immediately preceding prior period… so it gets complex really quickly.. which starts to explain why they implemented it as a screen in c# and explains why others have been fine with a workaround where you put dummy entries into the period.

As it turns out, I solved my particular problem in a far easier way: instead of attempting to duplicate their functionality through a GI just so I could get a complete “trial balance” screen instead of a grid that pages, I just customized the screen…

Sometimes the solution is easy if you think about it long enough.  LOL

Thanks for your help!!

-Matt

but that beginning balance needs to be from the last prior period with activity in it, not necessarily the immediately preceding prior period…

This is exactly what the generic inquiry above does -- you can see in the screenshot that the most recent balance is not necessarily from the immediately preceding prior period.

Hi @Gabriel Michaud - do you have any recommendations on how we can get this to work if there are multiple subaccounts used per GL account? Also, I am finding variances on other accounts as well.. any idea why?

@ashleyb the discrepancies are due to what I described above in my reply to user matthewbeebe: 

GLHistory table only contains rows for periods that have activity. The inquiry above should already take care of returning the period with the most recent activity; if FinPeriodID<>ReferenceFinPeriodID, it means there was no activity in that period and that the record returned is from the most recent period. The balance you need to use as beginning balance is the closing balance of that prior period. 

The inquiry should also work with subaccounts already -- I just tested it and it shows me entries for accounts that have balances for multiple subaccounts: