January 2024 Update – Microsoft has improved the table navigation. Read about the lastest functionality: Navigating Dataverse Tables.
Dataverse makes building Power Apps a much more rewarding experience. In this post, I’ll show how to use the dot.notation to navigate Dataverse relationships. I’ll also explain the limitations of working with Dataverse relationships and when you’ll need to revert to using the Lookup function
Dataverse Relationships
Below is a simple table structure. Each of the Country, Region & County tables has a Lookup column to it’s respective parent. A parent record can have many child records but a child record can only have one parent record
Decendants
Cascading Galleries
This structure is easily applied to create a series of cascading galleries, with the Items property of each Gallery being set to the selected records of its parent. Below is the code for the Items property of the Country table
galContinents.Selected.Countries,CountryName
You can see the dot.notation is simpler and quicker to use than the equivalent LookUp function, though using a Lookup function would yield the same results. Similar code is used for the Items property of the other two tables, Region & County
Selecting a Continent in the first Gallery cascades the available options for Country and selecting a Country presents the options for Region, with the selected record highlighted in Red. All the Counties in the selected Region are then displayed in the final child Gallery
Identifying Ancestors
The requirement is to display the names of the parent, grandparent and great-grandparent in Labels in the County Gallery, as can be seen above
Because we have cascading galleries this could easily be achieved using Gallery.Selected, but instead we are going to to navigate the Dataverse relationships to provide the data
Parent
The Parent has a direct relationship with the child. Hence, the parent can be easily displayed using dot.notation via the Lookup column to the Region table to display the Region’s name
To display the parent data in the County Gallery in a Label, set the Text property of the Label to the following:
ThisItem.County_Region.RegionName
County_Region traverses the relationship to the Region table. RegionName displays the name of the Region and this could be substituted to display any other column in the table. If you are using Dataverse and you need data from a Parent table, this is the ‘go to’ solution
Grandparent
One of the limitations of using Dataverse in Canvas Apps is that it’s not possible to use dot.notation to chain 2 Lookup columns together to access the grandparent
However, we can display the grandparent via a combination of dot.notation and the Lookup function. First, Dot.notation is combined with a Lookup function to identify the parent record in the Region table. Then dot.notation is used to traverse the Lookup column from Region to Country to identify the associated grandparent record called CountryName. Set the Text property of a Label to the following:
LookUp(Regions,RegionName=ThisItem.County_Region.RegionName).Region_Country.CountryName
Great-Grandparent & Older Ancestors
While this works to identify the Grandparent, in the same was that it is not possible to traverse 2 Lookup columns , it is also not possible to embedded a LookUp function inside another LookUp function to access the Great Grandparent record
However, we can get to the Great-Grandparent by using a single LookUp that references the existing Grandparent Label as below:
LookUp(Regions,Region_Name=lblRegion.Text).Region_Country.Country_Name
This method would work equally well on getting data on even older ancestors. If you wanted to display the great-grandparent but not the grandparent for example, still create the grandparent Label so it can be used, but set its Visible property to false
In the above examples, I’ve used the name columns for each table, but in a production app the GUID would be more likely to be used to ensure the correct record is returned and avoid duplicates
The limitation of only being able to directly access the parent record, rather than being able to traverse multiple tables means that the design of the Dataverse database is extremely important. It is well worth spending time to make sure the design meets your requirements before starting building your app as amending the database once app is underway can be time-consuming and complex
Resources
Power Apps Reference: Lookup Function