Searching on multiple fields or columns

[ChrisMedeiros]

Hi-

I'm very new to PowerApps and have been trying to read everything I can. I'm creating an app that allows a police department to enter in information on vehicles and later search. I'm storing the data in a sharepoint 365 list and have had it build the powerapp for me. I made some minor cosmetic changes, but the search only seems to be looking at a single field, in this case "color". I'd like to be able to search Vin#'s, License plate #'s, case #'s etc.. I don't want the search to only be able to look at one field as the officer may be looking on various information. 

Is there a way to address this issue? My current statement in the Items formula is: SortByColumns(Filter('Tow/Hold Reports', StartsWith(Color_x003a_, TextSearchBox1.Text)), "Color_x003a_", If(SortDescending1, Descending, Ascending))

Thanks,

Chris

[v-micsh-msft]

Hi @ChrisMedeiros,

Do you mean to make the formula to be able to search on different columns only with a single TextInput Box?

If this is what you would like to achieve, then you may replace the Filter function with the Search function, by modifying the formula you posted as below:

SortByColumns(
                Search('Tow/Hold Reports',
                             TextSearchBox1.Text,
                              Color_x003a_,Vin#'s, License plate #'s, case #'s),
                "Color_x003a_",
               If(SortDescending1, Descending, Ascending))

The Search function Syntax:

Search( Table, SearchString, Column1 [, Column2, ... ] )

• Table - Required. Table to search.
• SearchString - Required. The string to search for. If blank or an empty string, all records are returned.
• Column(s) - Required. The names of columns within Table to search. Columns to search must contain text. Column names must be strings and enclosed in double quotes. However, the column names must be static and cannot be calculated with a formula. If SearchString is found within the data of any of these columns as a partial match, the full record will be returned.

Reference: Filter, search, and LookUp functions in PowerApps

Please post back if you have any further questions.

Regards,

Michael

[Anonymous]

@v-micsh-msft

I have a similar app and cannot seem to get the search bar to work.  I have a search box called 'TextSearchBox1' that the user will input the search string to compare against fields that are called, for example, 'CallLoc'.  Other columns I would like to search against the same user text input box are called, 'CallBeat', 'sikr', and 'OData__x006b_b99'.  All of the columns are text columns except OData__x006b_b99, which is a date and time field. (I realize the date and time might be a problem because its not straight text.)

I tried doing this with just one column to search with the following syntax in the OnChange property:

SortByColumns(Search('AV Log',TextSearchBox1.Text,"CallLoc"),"CallLoc", If( SortDescending1, Descending,  Ascending))

It didn't work.  Nothing happens when text is typed in and/or an enter key is pressed after text is entered.

I also tried multiple column searches with the code you gave:

SortByColumns(Search('AV Log', TextSearchBox1.Text, "CallLoc", "CallBeat","OData__x006b_b99"), "CallLoc", If(SortDescending1, Descending, Ascending))

This also did not produce anything.  I tried to also use the Filter command in place of the search command since I actually want to filter out everything that doesn't match the text input, but it didn't work. 

I should also mention I had a sorting issue on this same gallery that @Anonymous so kindly helped me resolve so sorting the gallery work.  I seem to think the two are related. The current code I have in the 'Items' property of the gallery is this:

Sort(Filter('AV Log',sikr > Today() - 60), sikr,If( isSortDescending, SortOrder.Descending, SortOrder.Ascending ))

I did this so I could keep the number of records being brought into the app down to just those that are less than 60 days old.

Any help from you, or anyone else with a working search box, would be greatly appreciated as this is really the only issueholding up deployment of the app.

Thank you!

[timl]

@Anonymous

I suspect the cause of this problem is because you added the formula to the OnChange property of the TextSearchBox1. If you add your search logic to the Items property of the gallery control, the gallery control should return more accurate results.The code you would use would look something like this:

SortByColumns(Search(Filter('AV Log',sikr > Today() - 60),
                     TextSearchBox1.Text,
                     "CallLoc"
              ),
              "CallLoc",
              If( SortDescending1, Descending,  Ascending)
)

[Anonymous]

@timl

Thanks for the reply.  I tried your solution in the gallery items field and it didn't work for me.  It basically made all records that were visible disappear and the search bar still didn't work to bring anything up.  I have it sorting by the date column fine, but when we add the 'SortByColums' and "Search' it goes haywire.  As it is now, I use this code in the gallery items field to limit the records to the past 60 days and to toggle the sort from up to down and back in dates and that by itself works fine. 

Sort(Filter('AV Log',sikr > Today() - 60), sikr,If( isSortDescending, SortOrder.Descending, SortOrder.Ascending ))

I tried even changing the Sort to SortByColumns in my code and that did the same in blanking out all records. 

Thanks again for your suggestion.

[timl]

@Anonymous

At this stage, my suggestion would be focus on the formula that carries out the search.

If you set the items property of the gallery control to the formula below, does it return the matching items from the CallLoc and CallBeat columns?

Search('AV Log',TextSearchBox1.Text,"CallLoc","CallBeat")

Something else that could help is to use a conditional statement for the items property.

The formula beneath will show records for the past 60 days if the search textbox is blank. Otherwise, it'll return records that match the search criteria.

If(TextSearchBox1.Text = "",   
   Sort(Filter('AV Log',sikr > Today() - 60), sikr,If( isSortDescending, SortOrder.Descending, SortOrder.Ascending )),   
   Search('AV Log',TextSearchBox1.Text,"CallLoc","CallBeat")
)

[Anonymous]

@timl

So I tried what you gave me this time and still no luck.  However, I did some tinkering and came up with a working solution.  Your orignal suggestion would have probably worked, except for two things.  First, somewhere along the lines I had changed the sort button properties to try and get that working and it did not match the sort syntax code you used.  So in the end, I set the Items property of the gallery screen to this:

Sort(Search(Filter('AV Log',sikr > Today() - 60),TextSearchBox1.Text,"CallLoc","OData__x006b_b99"), sikr,If( isSortDescending, SortOrder.Descending, SortOrder.Ascending ))

And I set the OnSelect property of the sort button to this:

UpdateContext({ isSortDescending: !isSortDescending })

The only other thing I had to change from what you gave me was I changed the SortByColumns to just Sort.  Using SortByColumn gave me a blank screen.

Once I made both of these changes, all began to work as it should.  I can now sort the gallery by date (ascending/descending) and when I enter a location or identifier into the search bar, it filters the records in the gallery accordingly.

Thanks so much for the help!

[Tangi12]

I have the exact same issue. I have a single search box that I want to filter on multiple columns. When I add the second item (which is a dropdown value) the data in my gallery dissappears. If I change the second column to search a text field it works. So I am suspecting it has something to do with choice fields. So I created calculated fields to bring in the text on Sharepoint. That seemed to work for the formula. The gallery was now showing all items, however, when I type anything in the textbox, everything dissappears no matter if I am searching in the Title or a calculated field. Doesn anyone know what I am doing wrong?

Here is my formula.

SortByColumns(Filter('RVP Legacy Reporting Migration',TextSearchBox1.Text in Title || TextSearchBox1.Text in Target_x0020_Report) ,"Title",If(SortDescending1,Descending,Ascending))

[nickduxfield]

Regarding your issue, you go the syntax correct now by using Or ||

When you have a dropdown, add dropdown.selected in what ever way you need as shown below.

SortByColumns(Filter('RVP Legacy Reporting Migration',TextSearchBox1.Text in Title || DropDown1.Selected.Value=Target_x0020_Report) ,"Title",If(SortDescending1,Descending,Ascending))

 Consider when you group columns for search together like this the criteria wont be met if you use , or &&.

In this case, for each secondary columns (where its not dependant but optional data) you need the OR || so that it will always return a result in the gallery.

Note that if you add dropdowns. In the case of a default filtered view, you may need to add a value of "Un-selected" to list items so you can create simple rules to search and filter easily.

[Anonymous]

Need to be able to do this without using search.  

Search does not support delegation with SharePoint list.