I guess that most of us by now have used the data import export framework for imports while setting up a new Dynamics AX implementation, but there are some imports that the business will want to perform on an on-going basis long after the implementation team have celebrated a successful go-live, and moved on to their next project.
One such example is purchase prices.
Typically to do an import you’ll fire up the Data import export framework, create a processing group, select your target, and run the wizard to create a source file.
For the purchase price import we import into a trade agreement journal and the wizard will give us a source file like this:
Now, it’s perfectly possible to create an input file in that format, but your users probably already have a purchase price spreadsheet, and it’s going to be in a different format – something like:
Now we’re going to have to do a couple of compromises. We’re importing into a journal, and to get all of the lines added to the same journal, we’re going to need to define the journal number on each line. We need the Vendor code and Currency on each line, and unless all our items have the same unit of measure, we’re going to need that on each line too (but in my case all of these items have a purchase unit of ‘ea’. That’ll give us:
Right. Let’s start cooking. First save the Excel spreadsheet as a CSV file (don’t forget to close the spreadsheet after saving the CSV file – the DIEF won’t import from a CSV file if the spreadsheet’s still open. Then from your processing group setup a new Source data format of CSV+4 (because we’re starting the import at Row 4)
Now we’re going to customise the input fields. Click ‘Entity attributes’ on the Select entities for processing group form. You’ll be used to seeing setups like this:
But you can create your own mapping. In a moment we’re going to set default values for all the fields we’re not importing, so here specify the fields in your import file and organise your setup so that they’re specified into the correct columns. In my example that means I end up with this:
Now we need to map these input fields to the target fields. Back on the Select entities for processing group form click ‘Modify source mapping’. Adjust the mapping details until you’ve mapped all of the fields you want to import:
Now obviously we have to set default values for those fields not present in the import file. Add a new row select a target field and tick ‘Auto default’. Then click ‘Default values’ and enter your value, like:
When you’re done you should have:
Note. For the Line number I used Auto-generated, not default. It’s a key field for the journal line.
On the Select entities for processing group form you can use the Validate button to check that all is well, and you can preview the source data file:
So to re-cap, our data import format is:
And once imported into the Price/discount agreement journal we have:
Obviously this technique can be used for any ‘business as usual’ data import.