How to display related records in a 1:N CDS relationship!

Background

Working at Hitachi Solutions is a lot of fun for various reasons! One of the biggest reasons is the access to and interactions with so many smart people! This post stems out of the many interactions I have had with 3 such people - Joe Unwin aka FlowJoe, Donald Panek, and Scott Salvner! I am super thankful to them for motivating me to write this one as the approach described here isn't as straightforward for someone getting started with CDS and Power Apps. Looking forward to many more conversations in the future!

1:N relationships in CDS

1:N is a relationship where one entity record for the Primary Entity can be associated to many other Related Entity records. You can create such a relationship by adding a lookup field to an entity . With the lookup field, you can associate multiple child records of that entity to a single parent entity record. An example of this is the primary contact of an account. An account can have only one primary contact. However, a contact can be the primary contact of multiple accounts. Thus, primary contact->account is a 1:N relationship whereas account->primary contact is a N:1 relationship.

Displaying 1:N related records - Method 1

Consider an example where you have a gallery of contacts . Suppose you want to show the list of related accounts for which the select contact is the primary contact (the lookup field on the Accounts entity for the Contacts entity is named 'Primary Contact'). One of the first attempts, in most cases, is:

Filter(Accounts, 'Primary Contact' = Gallery_Contacts.Selected)

This makes perfect sense. If I read this in simple English, it says I want to filter the list of accounts where the primary contact is the contact I have selected in the gallery. Seems correct right? Not really, this results in an invalid argument type error. Now you may ask why. Here is the reason: although you do want to compare two records (primary contact on accounts to the selected contact), simply comparing the entire records doesn't work. Each record in CDS has a unique ID or GUID. So to compare two records, we need to compare their GUIDs. The way to make it work in this example is:

Filter(Accounts, 'Primary Contact'.Contact = Gallery_Contacts.Selected.Contact)

'Primary Contact'.Contact means that it will compare the GUID of the 'Primary Contact' of each account to the GUID of the selected contact and will return only those accounts for which this condition is true. Note that the name of the GUID field for the Contacts entity is Contact. To make matters more confusing, imagine if the name of the lookup field on the Accounts entity was called Contact instead of Primary Contact. The required expression in that case would have to be:

Filter(Accounts, Contact.Contact = Gallery_Contacts.Selected.Contact)

On the left hand side of the equation, the first "Contact" denotes the lookup field's name (and not the lookup entity). The second "Contact" denotes the name of the GUID field on the lookup entity. To generalize, the expression to fetch related records of Entity B in a 1:N relationship with Entity A is:

Filter(Entity A, 'Lookup field on Entity A'.'GUID field of Entity B' = 'Selected record of Entity B'.'GUID field of Entity B' 

Displaying 1:N related records - Method 2

Does method 1 seem confusing? I don't blame you! Here's another approach that is WAY simpler than the first one! Taking the same example, you can get the same results by simply using the following to fetch related accounts for which the primary contact is the selected contact:

Gallery_Contacts.Accounts

Isn't that clean and simple? I mentioned method 1 first to enable you to understand how lookup fields work. But if your main goal is to simply display the list of related records in a 1:N relationship, use this approach. Note that in certain cases, you might be prompted by the intellisense to use the relationship name instead of the Accounts entity name. To generalize, the expression to fetch related records of Entity B in a 1:N relationship with Entity A using this approach is:

'Selected record of Entity B'.Entity A

Summary

Displaying related records in a 1:N relationship between two CDS entities (1 - Entity B: N - Entity A) can seem confusing especially if you go down the filter route. You can make it work in a couple of ways:

1. Using the Filter method by following this syntax/approach:

   Filter(Entity A, 'Lookup field on Entity A'.'GUID field of Entity B' = 'Selected record of Entity B'.'GUID field of Entity B'

2. The easier and cleaner method is to simply use:

   'Selected record of Entity B'.Entity A

Hope this helps you when dealing with 1:N (or N:1) relationships between two CDS entities!

Recent Articles

1. Responsiveness – how to test in the studio!
2. Power Apps Buttons – know when to use AutoDisableOnSelect
3. Data tables – New vs Old