Add Two-level up Related Column to a View in Model-driven Apps

In this post, I will explain how you can add a column from the related table (up to two-level up) to the views in model-driven apps.

In Dynamics 365 projects or Power Apps (model-driven apps) projects, it is a common requirement to edit the system view to show the columns from the related table.

With the out-of-the-box view designer, you can easily add the column from the directly related table by 

Example Scenario

For the example below, the Invitation table has a lookup to the Account table and the Account table has the Primary Contact lookup which is linked to the Contact table. If the requirement is to show the Account Number from the Account table in the view of the Invitation, you can simply add the related column as in the screenshot above.

But what if the requirement is to show the Email column of the Primary Contact of the Assign to Account in the view of the Invitation? The Contact table is not directly related to the Invitation table and this cannot be easily achieved by the out-of-the-box view designer. Here are a few ways to achieve it and the pros/cons of each approach.

Possible Solutions

I have tried and documented 3 main possible solutions on how to add a two-level related column to a view. I have included the pros and cons so that based on your situation, you can decide which approach will be best suited for you.

I personally like the last approach. Read on to find out why. 😊

1) Calculated Column in Related Table

In this approach, you need to create a calculated column in the directly related table (which is the Account table in this scenario).

Then, set the value with the required column from the second level related table.

After that, the calculated column can be easily added by using the view designer because the column is now in the directly related table.

Pros:

• Quick and easy
• Normalised data
• No development effort required

Cons:

• A new calculated column is required to be created for each column to be displayed from the second level related table

2) Direct Relationship with Second Level Related Table

In this approach, you need to create a direct relationship with the table with the required column.

In this scenario, the requirement is to add the column from the Contact table to the view of the Invitation. That is why you will need to create the direct lookup from the Invitation to the Contact table.

Now that the Contact is directly related to the Invitation table, the required column can be easily added by using the view designer.

The only missing piece in the puzzle now is how to automatically populate the newly created lookup. There are two ways on how this can be done:

2A) Column Mapping in Relationship

With this approach, you can populate the lookup value of the Primary Contact by setting the column mapping in the relationship. The relationship to add the mapping is the original direct relationship (which is the Invitation and Account relationship in this scenario).

In the relationship, map the Primary Contact lookup column in the Account table with the newly created Primary Contact lookup column in the Invitation table.

Pros:

• Quick and easy
• No development effort required

Cons:

• Denormalised data
• It only works when the Invitation is created from the subgrid/associated view of the Account form.
• It does not work for an update of the Account lookup
• It does not work if the Invitation is created from anywhere else other than the Account form
• It does not work if the Invitation is created by the backend processes (SDK, plug-in, workflow, cloud flow, etc.)

2B) Automated Process

With this approach, you can populate the lookup value of the Primary Contact by creating an automated process. In the following example, the cloud flow is used to automatically populate the value of the Primary Contact OnCreate and OnUpdate of the Assign to Account lookup column. But the same result can be achieved with the other types of automated processes such as plug-in, workflow, etc.

Pros:

• Consistent data

Cons:

• Denormalised data
• More effort required for the configuration/development of the automated process
• A thorough analysis is required to cover all scenarios
• e.g. If the Primary Contact of the Account is changed, another process is required to trigger the automated process of the related Invitation rows to the affected Account (to fetch the updated Primary Contact value).

🔗 3) FetchXML and LayoutXML of View (UNSUPPORTED)

⚠ Important

This approach is not documented and not supported by Microsoft. Even if it may be working right now, it might break anytime in the upcoming updates to the grid view control. Even in the latest Power Apps read-only grid control, the column header is empty for those columns added to the view with this approach. Use it at your own risk.

In this approach, you need to update the FetchXML and LayoutXML of the view. It can be achieved by updating the Customization XML and importing it back but using the tools would make your life a lot easier.

Open the view using the View Designer tool in XrmToolBox.

Click on the "Edit Query" button to open the FetchXML in the FetchXML Builder tool. Update the alias value of the existing FetchXML and add the nested link entity for the second level related table (which is the Contact table in this scenario). Test the FetchXML and click on "Return FetchXML" to return the updated FetchXML back to the View Designer tool.

This is the FetchXML used in the example above.

<fetch version="1.0" output-format="xml-platform" mapping="logical">
  <entity name="adx_invitation" >
    <attribute name="adx_invitationid" />
    <attribute name="adx_invitationcode" />
    <attribute name="adx_assigntoaccount" />
    <link-entity name="account" from="accountid" to="adx_assigntoaccount" link-type="outer" alias="account" >
      <attribute name="accountnumber" />
      <attribute name="primarycontactid" />
      <link-entity name="contact" from="contactid" to="primarycontactid" link-type="outer" alias="contact" >
        <attribute name="emailaddress1" />
      </link-entity>
    </link-entity>
  </entity>
</fetch>

In the View Designer tool, click on the "Edit XML" button to update the LayoutXML. Add a new column added to the FetchXML with the proper alias (update the alias of the other table if required). Save the view and publish it.

This is the LayoutXML used in the example above.

<grid name="resultset" object="10137" jump="adx_name" select="1" icon="1" preview="1" >
  <row name="result" id="adx_invitationid" >
    <cell name="adx_invitationcode" width="150" />
    <cell name="adx_assigntoaccount" width="150" />
    <cell name="account.accountnumber" width="150" />
    <cell name="account.primarycontactid" width="150" />
    <cell name="contact.emailaddress1" width="150" />
  </row>
</grid>

Refresh the page and the new column is now added to the view. (Note: The following screenshot was taken with the old Read-Only grid control. The column headers are going to be empty in the Power Apps read-only grid control - refer to the screenshot in the last point under Cons section)

Pros:

• Normalised data
• No development effort required

Cons:

• The column header is alias + logical name instead of proper column display name
• The advanced filtering pane is not working for the view
• The view filter cannot be updated in the view designer
• Other view configurations such as adding/removing columns can still be done
• The view filter can only be updated using the View Designer tool
• 
  
  
• The view is not working in the Advanced Find too

• The column header is empty for those columns added to the view with this approach in the Power Apps read-only grid control

Summary

There are a few ways to add a column(s) from the grandparent table to the view but editing the FetchXML and LayoutXML of the view is the most effective and efficient way to achieve the result if you are not too particular with the cons listed for this approach. With this approach, multiple columns can be added without creating any unnecessary columns and duplicating data (no copying required, etc.)

If you have any other way to fulfil this requirement, please leave it in the comment below. I am curious to know about it.