Power Apps Search SharePoint List Examples

This PowerApps Tutorial explains what is the difference between Power Apps Search and Power Apps Search SharePoint List. Also, by taking some various scenarios, we will cover these below topics:

• PowerApps Lookup SharePoint list
• PowerApps Search multiple columns
• PowerApps Search by date
  • PowerApps Search records by Today’s date
  • PowerApps Search records by a Date range (Start Date and End Date)
  • PowerApps Search records by a specific date
  • PowerApps show all records when the user does not enter a date
• PowerApps Search Choice Column
• PowerApps Search Created by
• PowerApps Search Column does not exist
• PowerApps Search SharePoint list Person Field
• PowerApps Search Distinct
• PowerApps Search large SharePoint list
• PowerApps Search SharePoint List not Working

Power Apps Search SharePoint list

• As we know, PowerApps Search function helps to find the record from a table and the result displays in any of the controls like PowerApps Gallery, PowerApps Data table, etc.
• Similarly, whenever a user wants to find and displays any records from a SharePoit list data source, then it is known as Power Apps Search SharePoint list.
• While working with the PowerApps Search function in SharePoint, always make sure to connect the Sharepoint List data source connector to the app.
• To know more details about the PowerApps Search function and its uses, go through this complete PowerApps tutorial: PowerApps Search Function + How to use with example

PowerApps Lookup SharePoint list

Do you know what does the mean of PowerApps SharePoint list lookup and how to use it in the app? Check out this PowerApps tutorial, everything you will get to know in detail: PowerApps Lookup SharePoint list

Power Apps Search multiple columns

Do you want to search multiple columns in a PowerApps Gallery control? Follow the below scenario that how you can achieve it.

• I have a SharePoint List called Employee Onboarding. This list has these many below fields with different data types as:

1. Employee Name = By default, this is the Title field with single line of text data type. I just renamed it to Employee name.
2. Employee Last Name = This is also a Single line of text field
3. Employee ID = This is a Number Data type column
4. Department = This is a Choice column having with different choice values like IT, HR, FINANCE, etc.
5. Location = This is also a Choice field having with different choice values like Australia, America, Frnace, etc.

• In the PowerApps, I have a Text box and a Gallery control. All the gallery records are retrieved from the SharePoint list (Employee Onboarding). Here what I have to do is, I want to search multiple fields in a single text box.
• For example, in the text box, I would like to search Employee title, Department, and as well as Location. And at the same time the gallery will filter and display with the user serachable record details as shown below. To achieve it, we can use the PowerApps OR (||) operator.

• So as I searched the location of Australia, then the gallery filtered and it returned me those two records that are related to Australia only.

• To workaround this, we will create a PowerApps Collection where it will store the values from the SharePoint list. you can create the collection on the app’s OnStart, the screen’s OnVisible, or a combo box’s OnChange property,etc.
• Here, I have created the collection on my screen’s OnVisible property as:

OnVisible = Collect(
    colEmployeeList,
    'Employee Onboarding'
)

Where,

1. colEmployeeList = Provide the collection name
2. ‘Employee Onboarding‘ = SharePoint list name

• Next, select the gallery control and set the below code on its Items property as:

Items = Filter(
    colEmployeeList,
    txtEnterDetails.Text in Title || txtEnterDetails.Text in Department.Value || txtEnterDetails.Text in Location.Value
)

Where,

1. colEmployeeList = Specify the collection name
2. txtEnterDetails = Text input control name where the user will search fields
3. Title = This is the single line of the text field from the collection
4. || = This symbol defines the PowerApps OR operator that helps to return true if any of its arguments are true.
5. Department, Location = as these are the SharePoint Choice fields, that’s why we need to specify with a .Value parameter

This is the one kind of formula that you can use to get the values in PowerApps.

• There is another approach that you can use for the Single line of text fields in PowerApps. Here, you can not use any SharePoint Choice fields, Number fields, etc. Also, it is applicable if your dataset is less than 2k items.
• For example, I can search either the Employee first name or last name in the text box control (because these are the SharePoint single line of text fields). When I searched the Employee last name, then the gallery filtered and returned me those searchable related records as shown below.

