Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Betty Hall]

I would like to identify GL Accounts with no activity.  What is the best way to do this?

——————————
Betty Hall
Powell Christensen inc
Richland WA
——————————

[Tanya Henderson]

?This might be the hard way to do it, but it will depend on if you are looking at Open or History.  You could compare the GL00105 account table to the GL20000 (Open) or GL30000 (History) and see which accounts are in neither of those tables and that would tell you the accounts that have never been used.
You could also print a Trial Balance and mark the box to Include Zero Balance/No Trx accounts.

There might also be a way to do it in Smartlist.
Lots of options.
Tanya

——————————
Tanya Henderson
Senior Consultant
S2 Technology
Park City UT
——————————
——————————————-

[Barbara Gavron]

Here is a SQL script that will tell you the last time a GL account was used. And if it has never been used the query will return a value of NULL. Run this script against the database in question:

WITH vt1 as –creating a virtual table on the fly

(

SELECT

actindx,

MAX(TRXDATE) AS maxdate

from dbo.GL20000 — get the maximum transaction date for a particular account index from the GL20000

GROUP BY actindx

UNION — put the 2 queries together into 1 result set. The columns have to be the same in both queries

SELECT

actindx,

MAX(trxdate) AS maxdate

from gl30000 –get the maximum transaction date for a particular account index from the GL30000

GROUP BY ACTINDX —

)

, vt2 as — creating a virtual table on the fly

(

SELECT

actindx,

MAX(maxdate) AS maxdate2

FROM vt1 — get the maximum for the unioned results

GROUP BY ACTINDX

)

SELECT

g.actindx,

g.actdescr,

g.ACTIVE,

CAST(g.CREATDDT AS DATE) AS CreatedDate,

vt2.maxdate2

