Tags
Advanced bank reconciliation, Bank reconciliation, Electronic reporting, General Ledger journal, MT940
Need more information on electronic reporting? Then check out this MS docs site
BANK STATEMENT IMPORT – PART 3
21 Monday Sep 2020
Posted in Accounts Payable, Accounts Receivable, Bank Management, General Ledger
Microsoft BizApps Finance & Controlling 
I’ve been trying to follow your steps and try to run the example bankstatement file where I occurs an error about the statementSeparator not found.
Error message:
Evaluating expression for path ‘ALL’.
Evaluating expression for path ‘GLJModel/GLJ_Header’. format/StaFile/StatementSeparator/StatementDataRecordList Element of the configuration 3 – ‘StatementSeparator’ is not found
LikeLike
Hi, Do you use a different country/region setup for the separations of text, numbers, decimal places, etc? Can you please check your regional settings on your computer and inside of D365FO? Best regards, Ludwig
LikeLike
Hi, We’re using the dutch variant of MT940 and in the statement we’re using -}{5:} as a separator for the different statements.
LikeLike
Hello William, I don’t know if the Dutch MT940 format is exactly identical to what is used in Germany. If the formats are only slightly different then you must modify the ER configuration to fit your format. Best regards, Ludwig
LikeLike
Hi Ludwig, Is it then possible to debug this with visual studio to analyse what’s wrong with it? Kind Regards, William Chan
LikeLike
you can test things directly in the report designer. you don’t need visual studio
LikeLike
Hi Ludwig,
Thank you for this insightful tutorial, our client requested to import MT940 bank statement to the GL journals and this tutorial assisted.
We had to tweak a few of the formulas because they don’t use gvc codes but they use bank transaction types, i.e NMSC.
NMSC = Customer Payments (CUST)
NCOM = Commission/Bank charges (LEDGER)
NTRF = Transfers (LEDGER)
So based on this, have changed the formula on account types and account:
Account type:
IF(
CONTAINS(
@.CF_Rows_Part2b_Description.value, “NMSC”
),
“Cust”,”Ledger”
)
CF_Row_Account:
WHERE(CustTable,
IF(
CONTAINS(@.CF_Rows_Part2b_Description.Value,”NMSC”),
“”,
LEFT(TEXT(
TRIM(
FIRST(REVERSE(SPLIT(
REPLACE(
TEXT(first(SPLIT(
TEXT(first(SPLIT(@.CF_Rows_Part2_Description.Value,”:86″)).value),
“?34”)).value),
“?33″,””,false),
“?32”))).VALUE)
),6))
=LEFT(TEXT(CustTable.’name()’),6))
GLJ_Lines_L7_Account:
IF(
CONTAINS(
@.CF_Rows_Part2b_Description.value, “NMSC”),
IF(
ISEMPTY(@.CF_Rows_Part7_Account),”RMEG”,@.CF_Rows_Part7_Account.AccountNum),”361007———-“)
With the above formulas, it picks up the correct account type is you have one type of the bank transaction code in the bank statement, if you have NCOM only, the account type in the journal lines will be “Ledger” and the main account will be “361007”.
If you only have NMSC only in the bank statement, the account type will be “CUST” and customer accounts are allocated correctly with the formula that finds the customer accounts and the dummy account when there customer name is blank or not found in D365.
The problem is when you have different types of bank transaction types on the bank statement.
If NMSC is the first transaction type that is found on the bank statement, it will make all the following transactions on the bank statements to be “CUST” which is incorrect.
I am not sure what I am missing, please advise.
Regards,
Xoli
LikeLike
Hi Xoli,
I am not sure what might be wrong. Probably some bindings are not correct?! What I did to test and setup things was to start with a small part of the ‘code’, then tested it, built upon it again until I got my outcome. Guess that might be a way forward for you too.
LikeLike
Hi Ludwig,
I followed all the steps and the import work pretty nice. But only the first lines (:61:) are imported for each bank statement. May be I missed something with my CF_Header and CF_Lines formulas. Below the formulas I used:
CF_Header formula:
FIRSTORNULL(WHERE(@.StatementLinesRow66ff,REPLACE(@.StatementLinesRow66ff.StatementLinesRow66ff,”:61:”,””,false)=@.StatementLinesRow66ff.StatementLinesRow66ff))
CF_Lines formula:
FIRSTORNULL(WHERE(@.StatementLinesRow66ff,REPLACE(@.StatementLinesRow66ff.StatementLinesRow66ff,”:20:”,””,false)=@.StatementLinesRow66ff.StatementLinesRow66ff))
Could you please, provide me with the detailed formulas about CF_Header and CF_Lines.
Thanks.
Regards,
Arnaud.
LikeLike
Hi Arnaud, I don’t think that I can help much here because I don’t have the time for that. I built the whole thing based on German MT940 bank statements. If your statement file has a slightly different setup/configuration you need to adjust things as needed. In case you cannot make it yourself, talk to a developer who should be able to identify where the error lies. Best regards, Ludwig
LikeLike
Hi Ludwig,
Thank you very much for your quick feedback.
I double check and I found the root cause.
The import works fine now.
Regards,
Arnaud.
LikeLike
Hi Arnaud, did you perhaps solve your issue with only the first line importing? I am experiencing the exact same problem.
LikeLike
Hi Mia,
Sure,I solved it just by removing the “FirstOrNull” expression in the CF_Header and CF_Lines formulas.
Regards,
Arnaud.
LikeLike
Thank you very much, that solved my problem and now it successfully imports multiple lines!
LikeLiked by 1 person