Just about every Dynamics GP customer I have worked with, at one time or another, has had occasion to search the General Ledger for a particular dollar amount. Usually, I use the Account Transactions SmartList for this and search for Debit Amount = XXX or Credit Amount = XXX (using the Match 1 or More search type). Often this is fine. However, once in a while this comes back with thousands of results. When you’re doing an ‘or’ search in SmartList, you cannot at the same time add an ‘and’. So you’re kind of stuck here, you can get all the results and then dump them to Excel to do further searching, do multiple searches in SmartList, or you can to go into SQL and do some more sophisticated querying there.
To combat this, I was recently asked to create a custom SmartList that would make it possible to both search for an amount and add some additional criteria. I thought that would be a great topic for my blog, so I have some up with the view below that returns the common columns I like to see when I search the General Ledger and also has a column for ‘Amount’ – this will be either the debit or the credit, whichever is not zero.
While I was at it, I also added a few other things, like a timestamp and the transaction note. I think it’s really useful to be able to see the date and time something was posted using the DEX_ROW_TS (this means it will only work in the last few GP versions, GP 10.0, 2010 and 2013). Having this allows answering a question like, “I know my AR and GL reconciled yesterday afternoon, but now they do not, what was posted for $297 after 5pm yesterday?” The timestamp in my code is converted to EDT, since that’s where I am, but you can easily change the code to work with your time zone if you prefer by editing rows 68 and 70.
Some additional resources:
create view view_GL_Trx_for_Searches as -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ -- Created September 4, 2014 by Victoria Yudin -- Flexible Solutions, Inc. -- For other code, please visit http://victoriayudin.com -- Converts to EDT time zone -- Update on Sep 14, 2014 to move amounts and account higher -- in the list of fields -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ select g.OPENYEAR Fiscal_Year, g.JRNENTRY JE, case when g.CRDTAMNT <> 0 then g.CRDTAMNT else g.DEBITAMT end Amount, g.DEBITAMT Debit_Amount, g.CRDTAMNT Credit_Amount, a.ACTNUMST Account_Number, d.ACTDESCR Account_Description, g.SOURCDOC Source_Doc, g.REFRENCE Reference, g.DSCRIPTN [Description], g.TRXDATE Trx_Date, g.TRXSORCE Transaction_Source, g.LASTUSER Last_User, g.LSTDTEDT Last_Edited, g.USWHPSTD User_Who_Posted, g.ORGNTSRC Batch_Number, case g.SERIES when 1 then 'All' when 2 then 'Financial' when 3 then 'Sales' when 4 then 'Purchasing' when 5 then 'Inventory' when 6 then 'Payroll – USA' when 7 then 'Project' when 10 then '3rd Party' else '' end Series, g.ORTRXTYP Originating_Trx_Type, g.ORCTRNUM Originating_Control_Number, g.ORMSTRID Originating_Master_ID, g.ORMSTRNM Originating_Master_Name, g.ORDOCNUM Originating_Doc_Number, g.ORPSTDDT Originating_Posted_Date, g.ORTRXSRC Originating_Trx_Source, g.CURNCYID Currency_ID, case g.ICTRX when 1 then 'Yes' else 'No' end Intercompany, g.ORCOMID Originating_Company_ID, g.ORIGINJE Originating_JE, g.ORCRDAMT Originating_Credit_Amount, g.ORDBTAMT Originating_Debit_Amount, case when g.ORCRDAMT <> 0 then g.ORCRDAMT else g.ORDBTAMT end Originating_Amount, case g.VOIDED when 0 then 'No' else 'Yes' end Voided, cast(dateadd(hour, -4, g.DEX_ROW_TS) as date) EDT_Date_Stamp, cast(cast(dateadd( hour,-4,g.DEX_ROW_TS) as time) as char(5)) EDT_Time_Stamp, coalesce(n.TXTFIELD,'') Note from (select OPENYEAR, JRNENTRY, SOURCDOC, REFRENCE, DSCRIPTN, TRXDATE, TRXSORCE, ACTINDX, LASTUSER, LSTDTEDT, USWHPSTD, ORGNTSRC, SERIES, ORTRXTYP, ORCTRNUM, ORMSTRID, ORMSTRNM, ORDOCNUM, ORPSTDDT, ORTRXSRC, CURNCYID, NOTEINDX, ICTRX, ORCOMID, ORIGINJE, CRDTAMNT, DEBITAMT, ORCRDAMT, ORDBTAMT, VOIDED, DEX_ROW_TS from GL20000 union select HSTYEAR, JRNENTRY, SOURCDOC, REFRENCE, DSCRIPTN, TRXDATE, TRXSORCE, ACTINDX, LASTUSER, LSTDTEDT, USWHPSTD, ORGNTSRC, SERIES, ORTRXTYP, ORCTRNUM, ORMSTRID, ORMSTRNM, ORDOCNUM, ORPSTDDT, ORTRXSRC, CURNCYID, NOTEINDX, ICTRX, ORCOMID, ORIGINJE, CRDTAMNT, DEBITAMT, ORCRDAMT, ORDBTAMT, VOIDED, DEX_ROW_TS from GL30000) g left outer join SY03900 n on g.NOTEINDX = n.NOTEINDX left outer join GL00105 a on g.ACTINDX = a.ACTINDX left outer join GL00100 d on g.ACTINDX = d.ACTINDX go grant select on view_GL_Trx_for_Searches to DYNGRP
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.