Person typing on a keyboard

When reconciling a checkbook in Dynamics GP, you may get an error stating the “difference must be zero,” although the reconciliation window shows no difference. Microsoft KB 860290 provides guidance for resolving this error, but experience has shown it may be necessary to take a few additional steps.

First, to review the steps in the KB, we need to run an update on CM20200, the CM transaction table. This is the query from the article:

This uses the SQL FLOOR and CEILING functions to basically round the TRXAMNT column to two decimal places. FLOOR rounds down, while CEILING rounds up. The math in the query shifts the decimal point two places to the right by multiplying by 100, eliminates any values to the right of the decimal point with FLOOR or CEILING, and then moves the decimal point to the left two places again by multiplying by .01.

The only issue with the query as written is that the conditions in the WHERE clause are identical for both functions. It will round down with FLOOR and then immediately round up with CEILING on records where the transaction amount is nonzero and positive, but won’t do anything to negative values. I believe one of the two WHERE clauses (it could be different depending on your data) should read as follows:

WHERE trxamnt <> 0 AND trxamnt < 0

If you run FLOOR against positive values and CEILING against negative values, both will round closer to zero. A negative number has to be rounded up with CEILING to move closer to zero. Whatever the case, this is how you control the rounding if you want negative and positive numbers to be consistently affected. As with all things SQL, you could take a more sophisticated approach, such as using CASE statements, but that’s entirely a matter of personal skill level and style.

Check Links needs to be run on the CM transaction logical table to address any referential issues caused by data that has been changed.

The second part of the query updates the CURRBLNC column in CM00100, the Checkbook Master table. This is required if a value with more than two nonzero decimal values is being stored in the column. The article specifies a very basic UPDATE query, but it also could be accomplished using the FLOOR or CEILING functions just as was done with CM20200. It might be written like this:

UPDATE cm00100
SET    currblnc = ( Floor(( currblnc * 100 )) ) * .01

In recently troubleshooting this issue for a client, we encountered some additional issues that the KB doesn’t address. In this client’s case, the original payables transactions were being integrated from another application, and we didn’t have visibility to how this was happening at the time, although it appeared it wasn’t respecting GP’s business rules and data requirements and was passing values that were ultimately causing an issue in bank reconciliation. To assist the client with identifying the root cause of the issue, we wanted to isolate transactions with more than two nonzero decimal values without manually scanning thousands of transactions. We wrote the following query using the CAST function:

SELECT trxamnt,
FROM   cm20200
WHERE  ( ( trxamnt * 100 ) <> Cast(( trxamnt * 100 ) AS BIGINT) )

This returned only the records that were a problem, which was useful in further diagnosing the issue.

There was a second concern:  Because the customer was in the middle of reconciling a checkbook, the ClrdAmt (Cleared Amount) column in CM20200 also had incorrect values. We used the same procedure to correct these values as we did for TRXAMNT, but the reconcile still returned the “difference must be zero” error. We isolated the transactions using the CAST method above and tried simply unchecking and rechecking the transactions in the reconcile window, but that didn’t solve the problem.

Additional examination of the CM20200 table revealed the incorrect values also were being stored in the ORIGAMT (Originating Amount) and Checkbook_Amount columns. Once again, we used the FLOOR and CEILING update procedure to fix these values and ran Check Links again. At this point, the reconciliation showed a difference of $0.03, but after an adjusting entry, the reconciliation could complete successfully.

We wanted to provide the customer with an automated way to resolve this as needed until the integrating application’s data issue was addressed, so we created an on-demand SQL job that executed the following query against the affected company database:

UPDATE cm20200
SET    trxamnt = ( Floor(( trxamnt * 100 )) ) * .01,
origamt = ( Floor(( origamt * 100 )) ) * .01,
checkbook_amount = ( Floor(( checkbook_amount * 100 )) ) * .01,
clrdamt = ( Floor(( clrdamt * 100 )) ) * .01 — Needed if a reconcile is in progress
WHERE  trxamnt <> 0
AND trxamnt > 0

UPDATE cm20200
SET    trxamnt = ( Ceiling(( trxamnt * 100 )) ) * .01,
origamt = ( Ceiling(( origamt * 100 )) ) * .01,
checkbook_amount = ( Ceiling(( checkbook_amount * 100 )) ) * .01,
clrdamt = ( Ceiling(( clrdamt * 100 )) ) * .01 — Needed if a reconcile is in progress
WHERE  trxamnt <> 0
AND trxamnt < 0

UPDATE cm00100
SET    currblnc = ( Floor(( currblnc * 100 )) ) * .01
WHERE  currblnc > 0

UPDATE cm00100
SET    currblnc = ( Ceiling(( currblnc * 100 )) ) * .01
WHERE  currblnc < 0

You might ask why we didn’t just configure this as a scheduled job. It would make the customer’s life a little bit easier, but it also might dissuade them from pursuing the root cause of the issue. By making it an on-demand task, it keeps the issue in front of them, gently reminding them that not all is right with their data integration.

Contact your BKD Technologies advisor if you have additional questions.

Related FORsights

We want to work with you

Contact Us

Get more information about our services, submit a request for proposal, or get in touch with us today!

Let's Connect