Export Message Creation in D365FO Electronic Reporting: Part IV – The Format

In this final part of the series, we’ll explore how to create a text file using some of the data we’ve gathered in our mapping. The file will be organized into sections, with information sorted both by a suffix on each line and the data’s position within the line. This format is commonly used in Spain for electronic communications like reverse factoring, bank statements, and simplified EDI files. Our goal is to export a file featuring a header for each currency that includes total amounts, followed by a line for each invoice in that currency, like:

First, let’s start by setting up the format inside our model. Then choose ‘Designer’:

On the format screen, you’ll see two sections: on the left, the nodes of our format; on the right, four columns:

Format: Here, for each node we add on the left, we can specify parameters, such as length. This column must be selected to add, move, or delete nodes in the format.
Mapping: This column is named mapping, but it actually represents our model, which obtains its data from the mapping. It needs to be selected to link data into the format or to edit the formula for each node.
Transformation: This allows us to create formulas that might be used in multiple nodes, like transforming text to uppercase, to avoid redundant work.
Validation: Here, we can add validations, warnings, and errors, based on the data encountered during execution.

Now, let’s start creating our file:

Inside the ‘File’, we’ll create a sequence used for line breaks:

Next, we build the rest of the structure:

For ‘InvoiceGroup’, we’ll need to add a line break as well. Then we assign to labels a minimum and maximum length of 8 and let’s give it a value selecting ‘Mapping’ and ‘Edit formula’:

Given we have multiple invoices, let’s group them by currency by creating a grouped node at the same level as ‘Invoices’ on the ‘Mapping’ tab:

This interface provides a ‘What to Group’ section for the list of records, fields for grouping on the left, and aggregations in the center. On the right, our model allows us to select records and fields to add with the ‘Add field to’ button. So, for ‘What to group’, we use ‘model.Invoices’. For ‘Group by’, we use ‘@.Amounts.Currency’, and for ‘Aggregations’, we select ‘@.Amounts.TotalAmount’ and ‘@.Amounts.Taxes’, choosing ‘Sum’ for both.

Sometimes, we might only need to sum a field from a list without actually grouping; in those cases, we leave the ‘group by’ field empty.

A new node is created, which we can then link to ‘InvoiceGroup’:

Now, we can connect the rest of the nodes from this node. Some can be directly bind, but for calculations like ‘SubTotal’, we’ll need to use ‘Edit Formula’.

Finally, we assign a length to each field, align numbers to the right, and fill empty spaces with zeroes.

And let’s test our format:

if all goes well, you should have a text file as intended:

Lastly, let’s change the file name to reflect our customer’s geographic classification plus a timestamp. First, let’s declare our model enumeration:

And lets change the file name:

CASE(
FIRSTORNULL(model.Invoices).Customer.Address.GeoClassification,
    GeoClassification.National,"N",
    GeoClassification.'Economic Area',"E",
    "F"
) & "-" & DATETIMEFORMAT(NOW(),"yyyyMMddHHmmssfff")

The CASE formula begins with a parameter that evaluates a node or formula. The second parameter represents a possible value, and the third specifies the return value if the first parameter matches the second. Additional parameters can follow in the same pattern, allowing for multiple conditions. Optionally, a final parameter can be included to handle cases where none of the previous conditions match. Without this fallback parameter, an error will occur during execution if no condition is met. For our scenario, we designate ‘N’ for national customers, ‘E’ for those in our economic area, and ‘F’ for all others. We then append a dash and a timestamp, formatted to the current date and time.

Now, we can complete the format and perhaps ask a programmer to integrate it into an ERP system for final user access.

Additionally, we can save a physical copy of our model, format, and mapping for each node:

And import them into other environments using the ‘Exchange’ button, starting with the model.