Skip to main content
Answer

Account Details - recreation GI (No account filter)

  • November 30, 2022
  • 8 replies
  • 303 views
ketiller
Jr Varsity III
Forum|alt.badge.img

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

Best answer by BenjaminCrisman

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.

 

8 replies

BenjaminCrisman
Acumatica Employee
Forum|alt.badge.img+4
  • BenjaminCrisman
  • Acumatica Support Team
  • Answer
  • November 30, 2022

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.

 

lauraj46
Chris Hackett
ketiller
ketiller
Jr Varsity III
Forum|alt.badge.img
  • ketiller
  • Author
  • Jr Varsity III
  • November 30, 2022

@BenjaminCrisman How would you join the tables GLHistoryByPeriod to GLTranR

 

Thank you,

KT

BenjaminCrisman
Acumatica Employee
Forum|alt.badge.img+4
  • BenjaminCrisman
  • Acumatica Support Team
  • November 30, 2022

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

J
Semi-Pro III
Forum|alt.badge.img
  • jzhu
  • Semi-Pro III
  • July 10, 2025

Hi ​@ketiller Were you able to get beginning and ending balance field to show on your Account Details GI? I am running into the same issue and still struggling to get it to show. Thanks!

BenjaminCrisman
Acumatica Employee
Forum|alt.badge.img+4
  • BenjaminCrisman
  • Acumatica Support Team
  • July 10, 2025

​@jzhu We have a DAC overview for finance tables and should include details on how to link these tables:

https://help.acumatica.com/Help?ScreenId=ShowWiki&pageid=14ba5fca-ff8f-41fc-a3a4-2114235b3362

Chris Hackett
ketiller
Jr Varsity III
Forum|alt.badge.img
  • ketiller
  • Author
  • Jr Varsity III
  • July 10, 2025

​@jzhu I just created a GI using GL history and filter down by specific year, Branch, Account ect. 

 

Thanks,

Kage

Chris Hackett
A
Freshman I
Forum|alt.badge.img
  • AHanke
  • Freshman I
  • July 16, 2025

​@ketiller are you able to share the working XML for this GI? I am looking to build the exact same! 

ketiller
Jr Varsity III
Forum|alt.badge.img
  • ketiller
  • Author
  • Jr Varsity III
  • July 17, 2025

​@AHanke I attached the xml, the conditions and groups can be customized to fit your needs. I would recommend turning on the group by ledger ID if you have multiple ledgers.

 

Best,

Kage 

1 Attachments
Chris Hackett
A
Terms & ConditionsCookie settingsAccessibility statement
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