I have 2 Dataverse Tables one that is a header Order Header and the other Order Line. And what I'm trying to achieve is to show records from the Order Header based on a filter from the Order Line. 

The Objective

In the diagram below you can see an Order Header table that will have three customer records in it. 

Then I will create an Order Lines table. In my example this table has relationship to the three customers from the Order Header table.  The Order Lines Table has columns Item and Allocated. I want to filter my records where Allocated is Yes. So in the example below the filter I'm after is the three records in Green from the Order Lines Table.

Only Customer 1 and Customer 3 have matching Order Lines has Allocated set to Yes. Therefore, the result I am after is an additional table that looks like this diagram. 

App Build

Let me show you now in Power Apps how this is all put together.

The first part in Dataverse is I've just got the two simple tables. I've got a Customer Order Header and a Customer Order Lines table and they are connected by a relationship.

So let's go to the Canvas App and see how I was able to build this merge.

The diagram below shows a Gallery where Items is the Order Header table ('Customer Order').  The Gallery shows 2 columns from the table:

• Customer Name (ThisItem.Name) with the three customers.
• Location Types (ThisItem.'Location Types')

The next diagram shows a Gallery where rows come from the Customer Order Lines table ('Customer Order Lines'). It shows the following columns: 

• The Customer Name which is related back to the table Order Header (ThisItem. 'Customer Name'.Name).
• Item (ThisItem.Item)
• Allocated (ThisItem.Allocated) - This is the Choice column I want to filter on.

To achieve the objective, I will create 3 Collection statements that need to run after each other. You can either run these collection in the App Startup or from a Button (as per my example below).

Collection 1

The first collection will filter the Order Lines Table where Allocated is Yes (true). The OnSelect Button has the following code:

ClearCollect(colOrderLinesAllocated,Filter('Customer Order Lines',Allocated=true));

The Result will be:

Collection 2

The second collection is where it gets a little bit tricky. So this Collection added to the existing above. I've added a column to bring in the related Order Header record. With the additional line for the 2nd Collection the OnSelect Button has the following code:

ClearCollect(colOrderLinesAllocated,Filter('Customer Order Lines',Allocated=true));
ClearCollect(colOrderLines,AddColumns(colOrderLinesAllocated,"MyOrderHeader",'Customer Name'));

The Result will be:

The Labels in the Gallery are:

• Customer name - ThisItem.MyOrderHeader.Name
• Location Type - ThisItem.MyOrderHeader.'Location Types'
• Allocated - ThisItem.Allocated

Collection 3

The third collection is taking Collection 2 and using Distinct to remove duplicates. With the additional line for the 3rd Collection the OnSelect Button has the following code:

ClearCollect(colOrderLinesAllocated,Filter('Customer Order Lines',Allocated=true));
ClearCollect(colOrderLines,AddColumns(colOrderLinesAllocated,"MyOrderHeader",'Customer Name'));
ClearCollect(colOrderLinesDistinct,Distinct(colOrderLines,'Customer Name'));

The Result will be:

The Labels in the Gallery are:

• Customer Name - ThisItem.Value.Name
• Location Type - ThisItem.Value.'Location Types'

So I hope that he hope that help you and thanks for reading. This article is based on my video -> YouTube Video