Unanswered

Electronic reporting - Is it possible to add characters during import?

(1) Share

Report

Posted on by Ian365

Hi experts,

I'm currently learning about the Electronic reporting in D365FO and would like to ask for your guidance on model mapping formula design.

Recently I was assigned the task to import the bank statement in MT940 format to D365FO system via Electronic reporting. (Set up advanced bank reconciliation import by using Electronic reporting - Finance | Dynamics 365 | Microsoft Learn)

The bank statement file I need to import looks like this.

While the sample file from Microsoft looks like this. The difference is that the sample file has the curly brackets { and } at the start and end of the file.

Here are the ER configurations to be used:

When I first tried to import the file without any modification, I got this error: "Evaluating expression for path 'CashBalanceAmount'. Can't cast ... - to real" This error refers to the hyphen at the last line

To resolve this error, initially I think of 2 options:
1. Manually add an ending bracket } after the hyphen - on the last line of the import file to make it looks similar to Microsoft sample file.

2. Modify the model mapping to remove the hyphen during the import.

Currently it seems the model mapping only finds and removes -}, not - at Endling balance (Path: format/StatementFile/Statement/EndingBalanceLine/EndingBalance)

So I derive the model mapping, then edit the formula
From
TRIM(FIRST(SPLIT(FIRST(SPLIT(FIRST(REVERSE(SPLIT(REPLACE(@.VALUE, "(\r\n|\r|\n):62.:", "?:62:", true), "?:62:"))).VALUE, ":")).VALUE, "-}")).VALUE)
To
TRIM(FIRST(SPLIT(FIRST(SPLIT(FIRST(REVERSE(SPLIT(REPLACE(@.VALUE, "(\r\n|\r|\n):62.:", "?:62:", true), "?:62:"))).VALUE, ":")).VALUE, "-")).VALUE)

Both of these 2 methods are working, and the 2nd method is preferred as it doesn't require user manual edit before importing.
So, the task is basically achievable for now. However, I'm thinking of a scenario where I not only need to remove characters from the file, but also need to add more characters, for example, an opening curly bracket { at the start of the file.
Is it possible to achieve that within ER? If yes, could you please give me some basic examples?

Thank you so much in advance!

Categories:

Financial Management

All responses (3)

Answers (0)

Adis 447 on at

Like (0)

Report

Electronic reporting - Is it possible to add characters during import?

Glad it helped!

Would you mind marking the question as answered, so others see its answered?

Thanks in advance, Adis
Ian365 5 on at

Like (1)

Report

Electronic reporting - Is it possible to add characters during import?

Hi Adis,

Your detailed response is very appreciated!
Adis 447 on at

Like (1)

Report

Electronic reporting - Is it possible to add characters during import?

Hey,

Yes you can achieve this with restrictions. F.e., the first best solution would that you can add one or more chars to the first line and then it populates the rest whatever comes along, however, I dont think thats possible.

What I would think of is a solution using Transformations

Formula designer in Electronic reporting (ER) - Finance & Operations | Dynamics 365 | Microsoft Learn

First you create a general transformation logic.

The simpliest formula would be something like this if it is a string.

Then you add the transformation to the field, by selection the field and under Format you ll find your transformation.

There are some things to consider:

-This works if the field you assign the transformation is always the enabled, i.e. the first field being populated. In case the the field is disabled if there is no data coming in, then you would need to assign the transformation to the other field

-if the field isnt a string, you need to transform first to a string in order to be able to use Concatenate

I hope it helps.

Kind regards, Adis