Problem Statement
Microsoft Dynamics Business Central comes with several Data Exchange Definitions out of the box to use. For our Canadian clients, the usual format used is ‘CA EFT Default’. However even the five Canadian major banks have subtle difference on the file format that require some level of editing.
Primary Antagonist: the Date Field
Date/time fields form the primary calls from our client base with troubleshooting ACH file formats. This is because some banks prefer the country format or, Julian format, while other prefer regional date format. This is were we need to go into the Line and column definition of the Data Exchange Definition (ACH file) and update the formatting for that bank.
When the Accepted Date Format does not Match Business Central
Currently the accepted Julian Date format doesn’t match that information that exports from Business Central.
For Example: June 2, 2022 show as:
• Standard Julian: 2022153
• Business Central Julian: 0022153
Now Microsoft will eventually fix this issue in a future version, but this is the perfect example of a no-code fix for a file that most people
never want to touch.
Issue Identification
• When opening the CA EFT DEFAULT definition, you will need to:
• highlight the Header line definition,
• then go to the file creation date.
• You will notice that the length is seven characters. If we leave this, printing the ACH file will have two leading 00’s in the file when we want the leading characters to be 20.
• So instead of printing 0022153 which the bank will reject. We want the file to print 2022153.
The Fix
We have to change the date in two Line definitions. Fixing the Header–> File Creation date is detailed in the steps below. Once completed, you can repeat the steps for the Detail–> Payment date field.
1. In Microsoft Dynamics Business Central, navigate to the Data Exchange Definition form and select the Definition you use for Bank payments (Screenshot example is CA EFT DEFAULT)
2. Line definition:
1. Update the Header Line Definition by increasing the Column Count by 1.
3. Column Definitions:
1. Scroll to the last Column No. and start increasing the Column No. field by 1 and move up a field until you change the Column No. for the “File Creation Date’ field.
1. Business Central will ask if you want to save this change after each line change. Click on “Yes”
2. On the File Creation Date field update the length of the field to 5.
3. Insert a new row above the File Creation Date. We label it ‘Century Constant’. Make sure the length of the field is 2 and the constant is 20 (I know, this only resolves the issue until the year 2100).
4. Repeat steps 2 and 3 for any other Julian date fields.
Conclusion
Data Exchange Definition (ACH, or EFT) files can be tricky, but they can be edited to meet the file transmission requirements of your chosen bank.
Please feel free to contact us about any other Microsoft Dynamics tips and tricks.
Thank you,
Sonny Pawchuk
Solution Architect, Geoson Solutions.
sonny.pawchuk@geosonsolutions.com
