How to: Add a new relation for a new field to the same table to which a relation already exists [AX 2012]
In Microsoft Dynamics AX 2012, the EDT relations were moved to tables and the creation of a new EDT relation is not possible anymore. This implies that only table relations can be added to the system.
In special cases, additional relations need to be added to tables for new fields that reference the same table to which a relation already exists. Consequently, this will be a table relation.
In the presence of multiple relations on the same key field(s), dependent objects which use the modified table as data source, and are designed to pick a relation automatically, may be impacted simply by the choice of the name for the new relation. This may happen because the system is designed to pick the alphabetically first relation by name. The algorithm is as follows:
- Get all relations from the foreign key table.
- Find relations that match the condition and the EDTRelation property of the relation is No; pick the first one.
- Find relations that match the condition and the EDTRelation property of the relation is Yes; pick the first one.
Choosing a name for the new relation that will appear behind any existing relation to the same table and key field(s) in this situation is essentially important in order to retain the existing standard and added functionality of dependent objects.
Objects that depend on tables as data sources:
- AOT Queries
- AOT Forms
- AOT Views
- Queries in Forms
- Queries in Views
- Views in Queries
- Views in Forms
- Queries in code
- Forms in code
Two scenarios are presented in the following examples in which special caution needs to be exercised when a new relation to the same table for a new field needs to be created.
- One or more table relations already exist in the standard.
- One or more migrated EDT relations already exist in the standard.
Example 1.
The first example demonstrates the addition of a new table relation for a new field of the table CustTable that references the table DirPartyTable to which a table relation from the CustTable table already exists in the standard on the Party field.
Existing relation: DirPartyTable_FK on the field Party
In the standard, the DirPartyTable_FK relation is picked automatically in dependent objects which use the CustTable and DirPartyTable tables as data sources.
Added relation: ZZZ_CustParty on the new field AAA_Party
While the new AAA_Party field is defined as follows:
The original automatic relation DirPartyTable_FK is kept in the standard query that has the CustTable and DirPartyTable tables as data sources.
Example 2.
To achieve the same expected behaviour when adding a table relation to the same table to which only EDT relation(s) already exist in the standard it may be not enough to just choose a name for the relation that will follow the name of the EDT relation picked in the standard so far. That is because, according to the algorithm, the new table relation will be picked by the system for dependent objects regardless of the name it has. The algorithm considers the table relations in the first line, and only if none is found, the migrated EDT relations will be considered.
The obvious solution in this case one could think of could be creating an EDT for the new field and creating a relation for it. This is not possible in Microsoft Dynamics AX 2012 though. The solution in this specific case is to configure the added table relation to behave as a migrated EDT relation by setting the EDTProperty to Yes.
The second example demonstrates the addition of a new table relation for a new field of the table CustTrans that references the table CustTable to which an EDT relation from the CustTrans table already exists in the standard on the AccountNum field.
Existing migrated EDT relations: CustTable_AccountNum on the field AccountNum, CustTable_LastSettleAccountNum on the field LastSettleAccountNum, CustTable_OrderAccount on the field OrderAccount
In the standard, the CustTable_AccountNum relation is picked automatically in dependent objects which use the CustTable and CustTrans tables as data sources.
Added relation: ZZZ_CustTable on the new field AAA_AccountNum
While the new AAA_AccountNum field is defined as follows:
The original automatic relation CustTable_AccountNum is kept in the standard query that has the CustTable and CustTrans tables as data sources.
In Microsoft Dynamics AX 2012, all EDT relations were migrated to table relations. The system does not look at the EDT relations anymore. The EDTRelation property flags relations that were migrated from EDT relations. Adding a new table relation this property can be used to make the new relation to be treated by the system as a migrated EDT relation.
References:
Migrating Extended Data Type Relations (White paper) [AX 2012]
https://technet.microsoft.com/en-us/library/hh272870.aspx
