cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Owizardo
Level: Powered On

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
Super User
Super User

Re: How to check if selected items match Sharepoint choice column

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

3 REPLIES 3
Super User
Super User

Re: How to check if selected items match Sharepoint choice column

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Owizardo
Level: Powered On

Re: How to check if selected items match Sharepoint choice column

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?

Super User
Super User

Re: How to check if selected items match Sharepoint choice column

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,277)