Sometimes when you run a customer trial balance, or check the customer values in the enquiry windows in Dynamics GP, the values displayed are showing one thing, but when you look at the actual transactions, the numbers don't add up.

We had a client that recently had this problem, and luckily, there is usually an easy fix.

For the client, they had a scenario showing that the customer had a negative balance in their 60-90 days overdue, which matched the value of a recent payment that the customer had made. The obvious answer was that the cash receipt had not been applied to the invoice.

When they went to apply the cash receipt, the invoice was not there. Checking the invoice, it was already in history. Clearly, the values showing on the reports and enquiry windows are wrong.

What has happened?

Dynamics GP operates on a what I call a 2-tier system:

One the first tier are all the actual transactions, which are located in various tables in the database.

On the second tier is a summary level table which holds a summary of the transactions that have been entered.

A little history: This two tier system may seem a little silly, but it actually goes back to the 90's when Dynamics GP was using a file system as a database. Opening and reading data from flat files, to get summary information was pretty inefficient. So, they opted for a summary file which meant getting summary information was faster.

What should happen everytime you enter a transaction, is that the summary data in Dynamics GP is updated. However, every now and then, it is not updated as it should, and then is left out of sync with the actual transactions.

The fix: run the reconcile utility against the customer.

Go to the menu: Sales >> Utilities >> Reconcile

This will open the Dynamics GP 'Reconcile Receivables Amounts' window:

I have highlighted 5 parts to look at here:

1. Reconcile: the top two options in your reconcile box need to be run individually. When you open the window, it will default to the 'Current Debtor Information' option. If selected, this option will update the summary information of the customer. So, for the purposes of this article, running this utility will generally do the trick and bring the customer balances and summary values back in line with the actual transactions.
2. The 'Outstanding Document Amounts' it a utility that will look at each transaction and will check the apply information and make sure that it is up to date on that transaction - if the current balance on a transaction is incorrect, then this utility will update it. Note: Each of these options need to be run individually.
3. Range: select the range of customers you want to run this against. If you only have an issue with a single customer, then select the individual customer. If you run this against all debtors, then it will check every customer and make sure that the summary info is up to date. It is not a bad thing to run this against all customers every now and then, and it generally won't do any harm as it is only updating summary information.
4. Reconcile date: this will usually default to your system date, but you can change it to a different date in the past if you want to.
5. Options: you can tick the boxes at the top right of the window, to run the reconcile, and/or to print the report. If you are interested to see if anything will be changed before you actually get the system to make the update, you can untick the 'Reconcile' tickbox and this will then just give you the report to show you what will happen if you had the 'Reconcile' tickbox ticked.

When you click on 'Process' in that window's menu, it will run the utility against the options that you have selected.

The report that is produced will show you a 'Before' and 'After' picture of the results of the reconcile and this will usually be the fix that you need to bring the summary information back in line with reality. In the report below, I have highlighted the before and after columns for clarity, and in this example, the customer's On Order balance has been updated.

However, if this doesn't fix your issues, then there could be a problem with your data at database level, and will require a consultant to take a look at the transaction data and customer information to see why this is out of sync.