Blogs, How-to, How-to

Let Dynamics NAV do the job: automatically map Excel fields

By
Daphne Tollenaar

Date
8 March, 2018

Reading time
4min. reading time

How often does it happen you receive an Excel file from a transportation or shipping company where they have listed all the goods of different customers in one overview? If you are a bit familiar with Excel, then you have probably found an easy way to split the different customers in order to be able to import them into Dynamics NAV. So it probably would only take up to 10 minutes per file? But did you know that these tricks aren’t even necessary? You can simply leave it up to Dynamics NAV.

In my previous blog, I already shared how you can use Excel import within Dynamics NAV to import data faster and less prone to errors. In this blog, we dive deeper into the use of Excel import.

Learn more about:

How customer items work
How to create multiple documents
How you can easily filter on a customer number by using line/header
And how the internal mapping values work within Excel

Import customer articles

In my previous blog, I imported a receipt document with one Excel import based on a (fictitious) customer article number. Within Dynamics NAV, customer items can have multiple unique values. The most common are internal number, external number* and EAN. Customer items can be recognized by all these values, using both EAN and external numbers in the same column.
*When referring to external number, we mean the number that your customers use for an article.

Multiple documents

When using the function to create multiple documents it assumes that if a certain row has different values, a separate document header must be created. A useful scenario for this is the ‘External Document Number’.

In the example below, I will make three different receipts in which the articles are randomly divided.

Order customer number

On the header of the Excel file, it is possible to specify the (order) customer number. Particular important when importing a file in order to match the right customer article and to create the document. With the field “Customer No.”, you also have the option to import different receipts based on the customer number. In other words, you can process one Excel file that specifies the expected receipts of multiple customers. For example when a shipping company transports goods for different customers and sends you an Excel file in advance with the goods that will be delivered. Now, you can easily create different receipts simply by using the customer number. Note: you have to link these from the line within an Excel file.

Mapping

It often happens that your customers work with values that aren’t available in your Dynamics environment, but which can be referenced to. For example, the different suppliers that deliver the goods of your customer. They can maintain different values for a customer article, carrier type or unit of measure.

These items will be discussed in the example below:

1. Create an import definition

Excel:
In the example below, the article “T-SHIRT” has an EAN code instead of an external number.

Import definition:

Use a filter on the column “range” (1) to show only fields that have been mapped. Step 2 shows the filter that I have used to shows these values.
In order to let Dynamics NAV know it has to split the document on header level, select the checkbox “split documents” (3).
For the carrier types, I specified a mapping table that ensures that my values in the Excel are correctly translated to the values in Dynamics NAV. The ‘internal value’ is the value within Dynamics NAV, the ‘external value’ is the value that could be used in the Excel document. If the value in the Excel document does not appear in the mapping table, the value as specified will be used.

2. Excel import

In the previous example, we’ve used there are three different unique external document numbers. So, when importing, we also expect three unique documents in Dynamics NAV. Once the import has been completed, you will receive a notification with the number of documents that have been created.