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

How to use Multiple Values taken from SharePoint list used as Filters to secondary SharePoint List?

My team and I are completely new to powerapps and have only done small scale projects. We have task that we have been trying to accomplish but have come up empty so far... We have 2 SharePoint lists one list contains a series of keywords simply items with titles. The second list contains a series of information with the second list functioning as a lookup column for the first. We would like to create a powerapp that allows the user to select several of the "keywords" from the first list to be used as filter values for the display of the second list. Is there a way to use multiple selected values to filter results? If so how can this be best accomplished? As a side note these "keywords" do not necessarily need to be stored in a separate list however there will be several dozen keywords upwards of 75-100.

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff cherie
PowerApps Staff

Re: How to use Multiple Values taken from SharePoint list used as Filters to secondary SharePoint Li

Unfortunately we don't have it in built to support multiple values from a sharepoint list, you can vote for the feature here (https://powerusers.microsoft.com/t5/PowerApps-Ideas/Multiple-value-lookup-Sharepoint/idi-p/31408).

 

However I did figure out a solution.

 

I have two lists.

 

ListA (contains a column Key)

ListB (contains a column Key, and column description)

 

Add a ListBox control.

Set ListBox.Items = Split(Concat(ListA.Key, Key&","), ",")

 

Add a gallery control

Set Gallery.Items = Filter(ListB, If(Key in ListBox.SelectedItems.Value, true))

 

I hope this helps!

 

Thanks

Cherie

 

View solution in original post

12 REPLIES 12
PowerApps Staff cherie
PowerApps Staff

Re: How to use Multiple Values taken from SharePoint list used as Filters to secondary SharePoint Li

Unfortunately we don't have it in built to support multiple values from a sharepoint list, you can vote for the feature here (https://powerusers.microsoft.com/t5/PowerApps-Ideas/Multiple-value-lookup-Sharepoint/idi-p/31408).

 

However I did figure out a solution.

 

I have two lists.

 

ListA (contains a column Key)

ListB (contains a column Key, and column description)

 

Add a ListBox control.

Set ListBox.Items = Split(Concat(ListA.Key, Key&","), ",")

 

Add a gallery control

Set Gallery.Items = Filter(ListB, If(Key in ListBox.SelectedItems.Value, true))

 

I hope this helps!

 

Thanks

Cherie

 

View solution in original post

LexHall
Level: Powered On

Re: How to use Multiple Values taken from SharePoint list used as Filters to secondary SharePoint Li

That works perfectly... However we have one additional requirement that perhaps you could help with. We would like the ability to use more than one Keyword. Currently our SharePoint List has a Concated Text Field that would have content similar to "Key 1; Key 3" would there be a way to change the Filter into something that would find multiple Keys? I see right now that its searching for the Key in the ListBox data but is there a way to make each ListBox value to search in the Key one at a time? I hope this makes sense.

PowerApps Staff cherie
PowerApps Staff

Re: How to use Multiple Values taken from SharePoint list used as Filters to secondary SharePoint Li

I'm not sure I quite understand, you could split the concatenated text field by the ; symbol? Could you provide a small example of what the data is like?

 

 

 

LexHall
Level: Powered On

Re: How to use Multiple Values taken from SharePoint list used as Filters to secondary SharePoint Li

Sure thing... So we now have a list that contains 2 columns a "Title" and a "Keywords". The "Keywords" column is a single line of text field and through infopath concats Keywords found in a seperate list. So the "Keywords" column would look something like "Key 1 ; Key 3" What we would like to do is be able to use the ListBox Selections to filter the Gallery by the Selected Keywords. This would allow the ListBox Selection of "Key 1" and/or "Key 3" to display items containing "Key 1 ; Key 3" Keywords and so on. I found the Split function but wasnt able to work out a way to use the resulting table to compare to the Values selected from the ListBox and then filter the gallery.

LexHall
Level: Powered On

Re: How to use Multiple Values taken from SharePoint list used as Filters to secondary SharePoint Li

I thought that perhaps providing some screen shots of the actual project might be helpful

 

 

 

KeywordList.JPG

Here is a shot of the Keyword list we are using. The Category Column corresponds to a seperate List we are using to further sort the Data. 

 

 

 

 

ServicesList.JPG

