Sage 100

One of my customers requests an export of the past 24 months of GL balance history.

They have a total of about 60 months of history

1. In month one, they would like that to be the Beginning Balance that is exported.
2. For month two+, they would like this JUST to be the net change for the period ( month )

On the surface, it appears this is easy - use GL_PeriodPostingHistory

However, I've found that GL_PeriodPostingHistory appears only to hold BeginningBalance for the FIRST period in the file.

This means in order to only get 24 months of data ( when they have 60 months)  to get to the opening balance, I'd have to calculate all prior balances as BeginningBalance - CreditAmount + DebitAmount.

Am I missing a trick to extract this data? There doesn't seem to be a table that holds the current monthly balance for a specific GL Account.

As of now, I'm thinking that my workaround might be just to purge out GL history for all but the periods ( 24 in this case ) that they want to export, and then I should have a good opening balance in that first period which would save me re-calculating on-the-fly.

But I feel like there should be an easier way to extract these balances - am I overlooking a table or two?

------------------------------
Wayne Schulz
wayne@s-consult.com
Schulz Consulting
(860) 516-8990
Moodus, CT
------------------------------

There should be a beginning balance for each year's period 1.  To get the beginning balance of any other period in that year, you have to add things up yourself.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Thanks! 
I was scrolling several thousand pages of history and didn't go far enough to check the next fiscal year

There is an opening balance for the first period of each fiscal year.

------------------------------
Wayne Schulz
wayne@s-consult.com
Schulz Consulting
(860) 516-8990
Moodus, CT
------------------------------

Original Message:
Sent: 06-13-2023 12:55
From: Kevin Moyes
Subject: Exporting GL Balances - GL_PeriodPostingHistory

There should be a beginning balance for each year's period 1.  To get the beginning balance of any other period in that year, you have to add things up yourself.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

I created the attached reports for beginning balance and net transactions.  They may be of help to you.  You can probably modify the formulas to accommodate the years you need.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles
------------------------------

Thanks! I'll take a look at these

------------------------------
Wayne Schulz
wayne@s-consult.com
Schulz Consulting
(860) 516-8990
Moodus, CT
------------------------------

Original Message:
Sent: 06-13-2023 13:29
From: Doug Higgs
Subject: Exporting GL Balances - GL_PeriodPostingHistory

I created the attached reports for beginning balance and net transactions.  They may be of help to you.  You can probably modify the formulas to accommodate the years you need.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles
------------------------------

If your client would prefer an Excel solution you might look at the BetterBeginningBalance column in these. They are refreshable with one formula and one can use the table filters to isolate the year. One uses MS Query and the other uses Power Query.

------------------------------
Dan Burleson
Software Consultant
Connex Software
------------------------------

Original Message:
Sent: 06-13-2023 14:08
From: Wayne Schulz
Subject: Exporting GL Balances - GL_PeriodPostingHistory

Thanks! I'll take a look at these

------------------------------
Wayne Schulz
wayne@s-consult.com
Schulz Consulting
(860) 516-8990
Moodus, CT
------------------------------


Original Message:
Sent: 06-13-2023 13:29
From: Doug Higgs
Subject: Exporting GL Balances - GL_PeriodPostingHistory

I created the attached reports for beginning balance and net transactions.  They may be of help to you.  You can probably modify the formulas to accommodate the years you need.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles

Maybe Sage Intellignece could be used to pull the data.

------------------------------
Bruce Kern
SWK Technologies Inc.
------------------------------

Your method sounds like it will do the trick.  Another option is to use the previous month's ending balance as the current month's beginning balance; I did this and the results are spot on.

------------------------------
Susan Pawlowic
Sage 100 Solutions Architect
RKL eSolutions, LLC
spawlowic@rklesolutions.com
(717) 735-9109 Ext. 4352
------------------------------

Thanks, Everyone.

For clarity - part of the issue is that GL_PeriodPostingHistory contains an opening balance, but it's only for period 1.

To compute a period-end balance for any period except period 1 you'll have to create a cumulative summary - the easiest way seems to be via subtotals from a report grouped by period,  account.

In my case, it turned out the customer was ok taking the full year of period history and then pulling that into their system and letting the system create the period end balances.

------------------------------
Wayne Schulz
wayne@s-consult.com
Schulz Consulting
(860) 516-8990
Moodus, CT
------------------------------