EFT (Electronic Funds Transfer), or what we frequently refer to as ACH in the US, should be something easy to set up, right? I can tell you from working with it over the last couple of years the answer is “Yes and no.”
My mission in this article is to ensure that you can answer “Yes” to this question when someone asks you. Follow along as I share with you 11 lessons I learned the hard way while working with EFT in Microsoft Dynamics 365 Business Central. If you haven’t read my article Tear up those paper checks! Dynamics 365 Business Central makes ACH easy (also here on MSDW) check it out for a few additional tips. If you are using Dynamics NAV, please read on as there are lessons for you as well, depending on your version.
Let’s start at the very beginning, a very good place to start (now I have that “Sound of Music” song repeating in my head.) But seriously, let’s walk through the process so I can share with you what I learned related to setup, payment journals, generating EFT files, and data exchange definitions. I’ve grouped the lessons by general topic area.
For reference, I am using Version: US Business Central 18.3 (Platform 18.0.27224.28780 + Application 18.3.27240.27381) as I write this article.
Working with the Payment Journal
1. Do not leave the Preferred Bank Account on the Vendor Card blank
If you are not current on your BC updates and you manually fill in the Recipient Bank Account on the Payment Journal line, you may get a non-specific error along the lines of “the bank account does not exist.” What bank account? The source of this error may be a blank Preferred Bank Account on the vendor card. Once you assign the preferred bank account on the vendor card, not only is the error resolved, but this account will default to Recipient Bank Account on the payment journal line. I tried to recreate this error for this article and cannot in my current version. I do not know in what version this was fixed, but on Dynamics on Azure it was 18.0 or later.
2. Confirm the assigned Country Export Format is correct
“Record(81) is not compatible with Codeunit.Run….” If you get this error, go to the bank account assigned on the payment journal line and check the Country Export Format. It is most likely set to the wrong country for the Payment Export Format selected for this bank account. In my test case, it was set to “Other” and should have been set to US.
Note that in a recent update, the blank option for this field was eliminated. If you were working in BC prior to the update and the value was unassigned (blank,) the update did not assign a value and it will still be blank. Once you select a value in this field, you cannot revert it back to blank. Regardless, a value in this field is required to use exports.
After update, valid options do not include blank:
3. Set the Balancing Account Type and Number correctly
You get the error “The Bank Account does not exist. Identification fields and values: No.=’’.” The Preferred Bank Account is assigned to the vendor on the payment journal line. You have also indicated the Bal. Account Type is “Bank Account” and the Bal. Account No. is selected on the payment journal lines.
To resolve this error, go to General Journal Batches and set the balancing account type to Bank Account and select a bank account as the Bal. Account No. to be used with this batch.
4. Do not remit a Customer refund
Your Remittance Advice shows the total deposit amount, but not detail of the payment.
This is a bigger issue than it first appears. Your remittance is most likely to a customer for a refund. Well guess what, you are lucky because you have learned early in the process that you cannot send customer refunds via an EFT file generated by BC.
If you don’t find this out by looking at the remittance, you will fill out when you try to export the transaction through Generate EFT Files and it fails.
Problems with Generate EFT Files
5. Do not remit a Customer refund #2
There is nothing more frustrating than trying to export your payment file and getting an error that makes absolutely no sense. Especially this one: “The account type or balance account type should be vendor on the following General Journal Line: Journal Template Name = PAYMENT, Line No. = 20000.”
This is the error you receive when you try to Generate EFT Files and a record exists for a customer refund. If you didn’t catch the problem at the Remittance Advice when it didn’t have the detail you expected, you will find out about it here. At this time, we cannot send customer refunds via EFT file generated by BC. This limitation may only exist when using specific country formats, “US” being one of them. I would love to hear from someone using a format where it does work! Please share via the comments box below.
6. Generate your EFT files before posting your Payment Journal
When using a US format, you cannot Generate EFT Files after the payment journal is posted. I wrote an article back in May of 2020 How to setup Electronic Funds Transfer to pay vendors with D365 Business Central. In that article, I showed that you could post multiple payment journals throughout the day and send one combined EFT file with payments from all the posted journals at the end of the day. Well, somewhere along the line this changed.
When I tried to show someone how to send at the end of day in the current version of BC, we got an error. Thankfully we were using a test environment; otherwise, we would have had to build that file manually (not fun,) then mark and delete the offending lines in the Generate EFT Files list.
In short, you cannot Generate EFT Files from records that have been posted from the payment journal. Your order of process must now be 1) Export, 2) Generate EFT Files, 3) Post Payment Journal. I don’t know if this is country format specific, but we encountered it using a US country format export.
7. Save Your EFT File
If you lose your EFT file, you cannot regenerate it. Answer: Make sure you save it so you have it if you need it again.
8. Be very careful when deleting Payments
Delete in Generate EFT Files may delete everything in the list of lines, even what you don’t want it to delete. Ouch! So, what did you do wrong?
First, let’s look at the message we get when we select to delete lines:
Note this is indicating it will be deleting marked payments. So, what are marked payments? It is any line checked to Include. If you select to Unmark All from the navigation bar, you will see that every line is unchecked for Include. If you Mark All, the opposite happens. You can mark and unmark lines manually by checking and unchecking the box. Include is what determines which lines will be deleted.
If you Select More to select lines for deletion, as we do in so many other places in BC, it does nothing in relation to Delete here.
Delete on Generate EFT Files uses the Include check box to determine what to delete.
Data Exchange Definitions
9. Julian date format is not supported out of the box
Moving on from the US to Canada, one of the larger banks in Canada requires dates to be Julian format in the uploaded EFT file. Even using a data exchange definition with the Country Export Format of CA, this required a developer to make a change by creating a custom Transformation Type that could be applied in the field mapping as a Transformation Rule. Julian date is not otherwise an available date option.
Bank Statement File Setup
The Bank Statement File Setup available through Assisted Setup is great, with a couple of caveats. This is where I will share my last 2 tips.
10. Be very careful to use the correct date format
If you are getting errors importing your bank statements and the error is related to date, you may have the date format defined incorrectly. M/dd/yyyy is a commonly assigned format, but you may find you need to use MM/dd/yyyy. This will depend on how you get your file and if you modify it using Excel. If one does not work, try the other. This has been the answer to most of my date format issues. If you created your definition using Assisted Setup, you will need to use Assisted Setup again to define the date format differently. If you try to change the format directly in the Data Exchange Definition, you will get an error. You can modify the definition by adding a new column and field mapping, for example to bring in the Document No., but you cannot modify a column or detail field mapping created by the Assisted Setup.
11. Do not set up you Data Exchange Definition in your sandbox and import to production
You may not be able to import a Data Exchange Definition created through the Bank Statement File Setup. You may get a warning that behaves like an error. It stops the definition from importing. It appears that the Assisted Setup ignores system validation that a user cannot. If using Assisted Setup, create the definition in the company in which it will be used. Do not rely on exporting the definition for this bank import from the sandbox to production. You should still test in the sandbox, just know that you will need to create the definition using Assisted Setup again in production when you are ready to go live. Here is the error we get trying to import a tested working definition. Look familiar?
There you have it! 11 lessons learned the hard way for EFT in Business Central / NAV. You may have some of your own, and anyone reading this article would appreciate it if you added your thoughts in the comments below. I am on a mission to save you time and frustration by showing you how to work in Business Central.
Originally Appeared Here