Understanding Table Relations in D365 F&O - A Complete Guide

What are Tables Relations?

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 departmental field as its primary key. A child table named Employee can have a departmental field as a foreign key. All values in the foreign key field must exist in the primary key field of the parent table. Any value in the foreign key field can be used to find the parent row that contains the matching primary key value. Then any other field in the parent row can be accessed.

Use of table relations

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 with 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.

Example of Table Relation

A PurchaseOrder Table contains all the supplier orders and might have a field called VendId, which keeps the value of the associated Vendor Id for the purchase order. The VendTable contains all the vendor records and has a filed name VendId.

Here VendId in the VendTable is called as primary key, and VendId in the PurchaseOrder Table is called the foreign key. This is a simple example of table relations. 

To create a table relation specifies that PurchaseOrder.VendId=VendTable.VendId

Table Relations Types in D365

1. Normal
2. Field fixed relation
3. Related field fixed relation
4. Foreign key relation

How to add a relation to a table in D365

• In the AOT, move to Data Dictionary > Tables, and then expand the table that the relationship that will be added.
• 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 relationship 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.

1. Normal

A normal relation is used to specify a relationship without any conditions. A normal relation specifies related fields in another table. Multiple fields can also be added in normal relations. you can read more about the Normal Relation in D365 from the article Normal Relation in the Article with Example

Condition is, Table1.Field = Table2.Field

When we want Many to Many (M:M) Relationship then we use Normal relation.

2. Field Fixed

A Field Fixed Relation is used to specify relation fields to restrict the records in the primary table. Only records that meet the condition are selected. The field fixed is normally an enum.

The condition will bw look like below

Table.Field = <EnumValue>

Relations:
    Cricketer:
        TblPlayers.EnumPlayerType == 0
        TblPlayers.PlayerCode == TblCricketers.CricketerCode
     Footballer:
        TblPlayers.EnumPlayerType == 1
        TblPlayers.PlayerCode == TblFootballers.FootballerCode

Read More: Field Fixed Relation in D365 with Example

3. Related Field Fixed

A Field Fixed Relation is used to specify relation fields to restrict the records in the related table. Only records that meet the condition are selected. The field fixed is normally an enum. The field fixed is normally an enum. 

The condition is ANDed with your relation here.

<EnumValue> = Table.Field

4. Foreign Key

A Foreign Key Relation is used to specify a correspondence between a foreign key field in the present table to the primary key field in another parent table.

When we want 1 to Many (1:M) Relationship then we use Foreign Key relation.