Unanswered

Filter a gallery by another gallery

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello!

I am programming an application and I want to have 2 screens with a gallery on both screens.

On the first gallery, I want it to automatically filter all the double values out and only get one of each.

My SharePoint columns looks like this:
https://gyazo.com/a3a3afca0f2954f7403d087a41949573

We are going to be focussing on 3 out of the first 4 columns.

-Order
-Relatie
-Suborder

I want the first gallery to only show the Relatie with the order number. Example: (Aalberts Bouw B.V. 17056)
As you can see there are more rows with aalberts bouw and that order number. This is basically a project with different sub orders. I want to filter all of these rows and only show 1 of each project name + order number. So There should only be one "Aalberts Bouw B.V. 17056". Whenever I click on this project I want it to show all of the rows that match with the relatie + order number. It would be awesome if someone would be able to help me out.

Thanks in advance, if something is unclear, please tell me and I will explain it better. I am still very new to powerapps so it would be awesome if you could explain it all to me when you reply 🙂

Categories:

Building Power Apps

I have the same question (0)

All responses (26)

Answers (0)

Sort by

eka24 20,925 on at

Like
a
(1)

Report
Copy link

Link copied!

Use the following steps:
1. On screen1 with Gallery1, put this formula on the Item of the Gallery1:
   Distinct(ProjectLijtsen,Order)
2. Set a Variable OnVisible of the Screen:
    Set(MyOrder,Gallery1.Selected.Order)
3. On Screen2 with Gallery2, put this formula on the Item of the Gallery2:
      Filter(ProjectLijtsen,Order=MyOrder)

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Anonymous ,

I found that you have posted same thread within this forum already, please check my response within the following thread:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Filter-a-gallery-based-on-a-gallery/td-p/505155

Based on the needs that you mentioned, I think the GroupBy function could achieve your needs in your first Gallery. On your side, please consider set the Items property of the First Gallery to following:

GroupBy( ProjectLijsten; "Order"; "Relatie"; "GroupData" )

Note: The List separator (;) within above formula is based on your local region. In some regions, it use comma (,) as list separator, in other regions, it use semicolon (;) as separator.

Within your above first Gallery, you could only reference "Order" column value and "Relatie" column value directly, the rest of columns in your SP List is grouped into the "GroupData" (third argument of GroupBy function) attribute as a Table value. The result the above GroupBy formula returned is like below:

So could not reference the rest of column values from your SP List within your first Gallery directly, the rest of column values is grouped into the "GroupData" attribute as Table value (as above screenshot).

Within your second Gallery, please set the Items property to following:

Filter( ProjectLijsten; Order = FirstGallery.Selected.Order && Relatie = FirstGallery.Selected.Relatie )

When you click the specific item in your First gallery, the related records would be displayed within your second Gallery.

In addition, if you want to see the rest of column values from your SP List except the "Order" column and "Relatie" column, you could consider add another Gallery (third) inside your first Gallery, then set the Items property of this nested Gallery to following:

ThisItem.GroupData // reference the GroupData Table value from its parent Gallery

within this nested Gallery, you could add several Labels to display the rest of column values from your SP List ('ProjectLijsten') using the following formula:

ThisItem.Subordernumber
ThisItem.Omschrijving
ThisItem.Plaatsnaam

...

...

Please take a try with above solution, check if the issue is solved. If you have solved your problem, please consider go ahead to click "Accept as Solution" to identify this thread has been solved.

Best regards,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello!

Thank you so much for your answer. I am currently having this on my first screen, which is good!
https://gyazo.com/7cb6a339da7e0f08541d20333896ab7e

But now whenever I select a ticket, I don't get all the details of that details. Whenever I click on a ticket, I want to see all the tickets that have that relatie + ordernumber.

Because I want my first gallery to look like this:
https://gyazo.com/b980a8a848461360985e139e24eab760

I have done this:

Of the text of the label in the gallery I have put this:
ThisItem.Result

On the OnSelect of the seperator I've put this:
Navigate([@Overzicht]) (Takes you to the second screen)

