Dataverse Relations. Identifying Children, Grandchildren & Great-Grandchildren

January 2024 Update – Microsoft has improved the table navigation. Read about the lastest functionality: Navigating Dataverse Tables.

In this post, I’ll show how the Filter function can be used to identify Dataverse relations, including Child and Grandchild rows and even more distant descendants

Using the simple table structure below, I’m going to navigate downwards from the Continent table to identify the related rows in the Country, Region and County tables. Accessing distant descendants isn’t a business requirement that occurs frequently, but when it is required, it’s good have this trick in your toolbox. Using this technique, it is possible to navigate through as many tables as required to identify descendants

A word of warning though. Whist using dot.notation to identify the Child records is fully delegable, the code to list the Grandchildren and more distant descendants isn’t delegable so use only on modest data sets that are within the app’s delegation limit setting

Selecting a row in the Continent table can be done in a number of ways. Picking from a gallery is probably the most common means of selection, so I’ll use that approach to select a continent from the gallery galContinent

Also, bear in mind that a parent row can have none, one or multiple child rows. For this reason the child data should be displayed in a gallery or data table as these controls can accommodate all outcomes

Related Child Rows

Identifying the associated child rows is done purely by using dot.notation. Setting the items property of a gallery to the following lists all the related child rows from the selected parent:

galContinent.Selected.Countries

The name of the Country is displayed in the gallery by adding a label and setting its Text property to:

ThisItem.CountryName

Alternatively, the related child rows can be added to a collection instead. Setting the OnSelect property of the Continent gallery to the following will create a collection called colCountry:

ClearCollect(colCountry,galContinent.Selected.Countries)

Related Grandchild Rows (Non-Delegable)

Because a particular continent can have many child countries, and those countries can themselves have many child regions, the logical way to access the Grandchild rows is to use the Filter function with the ‘in’ operator

To display the Grandchild rows in a gallery, set the Items property of the gallery to the following:

Filter(
    Regions,
    Region_Country.Country in galContinent.Selected.Countries.Country
)

Alternatively, to assign the Grandchild rows to a collection, add the following formula to the OnSelect property of the Continent gallery:

ClearCollect(
   colRegion,
   Filter(
      Regions,
      Region_Country.Country in galContinent.Selected.Countries.Country
   )
)

The Microsoft documentation states that using ‘in’ inside a Filter function with Dataverse is delegable if the preview feature called ‘Enhanced Delegation for Microsoft Dataverse’ is switched on. However, at the time of writing, this is not the case if dot.notation is used inside the Filter function

Consequently, the above formulas return results that are not delegable and if the Region table has more than 500 rows (or whatever the defined delegable limit has been set to) the data may be incomplete

Related Great-Grandchild Rows – (Not Delegable)

Because a Filter function cannot be embedded within another Filter function, to obtain the Great-Grandchild rows we create an enabling collection with just the information we require, which in this case is the Region’s GUID

Setting the OnSelect property of the Continent gallery to the following creates both the enabling collection called colRegion containing the Grandchild rows and also colCounty that contains the Great-Grandchild rows:

ClearCollect(
   colRegion,
   Filter(
      Regions,
      Region_Country.Country in galContinent.Selected.Countries.Country
   )
);
ClearCollect(
   colCounty,
   Filter(
      Counties,
      County_Region.Region in colRegion.Region
   )
)

Again, for the reason already explained, the results are not delegable and may be incomplete on large data sets

Even More Distant Descendants (Not Delegable)

In the very unlikely event that even more distant relations are required, this technique can be extended by adding a further enabling collection to identify all the related Great-Great-Grandchildren. The technique can be used to navigate as far down a database structure as required. Bear in mind however, that for the Grandchildren and more distance descendants the results are not delegable

Related Grandchildren Using ForAll (Also Not Delegable)

The ForAll function can be used instead of Filter to create a list of list of Grandchildren. The ForAll function, scrolls through the rows in the Region table to check if the parent Country of each row is a child of the selected Continent. If this is the case then the row is added to a collection. This done by creating the following formula executed via the Continent galleries OnSelect property

Clear(colRegion);
ForAll(
   Regions,
   If(
      Region_Country.Country in ThisItem.Selected.Country.Country,
      Collect(
         colRegion,
         ThisRecord
      )
   )
)

However, because ForAll isn’t delegable, only the first 500 rows (or whatever the delegable limit has been applied to the app) are accessed. If the table has more than 500 rows there is a risk the data returned will be incomplete, so this isn’t an improvement on using the Filter function

Dataverse Relations Summary

Using the Filter function together with enabling collections is a powerful technique, but it requires all descedants in a table chain to be interrogated so the further you descend (and the greater the number of rows in each table) the longer it will take the query to run

More importantly, bear in mind that if any of the tables in the chain have more records than the delegation limit setting in the app, then only partial results will be returned

Descending a Dataverse structure is more complex than ascending. Ascending only requires a single row to be identified. Descending requires every row in the table to be queried. If you find you have the need to descend several tables, it’s likely to be worthwhile considering revising the Dataverse table structure and relationships

Hiredgun.tech: Dataverse Navigation. Identifying Rlated Child, Parent and Grandparent Rows
Power Apps Reference: Delegable Functions for Dataverse
Power Apps Reference: Filter Function