How to define your own import template with the master data import worksheet?
With Master Data Import Worksheet, you can define your own templates for how to import data into the system, meaning defining all the columns you want to import too.
Transcription of video
But with the standard setup from supply chain books, we have made a lot of templates for you to make it easier.
I’ll just explain how those templates looks.
So, if you go into the template list from the Master Data Import Worksheet, these are all the templates we made out of the box. Let’s take the customer template.
Clicking on the customer template, I can add the template card and here I can see how this template is setup. The type is customer meaning I’m importing to the customer table and as you can see here the first column, the heading with column one is greyed out meaning it’s not possible to edit.
That’s because the first field has to be the customer number which is the primary key of that table.
Drilling down, I can see that the second column will have to be the name from the customer, the third column name 2, search name, et cetera.
We provide Excel templates with this setup that fits to the setup or you can just go in here and look for yourself to create your own templates.
These are all the fields that normally would be entered when importing customers but it’s possible of course to change the templates here if you don’t want the city as column 7, you can just drilldown and it will look into the customer table and you can select whatever field you would like to import to.
Now, the primary key was greyed out because the customer number has to be the first one.
If we for example look at the Item Variant and looking at this template card, the primary key has more than one field, it has two fields, therefore two fields are greyed out and the first column has to be item number and the second one has to be the item variant.
Likewise, on the sales price which is a very complex key. So, if you import sales prices or purchase prices, you have to respect the column or the greyed out column in this order, which is the primary key they have to be entered in the Excel sheet even though the value is blanked out.
So, the unit of measure code has to be column seven even though you are not entering Unit of Measure Code.
And after that you can select all the different lines down here how you like them to be.
Last example would be on the item, this template I have modified, this is not the standard template because all the standard templates will apply directly to the item table, customer table, et cetera, but on this one, I have made changes and I’ve made that to show that it’s possible to apply to Master Data fields as well.
So, in column four on this one I could apply to configuration value and I could select material.
This way it’s possible to apply Master Data on all the tables as well but of course, we cannot put in the templates because we don’t know your Master Data setup, so you’ll have to define this by yourself selecting all the different fields and applying all the different tables.
So, this will be the gender, et cetera.
So, field can come from either, in this case the item table or the Master Data Information table on the items.
I would like to draw your attention to the setup of wheel diameter, it has both the – not configuration value but it applies to the column value, which is the Master Data Information Wheel Diameter and to the configuration value Inch because I’d like to have a dropdown on the information value.
So, whatever value I’m bringing here should be on that specific configuration value.
This means, just to show this example, if I’m looking at my items and I just take the first item, and if I use that template to Import to, it’s possible to import for instance 29 as a column in the Excel sheet and it would apply to INCH.
So, if I prefer to apply other stuff to millimeters, I will have to make another column or change it to millimeters.
So, I could for instance make three different columns applying to those three auto different values in here. So, that’s also possible.