AX Technical Tip: Understanding Best Practice Errors on Table Relations

AX Technical Tip: Understanding Best Practice Errors on Table Relations

Insights from: Chris Jamison (Fullscope Solution Architect)

Microsoft Dynamics AX 2012 uses table relations to indicate relationships between different tables by linking a field or fields from one table to the same field or fields in another. This can be used, for example, to implement parent-child relationships such as the sales order header and its order lines.

Having table relations in place facilitates some automatic functions within the client user interface (UI). For example: field value selections from a drop-down list of valid values, automatic validation of values entered in the UI form fields, and automatic linking of parent-child records in a UI form.

When developers are customizing existing tables or creating new ones, table relations are frequently part of the new functionality built for the custom requirement. During the development process, the new code will be compiled and checked for best practices (BP). At this time, BP errors will be generated if the table relations are not properly built by the developer.

And this is where “what you didn’t know” can lead to confusion and ambiguity. The problem is that the BP errors displayed in the compiler output don’t seem to indicate where the error is.

Table relations are defined on a table entity in the Application Object Tree (AOT). Figure 1 shows the definition of 5 relations for a table. The first two relations, CustTable and InventTable relate fields from the table, FSMasterTable, to existing Microsoft Dynamics AX tables of the same name.

Figure1-AOT-table-relation

Figure 1: AOT Table Relation Definitions on the Master Table

Three additional relations are added (Lookup1, 2 and 3) between the “CodeId” field and another custom table, FSLookupTable. The FSLookupTable is a simple “code and description” table common to AX (Figure 2).

Figure2-AOT-field-definition

Figure 2: AOT Table Field Definitions for the Lookup Table

The three relations, Lookup1, 2 and 3, are created to illustrate different ways to create a table relation, and highlight how BP errors can be interpreted and fixed (Figure 3).

Figure3-AOT-table-relation-fields

Figure 3: AOT Table Relation Field Reference Definitions on the Master Table

The Lookup1 table relation uses a RecId reference as the relation field (Figure 4). Relations 2 and 3 use the CodeId field, and appear identical when their properties are viewed (Figures 5 and 6).

Figure4-Lookup1-properties

Figure 4: Relation Lookup1 Properties

Figure5-Lookup2-properties

Figure 5: Relation Lookup2 Properties

Figure6-Lookup3-properties

Figure 6: Relation Lookup3 Properties

When the FSMasterTable is compiled and checked for BP errors, AX finds and displays an error (Figure 7). An initial read of the BP error message does not seem to indicate which of the 5 relations is causing this “Only foreign key constraints…” error.

Figure7-Compiler-output

Figure 7: Compiler Output Showing Best Practice Error on a Table Relation in the Master Table

The key piece of information is found in the Line column. This column is usually used to indicate a line of X++ code that is in error. But “what you didn’t know” is that for table relations, the line number refers to which table relation is causing the BP error. In the example, it is the 5th table relation, Lookup3 (Figure 6).

As already observed, the table relations Lookup2 and Lookup3 appear identical in their definition and properties. Clearly something is different between the two, and is causing a BP error for Lookup3 but not for Lookup2.

A second “what you didn’t know” is that additional properties for the table relation can be found in the exported .xpo file for the FSMasterTable. When the FSMasterTable is exported to an .xpo file, it is possible to see the original selections made when defining the field reference properties for CodeId in the table relation between the FSMasterTable and FSLookupTable.

It is useful to examine each of the three Lookupn relations in the .xpo file to see how the original table relation and field reference properties are stored by AX.

Lookup1 was added as a “foreign key> primary key” reference, as indicated below. The ReferenceType “PKFK” stands for Primary key – foreign key.

REFERENCE #Lookup1
PROPERTIES
Name                  #Lookup1
Table                 #FSLookupTable
RelatedTableCardinality    #ZeroOne
Cardinality           #ZeroMore
RelationshipType     #Association
RelatedTableRole     #Lookup1
Role                  #Lookup1
ENDPROPERTIES

FIELDREFERENCES
REFERENCETYPE PKFK
PROPERTIES
Field                 #LookupCodeRefRecId
RelatedField         #RecId
ENDPROPERTIES
ENDFIELDREFERENCES
ENDREFERENCE

Lookup2 was added as a “foreign key> alternate key” reference. This was possible since the FSLookupTable has an index defined on the CodeId field, and this index is set as an alternate key.

REFERENCE #Lookup2
PROPERTIES
Name                  #Lookup2
Table                 #FSLookupTable
RelatedTableCardinality    #ZeroOne
Cardinality           #ZeroMore
RelationshipType     #Association
RelatedTableRole     #Lookup2
Role                  #Lookup2
ENDPROPERTIES

FIELDREFERENCES
REFERENCETYPE PKFK
PROPERTIES
Field                 #CodeId
RelatedField         #CodeId
AlternateKey         #Yes
ENDPROPERTIES
ENDFIELDREFERENCES
ENDREFERENCE

An examination of the definition for Lookup3 will show where the problem lies, and explains the meaning of the BP error “Only foreign key constraints…”

REFERENCE #Lookup3
PROPERTIES
Name                  #Lookup3
Table                 #FSLookupTable
RelatedTableCardinality    #ZeroOne
Cardinality           #ZeroMore
RelationshipType     #Association
RelatedTableRole     #Lookup3
ENDPROPERTIES

FIELDREFERENCES
REFERENCETYPE NORMAL
PROPERTIES
Field                 #CodeId
RelatedField          #CodeId
ENDPROPERTIES
ENDFIELDREFERENCES
ENDREFERENCE

The .xpo file indicates that the Lookup3 field reference on CodeId was added as a “Normal” reference type, rather than as a foreign key. And due to the way the FSLookupTable is defined, this kind of reference is not allowed.  That is why the BP error gets generated.

This example is an obviously contrived one, but is useful to indicate the kind of errors that can be encountered when table relations are incorrectly defined on a table. A real-life scenario would likely be that only one relation is defined between the two tables. If that relation happens to be defined incorrectly (as in Lookup3), and an error is encountered, the solution would be to delete Lookup3 and re-enter the table relation, this time using the foreign key> alternate key definition.

See another AX Technical Tip:  Prevent Headaches with Data Consistency Checks
Would you like this level of knowledge from your ERP partner? Do you need a new Microsoft Dynamics AX partner? If so, contact us today.

Best Practices, ERP, AX Technical Tip, AX ERP Implementation, ERP Implementation