The ‘List Rows’ action in Power Automate retrieves multiple records from a Dataverse table. Frquently you’ll want to filter on a Dataverse lookup column. In this post, I’ll describe 2 ways you can do this and show you how to filter on not just a parent table, but a grandparent table too!
The Scenario
Let’s start with a simple structure consisting of 2 tables.
The first table is called County and contains all the counties within the UK. It has a lookup column called County_Region to a parent table called Region. The Region table lists all the regions in the UK. Each region has multiple counties.
Dataverse Column Names
As well as the display names, Dataverse columns also have a schema name and a logical name. As you can see below, the schema and logical names are very similar. Both of are prefixed by the publisher name with the logical name being all lower case.
Understanding the Query Structure
The List Rows action supports filtering your results using the OData query language. This is particularly useful when you only want to retrieve a subset of records that match specific criteria.
To filter on a lookup field, you’ll need to know the lookup field’s underlying GUID (Global Unique Identifier), which uniquely identifies the particular row in the parent table that you want to use in your query.
In Power Automate, the GUID can be hard-coded as in my examples below, but typically you would use a variable
Filtering on a lookup Column
The OData query is entered in the ‘Filter rows’ section of the ‘List rows’ action.
The first method filters directly on the lookup column’s stored GUID value
Filtering a lookup column requires the logical column name for the lookup field in the child table to be prefixed by ‘_’ and suffixed by ‘_value’ as in the following format: _<logical name>_value eq <GUID>
eq represents equals and GUID is the parent record in the Region table.
The second method filters based on the related table’s record GUID value.
Use the schema name for the lookup field, add a forward slash and then add the logical name of the unique identifier in the parent table in the following format: <Schema Name>/<logical name> eq <GUID>
This method produces the same result as the previous query, but as you’ll see below, it has the advantage that you are not restricted to using the parent GUID column, other columns can be used.
The example below lists all the counties that are in the East region
You can even take the OData query up a further generation to a grandparent table.
In this case I’ve added a further table called Country, which is a parent table to Region.
The example below lists all the counties in England
Summary
Filtering on lookup fields is a key requirement when using the Dataverse ‘List Rows’ action. Filtering lookup columns requires a different OData query structure to other types of columns.
As with much of the Power Platform, there is more than one way to get the job done. Pick the best method to address your need.
Give either of these approaches a try in your next flow, and you’ll find that working with Dataverse and lookup fields becomes much easier and more efficient!