How to Reconcile RM to GL in Dynamics GP using smartlists.

If you are not on version 10 or 2010 of Dynamics GP then you won’t have the GL reconcile tool. This article is intended to give advice on how to reconcile without this using the functionality in Excel.




If you were in balance last month, then you can create a report for the receivables transactions based on the system date range and then create a report for GL (account transactions) for the system date range. This way, you can compare when the information was physically entered in the system (even with a different document date between modules). If one of the modules has the transaction posted and the other is missing, you can determine if the transactions were posted with a different date in GL than in receivables.



Within Smsrtlists, open up the receivables transaction option. Add the column Posted Date. This column stores the date from your computer of when the document was physically entered in receivables. Even if the document date was for the previous year, the posted date stores the date of when you physically entered it. You can compare the posted date to the posting date (holds the document date) or the document date as well. You can narrow your search by entering this months posted date since you were in balance last month. This will tell you all of the transactions that were entered in receivables since then and you can compare your GL report to that and see if one module is missing the transaction, has a different document date, or a wrong amount as well(possibly the batch was edited in GL before it was posted).



Next, you will want to open the account transaction option within Smartlists. Add the columns Originating Posted Date and Originating Document Number. This stores the posted date (system date) from the originating module. Even if you change the batch date, this stores the date of when it was entered. You can compare the originating posted date to the trx date (document date) in account transactions as well to see if they vary from what your receivables transaction report returns. You may also want to add the Originating Document Number and Originating Master ID (debtor id) to this report, that way you can tick transactions off by using the document number instead of looking for amounts.



These reports will give you a good guideline on trying to narrow down when something was entered, but there could be other possibilities that could be a cause such as a posting interruption (but this may have updated one module and not the other so you can compare what's missing), receivables history was removed (which only effects receivables and not GL).



At this point you can actually use excel functionality to make life easier. If both sheets (RM and GL) are placed in the same excel work book , you can then create an extra column returning a result based on a vlookup between the two sheets , based on originating document number on the GL and document number on the RM , this will do a lot of the hard work for you. You then only need to investigate those transactions not matched up by the Vlookup.