cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JJarmuzek
Frequent Visitor

Filter items in gallery based on all items of another gallery

Hi. 

 

I'm building a power page based on the Dynamics 365 entities.

It consists of 2 galleries - left and right.

In the left gallery user has all Subcategories that are available. In the right one there is an entity called Chosen Subcategories which has lookup to Subcategory.

I need a formula to filter the left gallery to show only items that are not added as Chosen Subcategory. Something like 

GalleryLeft.Item.SubcategoryId != Any(GalleryRight.SubcategoryId).

Problem is that I can't compare ThisItem.SubcategoryId with GalleryRight.AllItems because it is different type and GalleryRight.Selected is wrong because I need to compare to all of the items.

 

JJarmuzek_0-1656416471520.png

JJarmuzek_1-1656416497976.png

 

 

9 REPLIES 9
One
Super User
Super User

Hi @JJarmuzek,

in the items property of your left gallery you will want to use something similar to the following:

Filter(NameOfLeftGalleryList, 
LeftGalleryName.SubcategoryID <> RightGalleryName.Selected.SubcategoryID
)

 However, this will only filter if an item in the right gallery has been selected. if you want it to default to show everything when there hasn't been a selection you will want to add an IF statement as follows:

If(
IsBlank(RightGalleryName.Selected.SubcategoryId),
NameOfLeftGalleryList,
Filter(NameOfLeftGalleryList,
LeftGalleryName.SubcategoryID <> RightGalleryName.Selected.SubcategoryID
))

You will need to adjust the list name and gallery names for your app.

 

------------------------------------------------------------------------------------------------------------------------------
Please click "Accept as Solution" if this post answered your question so that it is helpful for people having similar questions. If you found this post helpful smack that Thumbs up button! Thanks!

JJarmuzek
Frequent Visitor

hello,

 

thanks for the answer but this doesn't solve my problem.

Gallery on the left shows available items. User click "+" to create a new record of entity on the right. Item on left is only a lookup to an item on the right.

I can't show any of the left items that exist in the right gallery.

Any SubcategryId from left list must not exist in ChosenCategory.SubcategoryId (right).

It should trigger on load and button clicks

One
Super User
Super User

Can you try to elaborate on your issue a bit more? I am really confused as to what you are actually trying to do.

Also, you said you want it to filter on load, but if that filter requires an item in your gallery to have been selected then that filter wont work because it requires a selection to trigger it. 

JJarmuzek
Frequent Visitor

Req: I don't want to show on the left any entities that has been created before and are visible on the right.

JJarmuzek_0-1656579824901.png

 

 

One
Super User
Super User

@JJarmuzek,

ok I get what you're trying to do now, you're going to want to adjust the following code into your left gallery's items property:

Filter(LeftGalleryListName,Not(SubCategoryID exactin RightGalleryName.AllItems.SubCategoryID))

adjust the gallery/column names to yours and it will only show the entries in your list where left gallery's subcategory ID's don't exist in the right gallery

------------------------------------------------------------------------------------------------------------------------------
Please click "Accept as Solution" if this post answered your question so that it is helpful for people having similar questions. If you found this post helpful smack that Thumbs up button! Thanks!

JJarmuzek
Frequent Visitor

Thank you for the contribution, I think we are closer but there are still issues.

 

My filter statement is 

Filter('Request Subcategory',Not(ThisRecord.'Request Subcategory' exactin publicRequestCategories.AllItems.'Request Subcategory'))

 

But I get error on this formula: Can't convert this data type. Power Apps can't convert this Guid to a Record.

 

When I select the items in formula it says that "AllItems.Request Subcategory" is table and ThisRecord.Request Subcategory is Guid. Both datasources for galleries are Dynamics 365 entities. Are you able to help here?

Hi @JJarmuzek ,

You cannot filter a gallery conditional on its own content - you need to filter the source of the gallery - Assuming the list name here

Filter(
   Subcategories,
   !(SubcategoryId in GalleryRight.AllItems.SubcategoryId)
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

JJarmuzek
Frequent Visitor

@WarrenBelz @One 

Unfortunately this is still giving me the error, it looks like I can't access the primary key column on the source entity for this gallery.

 

JJarmuzek_0-1656929696502.png

 

@JJarmuzek ,

It is highly useful to mention your data source as this can completely affect any response. I am a SharePoint user, so will bow out, but I still believe the issue lies in the area I mentioned.

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (3,950)