D365 Snippets & Power BI

In the previous article, I already explained the normal relations in d365 finance and operations. In this article I will explain field fixed relation in D365 is f&o. 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.

You can read more about table relations from the previous articles.

Read More: What is table relation in d365.

Read More: What is Normal relation and how to in d365with example.

Field Fixed Relation

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 is ANDed with your relation here.

Table. Field = <Enum Value>

How to do it…

Here I am showing one simple example which should help you to understand the concept easily. 

For this, I have created one EDT named EDTPlayerType with two elements Cricketer(0) and Footballer(1) as shown in the figure. We use the same EDT to create the PlayerType field of the below three tables.

Read More: What is an EDT  and how to create an EDT in D365

EDTPlayerType

Once you created the EDT and elements, change the label property of the element cricketer to cricketer and footballer to footballer respectively. If you did not set the label property to the EDT elements, the column PlayerType lookup will show blank.

After creating EDT, we need to create three tables TblPlayers, TblFootballers, and TblCricketers. 

TblCricketers

TblFootballers stores Player Type, footballer code, and salary. Here the field EDTPlayerType is an EDT(EDTPlayerType) that we already created, you can drag and drop the EDT for creating the EDTPlayerType field in the Footballer Table. The other two columns FootaballerCode and salary fields are the string data type.

Read More: How to create a table using EDT.

TblFootballers

Similarly, TblCricketers stores Player Type, cricketer code, and salary. Here the field  EDTPlayerType is an EDT(EDTPlayerType) that we already created, you can drag and drop the EDT for creating this EDTPlayerType field in the Footballer Table and the other two columns CricketerCode, and the salary fields are of type string.

TblPlayers

TblPlayers stores the player name, player code, and player type. Here the PlayerType is an EDT that we already created and the player code is referred from TblFootballers or TblCricketers., you can drag and drop the EDT for creating the EDTPlayerType field in the TblFootballer Table. And PlayerCode and PlayerName fields are of type string.

Now we have completed all the basic steps to create a field fixed relation, here we have two parent tables TblFootballers and TblCricketers, and one child table TblPlayers. EDTPlayerType field of three tables is created by using EDT EDTPlayerType.

Now we can fill some dummy data in the tables TblFootballers and TblCricketers. for this right click on the table TblFootballers and Open Table browser, which will open TblFootballers in the browser.

You can fill in some dummy data as shown in the below figure.

Similarly, right-click on the table TblCricketers and Open Table browser, which will open TblCricketers in the browser. You can fill in some dummy data as shown in the below figure.

You can fill in some dummy data as shown in the below figure.

Now For Player Table, if we select PlayerType = “Footballer “and then on the “PlayerCode” field on the TblPlayers Table, lookup should open showing the records from TblFootballers only.

Similarly, with the case of the cricketer, if we select PlayerType = “Cricketer “then on the “PlayerCode” field on the TblPlayers, the lookup should be open showing the records from TblCricketers only. To achieve this, we can use the relation called ‘Field Fixed ’.

for this, we need to create a field fixed relation and a normal relation to list the player code in the TblPlayers based on the PlayerType chosen by the user.

Add a Relation

Right-click on the relation node of the table TblPlayers, then select new relation as shown in the below path.

TblPlayers > Relations > New > Relation

This will create a relation with the default name “Relation1”.

Rename “Relation1” to “Cricketer” as shown in the figure.

Then right-click on the Relation Cricketer and select new and then choose Field fixed as shown in the below path.

Cricketer > New > Field fixed.

It will create a default field fixed relation, and we need to assign the field property and value property of the field fixed relation . in our case EDT is EDTPlayerType and the value is 0. because '0' is the value of the element Cricketer.

Here Base Enum 0 = Cricketer (please see EDTPlayerType where Cricketer (0) and Footballer (1)), Now create a new record in Player Table: It’s only showing the lookup of Cricketer code which has Player type = Cricketer.

Also, you need to create one more normal relation to assigning the value of player code to the TblPlayers based on the PlayerType chosen by the User.

For this,

Then right-click on the Relation Cricketer and select new and then choose Normal as shown in the below path.

Read More: Learn how to create a Normal relation in D365.

Cricketer > New > Normal. 

Similarly, you can create a relation Footballer and create a filed fixed relation and a normal relation to the table TblFootballers as shown in the figure.

Where Base Enum 1 = Footballer

Now all the basic setup has been completed. Our project structure will be as shown in the below figure.

Build the project and open table TblPlayers by right-clicking the TblPlayers  and  Open Table Browser menu,

The column EDTPlayerType lists two player types, cricketer and footballer. when the user selects the type "cricketer", the column "PlayerCode" only populates the CricketerCode from TblCricketers. 

Similarly,  when the user selects the type "footballer", the column "PlayerCode" only populates the FootballerCode from TblFootballers.

This means if we select Player Type = “Footballer “and then on the “PlayerCode” field on the Player Table, lookup should open showing the records from TblFootballers only.

Similarly, with the case of the cricketer, if we select Player Type = “Cricketer “then on the “PlayerCode” field on the Player Table, lookup should be open showing the records from TblCricketers only. 

How it Works...

The fixed field says that the specified normal relation (on TblCricketers ) to TblPlayers only works if the PlayerType of the current record is set to 0 (Cricketer) else the relation is disabled.

Similarly, the specified normal relation (on TblFootballers ) to TblPlayers only works if the PlayerType of the current record is set to 1 (footballer) else the relation is disabled. 

so if a user selects Player Type = “Footballer “and then on the “PlayerCode” field on the Player Table, lookup should open showing the records from TblFootballers only.

Similarly, if the user selects Player Type = “Cricketer“ then on the “PlayerCode” field on the Player Table, lookup should be open showing the records from TblCricketers only. 

This is all about filed fixed relation in d365 fo. if the article is useful to you, please try to share it with your friends, if you have any doubts about this article please comment in the comment box. So happy coding with D365Snippets.

Some Useful Queries

Example of field fixed relation in D365
Example of field fixed relation Dynamics 365
What is the difference between field fixed and related field fixed?
How do I create a relationship between two tables in D365?
What are the different types of relationships in Dynamics 365?
What are table relations in D365?
table relation properties in d365fo
example of field fixed relation in ax 2012
how to create relations between two tables in d365

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.

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.

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.

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.