atinkerersnotebook: Using the Dynamics AX Excel Add-In

[Blog bot]

Источник: http://atinkerersnotebook.com/2013/0...-excel-add-in/
==============

The Excel Add-In is an incredibly useful tool because it doesn’t just allow you to download information from Dynamics AX, but it also allows you to publish information back to the system without compromising the data integrity. You can use this for the initial data setup within Dynamics AX, quick maintenance of multiple records, moving data from one system to another, and even aggregating data through matrix fields. And best of all, this tool is included for free with Dynamics AX, so you don’t need to buy additional licenses in order to use it.

In this worked example we will show how you can use the Dynamics AX Excel Add-In to make updating data a breeze.

Installing the Dynamics AX Excel Add-In

Before you start working through these examples, you need to make sure that you have the Dynamics AX Excel Add-In installed. If you open up Excel and there is a Dynamics AX tab in the ribbon bar, then you are fine. If not, you will need to install it.

How to do it…

To install the Dynamics AX Excel Add-In follow these steps:

1. The installation for the Office Add-Ins are included on the Dynamics AX 2021 installation kit. To find them, just select the Microsoft Dynamics AX Components from the Install group.
   
2. Skip through the welcome page by clicking on the Next button.
3. Make sure that you have the Add or modify components option checked on the next step of the dialog box and then click Next.
   
4. When the Add or modify components page is displayed, check the Office add-ins option within the Client components group, and then click Next, and complete the installation.
   

How it works…

Now, when you open up Excel, you should see a Dynamics AX tab with a couple of new options for you.


Downloading & Publishing Data through Excel

Once you have the Excel Add-In installed, you can start using it to download, update, and publish Dynamics AX data. This is useful not only for initial data loading, but also for ongoing maintenance of the system, because it allows you to maintain records within Dynamics AX quickly through Excel.

How to do it…

To download and publish data through Excel, follow these steps:

1. You can update pretty much any data that you are able to access through the Dynamics AX client. All you need to know is where to find it.
   
2. If you don’t know what table to update though, here is a quick trick. Find a form that has the data that you want to update through Excel. Then right-mouse-click on the form and select the Personalize option from the context menu.
   
3. When the Personalization form is displayed, select the Query tab. This shows all of the tables that are related to the form, and if you look at the first data source, then it is usually the table that you want to access.
   
4. To create a linked worksheet within Excel, select the Dynamics AX ribbon bar, and then from the dropdown menu of the Add data button, click on the Add Tables option.
   
5. This will open up a table selection dialog box. You can scroll down the list to find the table that you want to add to your worksheet.
   
6. Or you can also just type in the table name in the Filter field at the bottom of the form.
   
7. This will create a new worksheet for all of the tables that you selected to add, and also open up the field explorer on the left hand side with all of the available fields that you can select from.
   Note that the initial index fields are added by default to your worksheet, saving a little bit of time.
   
8. Now from the field list on the left, select all of the fields that you want to include in the worksheet.
   
9. When you have finished, click on the Fields button in the Design group of the Dynamics AX ribbon bar to return to update mode.

How it works…

Click the Refresh button on the ribbon bar and you will see all of the records from Dynamics AX.


To add a new record, simply start adding a new line on the next available row. It will automatically extend the table showing you that you are inserting a new line.

Also, as you are updating the records, if there is an enumeration associated with the field, then you can select it from a drop down, saving you time in trying to work out what the values should be.


After you have added your new record, then select the Publish All option from the Publish button.


If all of your field values are correct, and you don’t add any records that already exist, then the table will refresh and you will now see your new record.


It will also create a new sheet called Dynamics AX Status which will show you how many records were updated, and also any errors – if they occurred.


If we return back to Dynamics AX, then we will see the new record is there as well.


You can use this for initial data loading, saving a lot of repetitive data updates. For example, you can use this for adding all of the common codes that you need for a new company, and even for adding new chart of accounts in just seconds.


