Dynamics 365 Business Central: Add more custom processing when importing Bank Statement (Bank Export/Import Setup) – Customization

Hi, Readers.
Today I would like to talk about how to add more custom processing when importing Bank Statement.

Bank reconciliation helps ensure that what’s in your books matches the statements you receive from your bank. Bank account reconciliation compares and matches entries in the bank accounts you’ve set up in Business Central with bank transactions at your bank. Reconciliation can then post the balances to your bank accounts in Business Central to make them available to finance managers

You can import bank statement files in comma or semicolon delimited format (.CSV). Use the Set up a bank statement file format assisted setup to define bank statement import formats and attach the format to a bank account. You can then use these formats when you import bank statements in the Bank Account Reconciliation page. More details: New Features for Dynamics 365 Business Central 2021 release wave 1 (BC18): Simplified bank statement file import (Bank Statement File Format Wizard)

This is a great feature, but I recently received a question from a partner who wanted to import a file in the following format. There is nothing wrong with this format, but the problem is that Amount needs to determine whether it is a positive or negative number based on Debit and Credit. Is this possible?

Okay, first, let us try to create a new Bank Statement File for this format.
Choose Next in Bank Statement File Setup.

Select Define the bank statement file layout yourself, then choose Next.

Specify the number of header lines in your bank statement file, for example, 1

Specify the column separator, the number of columns, and line separator for your bank statement file.

Review and define the column definitions of your bank statement file.

Review and define the local formats that are used in your bank statement file.

Test that your new format is correct.

Looks good.

Select Bank Account and then choose Finish.

Done.

PS: The formats that have been created can be found in Data Exchange Definitions and can be edited.

Next try importing the csv file just created in Bank Acc. Reconciliation.

Test video:

Let’s get back to the question, so when importing, can we change the amount positive or negative based on the value of another column? Yes, you can find some additional settings in Data Exchange Definition.

Negative-Sign Identifier
Specifies the value that is used in the data file to identify negative amounts, in data files that cannot contain negative signs. This identifier is then used to reverse the identified amounts to negative signs during import.

This method was introduced in detail by CA Dhiren Nagar
More details: Configure Bank Statement Import through Data Exchange Definition Part 3

For example,
Add a new line in Column Definitions group: set Negative-Sign Identifier field to ‘Credit’

Then choose Field Mapping in Line Definitions group.

Add the following line, the Field ID is the same as the Field ID of the Amount field.

That’s all. When we import again, we will find that the amount is imported as expected.

So the next question is, can it be done if customized? Yes. Let’s see more details. The core logic of importing Bank Statement is as follows:
codeunit 1248 “Process Bank Acc. Rec Lines”:

procedure ImportBankStatement:

We can use OnAfterImportBankStatement(TempBankAccReconLine, DataExch) event. Because it contains table 274 “Bank Acc. Reconciliation Line” and table 1220 “Data Exch.”.

Table 274 “Bank Acc. Reconciliation Line” is not necessary to mention because it is the table where the data needs to be inserted.

As for table 1220 “Data Exch.” where is it used? Let’s look at the table record after importing the data.

This table holds information about the imported files.

This is related to table 274 “Bank Acc. Reconciliation Line”. But this table is not the key table this time, it’s just to lead into the following table.

table 1221 “Data Exch. Field”:

Table data:

This table records which number of files were imported and the values in which lines and columns.

This is also related to table 274 “Bank Acc. Reconciliation Line”.

All the information is already, let me do a simple process.

Let’s try it again.

Test video:

Source code:

codeunit 50119 ProcessBankAccRecLines
{
    [EventSubscriber(ObjectType::Codeunit, Codeunit::"Process Bank Acc. Rec Lines", OnAfterImportBankStatement, '', false, false)]
    local procedure "Process Bank Acc. Rec Lines_OnAfterImportBankStatement"(BankAccReconciliationLine: Record "Bank Acc. Reconciliation Line");
    var
        DataExchField: Record "Data Exch. Field";
    begin
        if BankAccReconciliationLine.FindSet() then
            repeat
                if DataExchField.Get(BankAccReconciliationLine."Data Exch. Entry No.", BankAccReconciliationLine."Data Exch. Line No.", 5, '') then
                    if DataExchField.Value = 'Credit' then begin
                        BankAccReconciliationLine.Validate("Statement Amount", -BankAccReconciliationLine."Statement Amount");
                        BankAccReconciliationLine.Modify(true);
                    end;
            until BankAccReconciliationLine.Next() = 0;
    end;
}

Great, this is just a simple example, you can also add some conditions, or some more complex processing. Give it a try!!!😁

PS:
1. You could also consider trying Transformation Rules, but that only works for the current field. More details: Transformation Rules

2. How to import data from CSV (comma-separated values) file

3. How to export data from tables to CSV (comma-separated values) file

END

Hope this will help.

Thanks for reading.

ZHU