Set up or modify the BAI configurator as follows:
1. First, very that each
Bank Account number used in the bank import file matches the Bank Account Number on one of the Checkbooks that you have tied to this Bank Download ID in the Download Maintenance window in Microsoft Dynamics GP. To do this:
a. Save the bank import file to your desktop or another location of your choice. Right click on your bank import file and open it with Notepad.
b. Look for all the Account Headerlines in the file (lines that begin with code '03') in the file.
c. The 2nd field in the Account Header line contains the Bank Account Number. Jot down all the Bank Account Numbers from all of the Account Header Lines in the file. You will need to verify that each account number exists on a checkbook in Microsoft Dynamics GP.
d. In Microsoft Dynamics GP, click on Cards, point to Financial, and click on Checkbook. Select the Checkbook ID you want to use and verify that the BANK ACCOUNT field matches the Bank Account Number you found in the bank file. (It must match exactly, so if the Bank Account number has leading zeroes in the bank file, you must also key those on the checkbook setup.) You should have one checkbook ID per Bank Account Number used in the file.
e. Now click on Microsoft Dynamics GP, point to Tools, point to Routines, point to Financial, point to Electronic Reconcile, and click on Download Maintenance. Select the Bank Download ID you are working with, and verify that each Checkbook ID is added that contains the Bank Account Numbers that are used in the bank file you are trying to import in.
- If you import in a file that has a Bank Account on an Account Header line that doesn’t exist on one of the checkbooks tied to this Bank Download ID, the Unprocessed Data Records Report will show the message: "Bank Account number from the text file could not be matched to a checkbook account number."
2. When you have the bank import file open with Notepad, also verify the following:
- Ensure that no lines are longer than 244 characters. GP will crash on character 245 within a line. This is the current design, and not a quality issue. You may choose to shorten lines longer than 244 characters, or inquire to the bank to not create lines longer than 244 characters. If any concerns, please log a product suggestion. For testing, you can shorten the description field in the line, to make the line shorter than 244 characters.
- Look for any lines that do not begin with one of the Record Type codes in the configurator file (see next step). Blank lines are okay.
- It is okay to have the same Bank Account number in more than one Account Header line (begins with '03'), as long as only ONE checkbook ID has this account number. The system will not read the line if the same bank account number is on more than one checkbook ID in Microsoft Dynamics GP.
3 Create a new configurator using the BAI format. To do this,
a. Click on Microsoft Dynamics GP, point to Tools, point to Routines, point to Financial, point to Electronic Reconcile and click on Configurator.
b. Enter a name for the Bank Format and a Description (as desired).
c. For the File Format, select BAI, and the rest of the file will automatically populate.
. Map the Detail line of the configurator to match your bank file. To do this:
a. In the configurator, click to select the DETAIL line (in the middle section, that has a Record Type Code of '16').
b. In the far right column for this line, change the # of Fields from '9' to '7' (or as needed per your bank file). (In Notepad, review the Detail rows [rows that begin with code 16] in the bank file, and verify how many fields across you will need, as it may not necessarily be 7. )
- When you tab off the line, you will get prompted "Fields will be removed from the bottom of the list. Is this OK?" Click Remove to have the respective number of fields removed.
5. Next, remap the Detail line in the configurator, to match your bank file. To do this:
a. In the configurator, click on the
Detail line again [line that begins with 16] to select it and the number of fields will display below.
b. Click on Field 5 and change the Bank Cleared Date to Filler/Ignore. if the bank file doesn't have an actual date in this field (or map it to the correct field type per your bank file.)
c. Remap the Check/Serial Number field to the correct line, as needed per your bank file.
6. Still in the configurator, do the steps below to set up the Transaction codes:
a. In the configurator, click on the CODES ENTRY button.
b. If this is a brand new install, you must select the CHECK PAID transaction type and click Save. Then select DEPOSIT CLEARED transaction type and click Save again. You must do this on a brand new install the very first time, so it will recognize the default codes for these transaction types. So save these codes once, and you will not need to do this again on this Microsoft Dynamics GP install again, even if you set up other configurators, or delete any existing configurators. This is a one-time step needed, (but never hurts anything to press SAVE in these windows either). This action will have the default codes be recognized by the system.
c. In the Codes Entry window, verify that all the Transaction Type Codes used in your bank file (the second field in each Detail line [ie. line that begins with '16']) exists in one of the Transaction Types. Be aware that not all codes are supplied by the default format in Microsoft Dynamics GP, so you may need to add some.
- The Transaction Code is the second field in the Detail line (line begins with '16'). Make sure all these codes used in the file are represented in the Codes Entry window under one of the categories. If you have a type that doesn't exist, use the Other category and you will have to manually reconcile this transaction in your Bank reconciliation.
- Transfer Credit and Transfer Debit do not have any default codes included in the format, so you will have to add those codes if needed if you have bank transfers in the file. (The code 277 is common for Transfers Credits, and 577 is common for Transfer Debits, but these codes may vary by bank.)
- If there are any codes in the file that do not exist in the Codes Entry in the configurator, the Unprocessed Data Records Report will show the message: "Transaction Code Type match not found for detail record. Code not found = XXX " (The XXX will indicate to you what code should be added.)
7. IMPORTANT: If you are using
Microsoft Dynamics GP 2013 (RTM or SP1 versions) and have the Check Number mapped as the
last field of the Detail line, and it is immediately followed by the slash delimiter in the bank import file, the slash will come in as part of the check number. This was fixed in Microsoft Dynamics GP 2013 SP2. So if you are using Microsoft Dynamics GP 2013 RTM or SP1, you may need to do these extra steps:
NOTE: You can skip this step if you are using Microsoft Dynamics GP 2013 (SP2 or later versions).
- Open the configurator file and unmark the checkbox next to Record Delimiter.
- Open the bank import file and do a Find/Replace ALL. Find the slash ‘/’ and replace it with a comma ‘,’
8. Test importing in the bank file. Note the following:
- Review the Unprocessed Data Records Report for additional errors.
- If GP hangs, review the ME142804 table in SQL Server Management Studio to determine what record it is having an issue with. This will give a hint on what line it is failing on and where to start looking.
- Also, if you are testing Electronic Reconcile for the first time and are not using it in any other checkbook yet, we suggest to to go the Download Summary window, select the Bank Download ID and click DELETEALL in between each time you try to bring in the same file. If you don't remove the prior download, it will cause issues with bringing in the same file again.