Just curious how we could find the generic inquiry behind any screen. For example, the 'Account Summary' Screen: ScreenId=GL401000.
I ask, because the mission is to create a dashboard tile showing the ending balance for a certain GL Account where the record period = the current period.
I’d like to understand where I can copy/edit/manipulate the data I see on the Account Summary screen. I tried:
- Navigating to the screen
- Customization button (looking for ‘Edit Generic Inquiry’)
But ‘Edit Generic Inquiry’ is not an option.
What is an efficient way to get the info in the grid below, assuming I can create the parameters for Ledger & Period?
Thank you for any input.
Best answer by Gabriel Michaud
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
- 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.