Here is the list containing the Data we want to sort... we are sorting by 4 values curently The "system" the "tier" the "group" and we would like to sort by the "Keys" The Keys are populated from the list above and then using InfoPath rules Concated into a single line of text with " ; " seperating each value the user adds.

 

 

 

 

PowerappsSnip.JPG

Here is the layout of the PowerApps form we are currently filtering the Keywords list by what Category is selected as seen above with Category 1 Showing only the Category 1 Keys. The filtering is working perfectly for Category Tier and System by using the code you provided earlier however we would now like the option to filter the Keys if more than one Key is Selected. Our filter code (Which is very much incomplete for right now) looks something like this.

 

Filter(ServicesCatalog,
If(Radio1.Selected.Value = "None", true) ||
If(Radio1.Selected.Value in UsedGroup && IsEmpty(ListBox1.SelectedItems.Value), true) ||
If(UsedKeys in ListBox1.SelectedItems.Value, true),
SearchInput1.Text in Title)

 

We are hoping that there is some way to check the Keys against the User Selected Keys to filter the results properly. I hope that this makes the situation a little more clear to what we are trying to accomplish... Also I want to thank you greatly for the first snippet of code that definitly got me started in solving this problem.

PowerApps Staff cherie
PowerApps Staff

Re: How to use Multiple Values taken from SharePoint list used as Filters to secondary SharePoint Li

I'm not 100% certain that I'm doing what you want, but I was able to use the search function.

 

https://powerapps.microsoft.com/en-us/tutorials/function-filter-lookup/

 

I have a collection of data (called MyData) like this:

{Key: "key1", UsedKeys: "key1;key2" }, {Key: "key2", UsedKeys: "key3;key2" },{Key: "key3", UsedKeys: "key1;key2" }, {Key: "key4", UsedKeys: "key1;key4" }

 

I have a dropdown contain all the keys.

 

Then I have a gallery control where the Items are bound to this:

Search(MyData, Dropdown1.Selected.Value, "UsedKeys")

 

 

When I modify the dropdown, the gallery updates to show the entries where the key is within UsedKeys.

 

Another option I did was this (instead of the search):

Filter(MyData, Dropdown1.Selected.Value in Split(UsedKeys, ";"))

 

 

Highlighted
LexHall
Level: Powered On

Re: How to use Multiple Values taken from SharePoint list used as Filters to secondary SharePoint Li

That is close to what we would like. However we would like to keep the Key dropdown as a ListBox to allow multiple selections... I think what I am really trying to do is use a table of filter parameters to filter a table of UsedKeys in the data source. Do you think that this is possible or am I trying to accomplish something simply not supported?

PowerApps Staff cherie
PowerApps Staff

Re: How to use Multiple Values taken from SharePoint list used as Filters to secondary SharePoint Li

Yes you can. I have a very basic example here

 

MyData contains

key1 | value1

1       | "a"

2       | "b"

3       | "c"

 

My ListBox Items is bound to MyData.

In the properties pane under advanced, you can change the value that is shown:

listboxdata.PNG

 

I then had a Label with the following formula:

Concat(ForAll(ListBox1.SelectedItems,value1),Text(Value)," ")

 

I did discover that key is a special keyword so it wasn't working when i had my column called key.

sasidhar_mudra
Level: Powered On

Re: How to use Multiple Values taken from SharePoint list used as Filters to secondary SharePoint Li

@cherie ,

 

Hi Cherie,

Hope you are doing good. I have a question and not sure whether it can be achieved or not. It is similar to this post.

 

I created a form from SP list (skip_test1). It has Manger Name, Employee Coulumn, etc.... I want to filter the employee names according to the manager name selected. Both Manager & Employee name columns are Single Line of Text in SP list.

 

In the form i changed the editing properites of Manager & Employee Name to ALLOWED VALUES so that i can filter the details. However, the DropDown did not allow me to select multiple employee names. So, i ended up adding ListBox and updated the property of ListBox same as DropDown. 

 

I completed the APP and Patched all the details to new SP list so that i can see what are the details getting updated. I see everything is updating in the SP list. However, i see only one employee name is getting updated even though i select multiple names in the ListBox.

 

Is there any work around to use FIlter() & Concat both in ListBox Item: property so that i can update multiple employee names in SP list?

 

Any help is appreciated.. Thanks in advance 🙂

 

 

Regards

Sasidhar

 

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
Users Online
Currently online: 181 members 5,628 guests
Please welcome our newest community members: