Dynamics AX 2012 Excel Add-in - Replacement Keys

Dynamics AX 2012 Excel Add-in – Replacement Keys

Purpose: The purpose of this document is to illustrate options how to change Dynamics AX 2012 Data Model by adding appropriate Replacement keys to make Excel Add-in working to import the data.

Challenge: Data model changes in Dynamics AX related to high normalization and introduction of surrogate keys made import of data more complex. Currently Excel Add-in has a number of limitations as it relates to the Data Model. In order to allow Excel Add-in to interpret Surrogate key relations between tables correctly the specification of Replacement key (Natural key) is required.

Solution: Dynamics AX 2012 ships with close to 4500 tables. Some tables were designed and implemented for previous releases; some of them were designed and implemented for the current release. “New” tables leverage inheritance, Surrogate keys and relations based on Surrogate keys when “old” tables still have Natural keys and relations based on Natural keys. Please also note that for all newly created tables relations between tables in Dynamics AX 2012 should be specified on table level, at the same time some relations are still specified on “old” EDT’s in the system.

The example of “new” tables would be AgreementHeader and AgreementLine tables. AgreementLine table is linked with AgreementHeader table by Foreign key which is Surrogate key based.

The example of “old” tables would be LedgerJournalTable and LedgerJournalTrans tables. LedgerJournalTable table has Primary key based on JournalNum field (Natural key) and this JournalNum field is used to link LedgerJournalTable and LedgerJournalTrans tables together. That’s why in this case there’s no need to tweak the Data Model to make Excel Add-in working.

Data Model:

Table Name	Table Description
AlexTable	Sample header table
AlexLine	Sample lines table

Data Model Diagram:

Sample Data Model

Walkthrough:

Connection

Add Tables

Dynamics AX Error

Header AlexTable table

Solution – Option 1 (this is what I implemented in this example): Use existing field(s) which will form unique Natural key

- Add Replacement key to AlexTable table

Solution – Option 2: Introduce brand new field(s) which will form unique Natural key

- Add Replacement key to AlexTable table

Dynamics AX Error

Lines AlexLine table

Solution – Option 1: Use existing field(s) which will form unique Natural key

- Add Replacement key to AlexLine table

Solution – Option 2: Introduce brand new field(s) which will form unique Natural key

- Add Replacement key to AlexLine table

Solution – Option 3 (this is what I implemented in this example): Create unique Replacement key based on one of the existing fields and RecId field

- Add Replacement key to AlexLine table

The easiest option is Solution – Option 3 using one of existing fields and RecID field.

If your Replacement key would contain only RecId this would not be different from Surrogate key, RecId adds uniqueness to the index and if RecID is complimented with another non-RecID field then we have unique non-Surrogate key. However sometimes your table which requires Replacement key assignment may not even have fields other than system fields such as RecID or CreatedBy, ModifiedBy, etc. This may happen with tables participating in table inheritance. In this case new field may be added to the table. Then you can implement Solution – Option 2 with brand new field – ImportID in my examples. Please note that your unique index will be based on this field which means that you will have to populate this field with unique values for all existing records if any. The easiest way is to write X++ job and populate this new field (String) based on RecId values in the same table. Going forward unique values for that field will have to be provided in Excel Add-in interface explicitly.

Solution – Option 1 will work only if you already have existing field or combination of fields in the table which could be your Primary key. However this approach is not universal because all depends on the purpose of particular fields in the table.

Project

AlexTable table

AlexLine table

Once Replacement key is added on AlexTable table Excel Add-in will be able to interpret Surrogate key relation between AlexTable and AlexLine tables using Natural key (Replacement key).

Query

Generate AIF Proxy classes

Select Document Parameters

Select Code Generation Parameters

Completed

Project AxdAlex

Generate Incremental CIL

If you encounter this error

please delete cache* methods

Field Chooser

This is how Field Chooser will look like if you use Index1 = {ID} as Replacement key in header AlexTable table. Please note that ID field in AlexTable table is used for Replacement Key <-> Surrogate Key translation

This is how Field Chooser will look like if you use Index1 = {FieldA, FieldB} as Replacement key in header AlexTable table. Please note that the combination of fields FieldA and FieldB in AlexTable table is used for Replacement Key <-> Surrogate Key translation

