In this Power Apps tutorial, We will discuss how to work with Power Apps Filter SharePoint List. And also, by taking some simple scenarios, We will cover all the below topics related to this post.
Complete SharePoint Training Course Bundle Just for $199
- PowerApps filter sharepoint list lookup column
- PowerApps filter sharepoint list by id
- PowerApps filter sharepoint list by current user
- PowerApps filter sharepoint list by created by
- PowerApps filter sharepoint list dropdown
- PowerApps filter sharepoint list lookup column delegation
- PowerApps filter sharepoint list person field
- PowerApps filter sharepoint list choice field
- PowerApps filter sharepoint list by current date
- PowerApps filter sharepoint list by date
- PowerApps filter sharepoint list by date range
- PowerApps filter sharepoint list by column
- PowerApps filter sharepoint list by collection
- PowerApps filter sharepoint list based on another list
- PowerApps filter sharepoint list distinct
- PowerApps filter sharepoint list not equal
- PowerApps filter sharepoint list gallery
- PowerApps filter sharepoint list limit
- PowerApps filter sharepoint list max rows
- PowerApps filter sharepoint list with more than 2000 records
- PowerApps filter sharepoint list yes no
- PowerApps filter sharepoint list status
PowerApps filter sharepoint list
- Suppose in PowerApps, you want to implement a filter in SharePoint list items, then you can refer to the below example.
- In this scenario, We will see how we can filter SharePoint List items based on an ID column in PowerApps.
- The below screenshot represents a SharePoint List named Credit Projects. This list has some columns like Title [Project Name], Current Status, Company Type etc.
- Also, it has some records having with different IDs. Now I would like to filter the item based on this item ID and that will display in a PowerApps Data table.
- On the PowerApps screen, it contains a search input field, a Button control to search the ID, and a Data table where the item will appear.
- Here, the user will enter the items ID [that he wants to view] and then click on the Find button. Once he will tap the button, that specific item details will appear in the data table as shown below.
- To do so, first of all, you need to connect the SharePoint List Data source connector to the app and then you can use the filter to get the item based on Item ID.
- Select the Button control [Find] and apply this below formula on its OnSelect property as:
Where,
- CreditClient = Collection name
- Credit Projects = SharePoint List name
- ID = Specify the ID column name from SharePoint List
- TextInput3 = Text input control name
- The above code specifies, I have created a collection called CreditClient and filter the data from the data source Credit Projects based on ID.
- When the user will filter any ID, then the specific item will save in the collection and it will bind the collection value in the PowerApps data table to display the result.
- Next, to bind the collection value in the data table, select the data table and set its Items property as:
Where,
CreditClient = Collection name that you have created
- At last, Save and Preview the app. Enter an ID and tap on the find button. Then you can see the specific record details in the data table.
PowerApps filter sharepoint list lookup column
Here we will see how to work with the PowerApps filter SharePoint list lookup column.
- I have two SharePoint Lists named Product Info and Shopping Cart. The Product Info list is having two columns:
- Title and Product Location [Both columns are Single lines of text data type]
- Also, this list has these many below records as shown below.
- The second list [Shopping Cart] is having these many below columns:
- Title = Single line of text data type
- Price = Currency data type
- Color = Choice data type
- Product = This is a Look up column from the first list [Product Info]. The information are retrieved from the Title column of the Product Info list.
This list has the below records as shown below.
- Now what I want to do is, I want to filter the Powerapps Lookup Column in sharepoint. To do so, Insert a Dropdown control and apply this below formula on its Items property as:
- Once you will preview the app, you can see the specific lookup value of that particular country will appear in the Dropdown control.
PowerApps filter sharepoint list by id
Here in this topic, We will discuss how we can filter the data table based on ID from another SharePoint list.
- Here I want to populate the records from a data table that matches an ID from a different data source.
- A PowerApps Datatable has a data source from a SharePoint list [sharepoint2] which is linked to a data source from another SharePoint list [Sharepoint1].
Example 1:
- In this example, there are two SharePoint Lists named Product Info and Shopping Cart. The first list [Product Info] list is having its own ID column that has created while adding a new record to it.
- Where the second list [Shopping Cart] is having the ID [Cart ID] that has created by the user.
- To populate all items for the second list where the ID is a value in the first list, then apply this below formula on Data tables Items property as:
Where,
Cart ID = ID column name that has been created by the user. Instead of this, you can use the ID column that the list is having by default.
- Once you will save and preview the app, you can see the result in the data table as shown above.
Example 2:
- In this second scenario, I have a text input control that helps to enter the ID value by the user. For example, If the SharePoint list ID is 3, then I would like to filter the data table to only display the items from ID 3.
- To do so, Insert this below code on the Data tables Items property as:
Where,
TextInput1 = Text input control name
- Now save and preview the app. Enter the ID [that you want to filter] in the text input control. You can see the filtered result in the data table as like in the below screenshot.
PowerApps filter sharepoint list by current user
Next comes how to filter the SharePoint list by the current user and display it in Power Apps. Follow this below scenario to do so.
- There is a SharePoint List named Travel Details. This list has some columns like Traveller, Source, Destination, Travel Start Date etc. Among all these columns, there is a Person field called Assigned To that assigned to whom.
- Here I would like to filter this Assigned To field from the SharePoint list. For example, when the user is Preeti Sahu, then she only can see her request, others can not see her request in the PowerApps.
- So first of all, If your SharePoint List is having more than 500 records, then PowerApps may cause the delegation issue. To work around this, follow these below things to do.
- Apply this below code on Screens OnVisible property as:
or,
OnVisible = UpdateContext[{UserName: User[].FullName}]Where,
UserEmail, UserName = Context Variable name
Here, I will recommend using the User[ ].Email as that should be guaranteed to be unique.
- Next, add a Gallery control and apply this below formula on its Items property as:
Where,
- Travel Details = SharePoint List name
- Assigned To = SharePoint Person field
- UserEmail = Variable name
- Save, Publish and Close the app. Reopen the app again. As the current user loggedin with Preeti Sahu, so all the travel tasks are displaying [in the gallery control] by her name only. Other than this, she cannot see any ones travel task.
PowerApps filter sharepoint list by created by
Suppose, you want to filter data using Created By in the SharePoint list. That means you can show only the data that you have created in the SharePoint List.
- To filter the SharePoint List Created By field, you must note that in PowerApps it is called Author and it is a lookup field.
- You can use the User[] function to get the current user in PowerApps.
- Below represents a SharePoint List named Customer Care Report Details. All the items has been created by the user [Preeti Sahu]. Now I want to get all the SharePoint list records that have been created by this user.
- On the PowerApps screen, Insert a Gallery control and apply this below code on its Items property as:
Where,
Author = Created By field in SharePoint List
- Once you will save and preview the app, you can see the appropriate result in the gallery control as like the above screenshot.
PowerApps filter SharePoint list dropdown
Do you want to filter a PowerApps Dropdown control that is having SharePoint List values? Refer to this below simple scenario.
- The below screenshot represents the SharePoint List [Shopping Cart]. This list has below columns:
- Title = Single line of text data type
- Price = Currency data type
- Color = Choice data type
- On the PowerApps screen, Insert a Dropdown control and and set its Items property as:
- Once you will apply the above code, then all the title values will appear in the dropdown control.
- Add a Data table and insert the below code on its Items property as:
Where,
- Title = SharePoint Column name
- Dropdown2 = Dropdown control name
- Save and Preview the app. Select any value from the dropdown control. Then it will display all the matching data [in the data table] related to the selected dropdown value.
PowerApps filter sharepoint list lookup column delegation
In some cases, you may face some issues with the delegation end while working with the SharePoint list Lookup ID column. Refer to the below scenario on how to overcome the PowerApps filter SharePoint list lookup column delegation.
- The PowerApps filter function can be delegated to SharePoint for SharePoint Lookup columns, but it seems that this functionality is limited to when you will use the ColumnName.Value but not the ColumnName.Id.
- You can see below represents two SharePoint Lists named Projects and Project Statuses. Where the Project Statuses list has a Lookup column named Project, and the values are retrieved from the Title column of the Projects list.
- Here what I would like to do is, I want to filter the Project Statuses based on the selection from the Project list, thats why I have taken a gallery and displayed the filtered items from the Project Statuses.
- And also, you can see there is no problem to work around with the Value attribute of the lookup column:
- The next thing when I used the Project.Id, then this threw a Delegation error. The error appears on the screen as Delegation warning. The highighted part of this formua might not work correctly with column Project.Id on large data sets. You can see the delegation error in the below screenshot [where I used the Project.Id].
Solution:
- As I searched in some sites and findout the solution that the PowerApps filter SharePoint list Lookup column ID works only on the Value, but not the SharePoint ID.
- For this, what you could do is, When you create the lookup in SharePoint, set the value of the lookup field to ID, and then use the additional lookup fields to add the other columns like Name, Description etc into the list.
- You would choose another field but if you donot choose ID, then you will get the delegation issue.
- If you will try SharePointList.MyLookUp.Id = A, then you will see the delegation warning. Instead of that, you can try as SharePointList.MyLookUp.Value = A.
- You can try this tested code on Items property of the gallery control as:
PowerApps filter sharepoint list person field
Do you want to filter the SharePoint list person field in PowerApps? Then what is the correct formula and how you can get it, refer to this simple scenario?
- As we have already discussed above that PowerApps User[] is a function that helps to retrieve the current user.
- On the PowerApps screen, add a gallery control and apply this below formula on its Items property as:
Where,
- Travel Details = SharePoint list name
- Assigned To = SharePoint Person field
Or you can apply this below formula:
Filter['SharePointListName',"The Specific Email Address" in 'Assigned To'.Email]- Once you will save and preview the app, then the gallery control will display all the records [from the SharePoint list] that are related to the currently logged-in user.
- Suppose in case in the SharePoint List, you dont enable the Allow Multiple Selections option for the Assigned To column, then you can refer to these below formulas:
Or you can try this below formula:
Filter['SharePointListName',"The Specific Email Address" in 'Assigned To'.Email]PowerApps filter sharepoint list choice field
Suppose you need to filter the SharePoint List Choice field in PowerApps, then there will be a simple formula that you can use to achieve this.
- Below represents a SharePoint List named Project Details. This list has a Choice column called Project Status. This choice column has some choice values like Submitted, Approved, Rejected and Pending.
- Now I would like to filter the choice field those are only Approved items. Also, the result will display in the PowerApps gallery control.
- To do so, Select the Gallery control and apply this below code on its Items property as:
Where,
- Project Details = SharePoint List name
- Project Status = SharePoint Choice column
- Approved = Specify the SharePoint choice value that you want to filter
- When you will save and preview the app, you can see the filtered result in the gallery control as shown above.
PowerApps filter sharepoint list by current date
Suppose you want to filter the SharePoint List by the Current date or Todays date, then it is a very simple process that you can do easily. Follow these different examples.
Example 1:
- In the below SharePoint List [Customer Care Report Details], there is a Date Time column named Report Date. This date column has some previous dates and as well as the current date [Todays Date].
- Here, I want to filter all the todays records in PowerApps and display it in a gallery control.
- On the PowerApps screen, add a date picker control. By default, the PowerApps Date picker control is having Todays or current date.
- To display all todays records from the SharePoint list, insert a Vertical gallery control and apply this below formula on its Items property as:
Where,
- Customer Care Report Details = SharePoint List name
- Report Date = SharePoint List Date Time column
- DatePicker1 = Date picker control
- Once you will save and preview the app, you can see all todays records in the gallery control as shown above.
To learn more about the Date filter in Power Apps, you can follow this below article:
Create PowerApps Date filter
Example 2:
- There is another formula where you can filter a SharePoint List [Here I have taken the same list as above] by the latest date [from the Date column]. Shortly we can say, we will get the latest date from the SharePoint Date column.
- To do so, apply this below formula on the Gallerys Items property as:
Where,
Report Date = Sharepoint Date Time column
- Once you will save and preview the app, you can see the result in the gallery control as shown above.
PowerApps filter sharepoint list by date
Next, We will see how to work with the Power Apps filter SharePoint list by date.
- Here, I have taken the same above SharePoint List [Customer Care Report Details]. I would like to filter the SharePoint list by date and display in PowerApps gallery.
- In some cases, PowerApps doesnot work when filtering a Sharepoint List by date. This means, it does not work to directly pass a date into the filter formula.
- For this, We need to pass the Day, Month and Year separately into the formula.
- On the PowerApps screen, Insert a Gallery control and apply this below formula on its Items property as:
Where,
- Dropdown3 = Dropdown control name
- Order Date = SharePoint Date Time column
- At last, Save and Preview the app. Select any value from the dropdown control, then you can see the gallery will appear with last 30 days of record details of that specific gadget.
- For more information about the PowerApps filter sharepoint list date range, refer Date Filter.
PowerApps filter sharepoint list by column
How to filter SharePoint list by column in PowerApps? Follow the below simple scenario.
- The below screenshot represents a SharePoint List named Project Details. This list has some columns like Title, Employee First Name, Employee Last Name etc.
- Among all these columns, there is one more column called Project Status that contains some choice vaues like Submitted, Approved, Rejected and Pending.
- Now in PowerApps, I would like to filter the SharePoint List based upon these two columns as Title and Project Status.
- There will be text input control and a gallery control on the PowerApps screen. Here, what exactly I want to do is, a user will enter any Title value [from the list] and the gallery will show either that the user entered item is approved or not. This means only approved value will appear in the gallery control.
- If that entered item has approved, then the gallery will display the approved details otherwise the gallery will blank.
- For example, in the text input control, I have entered the Title value as PowerApps project. As this is an approved value, So the gallery displayed it as like the below screenshot.
- To achieve this, Select the gallery control and apply this below formula on its Items property as:
Where,
- TextInput2 = Text input control name
- Approved = Specify the choice value that you want to filter. You can specify any value like Rejected, Pending etc. But the specified choice value should match with the SharePoint list choice value.
- Save and Preview the app. Once you will enter an item [Title] in the text input control, then the result will display in the PowerApps gallery control.
PowerApps filter sharepoint list by collection
Here we will discuss how to use PowerApps filter sharepoint list by collection.
- In the below screenshot, you can see there are some records in the Powerapps app. From these below records, I am going to filter some specific data that belongs to Bangalore.
- For that, Take a Gallery screen [Insert->Gallery->Vertical] in Power apps.
- Select a Data Source [CandidateInfo] in your app.
- Select the Gallery and apply the below formula on its Items property:
Where,
- Filter= Function Name
- CandidateInfo= Collection Name
- Address= Field Name
- Bangalore= Filter Value
- Not only Address, But also, you can filter any field as like Name, Id, Mobile Number etc.
- In the Gallery Screen, You can see all records [Name and ID] that belong to Bangalore.
This is how to use Power Apps filter sharepoint list by collection.
PowerApps filter sharepoint list based on another list
In PowerApps, Do you want to filter one SharePoint List based upon the other SharePoint List? Yes, you can obviously do this easily.
- There are two SharePoint Lists named Product Info and Shopping Cart. The first list [Product Info] is having two columns as:
- Title = By default, this is a Single line of text column
- Product Location = This is also a single line of text column
- Similarly, the second list [Shopping Cart] is also having the same column as Product Location with a Single line of text data type. I have specified the column values that should match with the first list column.
- Here, I would like to filter this list column [Product Location] and it will display all the values that are not present in the second list.
- As in the below screenshot, you can see in the list, there are only two values as USA and FRANCE and other two values are missing i.e. UK and AUSTRALIA. So in PowerApps, the gallery will filter and display the UK and AUSTRALIA.
- To do so, apply this below formula on Gallerys Items property as:
Refer to the below screenshot.
- Once you will save and preview the app, you can see the filtered result in the gallery control as in the above screenshot.
PowerApps filter sharepoint list distinct
Do you know what does the mean of Distinct is and how you can filter the distinct value in the SharePoint list using PowerApps?
First of all, PowerApps Distinct is a function that helps to remove duplicates from any data sources like table, SharePoint list, etc. Follow these different scenarios to filter the SharePoint list using PowerApps Distinct function.
Example 1:
- There is a SharePoint list named Gadget Details. This list contains some duplicates records or gadgets [like Mobile, Laptop, Tablet, etc]. Now I want to filter the gadget that should be unique [this means it will display only the distinct value].
- The below screenshot represents a Gallery control and a Dropdown control. The gallery contains all the items that are retrieved from the SharePoint List [Gadget Details].
- Next, I would like to filter the unique items from the SharePoint List and display it in the dropdown control. To do this, apply this below code on Dropdown controls Items property as:
Where,
- Gadget Details = SharePoint List name
- Title = Specify the column name that you want to filter. This is the column that has created by default in the Sharepoint list
Example 2:
- As you can see there are two PowerApps Gallery control on the screen. Where the first gallery displays the Gadget Names [this should be unique]. When a user will select any gadget [from the first gallery], then the second gallery will appear with all the Brands of that selected item.
- For example, If I will select Mobile, then the second gallery will show all the brands that are related to the mobile.
- Here, All the items are retrieved from a SharePoint List Data source named Gadget Details. This list has two columns as Title and Brand.
- To display the distinct or unique value in the first gallery, apply this below formula on the first Gallerys Items property as:
Where,
- Gadget Details = SharePoint List name
- Title = Column name that I want to filter and display the distinct items
- Next, Select the second gallery and set its Items property to this below code as:
Where,
Gallery13 = First gallery control name
- Now, Select the Label control [from the second gallery control] and set its Text property as:
Where,
Brand = SharePoint column name
- At last, Save and preview the app. Select any item from the first gallery control, then in the second gallery, you can see all the item brands of that specific selected item.
PowerApps filter sharepoint list not equal
Do you want to know what is about the PowerApps filter SharePoint list not equal? Refer to the below example.
- I have a SharePoint List named Travel Details. This list has a Person field called Assigned To. Now I would like to filter the gallery that is not equal to Assigned to.
- In the below screen, there are total four items that have not been assigned to anyone. I want to these four records in the PowerApps gallery control.
- To workaround this, Select the gallery and apply this below formula on its Items property as:
- Once you will save and preview the app, you can see the filtered result in the gallery control as shown above.
PowerApps filter sharepoint list gallery
Do you want to filter the SharePoint List in Power Apps Gallery, then there is a PowerApps detailed guide that describes all about the gallery filter. Refer to the below link:
Power Apps Gallery control filter
PowerApps filter sharepoint list limit and PowerApps filter sharepoint list max rows
- In the Powerapps, there is a certain limitation of items in every data sources like SharePoint, OneDrive, Common Data Service, etc].
- You can only add 500 items from a data source but not more than that. If you will add more than 500, then the PowerApps wont allow adding.Lets take an example.
- Suppose you have 550-600 items in an Excel file on your OneDrive for Business. When you will add this data source into the app, then it will allow only up to 500 items but not much more than that.
- In PowerApps, It shows a warning message whenever the data sources crosses the limit to 500. You can see the warning message as Delegation warning. The highlighted part of this formula might not work correctly on large data sets.
- To overcome this PowerApps Delegation issue, please overlook to the below points to get some solutions.
PowerApps filter sharepoint list with more than 2000 records
To overcome this type of Power apps Items limit, you need to do some below things as:
- Increase the total Item Limit in PowerApps App Settings:
- By using this below method, you can extend your Power apps item limits above 500.
- In the Power apps app, Click on file [top left corner] -> Settings -> Advanced settings -> set value for non-delegable queries as shown below.
- This method also is having one drawback. This method can allow only a maximum of 2000 number of items or values but not more than that. You can only add 1-2000 number in the Value field. Because Powerapps does not know about the 2001 item.
- So if there are more than 2000 items in your excel file, then this method will not work for you. For that, you can use the below second method.
2. Use Static Data:
- The best solution for this item limit case is, use static data. In this method, there will be 10000 rows and you can have access to all rows.
- In this case, You need to import the static data instead of using SQL Database where you have to spend some extra money.
- These static data are attached to your app where there may be an effect in the app loading time due to the enlarging of the total size of the app.
- You can add the Static data by using the below steps:
- View -> Data Sources -> Search the data source like Excel
- Click on Import from Excel
- Select excel file -> Choose a table that you want to import
If this method does not fulfill your needs, then remember one thing, Excel is a good method but you have to modify it from within the app.
3. OneDrive for Business Connectors:
- Suppose you may need more than 2000 rows stored in one data source, then you should switch from OneDrive for Business data source to any other data source like SQL, Common Data Service, SharePoint, etc.
- In this method, you cant get more than 2000 rows.
- In the OneDrive for Business connector, there is a separate connection to each table in the Excel file. The item limit of 2000 value is related only to the connections but not to the source.
Example:
- In this example, We will see how we can manage to filter items more than 2000 records.
- Suppose there is an Excel file that has 10000+ records. When I am trying to filter some items it wont show it all. Here the delegation issue occurs because the data source may have beyond 2000 records.
- To overcome this, you can run a multiple conditions to bring in the data in chunks of 2000, then group it into a larger collection.
- Powerapps just doesnt like dealing with all that data from an outside source, but temp storing within the browser works alright. Follow the below instructions that you can do.