Sunday, February 28, 2016

Export/Import data in Microsoft Dynamics AX RTW using Excel data connector

In my last post I coved Static export to Excel. In this post I will show you Dynamic Export to Excel, Similar to Excel add ins in AX 2012. In this type of Excel, you can do the CRUD operations in Excel and get the results in AX or vice-versa.

In AX7 RTW, this is enabled by using Excel Data Connector App. It uses same Data entities which are used by Data management. Excel Data connector app is based on Apps for office framework that provides JavaScript-based web API for Apps to communicate with office application. The biggest advantage of using this framework is that apps can run inside Excel on premise, Excel online, Excel on the iPad, and other Excel apps in the future.

I will take the example of Customers. Navigate to All customers form and click on Open in Excel for customer entity.

As you see in the above screenshot, There are 2 options in Open in Excel. It is because there are 2 data entities defined on the CustTable. The system will look at the root data source for the form and then look for entities that have a matching root data source. All those entities are listed under the Open in Excel menu.  To check this I created a custom data entity named: VINCustDataEntity.

Data entity have given a one base platform for the all communication between AX and outside world which is far better than any previous AX version.

Once I click on the Customers, I get the flyout for download. Click on Download and Excel sheet will open with data connector app in Excel.

As you can see in above screenshot, Data connector app shows the source entity and field names on top. On bottom following options are show:
New: create a new row.
Refresh: Read the data from AX and update the data in Excel
Publish: Read the data from excel and update the data in AX
Filter: Add/remove any filter on data entity
Design: This option lists all the entities available in AX. Using this entities, fields in excel workbook can be configured.

At the end you have 3 icons for settings, messages center and user menu.

Settings option looks like below:

If you update the data in excel and publish it, AX will be updated with the data in excel. If you get any error while uploading, row with error will be highlighted.

1 comment:

  1. How do you fix/troubleshoot the errors you encounter after you update the data and click publish like the error in your screenshot