Import/Export: Reinventing the wheel

I was suppose to create a small tool for simplifying integrations to freight forwarders, but I ended up with an easy to use and zero footprint import/export solution. I wanted to create a solution that was easy to use for end-customers and business consultants, enabling them to very easily create CSV, XML and Excel files.

The Consignor integration tool made available for Dynamics AX is a multipurpose tool for creating, exporting and importing CSV-files, XML-files or Excel spreadsheets. The tool has been specifically created to enable end-users and business consultants to perform data export and import, without knowing how do develop in Dynamics AX and X++. The feedback I have received colleagues is very good, and it works as a basis for the upcoming consignor integration.

  1. How to use the consignor integration

    For an end user it is very easy to use the tool. There are two approaches to start the export.

  2. Export file using short-cut

    In Dynamics AX it is possible to short-cuts. For the Consignor, this short-cut is CTRL-Z. Then the following menu will be visible, if there exists a defined consignor format.


    Here the user can select a predefined format. If a Excel format is selected, then Excel will be opened and filled in. Also if XML or CSV is selected, a file containing the defined format will be opened.


    Export file using Menu

    It is also possible to create menu buttons to start the export, and this require a small “drag-and-drop” of a menu item to the form you want to have the consignor integration activated on.


    How to setup an integration

    The consignor integration setup is located under system administrationàperiodic, and consists of two menu items.

    Consignor menu

    In the consignor message types, the definition of the integration is setup.

    The consignor messages is all logged messages, when this are enabled for an integration.

    Consignor message types

    The consignor message types, it the form that is used to defines integrations. The form contains several settings, and in this chapter all fields are described.


    Message file details

    Field Description
    Message type Unique identifier of the integration. This is the name that the user will select, when selecting an integration type.
    Name Name of the integration type
    Note A small note can be added. Use this to log changes made to the integration.
    Approved Select if the integration is approved. Only approved integrations can be selected by the user. Also, when approved, the integration cannot be changed
    Include column names When exporting to a CSV file, the first line can contain the fieldnames. For Excel and XML this parameter have no effect.
    Log message To log all messages, enable this parameter.
    Open file After a file has been exported, the consignor integration can open the file. CSV files will normally be opened in Excel, and XML will be opened in internet explorer. But it’s the windows file association that decides when program to use when opening the file.
    Import/Export Define if the definition is an export, or an import. In the initial release, import is only supported using CSV files. But this will be changed in future releases.
    File type Select the file type of the export/import. The following format are available: Comma Separated File(CSV), XML and Excel.
    File path File path where the export files will be exported, and where files will be imported from.
    Archive file path When importing a file, from the “file path”, the file can be moved to the “Archive file path”. The reason for this, is because when importing files, the import batch-job can read a folder, and fetch all files that is dropped into this folder.

    Message datasource setup

    Field Description
    Source Class It is possible to select a predefined class, where all the fields have been setup. This simplifies creating integrations. The source class also provides support for more complex integrations that require coding to perform. When a source class is selected, the Main table is also automatically given. The field is not mandatory. For a detailed, list of available source classes se the appendix 1 for source classes.
    Main Table identifier All integration need to have a main table identifier. This is used to help the selection of what integrations that can be used, based on the data when the integration is started. If the user is on the customer form, then only integrations that have the customer table defined in the Main Table Identifier can be selected.
    Query setup The consignor integration supports quite complex data structures, where information is fetched from different tables, and display methods. The query will use available relations to present what can be associated with the main table. Right click on the data source to select what tables you want to add. Both n:1 and 1:n relations is supported, but in a 1:n relation, only the first record in the applied relation will be used in the integration
    Button:Criteria The criteria button is used to simplify the consignor message type selection. If the user have several integrations for different customers, then it is possible to define a selection query, so that the correct integration is presented to the user.
    Button:Reset Resets the query, leaving only the main table left.

    Message field setup

    Buttons Description
    Add a new field to the consignor integration definition
    Remove the selected fields from the integration
    This button will create all the fields for the integration, based on the source class.
    This button will create all the fields, based on the query that is associated with the integration.
    Renumber will renumber the fields, so that no fields have the same number. The field field will get the value 1, and so on.
    Multi select fields, and activate/deactivate the fields.
    Field Description
    Field ID This is the name of the field. When manually creating a integration, it will be the column name, or the XML tag identifier. If a source class is selected, normally the fieldname is automatically provided.
    Type The field type can be a database field, Class method, display method or a free text. Selecting Field means that the value is fetched from the associated table and field.
    Class method, means that the value is fetched/calculated in the associated source class. Display method, means that the value is fetched from a table display method.

    Free Text means that the values is defined in the default value field.

    Table Tables means the table where the value is selected from. Since the data source can be a query, there can be several tables to select data from.
    Field This is the field from the selected table. If the type is a display method, then the name of the display method can be selected here. For Class methods, it is not necessary to select a field. But when importing data, the data will always be placed in the field defined.
    Default value When exporting or importing data, and the source is missing a value, then the default text will be used.
    Description Just a textual description of the field. By default the text here is fetched from the help field in the extended data type.
    Active Only active fields will be imported/exported. Fields that is not active will be filtered away on approved messages.
    Field number The field number defines the sequence that the fields will be defined in a file. In a CSV file, field number 1 will be first, followed by 2. The field number can be defined manually, or automatically by using the “renumber” button. The field number can also be moved up/down by using the “up/Down” buttons.
    Unique index field This field indicates will this field should be regarded as a unique index field. When importing data, sometimes we want records to be updated instead of being inserted. To find the associated record to update, the Consignor integration will use all “unique index fields” to find the record to be updated.
    Mandatory Fields can be defined as mandatory. This means, that when importing, this field needs to be filled in.

    Export format setup

    The export format is used to defined if the “export dialog” should be presented to the user.

    Field Description
    Worker Name of the worker
    Show dialog Indicates if the “export dialog” should be presented to the user.

    Other menu items

    In the top of the form, there are some more buttons.

    Button Description
    Create a new integration
    Delete an integration
    Shows logged messages
    Manually export data, using a query window
    Import messages by selecting a file, or by selecting a path to fetch all the files from. The import can also be set in batch, and can periodically be executed to import the files.
    To improve performance, the consignor integration does a lot of caching of export/import definition. When changing on an existing integration, the cache should be reset, by clicking on this button.

    Consignor messages

    All consignor import/exports can be logged, so that the exported/imported values is documented. There is a small performance penalty on logging messages.

    Field Description
    Message A unique message ID
    Message type Type of the message
    Processing date Date the message was imported/exported
    Worker Name of the worker that performed the import/export
    Import/Export Import/export type
    Filename Name of the file that has been exported/imported.

    Field values

    In the field values, all the field values are presented.

    Field Description
    Field ID Name of the field. This is used on column name, or in a XML tag.
    Field number The field number that is used
    Field value Value of the field
    Description Description of the field
    Multi line Field value In the bottom there is also a field that shows a multiline version of the value. Fields like addresses is often multiline, and will be correctly represented here.

    I hope to release the tool to CodePlex in the beginning of 2014.

    Happy DAXing J

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s