Using Dynamics AX Data Import Export Module to Upload Fixed Assets

Uploading legacy data into Microsoft Dynamics AX is not always an easy task. For example, when you want to upload fixed assets for multiple legal entities in your existing Dynamics AX environment, adding thousands of records and all related data will require a lot of dedication and effort. This can also result in errors as well. Dynamics AX now features a module built around importing and exporting data called Data Import Export Framework (DIXF). At a first glance, using this framework can be a little overwhelming. However, this blog will demonstrate how to upload a list of fixed assets from start to finish using this module.

From a high level this module works in a step by step process:

1. First, a source data format needs to be created
2. The data that needs to be migrated will have to be filled in to the source data format file. It is recommended that after filling the file with data the file be saved as CSV. This file is then uploaded into a staging environment.
3. The staging environment allows you to validate your data and make any necessary changes.
4. Lastly, the data in the staging environment is then uploaded to Dynamics AX.

In this example, my data is in an Excel spreadsheet. However, it is saved as a CSV file and I chose to use headers in my file as well.

Data Type

With the data file containing the list of fixed assets and the relevant data, the first step in this process is to setup the Source data formats. This is the where you determine which file format you will use. The supported file formats are: delimited, fixed width, XML, and Excel. In my example, using my CSV file, the setup for my Source data formats page is as follows:

1. Click new> define a source name and description for your reference
   • File format: Delimited
   • First row header: check
   • Row delimiter: {CR}{LF}
   • Column Delimiter: Comma {,}
   • Lastly, define the Multiple value separator, I will choose an underscore (_)
   • Screen should look as pictured below.
   • Close screen
     

Processing Group

The next step is to set up the processing group found in the common section of the module.

1. Give the group a name of your choice.
2. Next click Entities
3. From the Entities dropdown we will choose Fixed Assets Master
4. Source data format: CSV (defined earlier)
5. Be sure to click both checkboxes on this screen, Run business logic in insert or update method and Run business validations. Failure to check these boxes, you are able to upload data into Dynamics AX that has not been validated. Down the road this could cause major issues.
6. The next step is to click Generate source file> click next
   • Here you will select which fields you are uploading from your template to the staging environment
   • 1. Utilize the up and down buttons from this page to do so
     2. The generating your source mapping should look as follows:
        
     3. Click finish. Fill up the file that was generate above as shown below.
        It is important to have the order of the fields align with the headers from your upload file. Once the file is completely filled out, save it as a CSV.

     Go Back to DIXF and click the Entities screen> Click the folder next to Sample file path

     1. • Here you will choose your CSV file you are wishing to upload.
     2. Once you chose your respective file, click Generate Source Mapping
        • An info log should appear indicating that the entity mapping is done successfully. If not, ensure you saved your file in the correct file format and double check the above step in Generating source file.
        • It is important to note: you cannot have the file open during this process.
        • Click Validate and OK.
        • Click View Target mapping. This will display the first 10-15 rows of the file that you are trying to upload. Verify that the data is in the right columns.
        • Close the form.

     Upload

     1. From the Process group form you will now click Get Staging Data
        • Leave the defaulted Job ID and enter a description if you please
        • Click OK
          
     2. Ensure the file path is correctly pointing at your CSV file
        • Make sure that this file is not open
     3. Click Run > OK
     4. An info log appears that your data has been inserted into staging.
        • Your data is now ready to be validated and you can make changes if needed

     Verify Data

     1. From the processing group screen – select Execution History
     2. To view the data that is now inserted into the staging environment, click View staging data
        • From here you are able to validate the data you are planning to load into Dynamics AX and make changes if necessary.
        • After the necessary changes have been made you are ready to push the data into Dynamics AX
        • Close the view staging data form.
        • Click on Execution History
          1. View all the data that is inserted in to Staging. You can still make corrections to any data here prior to importing final data in.
     3. Click Copy data to target
        • Select the appropriate job ID
        • Click OK > Run > OK
          
     4. Your data has now been loaded into Dynamics AX.

     Error

     You might receive an error on import. To resolve the error on import go to Execution History, Click on View staging data and on the transfer status field type in error. This will list all the errors.

     

     Make necessary corrections to the file. Copy data to target again and at this time click on the following

     This will insert the rows with errors.
     

     All the data has now been uploaded successfully.

     
     The next step is to go to Fixed Assets module and create and acquisition journal. The acquisition data will be date of GO Live as you will have fixed assets from many years for which the periods do not exist in AX.

     Once the acquisition journal is posted you will have to run the following SQL query to reset the acquisition date.

     

      

     After this process, you can run the depreciation proposal as well and you are all set.
     

     RSM is a national partner for Microsoft Dynamics AX offering comprehensive professional services for Dynamics AX. We bring a breadth of experience and deep industry knowledge to integrate and streamline all aspects of your corporate operations. For more information on how we can help you with your Dynamics AX implementation, contact our professionals at erp@rsmus.com.

     Written by: Sunit Parikh

      

Sunit Parikh