Using Filters to Update Select Data

The Excel Add-In has a feature that allows you to filter out the data that is returned every time that you refresh the worksheets. This allows you to easily update just the information that you are interested in. This is really useful for maintenance of data where you need to update a number of records at once that match a certain criteria, because you can download all of the records, select the items that you want to view, and then quickly maintain the fields and publish them back to Dynamics AX.

How to do it…

To filter a table query and then update multiple records at once, follow these steps:

1. In this example, we have queries the Vendors table. Since this is still an Excel table, you can filter on any of the fields. You can select any of the values that are in the table for the filter, and then click OK.
   
   This returns a subset of the data.
   
2. Another option though is to open up the Add-In’s filter by clicking on the Filter button within the Dynamics AX ribbon bar. This will open up the filter designer. You can add a filter condition simply by clicking on the Add condition button.
   
3. This will allow you to select a field that you want to filter on.
   
4. And also, if you click on the … button, it will show you a list of all the valid field values that you can apply to the filter.
   
5. You can continue to add as many filters as you want, and when you are done, click on the OK button.
   

How it works…

The next time that you refresh the data, you will only get the data that matches the filter criteria. This is better than the Excel filter, because all of the additional records are not returned, rather than being hidden in the traditional filter.


Once you have your dataset that you want to update, you can change any of the field values. And then to update all of the selected records, just click on the Publish All option within the Publish buttons drop down list.


The Add-In will notify you of any problems, and also confirm that the records were updated.

This is useful for all of the final tweaking of data that you always do after you load all the records, and also for periodic changes to the subsets of data that you always have to perform.

Copying Data between Companies

Another useful way that you can take advantage of the Excel Add-In is for data migration. You have the option of selecting what instance, partition, and company that you want to upload and publish to, and they don’t have to be the same. So if you have a test and a live environment, and you want to move data between the two to ensure that you have the same configuration codes, then you can download the data from one, and then publish it to another. Or, if you are configuring a new company, then you can copy a lot of the base data from another existing company, saving time.

How to do it…

To use the Excel Add-In to copy data between entities, follow these steps:

1. In another company, I have not loaded all of my base data, so we will use our main company as the template to load the data.
   
2. From the Dynamics AX ribbon bar, select the Options button from the Configure group. This will option up the data source options for the current worksheet.
   
3. You can change the Legal Entity that will be used just by selecting it from the drop down list.
   
4. Note also that you can change the Server to point to another instance of Dynamics AX, and also change the Partition if you have multiple instances stored within the same database instance. Once you have selected the new target company, click on the OK button.
   
5. Before we refresh the data, we will save off the data from the original company for reference. This will make it easier to cut and paste the data back into the new company. So we just select all of the original table, and then copy it.
6. Then paste the data over to the right of the main data. Now that we have the template data saved we can continue on.
   
7. Return back to the linked table, and then select the Refresh All option from the Refresh drop down.
   
8. Since there is no data in the other company then the linked table will be blank. Select the template data that you saved and then paste it as text into the linked table.
9. This will add all of the data for you so that it matches the template. Although notice that I didn’t populate the Terms of Payment etc. here in this example – that’s only because those tables are not configured, but highlight that you can cleanse the data before pasting it in.
   
10. Now from the Publish ribbon bat menu, select the Publish All option to push the data to the new company.
    
11. If there are no errors, then the table should simply refresh itself. If there are errors in the update then you can check the Dynamics AX Status sheet to see what they are.

How it works…

Now when we look back into Dynamics AX then all of our new data is in the new company.


Once you have created the template worksheet you can then save it away and reuse it at any time. If you really want to be clever then you can create a worksheet that has all of the common data that you need to load and re-use it over and over again.



Importing Complex Data Structures

Not everything is as simple as loading one table at a time though. For more complex data structures like Products, Customers, Vendors, BOM’s etc. you may need to load multiple tables in order to get all of the data to show. You can do this as well through the Excel Add-In, you just need a little more planning.

