Field Fixed Relation in D365 with Example
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.
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
Published on:
Learn more