cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JimmyWork
Power Participant
Power Participant

Filter SharePoint column with multi values using combobox with multi values

Hi

 

I have a SharePoint meta data column that pulls items from a term set, this column allows multiple values.

I then want to filter my gallery by using a combox where i can select multiple values.

 

Basically what I select in the combox i want the gallery to filter. Now for some reason i'm not able to do this, i can do it if i use single selected item but not multiple.

 

This works for single item.

Filter(TestList, ComboBox1.Selected.Value in (MultiModule.Value))

If i try the following i get an error, Cannot use table values in context.

Filter(TestList, ComboBox1.SelectedItems.Value in (MultiModule.Value))
2 ACCEPTED SOLUTIONS

Accepted Solutions
CNT
Community Champion
Community Champion

@JimmyWork 

Try this,

  • Change List, column names as required.
  • ID is a column with a unique value.
  • This will give a delegation warning and may not work with more than 2K records.
Filter(yourSPList, ID in Ungroup(ForAll(yourComboBox.SelectedItems,Filter(yourSPList,Value in yourChoicesColumn.Value)),"Value").ID)

View solution in original post

CNT
Community Champion
Community Champion

@JimmyWork Glad it works for you!

The default setting for delegation is 500. But you can increases it up to 2000 in the Settings. If you want to pull in more than 2000, you will have to pull them in batches of 2K and put them into a Collection and use it.

To get the latest 500 - depends on what latest means for you. If it is the last 500 ID's than you can filter based on that. If it if last 500 modified records you can filter on the modified date and so on.

 

View solution in original post

6 REPLIES 6
CNT
Community Champion
Community Champion

@JimmyWork 

Try this,

  • Change List, column names as required.
  • ID is a column with a unique value.
  • This will give a delegation warning and may not work with more than 2K records.
Filter(yourSPList, ID in Ungroup(ForAll(yourComboBox.SelectedItems,Filter(yourSPList,Value in yourChoicesColumn.Value)),"Value").ID)

View solution in original post

JimmyWork
Power Participant
Power Participant

@CNT Thank you that worked perfectly.

 

For the delegation will it only pull the first 500 or 2000 items or how does it work?

Let's say my list contains 5000 items and I do a filter using the solution you provided, will I then only get the first 500? If it's always the latest 500 items then I'm good 🙂

CNT
Community Champion
Community Champion

@JimmyWork Glad it works for you!

The default setting for delegation is 500. But you can increases it up to 2000 in the Settings. If you want to pull in more than 2000, you will have to pull them in batches of 2K and put them into a Collection and use it.

To get the latest 500 - depends on what latest means for you. If it is the last 500 ID's than you can filter based on that. If it if last 500 modified records you can filter on the modified date and so on.

 

View solution in original post

JimmyWork
Power Participant
Power Participant

@CNT Thank you again for answering.

So if I use the following i would then always pull the latest 500 created items or 2000 if I increase it to 2000.

 

SortByColumns(Filter(TestList, ID in Ungroup(ForAll(ComboBox1.SelectedItems,Filter(TestList,Value in MultiModule.Value)),"Value").ID), "Created", Descending)
CNT
Community Champion
Community Champion

@JimmyWork According to the documentation (https://docs.microsoft.com/en-gb/powerapps/maker/canvas-apps/delegation-overview#sorting-functions), SortByColumns is delegable and so should work.

JimmyWork
Power Participant
Power Participant

@CNT Thank you but I still get the delegation warning.

 

Combobox thats gets data from a choice column in SharePoint that is connected to term store.

 

Search(
    If(IsBlank(FilterByRequester.Selected.Mail) && IsEmpty(FilterByRequester.Selected.Mail),
    SortByColumns(SharePointList, "Created" ,Descending),

Sort(
Filter(
		SharePointList,
    IsBlank(FilterByRequester.Selected.Mail) || IsEmpty(FilterByRequester.Selected.Mail) || 'Requested By'.Email = FilterByRequester.Selected.Mail,
    IsBlank(FilterByAssignedTo.Selected.displayName) || IsEmpty(FilterByAssignedTo.Selected.displayName) || 'Assigned To'.DisplayName = FilterByAssignedTo.Selected.displayName,
    IsBlank(FilterByStatus.Selected.Value) || IsEmpty(FilterByStatus.Selected.Value) || Status.Value = FilterByStatus.Selected.Value,
    IsBlank(FilterByModule.Selected.Value) || IsEmpty(FilterByModule.Selected.Value) || Module.Value = FilterByModule.Selected.Value), Created, Descending)),
    SearchInputAgent.Text,"TicketID", "Title")

 

I get the delegation warning here || Module.Value = FilterByModule.Selected.Value)

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,463)