atinkerersnotebook: Using Excel to Copy Data from One Company to Another

[Blog bot]

Источник: http://atinkerersnotebook.com/2013/0...ny-to-another/
==============

Setting up a new company within Dynamics AX is not hard, but it is time consuming because you have to configure a lot to base tables and codes such as payment terms, freight methods etc. that are not shared between the companies. Something that would make the process easier would be a mass load of data from a template.

In this worked example we will show how you can do just that with the Dynamics Excel Add-In, by using it to query the tables in one company and then update the data in another without even breaking a sweat.

Creating a Update Template

To move the data from one company to another, you just need to create a template that has all of the base data, and then apply it to another company. Once you have created the template you can re-use it over and over again.

In this example we will show how to create an update template and use it to move the data into a new blank company.

How to do it…

To create an update template, follow these steps:

1. To start off find the data that you want to transfer between the companies, Right-Mouse-Click on the form and select the Personalize option.
   
2. In the Personalization dialog box, switch to the Query tab and find the base table that is used in the form. In this case it is the BankTransType
   table.
   
3. Open up Excel, and click on the Add Data button within the Design group of the Dynamics AX ribbon bar. Then select the Add Tables option from the drop down.
   
4. Search the available tables to find the table that is referenced in the form, and click the > button to add it to your worksheet.
   
5. This will open up the Table Explorer and you will be able to see all of the available fields for the table that you selected.
   
6. Drag all of the other fields that you want to copy over to the worksheet.
   
   
   Note: For the dimensions that are referenced, there is no need to copy over the parent and the name of the dimension – just the main account.
7. Click on the Options button in the Configure group of the Dynamics AX ribbon bar to open up the configuration dialog. Select the Legal Entity that you want to copy the base data from and then click the OK button.
   
8. To retrieve all of the base data, click on the Refresh button in the Data group of the Dynamics AX ribbon bar.
   
9. In this example, we copied all of the table data that is returned to a blank spot on the worksheet (pasting as values) so that refreshing the data does not loose our base reference data. This has an added benefit of allowing us to modify the data if we need to when transferring to the new company.
   

How it Works…

Now click on the Options button in the Configure group of the Dynamics AX ribbon bar to open up the configuration dialog. Select the Legal Entity that you want to copy the base data to and then click the OK button.


To retrieve all of the target data, click on the Refresh button in the Data group of the Dynamics AX ribbon bar.

Copy your base data from the reference table and then paste it into the linked table.


To update the target company, just click on the Publish button in the Design group of the Dynamics AX ribbon bar, and select the Publish All option from the drop down box.

After you have done this, if you look at the Dynamics AX Status worksheet, then you should see that all of the records have been successfully updated to the target system.


If you look at the table in the target system, it should now match your source data.


Creating a Company Template

If you take this a couple of steps further, then you can create a workbook with all of the base data that you need to load in order to set up a new company.

How it Works…

Setting up all of the base data is now just a matter of selecting the worksheets, copying over the base data from the reference table, and then clicking publish. Hours of setup can now be done in a matter of minutes.



Summary

The Excel Add-In is fast becoming my most favorite utility available within Dynamics AX. It makes adding masses of data a breeze, and also makes mass updates simple as well since you can mass replace so much easier within Excel than in the standard grids. Once you start using it, you will probably find that you will start amassing a number of update templates to save you time.

Not only can you use it for migrating data from one company to another, but you can use it to manage the data that you have set up already.

Give it a go and test it out for yourself.




Источник: http://atinkerersnotebook.com/2013/0...ny-to-another/