Searching with multiple key words

[bees]

Hi, I'm building an app that will eventually be a searchable databse of undergraduate theses. At the moment I'm using the template provided by powerapps which is actually very similar to the style of databse I had envisaged. 

I'm having an issue with the search box. Ideally I would like to be able to enter multiple search terms seperated by a comma or even just a space which returns all cases that contain these terms. The search function I have come up with based on information in these forums is:

SortByColumns(Search(Table1_1,TextSearchBox1.Text,"Abstract","Author","Supervisor(s)","Project_x0020_Title"),
"Year", If(SortDescending1, Descending, Ascending))

When I enter a term like "biofilm" in the box it searches the columns "Abstract","Author","Supervisor(s)" and "Project Title" and returns any case containing the word biofilm in these 4 columns.

This is great but ideally I would like to be able to enter multiple search terms in the text box, for example "biofilm", "Smith", "ecotoxicology" to return any cases which contain these three words. Is this possible to do in Powerapps? At the moment if I enter multiple terms and the words aren't consecutive then the search returns a blank. So for example if I enter "john" "smith", projects by john smith are returned; however if I enter "smith" "ecotoxicology" then there is no cases returned.

Thanks!

[CalfordMath]

@vwyankeehmmm.  It sounds like your troubles have to do with connecting a record of a Gallery to a Form, which doesn't have to do with my keyword search formula directly.  The TrimEnds shoudn't make a difference because that is applied to the keywords before they are used in the search (i.e. "hot " wouldn't be detected in "hotdog" unless the space were trimmed first).  My suggestion would be to make sure the ID you are using in the criteria of your LookUp is indeed what you think it is.  Make a label or textbox in your Search result gallery to display the ID for each record.  I use little dummy-labels all the time to debug problems.  I wish you luck in your troubleshooting!

[vwyankee]

For others that may come across this. I got around that issue with setting a variable with the OnSelect of the gallery selector arrow and then using the variable in the formula for the other screen (item details) for the selected. Also, to @CalfordMath - I think it may have also been due to clearing the search box in the same formula that navigates to the form screen, which may explain why it was just showing for a split second. We needed to remove the clearing anyway so that if someone viewed an item details and came back they would still have the same search filter applied. I didn't change back from using the variable, but I'm guessing that was the cause behind it.

That said, now further along with a realistic amount of data I've found both solutions described for searching on multiple words will not work if you are working with a large amount of data since both have operators that do not support delegation - like "If" - so I'm back on the hunt for something that might do the job supporting delegation as well.

Maybe @CalfordMath and/or @PK_Hong will have some ideas.

[JC75NATS]

just explain again how we change it to search for any of the words? where is the >0 going?

[CalfordMath]

@JC75NATS 

The formula originally kept only cases were every word was found, so the strings of zeros and ones for successful record fields shouldn’t contain any zeros, making the filter:

IsBlank(Find("0"... 

If we are fine including fields with ANY (or, “at least one”) keyword, we only need to ensure the string of zeros and ones contains at least one 1, making the filter:

Find("1", ... ) > 0

Note that Find() returns a numeric relsult, not Boolean. Does this help explain?

[Anonymous]

Hi @PK_Hong ,

Thanks a lot for your formula, it really helped me! 

I just have one question concerning the formula. Can you explain me to what corresponds the number 1,2,3,4? 

Thanks a lot again 

[Anonymous]

Big OoOoOoOoF. The SQL connector doesn't support delegation for the || or Or() operator/command, so that first solution only works if you're only searching one field. Otherwise, great solution!

The first solution isn't delegable bc of the if statement. This causes me big sad.

[vwyankee]

The solution cannot be used when your connecting directly to the data source (SharePoint or SQL) but only on a collection. As stated it isn't delegable if you are connected directly to the SQL table or the SharePoint list. I'm using this method in multiple apps to search a dozen or more columns of data in the gallery (that's connected to a collection in the Power App). My completed items are shown in a view of a different gallery connected to the SP list without this multi-word search functionality. Instead I have a drop-down of the columns as choices and based on the selected drop-down choice the user can search any of the columns. It's a bit more limiting as the gallery filter has to use "StartsWith" instead of a contains argument for each column you want to include.

The other big pain about this is if you want column sorting it requires the gallery to have a large Switch or If statement to consider what column it's sorting on.

[PK_Hong]

Hi @vwyankee and all

Yes . I am the one who design this multi search formula and as I have posted in many reply and webminar, uses it based on your Database.

As I am using our own Custom Connector to our own Database system, (we don't use CDS, SpL and SQL, because they have all kind of delegation which u need to adjust formula to meet your needs if possible) which our own NowDB database and Swagger has no issue with delegation on any formulas.

So for those using CDS, SPL or SQL, I am sorry for my sharing of formula and confuse most of you.

Unless you are using the same method as mine if not please forget my multisearch formula. 

Even for those using Collection u might alos have problem as it has limited delegation when few formula are binded together. 

My advice.. If you are going for really big data (like me few millions) and yet no issue with delegations, then better off work with some one with Swagger and own Database system like us. 

Thanks

[Anonymous]

This is what I used to accomplish what I needed. It searches up to 5 terms across any of 3 (this can be changed) columns with On-Prem SQL, and only returns results that match all of the terms (if there's less than 5 it matches to all of the available terms). This would have to be done differently on sharepoint because it can't delegate the search function. I used this sort of "hard-coded" method to search for up to five terms because I wanted to reduce complexity and in our use case, nobody's going to be searching with more than 5 terms anyway. Here's the code:

Search(
    Search(
        Search(
            Search(
                Search(
                    '[DatabaseGoesHere]',
                    Last(FirstN(TrimEnds(Split(SearchBox.Text, " ")), 1)).Result,
                    "Column1",
                    "Column2",
                    "Column3"
                ),
                Last(FirstN(TrimEnds(Split(SearchBox.Text, " ")), 2)).Result,
                "Column1",
                "Column2",
                "Column3"
            ),
            Last(FirstN(TrimEnds(Split(SearchBox.Text, " ")), 3)).Result,
            "Column1",
            "Column2",
            "Column3"
        ),
        Last(FirstN(TrimEnds(Split(SearchBox.Text, " ")), 4)).Result,
        "Column1",
        "Column2",
        "Column3"
    ),
    Last(FirstN(TrimEnds(Split(SearchBox.Text, " ")), 5)).Result,
    "Column1",
    "Column2",
    "Column3"
)

Please, note again that the expression above only searches up to 5 search terms. If you try to search with more, you're just going to keep getting the same results.

Basically it starts with a search function that searches the last element of the firstN selection of 1 of the results of the split and trim from the text input against the original database. That piece is so that we can select the correct single search term for each nesting. It takes those results and passes them into the next level of the nesting and does the same thing, except it uses the next term. It repeats for all 5 terms. Note: this query is taxing on your SQL server and if you increase the number of possible terms (and therefore searches) or the number of columns it searches, you may need to increase the max node count on your server. Powerapps will tell you if it hits that limit.

Hope this helps somebody!

[posey]

I know this is an older thread, but for anyone wanting to be a knowledge base with search  capabilities, this is exactly what you are looking for!