Normal Relation in d365 with Example

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.

A relation on a table can restrict the rows in the table or restrict the values that can be 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.

In Microsoft Dynamics 365 there are four types of relations, Normal Relation, Field Fixed Relation,  Related Field Fixed Relation, and Foreign Key Relations. You can read more about table relations from the article Table Relations in D365.

In this article, I am only explaining Normal Relations in D365 FO with a simple example, the other relations which we will explain in the following articles. So let us start the topic.

What is the Normal relation in d365?

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.

Condition is, Table 2.Field = Table1.Field

where Table1 is the parent Table and Table2 is the child table.

How to do this...

In this example, we are creating two tables Product Category Table and Product Table. The product Category Table is named as PrdCategoryTable, which stores the product category details of the products, and the Product Table is named as PrdTable, which stores the details of the products, 

Here the table PrdCategoryTable is called the parent table because PrdTable refers to the table PrdCategoryTable. The table with keeps the relation to the parent table(PrdCategoryTable) is called the child table. here PrdTable is the child table. You can read more about table creation in d365 from the article Learn How to Create a Table in D365 FO.

Product Category Table

The product Category Table contains three fields, CategoryId, CategoryCode, and CategoryName as shown in the below figure. where the primary key is the CategoryId.

Product Table

The product Table contains three fields, PrdId, PartNumber, Description, and CategoryId as shown in the below figure. where CategoryId is the foreign key or relationship key which refers to the CategoryId field of the PrdCategoryTable table.

How to add a relation to a table in D365

To create a relation in d365 fo, after creating the tables follow the below steps,

Steps 1: Right-click the Relations node, and then select New Relation. 

Here we are adding a relation to the product Table PrdTable. Right-click the Relations node of the table PrdTable, and then select New Relation as shown in the figure.

Step 2: Right-click the newly added relation, and then select Properties as shown in the figure.

Then,

Step 3: Set the name of the new relationship by modifying the Name property. here I put the name CategoryId.

Step 4: Change the Cardinality Property to ZeroMore.

Cardinality : is the property that explains about nature of the relationship between two tables on the related table.

ZeroMore: You will select this, provided the child Table/related table can either have no record or have more related record.

Step 5: In the Table property, select the related table. in our example PrdCategoryTable.

Step 6: Change the Related Table Cardinality Property to ExacltlyOne.

ExacltlyOne : which shows the Cardinality of the related table, which  means table PrdTable refers PrdCategoryTable. so in the table PrdCategoryTable, exacltly one record should exist agains multiple record in the child table.

Once you done all this steps, 

Right click on the relation CategoryId > New > Normal as shown below figure.

One normal relation node will be created under the CategoryId node as shown in the below figure, right click and select the property.

Change the proprty as shown in the below figure,

Now the steps are over. you can see the relation created in the below figure.

Build and run the project, 

Right-click on the PrdCategoryTable and select Open Table Browser. Insert some dummy data in the PrdCategoryTable as shown in the figure.

Similarly, Right-click on the PrdTable and select Open Table Browser. Insert some dummy data in the PrdCategoryTable as shown in the figure.

Here you can see a lookup which is listed all the CategoryId field values which were stored in the product in the PrdCategoryTable. Select the required value from the CategoryId column and fill all the other columns as shown in the figure.

I think this article will be very useful for beginners and if the article is useful, please try to share it with your friends who are planning a career in D365 FO. In the next article, I will show you how to add Filed FIxed Relationship to a table. So Happy coding with D365Snippets.