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

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

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.
powerapps search multiple columns sharepoint list
powerapps search multiple columns sharepoint list
  • 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.
PowerApps search multiple columns
PowerApps search multiple columns
  • 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.
Power Apps search multiple columns
Power Apps search multiple columns
  • 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
PowerApps search multiple SharePoint columns

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.
powerapps search all columns
powerapps search all columns
  • 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

powerapps filter by multiple fields
PowerApps filter by multiple fields

This is how to work with PowerApps search multiple columns.

Also, read: PowerApps Search User

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
See also  How to Disable Attachments in SharePoint List?

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.
PowerApps search by date
PowerApps search by date
  • 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.
Power Apps search by date
Power Apps search by date
  • 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
search by date in PowerApps
Search by date in PowerApps

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
PowerApps Search records by Today's date
PowerApps Search records by Today’s date

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.
PowerApps Search records by a Date range
PowerApps Search records by a Date range
  • 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.

search by date PowerApps
Search by date PowerApps

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.
PowerApps Search records by a specific date
PowerApps Search records by a specific 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
PowerApps Search records by specific date

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.

PowerApps show all records when the user does not enter a date
PowerApps show all records when the user does not enter a date
  • 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
PowerApps show all records

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

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.

search choice column in PowerApps
search choice column in PowerApps
  • 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.
PowerApps search choice column
PowerApps search choice column
  • 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
Power Apps search choice column
  • 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.
See also  Power Apps Image Control - How to use

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.
how to search choice column in PowerApps
how to search choice column in PowerApps
  • 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
how to search choice column PowerApps
how to search choice column PowerApps

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

Also, read: Power Apps Search Gallery + 19 Examples

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.
PowerApps search created by
PowerApps search created by
  • 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
Power Apps search created by
Power Apps search created by

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.
Search created by in PowerApps
Search created by in PowerApps
  • 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.

PowerApps Search author
PowerApps Search author

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

  • 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.

See also  6 Easiest Ways to Use Power Apps ForAll Function

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

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
Search distinct value in PowerApps
Search distinct value in PowerApps

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
PowerApps search distinct choice value
PowerApps search distinct choice value

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
PowerApps search distinct
PowerApps search distinct
  • 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.
Power Apps search distinct
  • 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
Search distinct in PowerApps
Search distinct in PowerApps

This is how to work with PowerApps search distinct.

Read: PowerApps Cascading Dropdown (Detailed tutorial)

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.
Search SharePoint List not Working in PowerApps
Search SharePoint List not Working in PowerApps
  • 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.
PowerApps Search SharePoint List not Working
PowerApps Search SharePoint List not Working
  • 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.

PowerApps Search SharePoint not Working
PowerApps Search SharePoint not Working
  • 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
Power Apps Search SharePoint List not Working
Power Apps Search SharePoint List not Working

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

Also, you may like the below PowerApps Tutorials:

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
>