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

Filtering like strings in different sharepoint columns

Hello,

I have a list of strings that start with a number in one drop down and what I want to do is filter the next drop down by like items. For instance, 

 

Choices(EAPT_Apps.Business_x0020_Category) has 14 options.

 

For instance 9.0 Manage Financial Resources.

If I select 9.0 Manage Financial Resources, I want the next drop down Choices(EAPT_Apps.L2Processes) to be filtered by the first dropdown with items that begin with 9.

 

I then have a third dropdown that should be filtered by the selection of Choices(EAPT_Apps.L2Processes).

 

Also, since these are strings, the sorting of the options is off. It starts with 1.xxxx, 10.xxxx, 11.xxxx, 2.xxxx and so on. I would like to sort logically 1,2, 3, etc. Anyway to do that with strings in PowerApps?

 

See screenshot. Again, choose first dropdown, it filters the next dropdown with options only related to the first. Choose the options in the second dropdown and it filters the choices in the third dropdown. These are all in separate columns in sharepoint and their is no relationship in Sharepoint.

 

 

4 REPLIES 4
Super User
Super User

Re: Filtering like strings in different sharepoint columns

Hi @clundy 

 

To get only the filtered value based on dropdown selection, please follow the below steps:
1) Suppose your First dropdown is dropdown1, so to get the selected value, expression would be: Dropdown1.Selected.Value
 
2) Now in the items of second dropdown, please this expression,
Sort(Filter(Choices(EAPT_Apps.L2Processes),StartsWith(Value,First(Split(Dropdown1.Selected.Value,".")).Result )),Value,Ascending)
 
3) Now for the third dropdown items, Please use the below expression:
Sort(Filter(Choices(EAPT_Apps.L3Processes),StartsWith(Value,First(Split(Dropdown2.Selected.Value," ")).Result )),Value(Value),Ascending)
Value() function will sort the dropdown values logically.
 
Note: A dropdown doesn't allow multiple selection at a time, so if your scenario consist of selecting multiple options, then going for Combobox will be a good option.
 
Hope this helps!
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
clundy
Level: Powered On

Re: Filtering like strings in different sharepoint columns

This partially worked. While it did filter the results, it only filtered the last option entered not all of the options. Since this is a multi-select and there could be multiple values, I want to filter the next dropdown by all values for each item selected. For instance, if I choose 9.8 and 10.2, I should see all possible values for 9.8 and 10.2 not just 10.2 as it is now. It should take all values selected.

 

Also, the logical sorting is not sorting properly. It is still sorting 1, 10, 11, 2, 20, 3, 30, etc. instead of 1,2,3,4,5 etc.

 

Any ideas?

Super User
Super User

Re: Filtering like strings in different sharepoint columns

@clundy 

 

Are you using a combobox? Dropdown doesnot support the functionality of multiple selection. 
 
To perform a logical sort, please update your second dropdown item as:
Sort(Filter(Choices(EAPT_Apps.L2Processes),StartsWith(Value,First(Split(Dropdown1.Selected.Value,".")).Result )),Value(Value),Ascending)
 
Hope this Helps!
Super User
Super User

Re: Filtering like strings in different sharepoint columns

@clundy 

 

Please use the following expression on each of your combobox and this should work fine:
 
Dropdown1:  Sort(Choices(List1.FirstDropChoices),First(Split(Value,".")).Result,Ascending)
here List1 is the name of my Sharepoint list and FirstDropChoices is the name of its attribute. Please replace it with the values in your lists. eg. Choices(EAPT_Apps.Business_x0020_Category)
 
Dropdown2:
Sort(Sort(Filter(Choices(List2.ChoiceColumnDrop2),Concatenate(First(Split(Value,".")).Result,".") in ForAll(ComboBox1.SelectedItems,Concatenate(First(Split(Value,".")).Result,"."))),Value(Last(Split(First(Split(Value," ")).Result,".")).Result),Ascending),First(Split(Value,".")).Result,Ascending)
 
Here List2 is my second sharepoint list and ChoiceColumnDrop2 is the name of my attribute. We need to do a multisort because Value() was not covering the decimal points alone. So we split the number and apply a multiorder sort. Replace  Choices(List2.ChoiceColumnDrop2) with Choices(EAPT_Apps.L2Processes).
 
Dropdown3:
Sort(Sort(Sort(Filter(Choices(List3.ChoiceColumnDrop3),Concatenate(First(Split(Value,".")).Result,".",Last(FirstN(Split(Value,"."),2)).Result,".") in ForAll(ComboBox2.SelectedItems,Concatenate(First(Split(Value," ")).Result,"."))),Value(Last(Split(First(Split(Value," ")).Result,".")).Result),Ascending),Value(Last(FirstN(Split(Value,"."),2)).Result),Ascending),Value(First(Split(Value,".")).Result), Ascending)
 
Here List3 is my third sharepoint list with ChoiceCOlumnDrop3 as my choices attribute. Again, applied a multisort to sort the values on all the three numbers "a.b.c" 
 
I replicated the same values in my SP list and able to see the sorted results based on selection in other comboboxes.
MicrosoftTeams-image (81).png

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 60 members 4,387 guests
Please welcome our newest community members: