cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Owizardo
Helper III
Helper III

How to check if selected items match Sharepoint choice column

Hello,

 

I am hoping this is a simple answer for someone. I have a simple list in sharepoint with a choice column (example: Red, Blue, Green). In my form a have a multiple choice list box with the same items (Example Red, Blue, Green). 

 

In my form I want to be able to filter my Sharepoint list based on the choice column. For example, on my form I click Red in my list box, I am expecting it to return all items that that match Red from my Sharepoint choice column.

 

Since its a multiple choice column, I would also expect that if I select Red, Blue in by selection box, it should return all items that that match Red and Blue from my Sharepoint choice column.

 

I tought this was going to be a simple formula:

ForAll(Sharepoint_Source_Data,
    If(Colors = Data.SelectedItems, 
      Collect(Filtered_Collection, ThisItem)
    )
)

But this dosent work and give me errors saying the equal in my formal is not valid. 

 

Any ideas how to accomplish this?

Update:

I feel like im getting closer but still cant get this to work. Heres where im at:

 

Clear(Collection_List);
ForAll(Sharepoint_Source_Data,

	ForAll(Combox.SelectedItems,
		If(Value in Sharepoint_Source_Data[@Color], 
                   Collect(Collection_List, {Color: Sharepoint_Source_Data[@Color]} )
                )
	)
)
1 ACCEPTED SOLUTION

Accepted Solutions

@Owizardo 

Ah, sorry, I missed that you had multi-select on the columns.

So, you are basically saying that if a user selects "Red", "Blue" in the ListBox, then you want all the records that may have "Red" or "Blue" in the Color column? 

Quick and simple formula would be this:

Clear(Filtered_Collection); 
ForAll(yourListBox.SelectedItems.Value,  
          Collect(Filtered_Collection, 
                      Filter(SharePoint_Source_Data, Value in Colors.Value)))

This will give you a Collection with the matching records.

However...beware, this will produce duplicates if you have multiple selections on both the record and the listbox.

 

You could solve the above issue with:

Clear(Filtered_Collection); 
ForAll(yourListBox.SelectedItems.Value,
Collect(Filtered_Collection,
Filter(
Filter(
SharePoint_Source_Data, Value in Colors.Value
),
!(ID in Filtered_Collection.ID)
)
)
)

I hope that is more helpful for you.

 

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

3 REPLIES 3
RandyHayes
Super User
Super User

@Owizardo 

Typically a Form will show one record to view, edit or create.  So, I'm not following what you mean by filtering your list.  What are you filtering your list in?  Do you have a Gallery or some other type of list?

 

If you are just trying to build a collection of items in your Sharepoint list that match the color chosen, then you might want to consider this formula instead:

ClearCollect(Filtered_Collection, 
Filter(Sharepoint_Source_Data, Colors = Data.Selected.Value)
)

This assumes that Colors is a text column and that Data is your dropdown/combobox control.

 

I hope that helps.  If the assumptions are different, then post back.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hi Randy thanks for the quick answer. 

 

I feel like you answer would work on a single choice selection. As both my column and my combo box are multiple choice values. How would filter that?

 

I tought this could work but it gives me errors at the colors saying it cant handle table data:


ClearCollect(Filtered_Collection, 
Filter(Sharepoint_Source_Data,
Colors in Data.Selected.Value)
)  

 

I also tried the following, this dosent give me errors but dosent seem to collect anything...

 

Clear(Collection_List);
ForAll(Sharepoint_Source_Data,

	ForAll(Combox.SelectedItems,
		If(Value in Sharepoint_Source_Data[@Color], 
                   Collect(Collection_List, {Color: Sharepoint_Source_Data[@Color]} )
                )
	)
) 

Any ideas?

@Owizardo 

Ah, sorry, I missed that you had multi-select on the columns.

So, you are basically saying that if a user selects "Red", "Blue" in the ListBox, then you want all the records that may have "Red" or "Blue" in the Color column? 

Quick and simple formula would be this:

Clear(Filtered_Collection); 
ForAll(yourListBox.SelectedItems.Value,  
          Collect(Filtered_Collection, 
                      Filter(SharePoint_Source_Data, Value in Colors.Value)))

This will give you a Collection with the matching records.

However...beware, this will produce duplicates if you have multiple selections on both the record and the listbox.

 

You could solve the above issue with:

Clear(Filtered_Collection); 
ForAll(yourListBox.SelectedItems.Value,
Collect(Filtered_Collection,
Filter(
Filter(
SharePoint_Source_Data, Value in Colors.Value
),
!(ID in Filtered_Collection.ID)
)
)
)

I hope that is more helpful for you.

 

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (2,974)