Answered

How to check if selected items match Sharepoint choice column

(0) Share

Report

Posted on by Owizardo

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]} )
 )
	)
)

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@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.
Owizardo 98 on at

Like
a
(0)

Report
Copy link

Link copied!
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?
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@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.