Table Relations in AX 2012

What is Table Relation ?

A table relation associates two tables that contain related information. Usually the primary key field of one table appears as a foreign key field of the related table. The table with the primary key is called the parent table. The table with the foreign key is called the child table.

For example, a parent table named Department can have a departmentId field as its primary key. A child table named Employee can have a departmentId field as a foreign key.

Detail Explanation :- https://msdn.microsoft.com/en-us/library/aa675395.aspx


The foundation of Microsoft Dynamics AX is the relational database. Relationships can be created between tables that contain related data. In Microsoft Dynamics AX, the relationship between tables is called a relation.

Relations in Microsoft Dynamics AX:

• Keep the database consistent (enforce referential integrity).
• Are used by the Auto Join system in forms.
• Enable the look up of values in other tables (through lookup list/selection list boxes and the View details Form command).
• Are used to validate data.
• Automatically propagate changes from one table to another.
• Auto-define table relationships in queries.

Table relations are most commonly used in form fields to enable the look up of information in another table. If a table relation exists, the lookup button can be used to display a lookup list of values for a particular field.

A table relation is created by specifying one or more fields in a table that contain the same value in a related table. The matching fields typically have the same name in each table.

For example, a SalesOrder table containing orders might have a field called SalespersonID, which identifies the salesperson that took the order. The Salesperson table, containing the names of sales people, would also have a field called SalespersonID.

To create a table relation, specify that the SalesOrder.SalespersonID field is related to the Salesperson.SalespersonID field.

Source :- https://msdn.microsoft.com/en-us/library/bb190076.aspx

Adding a Relation to a Table :-

1. In the AOT, move to Data Dictionary > Tables, and then expand the table that the relation will be added to.
2. Right-click the Relations node, and then select New Relation.
3. Right-click the newly added relation, and then select Properties.
4. Set the name of the new relationship by modifying the Name property.
5. In the Table property, select the related table.
6. Use the Validate property to determine whether the relation should be used to validate data when information is entered into forms.
7. Right-click the new relation, select New, and then click one of the following:

• Normal to specify relation fields without conditions.
• Field fixed to specify relation fields to restrict the records in the primary table.
• Related field fixed to specify relation fields that restrict the records in the related table.
• ForeignKey to specify a correspondence between a foreign key field in the present table to the primary key field in another parent table.

Source:-https://msdn.microsoft.com/EN-US/library/aa556809.aspx

Conditional Table Relations :-

Define conditional table relations to filter the records in either the primary or the related table. Following are the conditional table relations that can be specified when you define the fields in a table relation:

• Field fixed
• Related field fixed

You create a conditional relation by right-clicking the  AOT > Data Dictionary > Tables > YourTable > Relations > YourRelation node. Then click either Field fixed for Related field fixed.

Understanding the Conditions :-

Field Fixed :- Table.Field == <EnumValue>

Restricts the records selected in the primary table. Only records that meet the condition are selected.

The condition is ANDed with your relation.

Related field fixed :- (<EnumValue> == Table.Field)

Restricts the records selected in the related table. Only records that meet the condition are selected.

The condition is ANDed with your relation.

Source :- https://msdn.microsoft.com/EN-US/library/bb190115.aspx

ForeignKey Relation :

ForeignKey to specify a correspondence between a foreign key field in the present table to the primary key field in another parent table.

There are 2 ways of creating foreign key relation:

• PrimaryKeyBased- Primary key is the unique index specified in the Parent table PrimaryIndex property. It can consists of 1 or more fields

• SingleFieldAlternateKeyBased- Alternate key is an unique index specified in the Parent table, with its AlternateKey property set to Yes. However, to be eligible for use for creating foreign key relation, The alternate key can only consist of a single field.

Normal Relation :

To specify relation fields without conditions.