Power Community

Power Community

Creating a Collection that filters related Dataverse Tables in Power Apps

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. 

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

365Assist_0-1682901019961.png

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.

365Assist_2-1682901600692.png

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. 

365Assist_3-1682901853768.png

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.

365Assist_2-1682902413397.png

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’)

365Assist_3-1682902852977.png

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.

365Assist_0-1682906224101.png

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:

365Assist_0-1682904424898.png

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:

365Assist_1-1682904537468.png

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:

365Assist_2-1682905094056.png

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 

This post was originally published on this site

- Advertisement -spot_img

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisement - Advertisement

Latest News

Announcing SharePoint Embedded General Availability

Authored by: Zach Rosenfield Today we’re pleased to announce the general availability of SharePoint Embedded, a new way to build...

More Articles Like This

- Advertisement -spot_img