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

Dataverse Relations

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’

Filter Dataverse Lookup Column

So there we have it, 3 options for filtering tables including multiple LookUp columns.  Pick the one that best meets your needs and happy filtering!

Power Apps Reference: Filter & ForAll

Interested in why you should use another Dataverse table as reference data rather than a Dataverse choice column?  Find out here

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top