This new functionality enables organizations to automatically import bank statements issued by banks from a SharePoint folder.
Hi! Im writing this blog in English because I´ve realized that there isn´t any Doc from Microsoft related to this feature.
I´ve also recorded a video but in Spanish (sorry about that!) I´ll try to do it in English next time 😉 but meanwhile, if you need it, you can use the subtitles from Youtube or check the main setup needed from here 😊 The link to the video is at the end of this blog.
So… I am going to start by the beginning just describing the setup needed to import a bank statement using ERs (first, manually) using as example the MT940 format.
If you already know how to do it.. just scroll down.
In the second part I’m going to be describing the setups to import the bank statements automatically.
🌱 ELECTRONIC REPORTING SETUP
- Electronic Reporting
From the global repository of Microsoft filter by the name mt940. It´s the easiest way to select all the components needed for the bank statement with the format MT940, and then click on the option ‘Import’:
And the next step is enable the ‘Default for model mapping’ check in the format:
2. Bank statement format
Cash and bank management >> Setup >> Advanced bank reconciliation setup >> Bank statement format
In this form we´re going to need adding an Id, a name, the file type is “txt”, and enable the check ‘Generic electronic Import configuration’ to be able to select the ER format downloaded previously in the field ‘Import format configuration’:
3. Bank account
Cash and bank management >> Bank accounts >> Bank accounts
In the bank account enable the check ‘Advanced bank reconciliation’ and add the bank statement format created in the step 2.
*The rest of the configurations are not mandatory to import the bank statements.
🌱 AUTOMATICALLY IMPORTING BANK STATEMENT FROM SHAREPOINT FOLDER
And now… the setup for this automation!
- Sharepoint folders
We´re going to need 4 different folders, one for the input and the other 3 related to the result of the importation process.
The ‘Input’ folder is going to be the place where the bank will send us the statements.
Although its not mandatory to have 4 folders (because D365F doesn´t validate it), I personally recommend to have separate Sharepoint folders because it´ll be easy to look for the bank statements. And also, we can create rules and alerts to receive emails, for example, if there´s a new document in the folder for the MT940 errors.
2. Enable the feature
From the feature management enable the feature: Automatic importing bank statement from SharePoint folder
3. Document types
Organization administration module >> Document management >> Document types
In this form we´re going to link the document type for the files with its related folder in Sharepoint.
4. Electronic reporting source
Organization administrations module >> Electronic reporting >> Electronic reporting source
In this form, we´re going to connect the Electronic reporting format setup (downloaded from the Global repository) with every document type (and its url in Sharepoint).
First, you need to select the ER format in the field ‘Format’.
Then, and its super important, add the file name mask. In our case is *.txt. This is how the system filters the documents for the importation process.
In the settings, enable the check to be able to select every document type.
At first, we need to select the document type with the url where the bank statements are going to be sent by the banks (MT940 INPUT).
Then, according to the result of the automated process, the system deletes the file from the input format and moves it to one of the other three: processed, if the result is correct, warning if there´s a warning to check and solve, and failed if the process is incorrect.
5. Batch job
In the last step we´re going to setup the recurrence for the importation process.
We can easily access from the bank statement form:
Cash and bank management >> Bank statement reconciliation >> Bank statements
Click on ‘Import statement’ and define the recurrence:
For example:
🧙🏻♀️ TRICK
And last but not least, you can check if the sharepoint is setup correctly, and the files are linked to the ERs, from the File states for the sources (before waiting for the batch job to end) and the Er workspace.
First, in this form, we can check all the files in the input url, and the status of the import process, with its history:
If the status file is ‘Ready’, you can import it from the ERs workspace and check the result (with the mapping of the fields):
In the MT940 format, click on ‘MT940 format mapping’:
And run to execute the importing:
As result, a xml will be downloaded with the information related to every field of the ER:
👉 And here the link to my video in Youtube:
I hope this blog helps! See you next time!