• For this, we will use the PowerApps Search function. Select the gallery control and set the below code on its Items property as:

Items = Search(
    colEmployeeList,
    txtEnterDetails.Text,
    "Title",
    "EmployeeLastName"
)

Where,

“Title“, “EmployeeLastName” = SharePoint single-line text fields

This is how to work with PowerApps search multiple columns.

Also, read: PowerApps Search User

Power Apps search by date

In this topic, we will see how to search the items by date in PowerApps. Also, we will discuss the below topics that are related to the PowerApps Search by Date.

1. PowerApps Search records by Today’s date
2. PowerApps Search records by a Date range (Start Date and End Date)
3. PowerApps Search records by a specific date
4. PowerApps show all records when the user does not enter a date

Refer to the below different scenarios.

PowerApps search by date Example

• I have a SharePoint list called Book Purchase Info. This list has a Date column named Sale Date as shown below.

• In the app, I have a date picker control and a Data table control. Now what I would like to do is, when a user will select a specific date, then the data table will filter and appear only those records that are related to the user search date.
• For example, I selected the date as 2/2/2022, so the data table control appeared those two record details that are sold out on that specific date as shown below.

• To do so, we need to apply the below code on the Data table’s Items property as:

Items = Filter(
    'Book Purchase Info',
    'Sales Date' = dtSelectDate.SelectedDate
)

Where,

1. ‘Book Purchase Info’ = SharePoint List name
2. ‘Sales Date’ = Specify the SharePoint Date field
3. dtSelectDate = Date picker control name

This is how to use PowerApps search by date.

Power Apps Search records by Today’s date

Suppose you want to search records by using the current date or today’s date in PowerApps, then you can use the PowerApps Filter function.

• To do this, Insert a Gallery control and apply the below code on its Items property as:

Items = Filter(
    'Book Purchase Info',
    'Sales Date' >= Today(),
    'Sales Date' < DateAdd(
        Today(),
        1,
        Days
    )
)

Where,

1. ‘Book Purchase Info‘ = SharePoint List Name
2. ‘Sales Date‘ = SharePoint Date column

This is how to search records by Today’s date in PowerApps.

Power Apps Search records by a Date range (Start Date and End Date)

Suppose in PowerApps, you want to search records in between two date ranges i.e Start Date and End Date. Then follow the below code to achieve this need.

• In the below screenshot, you can see there are two date pickers and a gallery control. When you will select a Start date and End Date, then the gallery will filter and it will display all the records that are coming in between these two specified date ranges.

• To do this, select the gallery and set the below formula on its Items property as:

Items = Filter(
    'Book Purchase Info',
    ('Sales Date' >= dtStartDate.SelectedDate Or IsBlank(dtStartDate.SelectedDate)) And ('Sales Date' < DateAdd(
        dtEndDate.SelectedDate,
        1,
        Days
    ) Or IsBlank(dtEndDate.SelectedDate))
)

Where,

1. ‘Book Purchase Info‘ = SharePoint List Name
2. ‘Sales Date‘ = SharePoint Date field name
3. dtStartDate = Date picker control name of Start Date
4. dtEndDate = Date picker control name of End Date

Refer to the below screenshot.

This is how you can work with PowerApps Search records by a specific date range.

Read: How to use date time picker in PowerApps

Power Apps Search records by a specific date

In PowerApps, if you want to search some specific records by using the specific date, then follow the below simple scenario.

• In the below image, when you will select a specific date from the date picker control, then the gallery will filter and display only those specific records that are related to the user selected date.

• To workaround with this, select the gallery and apply the below code on its Items property as:

Items = Filter(
    'Book Purchase Info',
    'Sales Date' >= dtSelectSpecificDate.SelectedDate,
    'Sales Date' < DateAdd(
        dtSelectSpecificDate.SelectedDate,
        1,
        Days
    )
)

Where,

1. Book Purchase Info‘ = SharePoint List Name
2. ‘Sales Date‘ = SharePoint Date column
3. dtSelectSpecificDate = Specify the Date picker control name

This is how to work with PowerApps Search records by a specific date.

Power Apps show all records when the user does not enter a date

