I'm attempting to build a search feature in a combobox in a PowerApps canvas app where there can be >17 billion permutations from multiple columns of choices.
For example:
ItemID | col01 | col02 | col03 | col04 |
Item1 | a,b,c | 1,2,3,4,5 | cd,g1,h5 | |
Item2 | w,x,y,z | ab,bd | 14,16,18,20 | c,d,e |
etc |
Is there a way to set up a search where a user can start typing "bd16" and Item2 would be returned without creating a collection of all 17 billion+ possible permutations, maybe with MatchAll?
Thanks in advance!
Solved! Go to Solution.
Figured out a solution (takes a few steps):
- Added a hidden slider with default = Len(ComboBox.SearchText)
-In OnChange of slider, create temporary collection with a concatenated list of potential matches, then add those to the ComboBox data source/collection with concat function by item#
-Include added column in search field of combobox
Slider OnChange:
Clear(tempMatches);
ClearCollect(tempTable,AddColumns(DataSource,"Matches",
With({
a:ForAll(Split(Concat(Filter(Split(ThisRecord.'col01',","),Find(Lower(Result),Lower(ComboBox1.SearchText))>0).Result,Result,","),","),Result),
b:ForAll(Split(Concat(Filter(Split(ThisRecord.'col02',","),Find(Lower(Result),Lower(ComboBox1.SearchText))>0).Result,Result,","),","),Result),
c:ForAll(Split(Concat(Filter(Split(ThisRecord.'col03',","),Find(Lower(Result),Lower(ComboBox1.SearchText))>0).Result,Result,","),","),Result),
d:ForAll(Split(Concat(Filter(Split(ThisRecord.'col04',","),Find(Lower(Result),Lower(ComboBox1.SearchText))>0).Result,Result,","),","),Result))
},
ForAll(Sequence(CountRows(a)) As A,
ForAll(Sequence(CountRows(b)) As B,
ForAll(Sequence(CountRows(c)) As C,
ForAll(Sequence(CountRows(d)) As D,
Collect(tempMatches,{ItemID:ID1,Matches:Concatenate(
Last(FirstN(a,A.Value).Value).Value,
Last(FirstN(b,B.Value).Value).Value,
Last(FirstN(c,C.Value).Value).Value,
Last(FirstN(d,D.Value).Value).Value)})
)
)
)
)
)
));
ClearCollect(DataSource2,AddColumns(ShowColumns(DataSource,"ID1"),"Matches",Concat(Filter(tempMatches,ItemID=ID1).Matches,Matches,",")));
This only works for a specified number of columns, but that's acceptable for my purposes. Would welcome any suggestions/comments if there's other methods to accomplish the dynamic search.
You can use the Search function and/or the in operator, but these will not be delegable. So you need to first determine if your datasource record totals will exceed the maximum record limit of PowerApps.
If so, they you will encounter issues with getting accurate results.
I hope this is helpful for you.
Figured out a solution (takes a few steps):
- Added a hidden slider with default = Len(ComboBox.SearchText)
-In OnChange of slider, create temporary collection with a concatenated list of potential matches, then add those to the ComboBox data source/collection with concat function by item#
-Include added column in search field of combobox
Slider OnChange:
Clear(tempMatches);
ClearCollect(tempTable,AddColumns(DataSource,"Matches",
With({
a:ForAll(Split(Concat(Filter(Split(ThisRecord.'col01',","),Find(Lower(Result),Lower(ComboBox1.SearchText))>0).Result,Result,","),","),Result),
b:ForAll(Split(Concat(Filter(Split(ThisRecord.'col02',","),Find(Lower(Result),Lower(ComboBox1.SearchText))>0).Result,Result,","),","),Result),
c:ForAll(Split(Concat(Filter(Split(ThisRecord.'col03',","),Find(Lower(Result),Lower(ComboBox1.SearchText))>0).Result,Result,","),","),Result),
d:ForAll(Split(Concat(Filter(Split(ThisRecord.'col04',","),Find(Lower(Result),Lower(ComboBox1.SearchText))>0).Result,Result,","),","),Result))
},
ForAll(Sequence(CountRows(a)) As A,
ForAll(Sequence(CountRows(b)) As B,
ForAll(Sequence(CountRows(c)) As C,
ForAll(Sequence(CountRows(d)) As D,
Collect(tempMatches,{ItemID:ID1,Matches:Concatenate(
Last(FirstN(a,A.Value).Value).Value,
Last(FirstN(b,B.Value).Value).Value,
Last(FirstN(c,C.Value).Value).Value,
Last(FirstN(d,D.Value).Value).Value)})
)
)
)
)
)
));
ClearCollect(DataSource2,AddColumns(ShowColumns(DataSource,"ID1"),"Matches",Concat(Filter(tempMatches,ItemID=ID1).Matches,Matches,",")));
This only works for a specified number of columns, but that's acceptable for my purposes. Would welcome any suggestions/comments if there's other methods to accomplish the dynamic search.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
User | Count |
---|---|
183 | |
46 | |
46 | |
34 | |
33 |
User | Count |
---|---|
254 | |
83 | |
78 | |
67 | |
66 |