Tuesday, September 4, 2018
How to Manually Add Financial Dimension Mapping to the Excel Journal Entry Import Template
Posted by Darrell Graham
The Excel journal entry import template that comes standard with Microsoft Dynamics 365 is mapped to the main account field only; mapping to financial dimensions is not included. This is due to the dynamic nature of creating the financial dimensions and accounting structure in D365. Once the financial dimensions and accounting structure are finalized, a development task can be created to permanently add the financial dimension mapping to the template. In the meantime, users can manually add the mapping to test the system by following the steps below each time they import a journal entry via Excel.
- Open a journal entry in Excel by creating a journal header, clicking the Open lines in Excel menu button (General ledger > Journal entries > General journals) and selecting General journal line entry, then clicking OK:
- In Excel, once the data connector (right pane) has connected and the metadata has been retrieved, the action buttons for the template will be enabled. Click on the Design button:
- Click the small pencil icon next to the LedgerJournalLine table to edit the mapping:
- In the Available fields window, find Account Display Value – Account and add it to the Selected fields window by double-clicking it (note that it will be placed at the very end of the Selected fields list):
- Repeat step 4 to add the OffsetAccountDisplayValue – Account to the Selected fields
- In the Selected fields window, click on the AccountDisplayValue – Account field and use the Up arrow button to move it up the list until it sits next to the MainAccount – Main account.Main account field:
- In the Selected fields window, click on the MainAccount – Main account.Main account field and click the Remove button to remove it from the template (you will be using the field you just added to enter the entire accounting string):
- Repeat steps 6 and 7 to move the OffsetAccountDisplayValue – Account field and to remove the MainAccount – Offset main account.Offset main account field.
- Click the Update button in the lower right corner of the Data connector A warning message will be displayed indicating that the template will need to be refreshed and any data already entered in the template will be lost. Click Yes to continue.
- Click Done at the bottom right of the Data connector window to close the editor.
- Click the Refresh button to activate your changes to the template
- You can now enter the entire accounting string in the Account and Offset account columns of the template per the legal entity’s accounting structure. Note that these added columns will not show the lookup values in the Data connector