Filter Dataverse Lookup Column

A frequent requirement with canvas apps and Dataverse is to filter the contents of a table by a particular column within that table. Depending on the complexity of the filtering, that can be very straightforward or if you want to filter dataverse LookUp columns in that table, it can be a little bit tricky! In this post I’ll explain 3 techniques to use to obtain the data

The requirement is to filter a table with a single dropdown box and assign the results to a collection with the same click. Let’s start with the cascading tables below that I’ve used in previous posts

Filter on a Text Column

The first example is very straightforward which is simply to filter the data in the County table based on the CountyName which is a text column

All that is required is to create a dropdown box and set it’s Items property to the County table and it’s Value property to CountyName. Then set the OnChange property of the dropdown to the following:

ClearCollect(
    colFilterA,
    Filter(
        Counties,
        County_Name = Dropdown.Selected.RegionName
    )
)

Selecting a county from the dropdown box immediately assigns the relevent row to the colFilterA collection

Filter on a LookUp Column

This is a little more complex. I want to filter the contents of a table by a Lookup column within in that table. Specifically I want to filter all the County Records for those that are in a particular Region

It’s not possible to assign a Lookup column to be the Value property of a dropdown box, so I need to change my appoach. Instead of setting the dropdown box’s Items property to the County table, set it to the Region table. Once done, set the dropdown’s Value property to RegionName. All the regions are now viewable in the dropdown box

Now we use dot.notation to filter the County table based on the selected Region via the OnChange property of the same dropdown box:

ClearCollect(
    colFilterB,
    Filter(
        Counties,
        County_Region.Region_Name = Dropdown.Selected.Region_Name
    )
)

Filtering Multiple LookUp Columns

With the final example, I not only want to filter the County table based on the user selected Region, but also on those where the air quality is classed as ‘Low’. This is obtained from a Lookup from County to the Environment table

We could have 2 dropdown boxes, one for Region and the other for Environment and filter the County table against both simultaneously, but our requirement is just one dropdown

This gives us a problem. We can’t access the Environment table from the Region table. Even if we could, how do we combine 2 LookUp columns into a single dropdown box?

The answer is to use the ForAll function. Using ForAll can get around so many situations where there isn’t an ‘out of the box’ Power Apps or Dataverse solution! Here we use ForAll combined with an If statement to cycle through all the records in the County table and add the names of those counties that meet the criteria to a collection

Clear(colFilterC);
ForAll(
    Counties,
    If(
        County_Region.Region_Name = Dropdown.Selected.RegionName
        &&
        County_Environment.AirQualityStatus = "Low",
        Collect(
            colFilterC,
            {Name: CountyName}
        )
    )
)

Selecting the dropdown automatically assigns the results to the collection colFilterC

Using this technique, further Lookup columns could be included within the If statement for even more complex queries. Any other type of column could also be added and queried too

Although in the above example, I’ve just captured the CountyName in the collection, more columns could be included in the collection if required, or even the whole row using ‘ThisRecord’