Then I've put this in the items of the first gallery:
Distinct(ProjectLijsten;Order & " - " & Relatie)

On the OnVisible of the first screen I've put this:
Set(MyOrder;Gallery2.Selected.Order) (You've told me to put Gallery1 but when I do that it gives me an error

Then at gallery2 I've put this in the items:
Filter(ProjectLijsten;Order=MyOrder)

I also think I am doing something wrong because I can't find some relatie/order numbers. ( I only get like 50 results)
This is a screenshot of Aalberts B.V. being in there: https://gyazo.com/dc617e5e832ba42cc3d1709a7023f22a

Now when I try to look it up:
https://gyazo.com/4a0eda3b51afeb99cb6ad8487c51993c

@eka24

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hello!

Whenever I tried your code I kept getting an error and I do not know what I am doing wrong. Here is the code:
GroupBy( ProjectLijsten; "Order"; "Relatie"; "GroupData" )
So based on this I can't try your code.
Here are the errors: https://gyazo.com/2ac931c819dbd1bc797a378055e0f724
@v-xida-msft

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Anonymous ,

Have you renamed the "Title" column into "Order" in your SP list? Where is your Title column?

Based on the issue that you mentioned, it seems to tell that the "Order" column is not existed in your SP List. If you have renamed "Title" column into "Order" in your SP list, you need to modify your as below:

GroupBy( ProjectLijsten; "Title"; "Relatie"; "GroupData" )

Note: You could choose proper column within above function based on the expression Intelligent prompts in your canvas app.

In addition, you could also consider take a try with @eka24 's solution. Set the Items property of the First Gallery to following:

Distinct(ProjectLijsten; Order & " " & Relatie)

then set the Items property of your second Gallery to following:

Filter( ProjectLijsten; Order = First(Split(FirstGallery.Selected.Result; " ")).Result && Relatie = Last(Split(FirstGallery.Selected.Result; " ")).Result )

Please take a try with above solution, check if the issue is solved.

Best regards,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hello!

I am able to use this code perfectly, I am also using the column "Order" in here and it's also saying it can't find the column Relatie. So the problem is not there @v-xida-msft

Search( Distinct( ProjectLijsten; Order & " - " & Relatie ); TextSearchBox2.Text; "Result" )

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Still ends up with an empty second gallery sadly. Is there any way of me sending my sharepoint list to you so you could maybe test with the database itself?

@v-xida-msft

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Anonymous ,

Do you want to reference the "Relatie" column value directly within your first Gallery or second Gallery based on the Distinct formula?

If you use above Distinct function in your First Gallery, you could only reference the Result attribute within your First Gallery, you could not reference other column values. The result the Distinct formula returned only contains Result attribute, so you could only reference the Result property in your First Gallery.

If you want to reference the "Relatie" column value directly within your first Gallery, I think the AddColumns function could achieve your needs. Set the Items property of the First Gallery to following:

Search( AddColumns( Distinct( ProjectLijsten; Order & " - " & Relatie ); "Order"; First(Split(Result, " - ")).Result; "Relatie"; Last(Split(Result, " - ")).Result ); TextSearchBox2.Text; "Result"; "Order"; "Relatie" )

then within your First Gallery, you could reference the "Relatie" column value directly.

If you still have issue within this scenario, I think a quick call in Teams may be better in your scenario.

Best regards,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

A call in teams would be amazing. When would you have time to get into a call with me? @v-xida-msft

Was this reply helpful? Yes No
eka24 20,925 on at

Like
a
(1)

Report
Copy link

Link copied!

Do you get same result when you use Distinct(ProjectLijtsen,Order) on Gallery1 instead of the Concatenate Distinct(ProjectLijtsen,Order&" "&testie)?

1. On screen1 with Gallery1, put this formula on the Item of the Gallery1:
Distinct(ProjectLijtsen,Order)
2. Set a Variable OnVisible of the Screen:
Set(MyOrder,Gallery1.Selected.Order)
3. On Screen2 with Gallery2, put this formula on the Item of the Gallery2:
Filter(ProjectLijtsen,Order=MyOrder)

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No