Filter based on Child Data

– Here’s a quick demo on how to get a filtered list of records from a parent Dataverse table based on data in a child table

In my user case, I have a simple Dataverse structure of two tables. The parent table is called Programme and the child table is Project. A programme can contain multiple projects

Embedded Tables

I want to create a sorted list of the programmes that contain at least one project that has a status of ‘Red’

To do this, I can take advantage of the fact that when a collection is created from a Dataverse table, any Lookup columns in that table will exist in the collection as an embedded table containing the parent data

This can be seen when viewing a collection. The fact a table exists, rather than data, is denoted by a ‘?’ as highlighted below

Although we can’t see the contents of the table when we view the collection, we can get at the data using dot.notation

Creating Collections with the ForAll Function

To do this we need to use the ForAll function to create a collection. We can’t use ClearCollect within a ForAll function because with each iteration of the ForAll loop, the existing data would be erased, so first we need to clear the collection of any existing data as a separate command

Clear(colRedProjects);

Now we can execute a ForAll function to filter all the Project records that are ‘Red’ and add the Programme, Project Name and Project Status into the collection

You can see the dot.notation that is used inside the Collect function on the Lookup column to get the parent programme’s name and add it to the collection

ForAll(
   Filter(
      Projects,
      'Project Status' = "Red"
   ),
   Collect(
      colRedProjects,
      {
         Programme: 'Parent Programme'.Name,   // <- dot.notation
         Project: Name,
         Status: 'Project Status'
      }
   )
);

Here is the output. All the ‘Red’ projects are listed together with their parent programme:

Tidying up the Output

As you can see, because a Programme can have multiple projects and more than one project could have a status of ‘Red’, the same programme can appear twice in the list. To remove any duplicates run a further formula using the Distinct function and Sort to order the collection alphabetically

ClearCollect(
   colRedProgrammes,
   Sort(
      Distinct(
         colRedProjects,
         Programme
      ),
   Value,
   SortOrder.Ascending
   )
)

Which gives the following output

And I now have the list I need

Happy (low) coding!

Hiredgun.tech: Sorting a Dataverse Lookup Column
Power Apps Reference: Filter Function
Power Apps Reference: ForAll Function

Filter based on Child Data