Transcription of video
The Master Data Import Worksheet lets you define your own templates for how to import data into Business Central. A template defines which columns you want to import to, mapping each column in your Excel sheet to a specific field in the target table.
The supply chain box ships with a set of ready-made templates for common tables like customers, items, item variants and prices. You can use these out of the box, modify them, or build your own from scratch.
The first column or columns in a template are always the primary key of the target table. These are greyed out and cannot be edited or reordered. For tables with a multi-field primary key, every key field must appear in the Excel sheet in the correct order, even if you leave its value blank.
You can also map columns to Master Data fields, not just standard table fields. Abakion cannot ship these in the templates because they depend on your own Master Data setup, so you define them yourself.
How the standard import templates are built
Open the template list from the Master Data Import Worksheet, and you find all the templates that come out of the box. Take the customer template as an example. When you open the template card, you can see how it is set up. The type is set to customer, which means you are importing into the customer table.
The first column shows the heading “Column 1” greyed out, so you cannot edit it. That is because the first field has to be the customer number, which is the primary key of the customer table. The second column is the customer name, the third is Name 2, then search name, and so on.
These are the fields you would normally fill in when importing customers. Abakion provides Excel templates that match this setup, so the columns in the spreadsheet line up with the columns in the template. You can also open the template and inspect it yourself before creating your own.
Changing which fields a template imports
The templates are not fixed. If you do not want the city as column 7, you can drill down on that column, look into the customer table, and select whichever field you want to import to instead. You arrange the remaining columns however you like, as long as the primary key stays in its fixed position at the start.
Handling primary keys with multiple fields
The customer number is greyed out as the first column because it is the single primary key field for that table. Other tables have a primary key made up of more than one field.
The Item Variant template, for example, has two key fields. Both are greyed out: the first column has to be the item number, and the second has to be the item variant.
Sales prices use a more complex key. If you import sales prices or purchase prices, you have to respect the greyed-out columns in the exact order they appear, because those columns are the primary key and must be present in the Excel sheet, even where the value is blank. For instance, the unit of measure code has to be column 7 even if you are not entering a unit of measure code. After the key columns, you arrange the remaining fields as you like.
Mapping columns to Master Data fields
Standard templates apply directly to the target table, such as the item table or the customer table. But you can also map columns to Master Data fields on a table. The item template can be modified so that, for example, column 4 applies to a configuration value such as material. This works across all the tables that support Master Data.
You have to define these mappings yourself, because Abakion does not know your Master Data setup. You select the fields and point them to the right tables. A field can come from either the item table or the Master Data Information table on the items.
Importing values to a specific configuration value
You can target a specific configuration value when importing. Take the wheel diameter setup on a modified item template. It applies to two things at once: the column value, which is the Master Data Information wheel diameter, and the configuration value Inch. The reason for pointing it at the Inch configuration value is to get a dropdown on the information value, so whatever value you bring in lands on that specific configuration value.
In practice, this means you can import the number 29 as a column in the Excel sheet, and it applies to the Inch value on the item. If you also want to import values in millimeters, you either add another column or change the existing one to millimeters. You can build three separate columns that each apply to a different configuration value, so the same physical measurement can be imported in inches, millimeters or another unit at the same time.
Q&A
What does the Master Data Import Worksheet do?
It lets you define templates that map columns in an Excel sheet to fields in a Business Central table, so you can import data into the system. The supply chain box includes ready-made templates for customers, items, item variants, prices and more.
Why are the first columns in an import template greyed out?
Because they are the primary key of the target table. The primary key fields are fixed in position and cannot be edited or reordered. For a single-key table like customers, only the first column is greyed out. For a multi-key table like item variants, both key fields are greyed out.
Do I have to include primary key columns in the Excel sheet even if they are blank?
Yes. For tables with a complex key, such as sales prices and purchase prices, every primary key column must appear in the Excel sheet in the correct order. For example, the unit of measure code has to be column 7 even if you leave its value blank.
Can I change which fields a standard template imports?
Yes. You can drill down on any non-key column, look into the target table, and select a different field to import to. You arrange the remaining columns in whatever order you prefer.
Can I import Master Data fields with the import templates?
Yes. You can map columns to Master Data fields and configuration values, not just standard table fields. Abakion cannot ship these in the standard templates because they depend on your own Master Data setup, so you define them yourself.
How do I import a value to a specific configuration value such as inches?
Set the template column to apply to both the Master Data Information value and the specific configuration value, for example Inch. Whatever value you import then lands on that configuration value. To import in another unit such as millimeters, add a separate column or change the existing one.