Solved

Account Details - recreation GI (No account filter)

  • 30 November 2022
  • 3 replies
  • 148 views
K
Rank
Userlevel 4
Badge

Hello, 

I am trying to export account details for the whole year 2022 by a specific branch for all accounts. on the Account details inquiry (GL404000) we have to include an account, so I was trying to recreate the screen as a GI where we do not have to include an account. I can get all the information in the GI besides the Beg. Balance and the End. Balance, any suggestions on how to get that information?

Data class: GLTranR

Data field: SignBegBalance

 

Screenshot of Account details screen:

Thank you,

KT

icon

Best answer by BenjaminCrisman 30 November 2022, 17:05

View original

3 replies

BenjaminCrisman
Rank
Userlevel 7
Badge +4

Hi @ketiller! For this issue you’ll need to join in the GLHistoryByPeriod table to get these balances:

https://help-2021r1.acumatica.com/(W(3))/Help?ScreenId=ShowWiki&pageid=0706f316-2b71-6254-44dc-65544025f479

GLHistoryByPeriod

GLHistoryByPeriod is a projection based on the GLHistory and FinPeriod DACs join, which obtains the last period to which each GL entry has been posted (last period for which GLHistory record exists) that is less or equal to the specified period for each account-subaccount pair.

GLHistory is used as primary on BQL select but semantically the basic table is FinPeriod: the projection joins GHistory records with less or equal FinPeriodID to each FinPeriod record which generates a set of all previous (including current) existing periods with posted data for each financial period. Then the projection groups obtained sets by FinPeriod.FinPeriodID field and extracts maximum GHistory. FinPeriodID as last activity period for each group. See the calculation scheme on the following diagram.

 

 

To obtain last activity period for the needed period, you should specify the needed period for GLHistoryByPeriod.FinPeriodID field; GLHistoryByPeriod.LastActivityPeriod will contain last existing period with data.

 

K
Rank
Userlevel 4
Badge

@BenjaminCrisman How would you join the tables GLHistoryByPeriod to GLTranR

 

Thank you,

KT

BenjaminCrisman
Rank
Userlevel 7
Badge +4

@ketiller I don’t know that it connects directly to GLTranR, you would need to link GLTranR to GLHistory and then GLHistory to GLHistoryByPeriod.

Did you already try this?

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