cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
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
Highlighted
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.
Highlighted
Helper III
Helper III

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?

Highlighted
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
Check this Out

Announcing

Class of 2020- Season 2

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,245)