Unanswered

Filter a gallery using two separate combo boxes which both have a large number of choices

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Dear experts,

I'm still building an app that allows filtering users from a SharePoint list (MySPList). Thanks to the community members (the post here), I managed to resolve a big problem; filtering the gallery using a combo box with multi-select choice using this formula added to the Items property of the gallery:

Filter(colMySPList, 
IsEmpty(SkillsBox_1.SelectedItems) || IsBlank(SkillsBox_1.SelectedItems) || Index(
 SkillsBox_1.SelectedItems.Value,
 1
 ).Value in Skills.Value || Index(
 SkillsBox_1.SelectedItems,
 2
 ).Value in Skills.Value || Index(
 SkillsBox_1.SelectedItems,
 3
 ).Value in Skills.Value || Index(
 SkillsBox_1.SelectedItems.Value,
 4
 ).Value in Skills.Value || Index(
 SkillsBox_1.SelectedItems.Value,
 5
 ).Value in Skills.Value,

colMySplist: is a collection of my SharePoint list. I'm using a collection to overcome the delegation problems.

SkillsBox: is the name of the combo box which has 5 choices.

I have two other combo boxes (Nationality and Language) that both have a large number of items (more than 100 for languages). Is there a way I can use a similar formula as above without adding up the index number over and over? There are two additional obstacles here:

The above items property of the gallery also includes some other options, like gender of the users that I'm not posting on this thread. So, the formula should be able to handle these additional filtering options.
In my SP list, I have two separate language columns, Major Language and Other Languages. In the combo box's item property, I have a collection that combines these two columns into one data source as below:

ClearCollect(
 colComboBox,
 ForAll(
 Choices('MySPList'.'Major Language'),
 ThisRecord.Value
 ),
 ForAll(
 Choices('MySpList'.'Other Languages'),
 ThisRecord.Value
 )
)

In other words, I should be able to filter the gallery using either both collections (colComboBox for languages and colMySPlist for the rest of the data) or in a way that enables me filtering for all the additional filters (gender etc.), the 5 skills, nationalities and both Major and Other Languages, without any delegation issues.

Thanks for your help,

Categories:

Building Power Apps

All responses (7)

Answers (0)

Sort by

WarrenBelz 156,750 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @thatakke ,

If you are using a collection, you have no Delegation issues as the processing does not call on the data source, so

Filter( colMySPList, Len(SkillsBox_1.Selected.Value) = 0 || Skills.Value in SkillsBox_1.SelectedItems )

You can simply add the two other combo boxes into the above. You can also (as per this blog of mine) use a Combo Box Items directly on a List largely solving Delegation

If( Len(SkillsBox_1.Selected.Value) = 0, MySPList, Ungroup( ForAll( SkillsBox_1.SelectedItems As aSel, Filter( MySPList, Skills.Value = aSel.Value ) ), "Value" ) )

and you might see if this helps your exercise.

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
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @WarrenBelz

Thank you very much for your answer. You might remember from another post, I'm very new to MS Power Apps, and thus unfortunately, I couldn't replicate your suggestion well enough. I have two separate collections as mentioned above (colComboBox and colmySPList), I couldn't merge your suggestion with the input I already had, so I duplicated the screen and tried to replicate your suggestion. When I add the below input to the items property of the gallery, it looks like it's working (there is some movement in the Gallery), but because I removed all of the fields (namely, the colmySPlist collection), I can't see anything in the Gallery. This is the input I had for the Items property.

Filter( colComboBox, Len(LanguageBox_1.Selected.Value) = 0 || Value in LanguageBox_1.SelectedItems )

I thought I could use your input for the "Nationality" field, as it uses the same collection:

Filter( colMYSPList, Len(NationalityBox_1.Selected.Value) = 0 || Nationality.Value in NationalityBox_1.SelectedItems, IsEmpty(SkillsBox_1.SelectedItems) || IsBlank(SkillsBox_1.SelectedItems) || Index( SkillsBox_1.SelectedItems.Value, 1 ).Value in Skills.Value || Index( SkillsBox_1.SelectedItems, 2 ).Value in Skills.Value || Index( SkillsBox_1.SelectedItems, 3 ).Value in Skills.Value || Index( SkillsBox_1.SelectedItems.Value, 4 ).Value in Skills.Value || Index( SkillsBox_1.SelectedItems.Value, 5 ).Value in Skills.Value, StartsWith( 'Name of the Person', SearchLabel_1.Text ))

When I add this to my Gallery, I'm getting an error under "Value" of this part:

Nationality.Value in NationalityBox_1.SelectedItems

"Cannot use Table values in this context". Like "Skills" column, the nationality column in my collection is a Table.
Any other guidance you might have?

Thank you,
WarrenBelz 156,750 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@thatakke ,

If Nationality is a single selection, you just need

Nationality.Value = NationalityBox_1.Selected.Value

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
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks once again @WarrenBelz
It is not a single selection, as some people might have multiple nationalities. This is also the reason I'm trying to use the same set up I used for the "Skills" column/combo box. Knowing it wouldn't work, I still tried changing the text to what you suggested, but as expected, it didn't work.

Thanks,
WarrenBelz 156,750 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@thatakke ,

Is the Nationality field also a multiple selection (as well as the combo box)
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @WarrenBelz
Yes, both the Nationality column and the combo box are multi select. The same goes for the Language column and combo box.
WarrenBelz 156,750 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@thatakke ,

This is pushing the limits of a workaround I have and you have really set yourself up for some potential problems with this structure. You can try this, but it has Delegation limitations, particularly on the second filter.

With( { wList: If( Len(SkillsBox_1.Selected.Value) = 0, MySPList, Ungroup( ForAll( SkillsBox_1.SelectedItems As aSel, Filter( MySPList, aSel.Value in Skills.Value ) ), "Value" ) ) }, If( Len(NationalityBox_1.Selected.Value) = 0, wList, Ungroup( ForAll( NationalityBoxBox_1.SelectedItems As aSel, Filter( wList, aSel.Value in Nationality.Value ) ), "Value" ) ) ) )

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