Within Dynamics 365 for Finance and Operations you can use the Excel integration or Data management to load general journal. When loading more than 500 lines I usually turn to Data management. Data management has a significant performance advantage.
Let's get started. First make sure you have a journal setup which uses voucher number allocation at posting. This will take away a lot of pain when loading data templates.
Make sure the 'Financial dimension configuration for integration applications' setup is complete before you start working with any data entity. More information about this setup can be found online.
Enable set-based processing for the 'general ledger' entity to improve the import performance. If set-based processing is enabled the voucher number will not be auto generated, this isn't a problem because the journal is setup to allocate a voucher at posting. To change the entity to set-based processing, select 'entities' from the Data management workspace and select the 'general journal' entity.
Afterwards you can create an import data project from the Data management workspace. Just press Import to create the data project.
Specify the name and description of the data project and add a entity. Use the 'general journal' entity to import general ledger journals. I like to select entities by uploading a data file. If you're not sure what fields to use, first run an export job for the entity. Use the export as a basis for your import template.
Upload your data template file and click close.
Dynamics will map source fields to staging fields if the column names are the same. Click Map to update the missing template mapping and add any logic.
Use the Mapping details view to add any logic.
- If the Journal batch number field is set to Auto-generated, a new journal batch number is created for every line that is imported. This behavior isn't recommended. The Auto-generated setting is found on the import project, under View map, on the Mapping details tab.
- If the Journal batch number field isn't set to Auto-generated, the journal batch number is created as follows:
If the journal batch number that is defined in the imported file matches an existing, unposted daily journal, all lines that have a matching journal batch number are imported into the existing journal. Lines are never imported into a posted journal batch number. Instead, a new number is created.
If the journal batch number that is defined in the imported file doesn't match an existing, unposted daily journal, all lines that have the same journal batch number are grouped under a new journal. For example, all lines that have a journal batch number of 1 are imported into a new journal, and all lines that have a journal batch number of 2 are imported into a second new journal. The journal batch number is created by using the number sequence that is defined in the General ledger parameters.
I have only added the bare minimum of fields, the list can easily be extended to support multiple scenario's.
Run created import jobs
Upload the data file and press import.
After the page refresh. Success!
When you use Excel files as data import template, the data sheet shouldn't contain any values outside of the upload range. If there are spaces or any other values entered outside of the upload range the upload might fail. When this happens Dynamics doesn't load any data into the staging table and returns a error message. Using CSV files can be more reliable.
You can restrict access to data project via 'set up roles for data projects'.
That's it for now...
Comments