By using this website, you agree to our Terms of Use (click here)
I am trying to create a Generic Inquiry to accomplish what the title indicates above. It appears that the data needed for this is conveniently located in the GLHistory table. However, I'm running into an intriguing and baffling architecture decision that I cannot figure a method around.
For simplicity, let's assume the GLHistory has the following columns:
- Branch
- Sub
- Account
- FinPeriodID
- YtdBalance
There are a few other columns but none that I believe are applicable to my problem.
Acumatica adds a row for each Branch, Sub, Account, FinPeriodID that has a value for example if I had the following:
- Branch XYZ
- Sub 1
- Account 123
- FinPeriodIDs - (201901, 201902, 201903)
- YtdBalance - (10, 20, 30)
I would have the following rows:
Branch -- Sub -- Account -- FinPeriodID -- YtdBalance
XYZ -- 1 -- 123 -- 201901 -- 10
XYZ -- 1 -- 123 -- 201902 -- 20
XYZ -- 1 -- 123 -- 201903 -- 30
That's easy to work with in a Generic Inquiry when totaling multiple accounts together to get high level totals such as revenue, COGS, expenses.
However, Acumatica doesn't write a row if there were no transactions in a month. So if I have account 124 with the following YtdBalance (note that in February there was no change in value):
- Branch XYZ
- Sub 1
- Account 123
- FinPeriodIDs - (201901, 201902, 201903)
- YtdBalance - (10, 10, 30)
I would have the following rows:
Branch -- Sub -- Account -- FinPeriodID -- YtdBalance
XYZ -- 1 -- 124 -- 201901 -- 10
XYZ -- 1 -- 124 -- 201903 -- 30
There is no row for February in the data! So when I total up Account 123 and 124 for February I get a total of 20 instead of 30 since there was no value in the data set for February.
I'm assuming I'm not the first person to want a P&L summary nor to run into this issue with what appears to be a very poor database architecture decision by Acumatica. The database space savings of this decision was definitely not worth the performance impact nor the development impact (internally and externally)!
Has anyone dealt with this perviously and can you suggest how you have solved this? Help greatly appreciated!
Yes dealt with it many times. You need to use GLHistoryByPeriod as your driving table. You will then need to join GLHistory twice. Give one of the joins the Alias GLHistoryLastActive. So basically what you want to do is select data from GLHistory if the period had a movement. If it didn't have a movement, you take it from GLHistoryLastActive. GLHistoryLastActive joins finPeriodID to lastActivityPeriod on GLHistoryByPeriod. In your results, you need to use IsNull to take data from GLHistoryLastActive if GLHistory is Null (GLHistory is joined to GLHistoryByPeriod on finPeriodID - when there's no movement for a period, null will be returned).
Make sure GLHistoryByPeriod is Left joined to both GLHistory and GLHistoryLastActive.
@royce-lithgo and @timrodman, Thank you both for the information. The information on the GLHistoryLastActive was very useful and will continue to be useful for GIs. That was exactly the piece I was missing.
This gave me the information to be able to solve it in SQL as well so I could provide a much richer set of data, providing a 12 month historical P&L with analysis as well as various charts and trend lines based on financial data.
Thank you again for your help!
This problem came up again for me and reading over my previous post was a little unclear. For a concise explanation of GLHistoryByPeriod, check the help:
https://help.acumatica.com/Help?ScreenId=ShowWiki&pageid=0706f316-2b71-6254-44dc-65544025f479
(scroll down a bit to Historical Tables)
Note: GLHistoryByPeriod is a very heavy DAC - just look at the Trace from a GI that uses it to see the SQL it generates. So be careful how you use it. In my case where I wanted YTD P&L, it was substantially faster to sum the GLHistory records up to the required period (for the Fin Year of the Period) and calculate the Balance than it was to use LastActivityPeriod from GLHistoryByPeriod to get the YTD Balance from that period.