Here we will see how to display all items when the user does not enter any specific date. And also if the user will enter any date, then it will display that specific date record only as shown in below.

• To achieve this, add a Date picker control and a Data table control. Set the IsEditable property of the Date picker to true. So that it will enable the user to clear the date easily.
• Then, select the data table and apply the below code on its Items property as:

Items = Filter(
    'Book Purchase Info',
    ('Sales Date' >= dtDate.SelectedDate And 'Sales Date' < DateAdd(
        dtDate.SelectedDate,
        1,
        Days
    )) Or IsBlank(dtDate.SelectedDate)
)

Where,

1. Book Purchase Info‘ = SharePoint List Name
2. ‘Sales Date‘ = SharePoint Date column
3. dtDate = Specify the Date picker control name

This is how we can show all records when the user does not enter a date in PowerApps.

Also to get some more ideas, read this: Create PowerApps Date filter

Power Apps Search Choice Column

Now we will see how to search the records based upon a SharePoint choice field in PowerApps. Check out the below simple scenarios to workaround.

Example – 1:

• There is a SharePoint list called Products. Among all the different type of columns, it has a Choice column named Status. This choice field has some choice values like:

1. Submitted
2. Approved
3. Rejected
4. Pending

Refer to the below screenshot.

• Next in the app, there is a Text input control and a gallery control. When the text box is blank, then you can see all the records in the gallery. When a user enter any choice value, then the gallery will filter and display only the search specific choice records.
• For example, in the search box, when I searched the value as Rejected, then the gallery filtered and appeared with only those specific rejected records as shown below.

• To work with this, select the gallery control and set the below code on its Items property as:

Items = Filter(
    AddColumns(
        Products,
        "ChoiceValue",
        Status.Value
    ),
    txtEnterChoice.Text in ChoiceValue
)

Where,

1. Products = SharePoint list name
2. ChoiceValue = Specify the new column name
3. Status = Specify the SharePoint choice column name with .Value parameter
4. txtEnterChoice = Specify the text input control name where user will search the choice values

• After applying the code in the gallery, make sure to specify the choice value in the gallery label’s Text property as:

Text = ThisItem.ChoiceValue

• Now save and publish the app. Enter the choice value in the text box and then the gallery will filter and display all the details related to the search box.

Example – 2:

• In this example, I have taken the same above SharePoint list as Products. So, in the text box control, a user can search the product title, product customer name, and as well as any choice-value that he wants.
• And at the same time, the gallery will filter and display those specific records that are related to the search text box.
• As in the below screenshot, you can see, I searched the title as “Laptop“, Status value as “Approved“, and Customer Name as “Smith“. So the gallery filtered and displayed all the records corresponding to the search box value.

• To achieve this, you can set the below code on the Gallery’s Items property as:

Items = Sort(
    Search(
        AddColumns(
            Products,
            "ChoiceValue",
            Status.Value
        ),
        txtEnterChoice.Text,
        "Title",
        "CustomerName",
        "ChoiceValue"
    ),
    'Sales Date',
    Ascending
)

Where,

1. Products = Sharepoint List name
2. Status = Provide the Sharepoint Choice column name
3. “Title“, “CustomerName” = SharePoint single line of text data type field
4. txtEnterChoice = Text input control name where a user can search either title, customer name, or status
5. “ChoiceValue” = Specify the new column name
6. ‘Sales Date‘ = SharePoint Date column name

• After applying the code in the gallery, make sure to specify the choice value in the gallery label’s Text property as:

Text = ThisItem.ChoiceValue

This is how to work with the PowerApps search choice column.

Also, read: Power Apps Search Gallery + 19 Examples

Power Apps Search Created by

Next, we will see how to search the SharePoint Created by field in PowerApps.

• As we know, SharePoint Created By field is a default column in the SharePoint list i.e. having with Person or People picker data type.
• Now in PowerApps, I would like to search the records based upon this SharePoint Created by field.

Example – 1:

• In the below image, you can see there is a text box and a gallery control. When a user will enter the SharePoint created person name, then the gallery will filter and display only those specific person related records. For this scenario also, I have taken the same SharePoint list named Products.
• Not only the user can search Created by field, but also he can search the Product Title, and Product Customer Name.

