Import a CSV file with ER (I)

In this series, I’m going to show an example of importing a CSV file using Electronic Reporting (ER).

This guide assumes you have basic knowledge of ER. If you are just starting, I recommend reading the series on ER export first, as it is more step-by-step and explains the basic concepts of ER.

In an ER import circuit, we have these separate elements:

• Model: The intermediary node that receives data from the format mapping and is used in the mapping to update the database.
• Format: Parses the incoming file fields.
• Format Mapping: Links the parsed format to the model.
• Mapping: Converts and links the model to the system’s tables or entities.

In this first post, we’re going to look at the model and the mapping.

We will be importing a CSV file containing data about external codes. “External codes” is a form/table in D365FO that is linked to multiple tables, such as countries, counties, or ZIP codes, and holds, as its name suggests, external codes of a certain record.

Our CSV file is formatted as follows: the first column contains the country’s ISO (2) code, the second column lists the external code ID, the third column shows the external code value, and the final column represents a date. If a code is repeated, as in the case of ‘BR,AA’, we will only use the most recent one.

So let’s start by the model:

And a mapping, with the direction pointing to destination:

In the mapping, we’ll declare the model we just created along with the table records from the ‘LogisticsAddressCountryRegion’ country table.

Inside our model, we add a new calculated field to hold the unique key, allowing us to filter for only one record of each unique key, the newest one.

Now let’s order and filter the record list:

REVERSE(ORDERBY(@.ExternalCodes, @.ExternalCodes.Date))

LISTDISTINCT(@.'$ExternalCodesOrdered', @.'$ExternalCodesOrdered'.'$Key')

For each record, let’s identify the country it belongs to and then filter out records where the country code does not exist in our system.

FIRSTORNULL(FILTER(LogisticsAddressCountryRegion, LogisticsAddressCountryRegion.ISOcode=@.CountryCode))

WHERE(@.'$ExternalCodesFiltered', @.'$ExternalCodesFiltered'.'$Country'.RecId<>0)

Now we create a container for a small trick that will be explained later:

In the third column, ‘data model’, we can specify the table we are targeting. To the best of my knowledge, there is no entity for this table, so I will simply point directly to the table.

Let’s link the fields. The table ExtCodeValueTable is somewhat unique because it is related to multiple tables. Instead of having a field for each table key, it has two fields: one for the ID of the related table and another for the ‘Recid’ of the referenced record.

The fields mapped in the ‘ExtCodeValueTable’ are:

Code(ExtCodeId): String = @.ExternalCode

Reference table ID(ExtCodeRelationTableId): Integer = TABLENAME2ID("LogisticsAddressCountryRegion")

Reference table ID(ExtCodeTableTableId): Integer = TABLENAME2ID("LogisticsAddressCountryRegion")

Reference(ExtCodeRelationRecId): Int64 = @.'$Country'.RecId

Value alias(ExtCodeValueAlias): String = @.ExternalValue

Value(ExtCodeValue): String = @.ExternalValue

The mapping is almost complete, but in reality, there are two tables for the external codes: one that holds the external codes and another that holds the values. If an external code does not already exist in the system, the record will still upload to the table but will remain in limbo, invisible in the UI. To address this, let’s open the related tables, identify the one that contains the external codes, and link it. Since the related tables need to be linked to lists, this is where our container will become useful:

And that concludes the mapping process. In the next post, I will explain the format and the format mapping.