Solved

Find the generic inquiry behind the 'Account Summary' Screen

  • 10 December 2020
  • 2 replies
  • 44 views

Hello all,

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.

 

icon

Best answer by Gabriel Michaud 10 December 2020, 18:31

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.

 

 

View original

2 replies

Userlevel 4
Badge +2

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.

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2020  Acumatica, Inc. All rights reserved