Field Fixed and Related Field Fixed relations in D365FO

In D365FO, there are two relation types called Field Fixed and Related Field Fixed that many developers find confusing. It isn't always clear when to use each one or what is the difference between them. As a result, developers might end up writing X++ code to override the lookup and achieve certain behavior, even though these relation types can handle many of these scenarios without any custom code.

So in this article, I'll show you the difference between Field Fixed and Related Field Fixed using two simple examples.

Field Fixed

Let's assume we want to create a table that stores the party type (Example: Customer or Vendor) and the corresponding account number

PartyType          PartyReference

----------------------------------------

Customer                 C001

Vendor                     V001

 

As you can see, the PartyReference field can store either a customer account or a vendor account

 

Now, when you want to create a new record in this table, and you choose Customer as the PartyType, you'll need to fill in the PartyReference field. How can you make sure that the lookup for PartyReference only returns customer accounts and not vendor accounts?

Similarly, when you choose Vendor, you want the PartyReference lookup to return only vendor accounts and not customer accounts.

To solve this, we can create a Field Fixed relation.

1. So first let's create an enum for the party type (We could also have used the standard CustVendACType enum instead of creating a new one)
   
   
   
   
   
2. Create the table with the two fields: PartyType and PartyReference
• PartyType: is the enum we just created, which indicates whether the party is a customer or vendor
• PartyReference: is the field that will store the accountNum for either the customer or the vendor (EDT used is CustVendAC)
  
  
  
  
  
3. Now let's create a new relation between the "LJPartyReference" table and CustTable
• Go to the "Relations" node and create a new relation (name it "CustTable")
• Set "RelatedTable" to CustTable
  
  
  
  
  
  
  
  
• Then right-click on the "CustTable" relation and create a "Field Fixed" relation
• Set Field to "PartyType"
• Set Value to "LJPartyType::Customer"
  
  
  
  
  
  
  
  
  
  
  
• Right-click on the "CustTable" relation again and create a "Normal" relation
• Set Field to "PartyReference"
• Set RelatedField to "AccountNum"
  
  
  
  
  
  

**This means that the relation between LJPartyReference and CustTable will be applied only when PartyType is Customer. In other words, the PartyReference lookup will return records from CustTable only when PartyType is Customer.


4. Repeat the same steps for the VendTable relation (create both Normal and Field Fixed relations) same as you did for CustTable in step 3
   **This means that the relation between LJPartyReference and VendTable will be applied only when PartyType is Vendor. In other words, the PartyReference lookup will return records from VendTable only when PartyType is Vendor.
   
   
   
   
   
5. See how, when choosing Party Type as Customer, only customer accounts are returned in the Party Reference lookup
   
   
   
   
   
6. Similarly, when choosing Party Type as Vendor, only vendor accounts are returned in the Party Reference lookup
   
   
   
   
   

Related Field Fixed

Let's assume we want to create a table that stores the colours and whether each colour is active or not:

ColourId         Name           Active

-------------------------------------------------

1                      Yellow           Yes

2                      Blue               Yes

3                      Red                No

And we want to link each product to a certain colour:

ProductId      ColourId         

-------------------------------------

A                      2

B                      1             

As you can see, the ColourId field in the Product table stores the colour assigned to each product.

Now, when you want to create a new product and choose a ColourId, you only want the lookup to return active colours. In our example, Red is not active, so we don't want it to appear in the ColourId lookup in the Product table. How can you make sure that the ColourId lookup only returns colours where Active is Yes?

To solve this, we can create a Related Field Fixed relation.

1. So first, let's create the Colour table with the three fields: ColourId, Name and Active
   • ColourId: Identifies the colour (string, you can create its own EDT)
   • Name: stores the colour name (string)
   • Active: indicates whether the colour is active or not (Enum: NoYes)
     
     
     
     
     
2. You can make the ColourId as unique index
   
   
   
   
   
3. Let's also create the Product table with the two fields: ProductId and ColourId (The unique index is ProductId. For simplicity, we'll assume that one product can only have one colour)
   • ProductId: Identifies the product (string)
   • ColourId: stores the colour assigned to the product (string)
     
     
     
     
     
4. Now let's create a new relation between the Product table and Colour table:
• Go to the "Relations" node in LJProduct table and create a new relation (name it "LJColour")
• Set "RelatedTable" to LJColour
  
  
  
  
  
• Then, right-click on the LJColour relation and create a Normal relation
• Set Field to ColourId
• Set RelatedField to ColourId
  
  
  
  
  
  
  
• Right-click on the LJColour relation again and create a Related Field Fixed relation
• Set RelatedField to Active
• Set Value to NoYes::Yes
  
  
  
  
  
  
  

**This means that the relation between LJProduct and LJColour will only consider colours where Active is Yes. In other words, the ColourId lookup in LJProduct will only return active colours.


5. Let's add some records to the LJColour table, with both active and inactive colours
   
   
   
   
   
6. Now, when choosing a ColourId in the Product table, notice that only the active colours (1 and 2) are returned in the lookup. ColourId 3 (Red) is not returned because Active is No
   
   
   

A real-life example for Related Field Fixed

I recently used Related Field Fixed in a real-life scenario.

We had a Parameters table, where I was asked to add two new fields: X Order Cancellation Reason Code and Y Cancellation Reason Code. Both fields allow the user to select a reason code from a Reason table. The Reason table contains  the Reason Code, Not in Use, and Reason Code Action fields.

However, there were some requirements for the lookups:

• Both lookups should only return reason codes where Not in Use = No.
• X Order Cancellation Reason Code should only return reason codes where Reason Code Action = X Cancellation.
• Y Cancellation Reason Code should only return reason codes where Reason Code Action = Cancel Y Item.

Instead of overriding the lookups in X++ to apply these filters, I used Related Field Fixed conditions in the table relations.

On the Parameters table, the relations were configured as follows:

Relations
│
├── ReasonTableXOrderCancellation
│   ├── Parameters.XOrderCancellationReasonCode == ReasonTable.ReasonCode
│   ├── No == ReasonTable.NotInUse
│   └── ReasonCodeActions::XCancellation == ReasonTable.ReasonCodeAction
│
└── ReasonTableYCancellation
    ├── Parameters.YCancellationReasonCode == ReasonTable.ReasonCode
    ├── No == ReasonTable.NotInUse
    └── ReasonCodeActions::CancelYItem == ReasonTable.ReasonCodeAction

Summary

It's called Related Field Fixed because the fixed condition is on a field in the related table.

For Field Fixed, the fixed condition is on a field in the current table, where the relation is defined.

So the easiest way to remember it is:

Field Fixed → fixed condition on the current table
Related Field Fixed → fixed condition on the related table