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

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

Comments

  1. Great article. Another drawback of point 3:
    Don´t apply this to the Default view of an entity. If you do so, it can cause undesired problems. If I remember correctly, we once had the case that we were not able to configure Dashboards for this entity anymore (this was ca. 2 years ago).

    ReplyDelete
    Replies
    1. Thanks for sharing your experience in the comment. 😊

      Delete
  2. Interesting calculated column idea.. it's both "out of the box" and "outside the box thinking", another pro for it is space saving... since it's calculated it doesn't take actual space in the database. Thanks Linn

    ReplyDelete
    Replies
    1. Yeah, it works really well other than the polymorphic lookups.

      Delete
  3. From a data point of view i've also disliked the denormalisation in 2a and 2b, but I've just recently used 2a as it worked for our client's requirement, and we wanted full control over the route that they create the entity from (I.e. they shouldn't be doing it from advanced find for various reasons).

    Technically wouldn't option 3 be unsupported, seeing as it breaks the advanced find functionality and the ability to edit the saved view via the solution designer view editor?

    Another option, although it also denormalises the data could be to copy the data from the related entity to the main entity, although I would consider this option inferior and would really only work if you are sure of a 1:1 relationship.

    ReplyDelete
    Replies
    1. Thanks for the comment, Eric. For option 3, the layout of the saved view can still be edited via the solution designer view editor and only the filter criteria cannot be edited.

      Delete
  4. Hi Linn,

    Thanks for the article.

    In my scenario i have
    - a custom table (Installations) which is 1:N related to..
    - a default table (Contact) which is N:N related to..
    - a default table (Webrole)

    There are look-up columns in both the tables 'Installlations' and 'Contact'.

    I choosed option 2 and created an calculated column named 'WebroleContact' which should save the value of Webrole.Name

    There is, however no value saved in this calculated column. Any idea what is going wrong? Is it not possible to implement option 2 having N:N relationships?

    Cheers,
    Chris

    -

    ReplyDelete
    Replies
    1. I am sorry. I mistaked the options numbers. I tried option 1: Calculated Column in Related Table

      Delete
    2. Hi Chris,

      I don't quite understand the part "There is a look-up column in the table 'Contact'". Lookup to which table does Contact have? Since it's N:N relationship with WebRole, I am not expecting the lookup to the WebRole.

      The option 1 will work only if you want to show a value from the table related via lookup. It will not work with N:N relationship.

      How do you expect your "Webrole Contact" column to show if the Contact has more than one Web Roles?

      I guess the only solution is to populate your custom column with an automated process (something similar to 2B Automated Process) and implement a plug-in for Associate and Dissociate messages to trigger whenever the web role is added/removed from the Contract.

      Delete
  5. Sorry to say that #3 doesn't work anymore in the model driven app - it works, but the column headers have no titles.

    ReplyDelete
    Replies
    1. I guess the new Power Apps grid control is not able to show the column header for such columns from the table which does not have the direct relationship with the current table. Thanks for letting me know. I will update my blog post.

      Delete
  6. Very helpful @Lin Zaw Win! I am using Option 1 very successfully for a number of related entities. The only drawback is how to achieve this for an Option field... as soon as I select "Choice" and "Calculated" the entity column will not save. Any suggestions?

    ReplyDelete

Post a Comment

Popular Posts