Solved

Find the generic inquiry behind the 'Account Summary' Screen

  • 10 December 2020
  • 8 replies
  • 1273 views
arlinewelty93
Rank
Userlevel 2
Badge

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

View original
https://twitter.com/DataQuestHello

8 replies

Gabriel Michaud
Rank
Userlevel 7
Badge +10

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.

 

 

1 Attachment
Gabriel Michaud Founder @ Velixo, Excel-based reporting for Acumatica
arlinewelty93
Rank
Userlevel 2
Badge

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

https://twitter.com/DataQuestHello
M
Rank
Userlevel 3
Badge

@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… 

 

 

Gabriel Michaud
Rank
Userlevel 7
Badge +10

@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.

Gabriel Michaud Founder @ Velixo, Excel-based reporting for Acumatica
M
Rank
Userlevel 3
Badge

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

 

Gabriel Michaud
Rank
Userlevel 7
Badge +10

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.

Gabriel Michaud Founder @ Velixo, Excel-based reporting for Acumatica
A
Rank
Userlevel 3
Badge

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?

 

 

 

Gabriel Michaud
Rank
Userlevel 7
Badge +10

@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:

 

 

Gabriel Michaud Founder @ Velixo, Excel-based reporting for Acumatica

Reply


Terms & ConditionsCookie settings
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 — 2024  Acumatica, Inc. All rights reserved