Importing Sales Order History Through the Data Import/Export Framework
Posted by Author, Curator, Visually Impared Squirrel Literacy Advocate & Dynamics AX Technical Solution Professional (TSP) at Microsoft on
When you implement Dynamics AX the last thing that you want to do is keep the old system around just so that you can access the historical information. One example of this is the sales history which is useful for reporting and trending, and also important when a customer calls up and asks about an order from a year ago. You may think that it is too much work to move all of that information over to your new system, but it’s not so. Using the Data Import Export Framework you can bring all of your historical sales orders over and be able to access them from within Dynamics AX.
You’re one step closer to cutting the cord on your old ERP system.
How To Do It…
- To set up the import, click on the Processing Group menu item within the Common group of the Data Import Export Framework area page.
- When the Processing Groups maintenance form is displayed, click on the New button in the menu bar to create a new record.
- Then give your new record a Group Name and Description.
- After you have done that, click on the Entities button in the menu bar.
- When the Entities Processing Group maintenance form is displayed, click on the New button in the menu bar to create a new record for the Sales Order Headers.
- From the Entity drop down, select the Sales Order Headers entity.
- Then select the Data Source Format that you want to use – in this case CSV will work fine.
- Then click on the Generate Source File button on the menu bar to start formatting the data source fields.
- When the wizard is displayed, click on the Next button to continue through the splash page.
- When the Display Data page is displayed, you can add any additional fields that you may need, but in this case we have enough information.
- Then click on the Generate Sample File button on the right hand side of the form.
- This will create the sample CSV file for us that we can save away in the Data Import Export working folder.
- After you have saved the sample file, just click on the Finish button to exit from the wizard.
- When you return back to the Select Entities For Processing Group maintenance form, click on the folder icon to the right of the Sample Path File field.
- When the explorer window is displayed, point it towards the SalesOrderHeader.csv file that you just created and click the Open button.
- Finally, click on the Generate Source Mapping button on the menu bar to create the mapping to the file.
- If everything is fine, then you should get an infolog box that tells you that everything matches up.
- Now that we have the format for the header configured, we need to do the same for the order lines. To do that, click on the New button in the menu bar to create a new Entity record.
- From the Entity dropdown, select the Sales Order Line entity.
- Then select the Source Data Format that you want to use – again, we will just use a CSV file.
- Click the Generate Source File button in the menu bar again to open up the wizard, and click on the Next button to skip the splash page.
- For the lines we need to add a couple more fields to the standard template.
- Search through the standard fields, and find the ItemId field and click on the Present In Source checkbox to add it to the mapping.
- After you have done that you will be able to see the ItemId field in the list.
- Add SalesQty, InventSiteID, and InventLocationID as well.
- Then click on the Generate Source File button to generate the template.
- When the CSV file is displayed, save the SalesOrderLine.csv file to the working directory.
- After you have done that you can click on the Finish button to exit from the wizard.
- When you return to the Select Entities For Processing Group form, click on the folder icon to the right of the Source File Path field.
- Then find the SalesOrderLine.csv file and click the Open button.
- All that is left to do is to click on the Generate Source Mapping button in the menu bar.
- If everything is connected correctly then you will get a non-descript InfoLog box.
- Now we can start populating the import files with our history. To do this navigate to the working directory and Open up the Sales Order Header template within Excel.
- You will be able to see all of the fields that you need to populate.
- Just paste in all of the header details into the spreadsheet and save the file.
- Then open up the SalesOrderLine.csv file the same way, paste in all of the line details and save the file.
- If you want to test the data file, just click on the Preview Source File button within the menu bar of the Select Entities For Processing Group form.
- You will see that the header information is correctly loading.
- And you can do the same for the lines as well.
- Once you are done, just click on the Close button to exit from the form.
- When you return to the Processing Group form, click on the Get Staging Data button in the menu bar.
- Then the Job dialog is displayed, click on the OK button.
- This will open up the Staging Data Execution form and all you need to do here is click on the Run button.
- Then click on the OK button to kick of the staging of the data.
- You will then get an InfoLog box telling you how many records were imported.
- Finally, click on the Copy Data To Target button in the menu bar and select the Job ID that you just created and click on the OK button.
- When the Target Data Execution form is displayed, click on the Run button in the menu bar.
- And the last step is to click the OK button to start the import running.
After it has completed, you can look at the Sales Orders, and you will see all of the history is now loaded and available for you to review.
How easy is that?
To view the original post, and also to download the full step by step walkthrough along with the screen shots, visit the Dynamics AX Companions page here:
Share this post
- 0 comment
- Tags: Dynamics AX
0 comment