How to: Add a Relation to a Table

Article
05/18/2015

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

You add relations to a table by using the Application Object Tree (AOT).

A relation on a table can restrict the rows in the table, or restrict the values that can be in particular fields. A common use of relations is to associate rows in one table to corresponding rows in another table. Relations enable many forms to display data from multiple tables.

Some relations restrict the rows in the table by testing the values in each row against constant values. Other relations restrict the rows by comparing values in each row to values in a row in another table.

Add a Relation to a Table in the AOT

The initial steps for adding a relation are the same regardless of the relation type that you are adding. The later steps diverge based on the relation type.

Remember to save your changes in the AOT.

In the AOT, move to Data Dictionary > Tables, and then expand the table that the relation will be added to.
Right-click the Relations node, and then select New Relation.
Right-click the newly added relation, and then select Properties.
Set the name of the new relationship by modifying the Name property.
In the Table property, select the related table.
Use the Validate property to determine whether the relation should be used to validate data when information is entered into forms.
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.
  
  Note
  
  For this option to function, you must first set certain properties on the new relation. A following section explains more about this option.
Proceed to the subsection that corresponds to the relation type that you selected in the earlier step.

Define the Relation Fields

Now set the properties on the new relation you created under the Relations node.

Normal Relation

In the Field property, select the field in the primary table that relates to a field in the present table.
In the RelatedField property, select the field in the related table.

Field fixed Relation

In the Field property, select the field in the primary table to use to restrict the records.
In the Value property, enter the value of the selected field as the filter. This relates only records in the primary table that match that field value. Only numeric values can be entered in the Value property. Field fixed relations can be created only on numeric fields. Each of the related fields are AND'ed in the table relation.

Related field fixed Relation

In the Value property, enter the filter value of the selected field. This causes only records in the related table that match that field value to be related. Only numeric values can be entered in the Value property. Related field fixed relations can be created only on numeric fields.
In the Field property, select the field in the related table to restrict the records. Each of the related fields are AND'ed in the table relation.

ForeignKey Relation

We often use the term child to refer to a table that has a foreign key column. And we use the term parent to refer to the other table that supplies the value for the foreign key column. But we never use the terms parent and child to describe the base and derived tables in an inheritance relationship.

Set the Table property to the name of the parent table, the table that contains the primary key field.
Set the RelatedTableRole property to a word or phrase that describes the purpose of the parent in the relationship.

Note

This value is added automatically as a method name on the child table. This method is displayed by IntelliSense in the X++ editor, but you cannot see the method in the AOT. For more information about how to use this method, see How to: Use the UnitOfWork Class to Manage Database Transactions.
Set the Name property. A helpful value is a combination of the Table property and RelatedTableRole property values.
Right-click the node for your relation, click New, and then click ForeignKey. Next click either PrimaryKey based or Single field AlternateKey based. A new field is instantly added to the child table. This field stores the foreign key values.
Under the Fields node, click the new field, and then change its Name property value.
For performance benefits, you might decide to add an index on the new foreign key field.