• To do so, you can apply the below code on the gallery’s Items property as:

Items = Search(
    AddColumns(
        Products,
        "CreatedByUser",
        'Created By'.DisplayName
    ),
    txtEnterAuthor.Text,
    "CreatedByUser",
    "Title",
    "CustomerName"
)

Where,

1. Products = Define the SharePoint list name
2. “CreatedByUser” = Specify a new column name where the person field values will store
3. ‘Created By’.DisplayName = This is the SharePoint People picker field. As this is a person field, so you need to specify this field with .DisplayName
4. txtEnterAuthor = Text input control name
5. “Title“, “CustomerName” = Sharepoint single line of text field names

• After applying the code in the gallery, make sure to specify the choice value in the gallery label’s Text property as:

Text = ThisItem.CreatedByUser

Also, to avoid the delegation issue, set the Data row limit option to a maximum value of 2000 within the General Settings of App Settings of your app.

This is the one way to search the SharePoint person field records in PowerApps.

Example – 2:

• Let’s focus on the other below simple scenario. When a user will select a particular person (from the SharePoint Created By field), then the data table will filter and display all the records related to that search-specific person as shown below.
• For example, When I selected a person named Arvind from the Combo box, then the data table filtered and it showed all the details about that specific person.

• For that, select the Combo box control and set its Items property as:

Items = Choices(Products.'Created By')

Where,

1. Products = Name of the SharePoint list
2. ‘Created By’ = Specify the Person field name

• Then, select the Data table control and set the below code on its Items property as:

Items = Filter(
    Products,
    'Created By'.DisplayName in ComboBox1.Selected.DisplayName
)

Where,

1. Products = Provide the SharePoint list name
2. ‘Created By’.DisplayName = SharePoint person field name
3. ComboBox1 = Specify the Combo box control name where a user can enter the person name. Also, you need to change both the DisplayFields and SearchFields property of the Combobox to this code:

["DisplayName"]

Refer to the below screenshot.

This is how to work with PowerApps search created by.

To learn more about the PowerApps people picker column, follow this link: Create People Picker in PowerApps with Combo Box

PowerApps Search Column does not exist

• in PowerApps, sometimes what happens is, while you are working with some collection functions, or patch functions, you may see an error like: “The Specified Column X does not exist, The column with the most similar name is X“.
• The column suggested is the same name and the column is from a Sharepoint List.
  When we tried to get the exact column name from Sharepoint using the URL trick, we cannot decode the 2 columns. The columns are “_x006c_ma0” and “_x006d_jj1“.
• So in this case, you can try the below thing is:
  • Something to really avoid is using special characters in SharePoint field names. To see what is in them, collect the list:

ClearCollect(colTest,YourListName)

Then choose View -> Collections -> colTest and look at the contents of those two fields.

Also, you can try out this PowerApps forum link: Column Does not Exist Error with SharePoint

PowerApps Search SharePoint list Person Field

If you want to search and filter the record based upon the SharePoint List Person field, then you can refer the detailed things from this link: PowerApps Search Person Field

Also, you can read this complete article related to the SharePoint Person field: Create People Picker in PowerApps with Combo Box

Power Apps Search Distinct

Do you want to search the distinct or unique values in PowerApps? Follow the below simple scenarios.

Scenario – 1: (PowerApps Distinct with SharePoint Single Line of Text field)

In this scenario, we will see how to search the SharePoint distinct values (of Single line of the text field) in PowerApps.

• To check out with this, I have taken a Dropdown control and set its Items property to the below code:

Items = Distinct(Products, Title)

Where,

1. Products = Provide the SharePoint list name
2. Title = Specify the SharePoint field name that you want to get the unique values

Scenario – 2: (PowerApps Distinct with SharePoint Choice field)

Next, we will see how to search the SharePoint distinct values (of Choice field) in PowerApps.

• In the same way, I have applied the below code on the Dropdown’s Items property as:

Items = Distinct(Products,Status.Value)

Where,

1. Products = Specify the SharePoint list name
2. Status.Value = Specify the SharePoint Choice column name that you want to get the unique values

Once you will save and preview the app, you can see all the unique values in the dropdown menu as shown in the above screenshots.

