This blog post will explain the concept of the format mapping in Electronic Reporting. After this blog post you should have a general idea on how to create a format mapping and the designer of the format configuration in Electornic Reporting.
This post assumes that you are familiar with the basics of Electronic Reporting and that you have a good understanding of data models and model mappings. Due to the size and complexity of a format mapping this topic will be split up into multiple posts.
In this post we will focus on the designer of the format mapping and the menu items you will encounter here.
Format mapping – General
In our previous part 1 we covered how to create a format configuration, after creating a format configuration you must configure it. To start the configuration, you must open the designer, which is done via the <designer>.
Keep in mind that the selected configuration version is the one opened in the designer. You can open completed versions in the designer. However, this will open the configuration in a view mode. Only draft versions can be edited. As the lay out for the view mode and edit mode is identical, we will focus on the edit mode.
Format mapping – Designer
The format mapping designer is built up from 3 different areas. Each of these areas has its own function and menu items. The menu items per area can change depending on current selections. In this post we will cover the general menu items and selections and in future posts we will go in depth on the specific menu items and selections.
To use the format designer for exporting data from Dynamics 365 for Finance and Operations to a file, you will be using the mapping menu in the format mapping area. To use the format designer for importing data (or writing back data for export formats), you will be using the map to model menu item in the action pane bar.
This means that for importing configurations you do not have to set anything up in the mapping menu in the format area and for exporting configurations, without data writeback, you do not have to set anything up in the map data to model menu item in the action pane bar.
Format mapping – Action pane bar
In the Action Pane bar area the general menu items for the designer are listed. These menu items are covering functions that are related to the file or designer view as a whole.
Save, saves the current configuration setup. Please keep in mind that there is no auto save in Electronic Reporting. You can either save manually, or when you exit the designer you will be asked to save the changes.
Show Details, by default Electronic reporting will show the name (and label if configured) of the data source type. If the show details menu item is enabled the contents of the data source type will be shown as well.
Show menu, this is a dropdown with 3 options. This option is useful when you want to analyze an existing model mapping and see the actual used nodes or when configuring a new model mapping.
- Show all, this shows all the nodes in the data sources overview and the data model definition overview.
- Show not-mapped only, this will change the view to only show the items that have not been mapped yet.
- Show mapped only, this will change the view to only show the items that have been mapped.
Format enumerations, when designing electronic reporting configurations, you have the option to add format specific enumerations to it.
Via this menu you can add these. In this menu you first define the enumeration by adding it in the top menu and in the bottom part you can add the enumeration values.
Map format to model, this will open the model mapping window. This is used for import formats or when you want to write back information for export formats. An example of a configuration using the latter is the <ISO20022 CREDIT TRANSFER> configuration. In this configuration the mapping is used to register the payments using the BankToCustomerDebitCreditNotificationInitiationISO20022CT component of the payment model (mapping is in the Payment model mapping to destination ISO20022 model mapping).
To create a new format to model mapping, press the new button, select the data model definition, and enter a name for the mapping. After this you must save the configuration and then you can click on designer to configure the mapping. This configuration is comparable to the data model mapping with the only difference that your data source will be the import format / export format.
Validate, this will validate the configuration and show any errors or warnings in the bottom frame. Errors are issues you
Run, this runs the current configuration as is. Before running you will be asked to save the current configuration.
Performance trace, this opens the trace parser menu. This is a menu you will be using for performance optimization.
Import, via this menu you can import a template and create or update the format nodes automatically from this template. The template will be uploaded as an ER attachment to the configuration by doing this. This option can save you a lot of time when creating a blank configuration, as in general it is quicker to create the template than it is to recreate this in Electronic Reporting.
View, has two options under it
- Paths to cache, this will show the nodes that are cache enabled.
- Attachments, shows the attachments on the format configuration. Note that this is a different attachment menu than the default D365FO attachments.
Options, this is the default D365FO option menu.
Search, this searches the action pane bar for any actions you want to perform. This option searches the actions present in the action pane bar only.
Format mapping – Format Designer Area
Format Designer Area, in this area the format nodes (building blocks of your import or export file) are listed and created.
Add root, this adds a root node. Based on the type of node different child nodes become available. For guidance and self-learning, I will include formats which you can import from the Microsoft LCS, which can be used as a starting point for learning how a format mapping works. In future blog posts we will cover the details of each file type separately.
Common, this adds general file related nodes.
- Case, this is used for import configurations to determine what type of format mapping needs to be available.
- File, this allows you to create a file with a custom defined extension and encoding. Contents of this file are text based. See EU Sales List TXT (UK) and GSTR-2 CSV as example files.
- File attachment, this is a general file attachment with no option to add child nodes to. General usage would be for static files like terms and conditions.
- Folder, this is a node you can only create as a root node. This node is essentially a zip folder in which you can several output files. When using this node, you will only get the zip file as output (assuming you did not configure any ER Destinations).
This is very helpful if you have a format configuration which generates multiple copies of a file, like for example a payment specification. If you do not add this and create each file as a separate top node you will get each file output as a separate file. So, with a big vendor payment file with 50 payment specifications you would get 51 files pushed to the browser instead of just 1 zip file.
You can use the GSTR-2 CSV as an example file. Not all file nodes are supported by the folder node, a list of supported file types is shown below.
Data source, these contain data source related nodes.
These are used for import configurations and even though as they can be added to export configurations they will not show up in the exported files. Under a data source you can create a data source item, this is a data node item, you can define this as a data type listed below.
Excel, even though the node type says Excel you can use this node to upload Word files as well. The attached template determines how the output is handled.
- Excel File, for Excel files you are required to use named ranges in the file. These named ranges will appear as nodes under format designer area. This configuration is template based, so it is recommended that you use the import function to create these file types. This way the Electronic Reporting engine will create the nodes from the template, this will save you a lot of time. You can use the <ISO20022 Credit transfer> as an example.
- Word file, Word files can be attached as well, and the binding is performed based on the XML Mapping framework. This is an option you can enable in Word, by enabling the Developer tab which would show you the XML Mapping Pane. As this is basically a mapping on its own, Electronic Reporting cannot import a Word file as a template. If you want to save time creating the nodes, you can create the named ranges in Excel, use this as a template import and then replace the template with the designated Word file. Unfortunately, there are no example templates featuring Word files that I know off. A separate post will focus on Word files.
JSON, a JSON object is the root for a JSON based export configuration.
These configurations are used for direct (outbound) integration (non-file based Electronic Reporting). An example of this is the <VAT DECLARATION JSON (UK)>.
PDF, this allows you to create export files in PDF format.
This requires you to create a fileable PDF form and bind the data sources to the fillable fields.
- File, this is the file node for the PDF configurations. Unfortunately, there are no predefined PDF formats available which you can use as an example.
- PDF Merger, this node gives you the flexibility to combine several PDF files into one file. The way you could use this is if you have one configuration covering multiple page lay out (single page invoice / multi page invoice).
Text, these node types are used for text-based configurations.
Although it is possible to add these without a file node as predecessor it is not recommended. See <EU Sales List TXT (UK)> and <GSTR-2 CSV> as example files.
- Date Time, adds a date time node. Please keep in mind that you are required to bind the date and the time, so just binding a date results in errors.
- Numeric, adds a numeric node.
- Sequence, adds a sequence node. Sequence nodes allow you to add repetitive nodes in a text file configuration.
- String, adds a string node.
XML Element, this is used for direct export configurations (not based on a file but on integration).
Although Electronic Reporting supports output to XML files these export configurations are preceded by a File type node. You can use the <ISO20022 Credit transfer> and <Semansys XBRL (NL)> as an example.
Add, this adds a child node to the selected node, the selection here is based on the selected node type. See the Add root functionality for more information, besides this we will cover this more in detail in future blog posts.
Delete, deletes the selected node and its child nodes. Please be aware that you can not delete nodes which have dependencies beneath them, delete these dependencies first before deleting selected node.
Make Root, this promotes the current selected (child) node to a root node. You can only use this option on nodes that can be a root node.
Move up, this moves current selected node up.
Move down, this moves the current selected node down.
Copy, copies the current selected configuration node and its child nodes.
Cut, cuts the current selected configuration node and its child nodes.
Paste, this pastes the copied or cut configuration node / child nodes.
Expand / Collapse, this expands or collapses the current selected node and the child nodes.
Search, this allows you to search the format nodes in the configuration.
Format mapping – Format mapping area
The Format Mapping Area is the place where you will find the configuration details. This area changes on functionality based on the selected menu item.
Format, this menu allows you to set up and configure the selected node. Based on the node type different options are available. This will be covered in detail in future blog posts.
Mapping, this is the menu in which you define the export model mapping nodes. If you select this node the menu bars for the format designer area and the format mapping area change.
Transformations, this allows you to add transformations which you can use in the format menu. Transformations are formulas which allow you to filter out certain characters. The following options are available.
Validations, this allows you to add validations to the selected node. Keep in mind that the validations shown in this form are the validations bound to the current selected node and all its child nodes. If you click add new validation you will bind the validation to the current selected node. This is relevant if you only want the validation to be active on certain nodes or validated by a specific order.
Conclusion
You now should be able to understand the designer and the menu items present in the designer. Taking the example formats into account you now should also be able to explore existing configurations and learn via this way what is possible in Electronic Reporting.
In future posts we will cover each of the areas more in depth, depending on the file type selected.