Creating a "Smart" Search

[EpicTriffid]

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:

Filter(
    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!

[v-bofeng-msft]

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

[EpicTriffid]

Hi @v-bofeng-msft 

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.

[EpicTriffid]

Slightly more investigation has found that in fields with multiple strings separated by ";", it's only searching on the first term in that field?

[v-bofeng-msft]

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

[EpicTriffid]

@v-bofeng-msft 

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!

[v-bofeng-msft · Accepted solution]

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

[EpicTriffid]

@v-bofeng-msft 

Success! Thank you! Would you mind explaining how that works? 

[v-bofeng-msft]

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":

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

[Hixk]

This is amazing! How could you incorporate a sort into this so that the record/item with the highest count is displayed on top?