Cashbook Management Reconciliation Error: “There is a problem with this Chequebook’s balances”

When supporting Cashbook Management (CBM) for Dynamics GP you may encounter the following error when opening the Bank Statement Reconcile window for new reconcile activity:

- There is a problem with this Chequebook's balances.

This error usually occurs in CBM when the prior reconcile event for the Chequebook in question was interrupted or did not complete successfully.

What often makes this error a difficult item to troubleshoot is the CBM reconcile tables may be in varied states of update and/or possibly missing data due to the incomplete prior activity.

The intent of this article is to (1) give the user a feasible workaround to get past the error, (2) insure the CBM Balance matches the Balance on the physical Bank Statement and (3) insure the same error will not be encountered on the next reconcile attempt.The main SQL tables involved in the CBM Reconcile Process are the folllowing:

If the current and calculated field values in CB900025 do not tie out correctly the error above will occur and the user cannot proceed with the reconcile.

To troubleshoot the cause of this error, the following SQL statement can be ran against the erring company db:
-- NOTE: replace 'XXX' with the chequebook in question and '###' with the period being reconciled.

If the ‘diff’ value returned by the SQL script is not ‘0’ it would outline why the ‘Problem’ error is occurring when attempting the new reconcile.

The most common correction route would be to use SQL update the CB_Opening_Balance field for the amount of the difference of the calc1/calc2 values so the ‘diff’ amount equals ‘0’ the next time the reconcile window is populated for the chequebook.

This is the only SQL option for CB900025 as the rest of the referenced fields are calculated values that will reset once the reconciliation attempt begins.

Running the above scripting should allow the user to open the window without error and start completing the new reconcile to determine what the needed correction route would be.

For example, due to the failed prior process there may additional items to include in the Reconcile or expected items completely missing.

In this state the user can fill the new reconcile as much as possible with the existing data before saving the reconcile and selecting 'Final Reconcile', which will then populate the Bank Statement Balance for the user.

The amount difference between the Cashbook Balance and the Statement Balance will outline the adjustment needed to match the CBM Balance with the Balance from the Bank Statement in order to complete the reconcile effectively.

For instance, say you open the window and encounter the 'Problem' error so you run the SQL statement above to resolve the error.

When you complete the reconciliation (including all associated new & old entries) you find the calculated Statement Balance of $100 does not match with the Statement Balance provided by the bank of $150.

When the $150 figure is entered manually by the user and they attempt to complete the reconcile Dynamics GP will generate the following error:

- Bank statement balance is not equal to Calculated statement balance. This recon cannot be posted.

To correct this issue and insure the CBM amount matches with the bank you can perform the following steps:
(1) close the reconcile window without performing a 'Final Reconcile',
(2) perform a dummy 'GL' Depost transaction in CBM for $50:
- insure you use the same date as the current reconcile for the transaction,
- use the 'GL' Type option so new transactions aren't created in other modules (other than GL),
- delete or void the resulting GL transaction created with the dummy entry,
(3) re-open the reconcile and include the dummy transaction in the reconcile,
(4) complete the 'Final Reconcile'.

As far as missing data there is not a tool or utility to replace the missing item(s) effectively in the CBM tables.Our 'data-fixing' support options would also be limited as the extent of the interruption may make such data replacement an infeasible activity.

As such it is always the best recommendation to:

(1) Insure a new backup is made of the company db prior to 'Final Reconcile' being selected, and

(2) Restore from the backup if any posting/processing interruption occurs during the 'Final Reconcile' / posting activity.The following is additional information to note on the SQL tables involved in the CBM reconcile process:
1.CB900025 (Cashbook Periods):

- CB_Opening_Balance=CB_Closing_Balance amount of the prior period

- Current CB_Period: CB_Closing_Balance=0

- Current CB_Period: CB_Payments=0

- or - sum(CB_Recon_Paid_AMT) in CB200001 with DATE1 value less than current reconcile date

- Current CB_Period: CB_Deposits=0

- or - sum(CB_Recon_Received_AMT) in CB200001 with DATE1 value less than current reconcile date

- Current CB_Period: CB_Outstanding_Deposits=sum(CB_Recon_Received_AMT) of CB200001 for DATE1 value less than 'Include Transactions to' date of current reconcile (calculated when Transaction Reconcile window opened)

- Current CB_Period: CB_Outstanding_Payments=sum(CB_Recon_Paid_AMT) of CB200001 for DATE1 value less than 'Include Transactions to' date of current reconcile (calculated when Transaction Reconcile window opened).

2. CB110001 (Recon Details Master):

- CB_Date_Last_reconciled=CB_Closing_Date of last successful CB_Period in CB900025.

3. DYNAMICS..CBINT605 (DYNAMICS Reconciled amounts):

- DATE1=CB_Closing_Date of corresponding CB900025 record

- CB_GL_Balance=CB_Closing_Balance of corresponding CB900025 record

- Note: Current CB_Period will not have a record in this table.

4. CB600005 (Company Reconciled amounts):

- DATE1=CB_Closing_Date of corresponding CB900025 record

- CB_GL_Balance=CB_Closing_Balance of corresponding CB900025 record

- Note: Current CB_Period will not have a record in this table.

5. The CB600001 (Reconcile Setup):

- CB_Last_Statement_Balance=CB_Closing_Balance of last successful CB_Period in CB900025

- DATE1=CB_Closing_Date of last successful CB_Period in CB900025.

6. CM00100 (Checkbook Master):

- Last_Reconciled_Date=CB_Closing_Date of last successful CB_Period in CB900025

- Last_Reconciled_Balance=CB_Closing_Balance of last successful CB_Period in CB900025.

7. CB200001 (Recon Transaction):

- Contains CBM records that will be available for selection of current CB_Period reconciliation.

I hope this is useful information regarding this fairly common CBM Reconciliation error in Dynamics GP.
As noted, the above information should allow the user to process through the subject error to get a better idea of how to proceed with the Reconciliation with the current CBM values intact.

When proceeding in this direction it is always a good idea to do so in a TEST environment before updating the LIVE in the same manner.
If the outlined process proves ineffective to avoid the error fully complete the new CBM Reconcile we would encourage you to open a new incident with our support team to take a closer look.