Microsoft Dynamics GP: Electronic Reconcile setup and how to remove a transaction from the Electronic Reconcile Unprocessed Data Records Report

Hello GP users!

Are you using Electronic Reconcile in Microsoft Dynamics GP to bring in your bank statement each month and have the system do the matching for you?  This will save you lots of time from having to manually clear the items in GP each month on the bank reconciliation.   It is easy to use and will say you a lot of time.  There is a one-time setup needed to configure the fields on your bank statement so the information can be imported into Dynamics GP and then you are in business from then on. If you are new to this, here are the Knowledge Base articles to get you started:

KB 850751 - Setup information for Electronic Reconcile Format Configurator (microsoft.com)

Steps to modify the BAI file format in Electronic Reconcile in Microsoft Dynamics GP

If you are currently using Electronic Reconcile, here are common questions around the Unprocessed Data Records report for Electronic Reconcile:

Q1: What is the Unprocessed Data Records report?

A1: This report lists all the records from the bank import file that were not recognized and unable to be processed or downloaded into Dynamics GP. If any such transactions are not processed you will get an alert message as shown below to let you know how many records were not able to be imported If you click OK on this message, then you will be prompted to print the Electronic Reconcile Unprocessed Records report showing the detail of the unprocessed records.

The report prints with the title 'Unprocessed Data Records' and lists the records from the bank txt file that could not be read. Below is an example of the report:

• The message "Unable to save record. Duplicate." means that this check number was already imported in to Dynamics GP in the past, so it cannot bring in the same record again. The user should research why the same check/transaction number is showing as cashed again on a different bank statement. Every transaction should only be imported in once.

• The message "Transaction Code Type match not found for detail record. Code not found = 233" means that the Transaction code in field 2 of '233' is not mapped in the CODES ENTRY button in the configurator file, so the system does not know what type of transaction this is (ie. check or deposit, etc), so it cannot import it in. The user should add this code to the CODES ENTRY button in the configurator so the record in read in the future, and can manually match the record on the current bank reconciliation in the meantime.

• The message "Unused row type' is just due to a blank line at the end of the file and can be ignored.

Q2: If I manually matched a record that printed on the unprocessed record, and it shows up again next time on the next Unprocessed report, how do I remove it from the Unprocessed report or prevent it from pulling on to this report each time?

A2: If a record is not processed, it should print on the current Unprocessed Data Records report, and get marked off in the ME142807 table when that bank reconciliation is completed, so it does not print again on the next unprocessed report. If the same record continues to print on the Unprocessed Data Records report in the future and does not drop off, then you can mark it off in the ME142807 table so it does not get pulled again. Typically it only prints once, so you shouldn't need to do this, but on occasion users want to know how to remove old documents from this report. You have to find the record in the SQL table and manually flip the flag as follows:

select * from ME142807 where CMTrxNum ='YYY' and CHEKBKID = 'UPTOWN TRUST'

--update the YYY placeholder for the check number for the CMTRXNUM value and the appropriate checkbook ID

update ME142807 set ME_Updated_CMTrx_Table = 1 where DEX_ROW_ID = X

--update the X placeholder for the DEX ROW ID for the record you wish to update

Q3: Why aren't the transactions on the unprocessed report listed in the Bank Reconciliation window?

A3: The unprocessed report displays the unprocessed records from the bank file that was imported. The Select Bank Transactions window or Bank reconciliation window displays bank transactions that were keyed in Dynamics GP (and stored in the CM20200 Bank Transactions table.) The transactions listed on the bank reconciliation window do not come from the imported bank file. The bank file is only used to match transactions that have cleared the bank. Just because a record is on the bank statement does not mean that it was keyed in Dynamics GP. If the record is missing from the Bank Reconciliation window, the user may need to either key it in GP and refresh the bank reconciliation, or key an Adjustment in the bank reconciliation to account for it so they can balance.

I hope this blog will give you some ideas on how to utilize the Electronic Reconcile module to help speed up the time it takes for a user to reconcile the checkbook balance to the bank statement and let the system do the matching for them. How transactions are matched can be found in this KB article:

KB 851279 - How Transactions are Matched in Electronic Reconcile for Microsoft Dynamics GP

I hope this information has been useful to you and provide education on what the Electronic Reconcile unprocessed report shows, and what transactions are displayed in the Bank Reconciliation window. The more the users can understand the full process and utilize the tools available to them, the easier the reconciliations will be for them.

Thank you,
Cheryl Waswick
Sr. Technical Support Engineer
Microsoft Dynamics GP Support