This is how Field Chooser will look like if you use Index1 = {ImportID} as Replacement key in header AlexTable table. Please note that ImportID field in AlexTable table is used for Replacement Key <-> Surrogate Key translation

This is how Field Chooser will look like if you use Index1 = {FieldA, RecID} as Replacement key in header AlexTable table. Please note that FieldA field in AlexTable table is supposed to be used for Replacement Key <-> Surrogate Key translation

However if Replacement key for header AlexTable table contains RecID then you will get an exception when you try to import lines into AlexLine table.

Conclusion: All Replacement key Options including [existing field(s) + RecID] are applicable for lines (derived) AlexLine table. And all Replacement key Options excluding [existing field(s) + RecID] are applicable for header (main) AlexTable table.

AlexTable

Field Name	Field Description
	ID
	A
	B

AlexLine

Field Name	Field Description
	AlexTable.ID
	C
	D

Sequence:

1. AlexTable – Publish Selected

2. AlexLine – Publish Selected

Result:

Dynamics AX – Table Browser

AlexTable

AlexLine

SQL Trace:

Limitations:

Document Services

Document Services are based on a query that defines their structure. Only certain query structures are supported. The following are the restrictions on queries that form the basis of document services. Services which violate these rules may be added to the document data sources form by the administrator, but will result in errors when accessed in the add-in. For this reason it is important that developers and administrators test services for use with the add-in prior to adding them to this form.

· Replacement Keys: The root level of the document service (the parent data source) must have unique indexes other than Rec-ID. This may be in the form of a non-RecID Primary Index or a Replacement Key.

· Related Replacement Keys: Each field within the service which is a RecID-based foreign key must relate to a table that has a replacement key specified.

· Relationship Direction: When parent-child relationships exist in the underlying query associated with the service, only relationships originating on the child element, and pointing to the parent may be used. For example, in the CustCustomerService, the CustTable parent data source holds a foreign key to the DirParty child data source. This pattern is not supported in the Excel Add-in.

· Query and Service consistency: Document services are based on an underlying query which defines the data contract used in the service at the time that the service is generated. The Excel Add-in uses this query definition to perform read operations when refreshing data into the workbook. Because of this, any overrides to the read method, or extension of the schema beyond what is in the underlying query will not be reflected in the service.

· View Support: Views may be used within document services to provide an easier to use data model for end users. However, the PrepareForXXXExtended methods must be implemented to properly handle information sent to the service within the views. Views may only be used as the “leaf” level node in the underlying query. For instance if there is a query “Parent, child, grandchild”, then only the grandchild node can be a view.

Only the following services shipped in Microsoft Dynamics AX 2012 are supported without modification in the Excel Add-in. Additional services meeting the above requirements can be constructed in order to extend the scenarios where excel can be used to update, create, and delete business data in Dynamics AX.

· BudgetTransaction

· EMSMeterReading

· EMSSubstanceFlow

· GeneralJournal

· ProductionPickingList

· ProjectHourJournals

· SysImportBusSector

· VendGroup

· VendRequestSignup

Supported tables

Not all tables presented in the “Add tables” dialog may be used with the Excel add-in, rather only those which meet the following requirements:

· Visible Identity: There must be a unique index on the table which does not contain RecID as a component. This may be either the “Replacement Key”, or the “Primary Index”

· Valid References: All relations in the relations collection for the table that refer to other tables via Rec-Id must be related to tables that have a “Replacement Key” specified.

Summary: In order to make Excel Add-in working to import the data the specification of Replacement key is required. Excel Add-in uses Replacement key to interpret Surrogate key based relations between tables. There’s multiple options how to change the Data Model and introduce Replacement key to the table: use existing field(s) which will form unique Natural key, introduce brand new field(s) which will form unique Natural key or create unique Replacement key based on one of the existing fields and RecID field.

Author: Alex Anikiev, PhD, MCP

Tags: Dynamics ERP, Dynamics AX 2012, Excel, Dynamics AX 2012 Excel Add-in, Data Import, Data Conversion, Data Migration, Application Integration Framework, Table Inheritance, Surrogate Key, Natural Key, Replacement Key.

Note: This document is intended for information purposes only, presented as it is with no warranties from the author. This document may be updated with more content to better outline the concepts and describe the examples. It’s recommended that all Data Model changes introduced as a part of this demonstration will be removed once you complete data import exercise.