January 2024 Update – Microsoft has improved the table navigation. Read about the lastest functionality: Navigating Dataverse Tables.
In the previous post, I showed how to navigate Dataverse relationships. In this post, I’ll demo a simple technique to save Dataverse records to variables and explain why this is incredibly useful!
Table Structure
First, a reminder of the simple table structure used in the previous post. Each of the Country, Region & County tables has a Lookup column to it’s respective parent
This structure is easily applied to create a series of cascading galleries. We could get the generational records through the Selected property of each Gallery, but lets not do and get the records through the Dataverse relationships instead
Dataverse Records
As I explained in the previous post, it’s not possible to directly traverse more than one Lookup relationship in Dataverse. However you can access the Grandparent record from the child by using a combination of dot.notation and the Lookup function. If you want to have access to the Great-grandparent record however, it’s not enough to just have the child record
A simple way to around this constraint is to create a variable for each of the generation’s selected records. In the example below I’ve captured the selected records for the County, Region and Country to their own variable by using the OnSelect property of the child Gallery (in this case the County Gallery):
Set(gblCountyRecord,ThisItem);
Set(gblRegionRecord,galRegion.Selected);
Set(gblCountryRecord,galCountry.Selected)
With these variables we can access the selected record across the generations. Because we’ve assigned the full record to each of the 3 variables, we’ll get an error if we try to display the variable (which is a table) in a Label without using dot.notation to specify a particular column. In the above example, if we select Essex as the County/child and set the Text property of 3 Labels to the value below then we get this result
gblCountyRecord.County_Name
gblRegionRecord.Region_Name
gblCountryRecord.Country_Name
Using variables has the big advantage that they can be used across all screens in an app without requiring the user to provide any further input or have any further Lookups, for example, to write a simple descriptor:
gblCountyRecord.County_Name & " is a County in " & gblRegionRecord.Region_Name & " " & gblCountryRecord.Country_Name & " with a population of " & gblCountyRecord.Population
Or to calculate the number of Counties in the South East Region that have a greater population than Essex:
There are " &
CountRows(
Filter(
Counties,
County_Region.Region_Name = gblRegionRecord.Region_Name && County_Population > gblCountyRecord.County_Population
)
)
& " Counties in " & gblRegionRecord.Region_Name & " " & gblCountryRecord.Country_Name & " with a larger population than " & gblCountyRecord.County_Name
Importantly, you can also use the variables to specify the default record on a subsequent screen, without having to refer back to the previous screen. This can be extremely useful. For example if you wanted to specify a record from the County table on screen A for viewing or updating, it can be automatically loaded into a Form in screen B using the following code:
Lookup(
Counties,
County_Name = gblCountryRecord.Country_Name
)
Just bear in mind that the variables exist outside of Dataverse so they are independant and there is no direct relationship between them. However, they can be used individually to access a record in Dataverse and then dot.notation can be used to traverse Dataverse relationships
For example, the name of the Great-grandparent can be accessed as follows:
Lookup(
Countries,
Country_Name = gblCountyRecord.County_Name
).Country_Continent.ContinentName
Resources
Power Apps Reference: Lookup Function