from gl00100 g LEFT JOIN vt2 — left join means give me all results from the gl00100 and all the records in the vt2 table that match (the g is giving the table an alias

–for easier reference

ON g.ACTINDX = vt2.actindx

——————————
Barbara Gavron
Atlantic Beach / Jacksonville FL
904-536-0129
——————————
——————————————-

[Beat Bucher]

Thanks Barbara,
Nice & concise script. .love it. 
Going to build an Excel RefEx for our accountants 🙂

PS: I slightly enhanced the last section of the code to include the full Acct number string to make it easier to read for our guys.. 

SELECT g.actindx
,GL00105.[ACTNUMST]
,g.actdescr
,g.ACTIVE
,CAST(g.CREATDDT AS DATE) AS CreatedDate
,vt2.maxdate2
FROM gl00100 g
LEFT JOIN vt2 — left join means give me all results from the gl00100 and all the records in the vt2 table that match (the g is giving the table an alias
–for easier reference
ON g.ACTINDX = vt2.actindx
INNER JOIN GL00105 on g.ACTINDX = GL00105.ACTINDX
ORDER BY GL00105.ACTNUMST

——————————
Beat Bucher
Business Analyst, Dynamics GP SME
Ultra-Electronics Forensic Technology Inc.
Montreal QC/Canada
@GP_Beat http://dyngpbeat.wordpress.com/
Montreal QC GPUG Chapter Leader
GP2013R2 / MR2012 CU14
——————————
——————————————-

[Joni Finnell]

?Great addition to Barbara’s script !

Thanks?

——————————
Joni Finnell [Designation]
GP Projects/Troubleshooter
Consumer Support Services, Inc.
Newark OH
——————————
——————————————-

[Joni Finnell]

?Love the script ! This is very useful.

Thanks?

——————————
Joni Finnell [Designation]
GP Projects/Troubleshooter
Consumer Support Services, Inc.
Newark OH
——————————
——————————————-

[Joni Finnell]

? & :

I tested the script and the results for the maxdate2 is incorrect. It is hard to tell if it is a result of Barbara’s script or Beat’s portion?? because I need the full GL account to tell the accounts apart to determine if it works before I added Beat’s portion. 😎

Thanks

——————————
Joni Finnell [Designation]
GP Projects/Troubleshooter
Consumer Support Services, Inc.
Newark OH
——————————
——————————————-

[Beat Bucher]

Hi Jonni,
My join of GL00105 has no impact on the date calculation.. this is done prior to the last SELECT statement in the vt1 & vt2 tables
If there are wrong dates, it must come from the MAX statement run against the GL20000 or GL30000 tables.

I believe however that the 1st section is correct, as it returns the most recent date of the 2 tables shown for each Acct Index entry : 

actindx maxdate
3713 2018-06-27 00:00:00.000
3714 2017-12-31 00:00:00.000
3714 2018-06-27 00:00:00.000
3715 2017-12-31 00:00:00.000
3715 2018-06-27 00:00:00.000
3716 2017-12-31 00:00:00.000
3716 2018-06-27 00:00:00.000
3718 2017-12-31 00:00:00.000
3718 2018-06-27 00:00:00.000

When there is only 1 it uses that date, otherwise the most recent one. 
——————————
Beat Bucher
Business Analyst, Dynamics GP SME
Ultra-Electronics Forensic Technology Inc.
Montreal QC/Canada
@GP_Beat http://dyngpbeat.wordpress.com/
Montreal QC GPUG Chapter Leader
GP2013R2 / MR2012 CU14
——————————
——————————————-

[Joni Finnell]

?I have some results showing NULL when there has been some activity. I also noticed the ones that had the activity were reclassed to a different account so the ending balance was zero. Would that make them show on this list?

Thanks

——————————
Joni Finnell [Designation]
GP Projects/Troubleshooter
Consumer Support Services, Inc.
Newark OH
——————————
——————————————-

[Jo deRuiter]

Hi

??I wrote a script a while back that will find these.   This script looks at the accounts you have setup in GP and compares that to accounts used in the GL tables (GL20000 and GL30000)  – See Attached

*Please do not use this to create any kind of SQL script to manually delete the accounts in the GL00105 or GL00100 because this script does not take into account all tables in GP – only the GL tables.  Modules such as Project Accounting and/or Manufacturing may need certain GL Accounts to function correctly and this script may report those accounts as never used since they do not hit the GL20000 or GL30000.*

However, this will show you all accounts not used in the GL.

——————————
Kindest Regards,

Jo deRuiter
“That GP Red Head”
Advanced Credentialed Professional-Dynamics GP
Member, GPUG GP Credentialing Exam Committee
Chairman, GPUG Partner Advisory Board
“……what isn’t she up to?”
Heartland Business Systems, LLC
Senior Financial Systems Consultant
Milwaukee, WI
770-906-4504 (Cell)
——————————
——————————————-

[Bruce Strom]

Or query the GL Summary tables, that would be much quicker.

——————————
Bruce Strom
Programmer Analyst
Associated Grocers of Florida / Supervalu
sunrise FL
——————————
——————————————-

[Victoria Yudin]

How about something a bit more simple? The code below will show all accounts not in any of the GL transaction tables.

The only thing to watch out for with this is if you have allocation accounts – they will get captured in here since they never have transactions actually posted to them. If this is a concern, we can add another piece of code to exclude them, but most companies do not use allocation accounts in my experience.

select actnumst Account
from GL00105 
where actindx not in (select actindx from GL10001) 
 and actindx not in (select actindx from GL20000) 
 and actindx not in (select actindx from GL30000)

——————————
Victoria Yudin
Microsoft Dynamics GP MVP 2005-2018
Flexible Solutions, Inc.
http://www.GPReportsViewer.com
http://victoriayudin.com
——————————
——————————————-

[Joni Finnell]

?:

This script also ?results in a GL account that has activity.

I have figured out why. We have archived our GL data using CDA up through 12/31/2013. When the archive is done it transfers and removes data in GL30000 even though I can still see the summary activity. So, when I run the script on my archive db the GL account doesn’t show up on the list because it has the GL data in that db. So, to get my true results for accounts with no activity I would need to combine the list from my live company and the archive company. The duplicates are my inactive accounts. 😎

Thanks everyone!

?

——————————
Joni Finnell [Designation]
GP Projects/Troubleshooter
Consumer Support Services, Inc.
Newark OH
——————————
——————————————-

[Victoria Yudin]

Joni,

Very good point. Glad you figured out how to get around that. Most people don’t have CDA, so this should be a pretty isolated issue.

——————————
Victoria Yudin
Microsoft Dynamics GP MVP 2005-2018
Flexible Solutions, Inc.
http://www.GPReportsViewer.com
http://victoriayudin.com
——————————
——————————————-

[Zubin Gidwani]

Please don’t forget to scan the GL00201  (Budget Line Item Details Table)

If you delete a budgeted account, and then add it back once you realize it was required by the budget even though it had no Actuals activity, GP will likely reassign it a new ACTINDX number.
This can wreak havoc on Mgmt Reporter and other budgeting and reporting systems dependent upon ACTINDX.

Thanks,

——————————
Zubin Gidwani
Dynamic Budgets

——————————
——————————————-