Relation in Table

    Table Relations Normal Related Field Fixed Field Fixed

In D365 FO

Table Relations (Normal, Related Field Fixed , Field Fixed)

 

Normal Relation: where the table1 relate to table2 with primary id 

Related  Field Fixed Relation: this relation defines where the field is not available and we have to related it from another table where the field is available

Field Fixed Relation: this relation defines where the field is available

We use Field Fixed relation when we have 1:N relation. On certain condition one of the relation become active & other will remain deactivate. Let me use the same theme as MSDN - Clothing.

        Let’s say you have 4 tables

1. ChildernClothesTable

2. MenClothesTable

3. WomenClothesTable

4. ClothesOrdersTable

Create BaseEnum CollectionTypeId with the following elements:

0 - Men

1 - Women

2 - Children

Create Extended DataType CollectionTypeEDT of BaseEnum CollectionTypeId. Now use this EDT in your ClothesTable.  

 

ClothesOrdersTable has the following fields:

1. ClotheId,

2. ClotheName ,

3. CollectionTypeId

4. Qty

 

ChildernClothesTable, MenClothesTable and WomenClothesTable has the following fields:

1. ClotheId,

2. Name

 Example 1:

Related  Field Fixed Relation: 

Case 1: 

On MenClothesOrder we create a new relation to ClothesOrdersTable and specify the following two:

1. Normal Relation:  ClotheId to ClotheId (Best practice to specify this on the EDT)  

2. Related Fixed Field Relation:  0 = ClothesOrdersTable.CollecTionTypeId.

* This shows that the lookup to the clothes table should show only clothes with the same ClotheId AND clothes that are of type Men

* because the table deals with order for mens' clothes.

We use 0 because Men is element 0 in the Enum. 

Case 2:

            On WomenClothesOrder we create a new relation to ClothesOrdersTable and specify the following two:

1. Normal Relation:  ClotheId to ClotheId (Best practice to specify this on the EDT)  

2. Related Fixed Field Relation:  1 = ClothesOrdersTable.CollecTionTypeId.

* This shows that the lookup to the clothes table should show only clothes with the same ClotheId AND clothes that are of type Women

* because the table deals with order for womens' clothes.

We use 1 because Women is element 1 in the Enum. 

Case 3:

            On ChildrenClothesOrder we create a new relation to ClothesOrdersTable and specify the following two:

1. Normal Relation:  ClotheId to ClotheId (Best practice to specify this on the EDT)  

2. Related Fixed Field Relation:  2 = ClothesOrdersTable.CollecTionTypeId.

* This shows that the lookup to the clothes table should show only clothes with the same ClotheId AND clothes that are of type Children

* because the table deals with order for children's clothes.

We use 2 because Children is element 2 in the Enum. 

Example 2:

 Field Fixed Relation:

This kind works the other way round:

Imagine you have a ClothesOrdersTable (generic) and you have seperate tables for MenClothesTable, WomenClothesTable and ChildrenClothesTable. Fixed field says that the specified normal relation (on ClotheId) to MenClothesTable only works if the CollectionTypeId of the current record is set to 0 (Men) else the relation is disabled.

Case 1:

On ClothesOrdersTable we create a new relation to MenClothesTable and specify the following two relations:
1. Normal Relation: ClotheId to ClotheId and
2.  Field Fixed Relation: ClothesOrdersTable.CollecTionTypeId = 0 

and SourceEDT = CollectionTypeEDT

This shows that the lookup to the MenClothesTable should show all clothes with the same ClotheId (Define in Normal Relation) AND clotheId will only bring from MenClothesTable as we are using type Men (Define in Field Fixed Relation) because we want to activate relationship for mens' clothes.

We use 0 because Men is element 0 in the Enum. 

Case 2:

On ClothesOrdersTable we create a new relation to WomenClothesTable and specify the following two relations:
1. Normal Relation: ClotheId to ClotheId and
2.  Field Fixed Relation: ClothesOrdersTable.CollecTionTypeId = 1 

and SourceEDT = CollectionTypeEDT

This shows that the lookup to the WomenClothesTable should show all clothes with the same ClotheId (Define in Normal Relation) AND clotheId will only bring from WomenClothesTable as we are using type Women (Define in Field Fixed Relation) because we want to activate relationship for Womens' clothes. 

We use 1 because Women is element 1 in the Enum. 

Case 3:

On ClothesOrdersTable we create a new relation to ChildernClothesTable and specify the follwing two relations:
1. Normal Relation: ClotheId to ClotheId and
2.  Field Fixed Relation: ClothesOrdersTable.CollecTionTypeId = 2

 and SourceEDT = CollectionTypeEDT
This shows that the lookup to the ChildernClothesTable should show all clothes with the same ClotheId (Define in Normal Relation) AND clotheId will only bring from ChildernClothesTable as we are using type Childern (Define in Field Fixed Relation) because we want to activate relationship for Childern clothes.

We use 2 because Childern is element 2 in the Enum.

 

 Create  three forms and Display Menu Item for display the form in UI and fill the details first in Parent Table (ClothesOrdersTable) and then according the CollectionTypeId the ID shown automatically in the child table.

       As I created here four tables , you can do this with two tables like :

                 ClothesTable & ClothesOrder

            ClothesTable has the following fields:  ClotheId, Name and CollectionTypeId

            ClothesOrder has the following fields:  OrderId, ClotheId, Qty 

                * OrderId could be a number sequence and Qty entered manually by the user.

                CollectionTypeId has the following elements( Base Enum Type)
                    0 - Men
                    1 - Women
                    2 - Children 

                 Example 1: Related Field Fixed

                On ClothesOrder we create a new relation to ClothesTable and specify the following two:

                    1. Normal Relation : ClotheId to ClotheId (Best practice to specify this on the EDT) 

                    2. Related  Field Fixed :  0 = ClothesTable.CollecTionTypeId.

             This shows that the lookup to the clothes table should show only clothes with the same ClotheId                    * Clothes that are of type Men 

          * because the table deals with order for mens' clothes. 

                    We use 0 because Men is element 0 in the Enum.

            Similarly we do this for rest enum 

     

           Related  Field Fixed :  1 = ClothesTable.CollecTionTypeId.(for women)

            Related  Field Fixed :  0 = ClothesTable.CollecTionTypeId (for children)

                  Example 2:  Field Fixed

           On  ClothesTable  we create a new relation to ClothesOrder and specify the following two:

                    1. Normal Relation : ClotheId to ClotheId (Best practice to specify this on the EDT) 

                    2. Field Fixed :  ClothesTable.CollecTionTypeId = 0 (for men)

                Similarly we do this for rest enum

                        Field Fixed :  ClothesTable.CollecTionTypeId = 1 (for women)

                         Field Fixed :  ClothesTable.CollecTionTypeId = 2 (for children)