Skip to main content

DYNAMICS AX Import Export Tool - digging a bit deeper.

Normally when I've used the export/import-tool in AX, it's because you want to dump data from a small database and import it in another environment, or if you want
to install some sort of demonstration database in an environment.

Up until now, if I've been presented with a task of importing data into a customers environment, i've always programmed some sort of class that handles the import.

A couple of weeks ago, a colleague of mine showed me that the import/export tool, can be used for the same task - that is importing data, from e.g. a .csv-file, that your customer prepared in Excel.

I was thrilled to discover that I do not actually always need to make a class to import data in a specific (or more specific) table(s).

The procedure is:

1) Create your own Definition group - make sure it is empty by removing all checkmarks on the Set up and include table groups tabs pages, and choose the type User defined.
Save the def. group.

2) Now click the Table setup button. Choose the table that you want to import data into. Choose the import status delete and import (if you want to clear the table of data before you import), or just import. Set up the file name of the file containing the data.

3) On the general tab you can specify field delimiter (in my case ; for a .csv-file).

4) On the Conversion tab you can actually write x++ code, that will be compiled and executed for each line in the file that is read in. In the method you get a table buffer of the table you choose in step 2, and a container containing all the fields read from the file.
This means you can make custom transformation, and even create records in related tables if you want, while at the same time importing data in to the chosen table.
You can let the compiler parse the code to verify syntax before saving the definition.

5) Using the last tabpage on the form, you can have the first record read from the file and shown to you where the values read are mapped to the fields chosen in step 6.

6) Using the button Field setup you can map the fields/values read from the file, to the fields of the table chosen in step 2.
Even on the field level you can write conversion code similar to the code mentioned in step 4.

All this works very nicely for importing data.

Now I've used this for making imports in the supply chain modules, Invent locations, wms locations, planning data and Item coverage data, preparing the go-live at a customer site.
This was done in a test environment.

When the customer had verified the test data, we wanted to move the import templates created using the above steps, to the live production environment, to make the "real" import.

How to be done ?

Moving the import-definition between environments (test and production) is done by exporting data in the tables:

SysExpImpGroup (which contains the definition group)
SysExpImpTable (which contains the tables of the def. group)
SysExpImpField (which contains the fields of the tables of the def. group)
And maybe
SysExpImpQuery (which contains any Export criteria - if used).

One little problem with the abovementiond procedure of moving the SysExpImp table.

When you import the exported data, the code you write in the conversion tab pages will be messed up. The import process apparently strips all newlines in the code.

You'll have to go through the conversion code and insert linebreaks after each semicolon, after import. Otherwise the import will not function correctly !!!

Comments

Popular posts from this blog

Suppressing the infolog

Supressing the infolog is often useful in D365FO when augmenting code. When augmenting code using COC (Chain Of Command), you can have new code run either before or after the code you are augmenting. This means that any infolog-messages that the standard application code does, will be shown to the user, even if your augmentation supports a scenario where there must be no infolog-messages. How do you avoid the standard application infolog-messages ? To the rescue comes temporary supression of the infolog. The suppression consists of: 1) Saving the current infologLevel 2) Setting the infologLevel to SysInfologLevel::None 3) Run your code 4) Restoring the saved infologLevel to the infolog For example a table could have a validatewrite-method that validates that you are only allowed to use 3 out of 6 options in an enum-field, and you need to allow for a fourth one. Table a - validateWrite method: boolean validateWrite() {     Switch (this.enumField)     {         boolean ret;         case

Dynamics ax 2012 traversing selected records in a form data source

A classical developer challenge in Dynamics AX is to enable a form button when multiple records have been selected in the form by the user. This usually involves writing some form of loop (for or while or do-while) that starts out with calling _ds.getFirst() and continuing the loop as long as _ds.getNext() returns a tablebuffer. Well things got a little bit easier in AX 2012. In AX 2012 you can use the MultiSelectionHelper class. One example is the following that I encountered in AX 2012: Can you make the customer collection letter print out run for each selected collection record in the Print/Post collection letters form (Accounts receiveable / Periodic / Collections / Print/Post Collection letters). If we ignore the possibility for setting up print destination for running each report we can do this in two steps: 1) Change the "Multiselect" property of the "MenuButton" and the "Menuitembutton" in the MenuButton in the form from "Auto&quo

Indicating mandatory field in a dialog (RunBase) class.

A classical problem is indicating that a field is mandatory in a dialog, when the field is not bound to a datasource/field in a datasource. Normally fellow developers will tell you that, that is not possible. I found a way to do this. In your Runbase-based class you can implement the putToDialog-method e.g like this: protected void putToDialog() { super(); fieldMask.fieldControl().mandatory(true); } where fieldMask is a DialogField object in your dialog. This will make the field act like it was a mandatory field from a datasource in a form, showing a red-wavy line under the field, and requiring the field to have a value. Attention: Your class has to run on the client.If you set your class to run on the server, you get a run-time error, when the fieldMask.FieldControl()-call is made.