Scenario – 3:

• There is a SharePoint list called Employee Onboarding. This list has some various data type fields as:

1. Emplyee Name = By default, this is a Title field with single line of text. Just I renamed it to Employee Name.
2. Employee Last Name = Single line of text data type field
3. Employee ID = Number Data type field
4. Department = It’s a Choice field

• Now what I would like to do is, when a user will select any department from the dropdown, then the gallery will filter and it will display only the unique Employee names as shown below.
• To work with this, we will use PowerApps Distinct and Filter function in the gallery control.

• To achieve this, follow the below formula on the gallery’s Items property as:

Items = Distinct(
    Filter(
        'Employee Onboarding',
        Department.Value = Dropdown1.Selected.Value
    ),
    Title
).Result

Where,

1. Distinct = PowerApps Distinct function helps to remove the duplicate values from the SharePoint List
2. Dropdown1 = Dropdown control name

This is how to work with PowerApps search distinct.

Read: PowerApps Cascading Dropdown (Detailed tutorial)

PowerApps Search large SharePoint list

Do you want to work with the PowerApps search with huge data against the SharePoint list Data source? Once you will refer to this PowerApps forum link, you will get to know everything in details: PowerApps Search large sharepoint list

PowerApps Search SharePoint List not Working

Here we will discuss about an issue and its solution while working with the PowerApps Search SharePoint List. Check out the below things to know more details.

• There is a SharePoint List named TSInfo Attachments. This list has some various data type fields like ID, Title, Attachment Types, Attachment Costs, etc.
• Among all, there is a Person field called Book Author. Now, in PowerApps, I would like to filter the records based upon the people picker field.

• In the app, there is a text input control and a gallery control. When a user will enter any book author name in the search box, then the gallery will filter and display with those searchable related records as shown below.

• But what happens is, whenever I was applying the below code, then I was getting an error like: “Delegation warning. The “Search” part of this formula might not work coorectly on large data sets. The function ‘Search’ has some invalid arguments. Wrong column type. Expects text type.”

Items = Search('TSInfo Attachments',txtEnterBookAuthor.Text,"BookAuthor")

Refer to the below screenshot.

• So after searching a while, I got to know that the issue was coming because PowerApps SEARCH requires a string value for the 3rd parameter but my column “Book Author” is a Person/Group object.
• To overcome of the above issue, you need to apply the below code on the gallery’s Items property as:

Items = Filter(
    'TSInfo Attachments',
    txtEnterBookAuthor.Text in 'Book Author'.DisplayName
)

Where,

1. ‘TSInfo Attachments‘ = SharePoint List name
2. txtEnterBookAuthor = Text input control name
3. ‘Book Author’.DisplayName = SharePoint Person field name. As this is a people picker field, so you need to specify with .DisplayName parameter

This is how to avoid the issue related on PowerApps Search SharePoint List.

Also, you may like the below PowerApps Tutorials:

• What is Microsoft Dataverse
• Power Apps Sort Gallery
• PowerApps Find Function With Examples
• You don’t have permission to view this data error in PowerApps
• PowerApps toggle control + How to use with examples
• PowerApps Mod Function
• PowerApps Now, Today, and IsToday function
• PowerApps Trim Function
• PowerApps StartsWith and EndsWith Functions
• Upload PowerApps Attachments to SharePoint Library Folder
• Power Apps RSS Feed

In this PowerApps tutorial, we discussed the difference between Power Apps Search and Power Apps Search SharePoint List. Also, by taking some various scenarios, we covered these below topics:

• PowerApps Lookup SharePoint list
• How to work with PowerApps Search multiple columns
• Use PowerApps Search by date
  • PowerApps Search records by Today’s date
  • PowerApps Search records by a Date range (Start Date and End Date)
  • PowerApps Search records by a specific date
  • PowerApps show all records when the user does not enter a date
• What is PowerApps Search Choice Column
• Work with PowerApps Search Created by
• What does mean by PowerApps Search Column does not exist
• PowerApps Search SharePoint list Person Field
• What is PowerApps Search Distinct
• PowerApps Search large SharePoint list
• PowerApps Search SharePoint List not Working