How to do it…

To use the Excel Add-In to create a template for loading master data within Dynamics AX, follow these steps:

1. As an example, in order to load a product, you need to populate (in this order) a minimum of three tables:
   • EcoResProduct
   • EcoResProductTranslation
   • InventTable
   In this example we were given a raw extract of all the products that we need to import into Dynamics AX which we pasted into our worksheet. Then to create an import template, first link in the tables that you want to load into the worksheet.
   
2. Because the raw data is not in the format that we really want to in we also created a new Product Data sheet, and then massaged the data a little to tidy up the information.
   
3. Each of the tables that we need to load will be in their own sheet within the worksheet.
   
4. We use the column headings that we have selected to populate for the sub-tables as a template column in the Product Data worksheet and then use formulas to map the fields.
   

How it works…

Now it’s just a matter of copying the data in blocks from the Product Data sheet over to the linked tables. And then selecting the Publish All option from the Dynamics AX ribbon bar. You can reuse this template once it’s created to load masses of data without even breaking a sweat.


Using Matrix Fields to Update Multiple Records

There is another neat feature that available with the Excel Add-In which allows you to create new Matrix Fields. These are query fields that allow you to aggregate data and show it as one value, making some data updates even simpler. You can use this for summing data, and also segregating data into groupings such as period ranges.

How to do it…

To use the Matrix Fields option to created total and period based aggregate fields, follow these steps:

1. For example, forecast data is managed within Dynamics AX at a calendar date level.
   
2. If we look at this information through Excel, then it is not really conducive to updates because it is linier rather than the normal tabular view that most people want to work with.
   
3. To aggregate the data, right-mouse-click on the table name within the field explorer and select the Create Matrix Field option.
   
4. This will open up a dialog box that allows you to create a new field based on an existing field.
   
5. Also you can filter the data so that you only select certain groups of values. This is useful because you can create period based fields that only apply to certain dates within the data selection.
   

How it works…

To use the Matrix Field, just create a new worksheet, and select the fields that you want to summarize to, and then add the matrix field.


When you refresh the data, then you will see the matrix field summing up all of the child values.


If you update any of the values, then it will prorate the changes over all of the related table records.

If you take this to the next level, you can then create Matrix Fields for periods, and then use it in this case to forecast by date ranges.


Importing & Exporting Data Through Document Services

There is one additional way that you can query and publish data with the Excel Add-In, and that is through the Document Services that are published within Dynamics AX. This is a little different from the table links, because it is accessing the data through classes, allowing you to update more than one table at a time, but also requires a little bit of coding in order to get new services configured. Luckily, there are some services that have already been deployed with Dynamics AX that you can use this on.

How to do it…

To use the Data option within the Excel Add-In to query and publish data, follow these steps:

1. Open the Document data sources menu item within the Document management folder in the Setup group of the System Administration area. This will list out all of the registered document services that you are able to use within Dynamics AX.
   
2. To use these with Excel, select the Add Data option from the Add Data button of the Dynamics AX ribbon bar.
   
3. When the Add Data dialog box is displayed, you should be able to see the same data sources.
   
   Note, some of the data sources have pencils in the icon, this indicates that you can publish the data through them – otherwise they are just read only.
   
4. The data services work exactly the same way as the tables, by showing you all of the available fields that you can add to the sheet.
5. Just select the fields that you want to use, and drop them onto your sheet.
   

How it works…

When you switch to edit mode, you can refresh the data, and then retrieve all of the records.


If you want, you can also create Matric Fields as well to reformat the way that the data is displayed.



Summary

In this walkthrough we showed how you can use the Dynamics AX Excel Add-In in a number of different ways to query, and also to publish data. It is a great tool to use to create update templates and also to make a lot of periodic update tasks a lot easier.




Источник: http://atinkerersnotebook.com/2013/0...-excel-add-in/