How to import bank statements in a custom format

The most recent changes on bank statement imports to D365 F&O have brought to light the need for using Electronic Reporting instead of the Data Management framework. The deprecation of XSLT is the reason that the Generic Electronic Import format field is checked without having the option to edit it when setting up a bank statement format - therefore an import format configuration must be used.

Plant

There are 3 pre-defined formats (BAI2, Camt.053 and MT940) that Microsoft allows to be customised to use when setting up the bank statement format. But there are banks that have their own format that does not correspond to the standard one, so the client is required to create their own structure based on the document received from the financial institution.

In this blog post, we will learn how to create a format from scratch and map it to the existing model (yes, we can use the standard model mapping as it is, so we can upload the information we import - we just need to create the format design that should match the imported document’s structure).

In this example, we will create a brand new XML structure, map its format to the bank statement model and then test it out with an imported file provided by the financial institution.

01

First things first, we need to create a new format based on the standard model, then set “Supports data import” to Yes.

02

Moving on to the designer mode, we add the XML nodes exactly according to the format provided by the bank - the Camt.053 format is the best example to follow when it comes to creating and setting the multiplicity of the nodes.

For the custom format we have previously created, we added node by node and whenever we had multiple sections present, such as the GroupHeader’s Section Element, we have set the Multiplicity to zero-many (0..*).

03

After creating the XML structure, we need to start mapping it to the model.

In Designer we click the Map format to mode button:

In the Model to datasource mapping form, we are required to include the following line which will generate the standard mapping and the XML structure we have previously added.

The result will look like this:

04

Above, we went over the Section Element node of the GroupHeader Element. Here we notice that by setting the Multiplicity to 0..*, we end up having a Record list which will be looped through at the moment of the import. From there on, we can set the information necessary in the Data model fields, which can be found on the right side, next to the data sources.

For example, the Statement expects a record list, which will contain all the statements that wish to be imported - in our case, it is the second level group that we set as 1..*.

05

After we have created everything and ensured the mapping is correct, we can then add a new Bank statement format record and select the custom configuration we either have completed or set the Run draft to Yes.

06

At the moment of the import, we can select the statement format we have added and then just import the statement.

Conclusion:

This is how to create your own format with ER, while still using the standard model mapping provided by Microsoft, to employ a completely new bank statement import format.

share article

our blog

Spikes

Latest insights & news from Falcon

Top

© 2022 Copyright: Falcon Trading