Implementing Search In PowerApps Gallery

In this article, we will be using the Search function in PowerApps to display search results filtered via Text Input and Drop-Down. Let us understand the scenario.

We have a SharePoint Online Custom List with columns

• Name
• Email
• Gender
• State
• Department
• Manager

We have a galley control on a screen in which we are displaying all the employees data present into SharePoint Online List.

Searching with Text Input Control in PowerApps Gallery

Now, we need to filter the result with name entered the text input control. We need to add a search functionality to the screen. Let us begin the fun!!!

Add a text input control on the screen above the gallery control and rename it as a “txtSearchText”

Click anywhere on the gallery and navigate to the Items property of the gallery where we have assigned SharePoint list as a data source.

Now we need to use a Search function of PowerApps. It’s syntax look like

Search(Table*, SearchString, Column1 [, Column2, … ] )

where,

Table – Table in which we need to search. This is required parameter.

SearchString – The string to search for. If we pass blank or an empty string, all records are returned. This is also required parameter.

Column1, column2…… – The names of columns within data source Table in which we need to look for the SearchString. Column names must be included within double quotes. If any search text is matched with column data, whole row will be returned by the Search function. Columns to search must contain text data.

Now let us add Search function to our gallery. So, replace the following formula into your Items property value of Gallery.

Search(‘Employee Registration’,txtSearchText.Text,”Title”)

Please note that “Title” is the internal name of the SharePoint list column. Search function will work with internal name of the column.

If you can see that internal name for “Full Name” column is “Title” in SharePoint list.

Run the app and enter “mir” into the text input control and you will see following result.

You can see that it has filtered the record where it finds the word “mir”. Few points to note.

• Start searching as soon as we start typing.
• Case insensitive. It means MIR, Mir, MiR, miR, mir all are same value for the function.
• Search anywhere into the given column. Like you can see that Mirza is the second word into the column, yet it finds and filter the result.

This was search based on single column, here the text was searched only in Title column.

Now we need to search the same text into multiple columns in the same gallery. For example, we need to match the value of search text into Title and Manager column both at the same time.

So, to do that, just replace the Item property of the gallery with below formula.

Search(‘Employee Registration’,txtSearchText.Text,”Title”,”Manager”)

What is changed? we just added a new column at the last of the formula. Similarly, you can add as many as column you want to add over here, and it will filter on all the columns.

Now let us enter the “Mir” keyword again and let’s check the search result.

You can see that, now we have 2 records into the search result because it has filtered on Manager Column also.

In this way we can apply filter on multiple columns using Search function with Text Input Control.

Searching with Drop-Down Control in PowerApps Gallery

Now, we need to filter the result with Department selected into the drop-down control.

Add a drop-down control above the gallery control and rename it as a “ddlSearchItem”

Click anywhere on the gallery and navigate to the Items property of the gallery where we have assigned SharePoint list as a data source.

If you notice, we have static values 1,2,3 in the dropdown. Here we need to search the item via Department. So, we need all the department value should be there into the dropdown. To do this we have 2 ways.

Way-1: We can simply replace the item property of the dropdown with below formula.

[“HR”,”Accounts”,”Back Office”,”DBA”]

The problem with this approach is, if in future department is increased and we have updated the SharePoint list column, then we need to edit the app, edit the value over here and re-publish the app. So, we can use another way.

Way-2: Connect the dropdown directly with the SharePoint list column. Add the below code in the Items property of the dropdown.

Choices([@’Employee Registration’].Department)

In this way you will have same value which we have in SharePoint.

Now let’s apply Search function on Choice column, replace the Item property of the gallery with below formula.

Search(‘Employee Registration’,ddlSearchItem.SelectedText.Value,”Department”)

You can see that the Search function is giving an error.

The reason of an error is, Search function cannot search in Choice column. It needs only Text column to search. Please find the snap from Official Documentation.

So now the problem is, how we can use Search function?

There is a workaround to do that. We have to use AddColumns function in Search function.

AddColumns(): This function adds a new column to a given table, existing columns does not affected.

Syntax [From Official Documentation]:

AddColumns( Table, ColumnName1, Formula1 [, ColumnName2, Formula2, … ] )

• Table – Required. Table to operate on.
• ColumnName(s) – Required. Name(s) of the column(s) to add. You must specify a string (for example, “Name” with double quotes included) for this argument.
• Formula(s) – Required. Formula(s) to evaluate for each record. The result is added as the value of the corresponding new column. You can reference other columns of the table in this formula.

So just modify the formula with following formula.

Search(

AddColumns(‘Employee Registration’,”DepartmentValue”,Department.Value),

ddlSearchItem.SelectedText.Value,

“DepartmentValue”

)

What is does?

It adds a new column “DepartmentValue” into the SharePoint data source and copy the value of Department column into it. And then it search on the newly added column. Now you can see that it has started filtering on the Choice column too.

In this way we can implement Search function to work on Choice column.

Summary

In this article we have seen how we can search on SharePoint List data source on Text Columns, Single Columns, Multiple Columns via Text Input Column & Drop-Down control.

If you have any query or suggestion, please feel free to add into comment section.