You’re offline. This is a read only version of the page.
345
Afternoon all,
So, this is a bit of a pet project of mine, but I'm attempting to create an FAQ page with a search box that caters to "common" speech. If someone types in "KPI", they get a gallery with just the KPI field, and if they type in "I want to see my KPI data", it'll show them the same KPI field. I'm planning on doing this with the use of stop text that allows the search to disregard the extranenous strings such as "I want to see my", but recognises KPI as the important bit to filter the gallery with.
The data behind it is a set of rows set out like below:
| Report Name | Key Terms | Query Area | Link |
| KPI | KPI; Performance Indicators | Staff | [URL] |
| Progression | Progression; Progressed; Retention | Students | [URL] |
This would be the items in a gallery, filtered by a search box. Now, I'm currently using Filter to analyse if whatever is typed into the search box matches what is either the report name or the key terms box:
Share
ReportFilter(
Table1,
TextInput1.Text in 'Report Name',
TextInput1.Text in 'Key Terms',
TextInput1.Text in 'Query Area')
So far, that works great. Now I want to implement the "smart" feature. In lieu of a more elegant solution to analyse against it, I basically just dropped in a whole set of stop text for each row of my data:
a ; about ; above ; across ; after ; again ; against ; all ; almost ; alone ; along ; already ; also ; although ; always ; among ; an ; and ; another ; any ; anybody ; anyone ; anything ; anywhere ; are ; area ; areas ; around ; as ; ask ; asked ; asking ; asks ; at ; away ; b ; back ; backed ; backing ; backs ; be ; became + about 50 others
So that's in there for each row. This way, it will keep every result in my gallery as someone writes their question into the search box, checking off the search string against each items in the stop text (which is same for all rows) until it sees the actual unique piece of info for that row (i.e. KPI) and then filters the gallery to just show that.
I know I can use the In Operator to analyse it all, but I quickly realised that if I type "I want to see my KPI data" in the search box, it's not reading each word as a separate search terms, but instead trying to look for that precise version of words in both my actual keywords and the stop text. So I figured I needed to split out my search term into separate strings in a collection, so that "I want to see my KPI data" becomes:
searchTerms collection |
I |
| Want |
| To |
| See |
| My |
| KPI |
| Data |
And now what I need to do if to analyse each rows against the search terms (Keywords and stop text). However, I A) can't figure out how to analyse each row each the search terms without using a ForAll along with the filter, and B) how to structure the Filter and ForAll to make it work.
If i use:
searchTerms.Result in 'Key Terms'
I get "Invalid argument type. Cannot use table values in this context", which makes sense, but I can't figure out how to get it to work otherwise.
I hope that all makes sense!
Whoops - posted in this thread yesterday and realized I didn't actually reply to your comment:
This is amazing! How could you incorporate a sort into this so that the record/item with the highest count is displayed on top? Thank you for taking the time to write this stuff up.
Hi @EpicTriffid :
The function of this formula is to convert a string into a table
Ungroup(ForAll(Split("Progression; Progressed; Retention",";"),Split(Result," ")),"Value")
For example,I assume that the target Text is "KPI; Performance Indicators":
All responses (
Answers (
Ungroup(ForAll(Split("KPI; Performance Indicators",";"),Split(Result," ")),"Value")The result would be:
| Value |
| KPI |
| Performance |
| Indicators |
I think this link will help you a lot(Operator ‘IN’):
Operators and Identifiers in Power Apps
Best Regards,
Bof
Hi @EpicTriffid :
Please try:
Filter(
Table1,
Sum(
ForAll(
Split(TextInput1.Text, " "),
If(
Or(
Result in Ungroup(ForAll(Split('Query Area',";"),Split(Result," ")),"Value"),
Result in Ungroup(ForAll(Split('Key Terms',";"),Split(Result," ")),"Value"),
Result in Ungroup(ForAll(Split('Report Name',";"),Split(Result," ")),"Value")),
{Count:1},
{Count:0})),
Count) > 0)Best Regards,
Bof
Ah, so close! It's now searching through the whole field which is great, but putting in text like "I want my" no longer works!
Hi @EpicTriffid :
Please try this formula:
Filter(
Table1,
Sum(
ForAll(
Split(
TextInput1.Text,
" "
),
If(
Or(
Result in 'Query Area',
Result in 'Key Terms',
Result in 'Report Name'
),
{Count: 1},
{Count: 0}
)
),
Count
) > 0
)Best Regards,
Bof
Slightly more investigation has found that in fields with multiple strings separated by ";", it's only searching on the first term in that field?
Thanks for that. It's working, kind of. I've extended the if statement into an OR to run across multiple search fields and that seems to break it. So your statement above has been modified to be:
Filter(
Table1,
Sum(
ForAll(
Split(TextInput1.Text, " "),
If(
Or(
Result in Split('Query Area', ";"),
Result in Split('Key Terms', ";"),
Result in Split('Report Name', ";")),
{Count:1},
{Count:0})),
Count) > 0)))
And it doesn't seem to be running against the Query Area or Search Terms. Typing in "I want my performance indicators", with "Performance Indicators" being one of the strings in Key Terms, it doesn't filter.
Hi @EpicTriffid :
I assume that I want to perform a 'smart search' on ‘Report Name’ column,Please try this formula:
Filter(
Table1,
Sum(ForAll(Split(TextInput1.Text," "),If(Result in Split('Report Name',";"),{Count:1},{Count:0})),Count)>0
)Best Regards,
Bof
#1
WarrenBelz
146,524
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,906
Most Valuable Professional
