January 2024 Update – Microsoft has improved the table navigation. Read about the lastest functionality: Navigating Dataverse Tables.
Recently, I wrote about how saving a Dataverse row to a variable can help Dataverse navigation. In that post, I used a series of cascading galleries to assign rows to variables for Dataverse navigation. In this post, I’ll demo an alternative technique that doesn’t require multiple galleries to be displayed. Not only are the columns within the selected row captured in the variable, but also the parent and child rows too. Finally I demonstrate how this technique can be applied to navigate as many levels as required up a parent Dataverse table structure
Dataverse Navigation
I’ll start by recapping on what can be done when saving a row to a variable. Consider the table structure below that I’ve used in many posts recently. I’ll capture a row from the Region table
Assigning a Row to a Variable
Often the best way to save a Dataverse row to a variable is via the OnSelect property of a gallery. Always use a global variable in case you need to use the variable across multiple screens. Simply clicking a row in the gallery will assign that row to a variable. I’ll use the Region table with the formula below:
Set(gblRegion,ThisItem)
Displaying Row Data
Once a particular Region has been assigned, to display the name of that Region in a label, set the label’s Text property to:
gblRegion.RegionName
Using this technique, any column within the Region table can be displayed
Displaying Parent Table Data
Not only can the columns in the row be accessed, but any data in the parent table (Country) can also be displayed using the LookUp column from Region to Country. Set a label with the following text property to display the parent Country name:
gblRegion.Region_Country.CountryName
It’s important to remember that the dot.notation here is only navigating within the variable we’ve assigned and not in Dataverse itself. Also, bear in mind, the data within the variable is a snapshot of the data when the variable is assigned. If the source data changes this won’t be updated in the variable
Displaying Child Table Data
Not only is it possible to navigate up a relationship , it is also possible to navigate down to display any children
Child rows must be displayed in a gallery and not a label as there may be multiple rows to display. Create a gallery and set its Items property to the following formula to display all the child records:
gblRegion.Counties
Within the gallery, the names of the child Counties can be displayed in a label with the Text property set to:
ThisItem.CountyName
So using the above techniques, it’s possible to assign all columns of a row to a variable. Additionally the variable also contains all the columns of the parent record and all the columns of each of the child records too. All that data can be accessed just using dot.notation
Accessing More Distant Ancestors
Just as it’s not possible to navigate more than one table up or down a Dataverse structure simply by using dot.notation alone, it’s also not possible to do this within the variable either
However, if the use of dot.notation is employed together with the Lookup function, we can navigate further up a Dataverse structure to the Grandparent
This is done my matching a row in Dataverse to the parent record of our variable and then accessing the parent of that Dataverse row
In our example the Grandparent of our Region row is in the Continent table
To show the name of the Grandparent of our variable, display the following Text property in a label control. ‘Country’ is the GUID of the row:
Lookup(
Countries,
Country=gblRegion.Region_Country.Country
).Country_Continent.ContinentName
To assign the whole Grandparent row to a new variable called gblContinent, set the OnSelect property of the Region gallery to the following:
Set(
gblContinent,
Lookup(
Countries,
Country=gblRegion.Region_Country.Country
).Country_Continent
)
We can extend this technique to ascend even further up a relationship to find the Great-Grandparent! I’ll be posting more on Dataverse navigation using variables in the near future
In the above examples, I’ve displayed the output on screen but you may want to use the data to run further queries. Because the data is contained in a variable this is easy to do
Resources
You can see that assigning a row to a variable can potentially contain a large amount of data, particularly if the selected row has lots of child records. Because of this, you may want to limit the data assigned to the variable to that you really need. This can be done when creating the variable by using the AddColumns or DropColumns functions – see the Power Apps reference below:
- Power Apps Reference: AddColumns & DropColumns
- Hiredgun.tech: Adding Database Records to Variables
Thanks for your most helpful guidance.
I set up the same as your example and found that your lookup to the grandfather continent didn’t work:
Lookup(
Countries,
Country=gblRegion.Region_Country.Country
).Continent.ContinentName
(Continent was not available as a dot reference from the lookup.)
This did work:
LookUp(
Countries,
Country=gblRegion.Region_Country.Country
).Country_Continent.ContinentName
Thanks for picking up the typo Higgo. Now corrected. I’m pleased you’re finding the content helpful