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.

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

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

Read More: What is table relation in d365 .

Field Fixed Table Relations in D365 F&O

Field Fixed Table Relations in D365 - Concept


// Create below relations in tblPlayers

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

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>

Also you have to give a Normal Relation for look up.

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 ENUM 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: This is not a EDT this is a Enum actually, The name of the Enum is EDTPlayerType

Field Fixed Relation in D365 with Example

Once you created the Enum 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 Enum elements, the column PlayerType lookup will show blank.

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

TblFootballers

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.

TblCricketers

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

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

How it Works...

The fixed field says that the specified normal relation (on TblCricketers ) to TblPlayesr 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 TblFootballersonly.

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

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

‹ Previous - Normal Table Relations in D365

Next › - Related Field Fixed - Table Relation - D365 F&O

Field Fixed Table Relations in D365 F&O