Working with large SharePoint lists in PowerApps

Today we are happy to announce support for connecting to SharePoint lists that have more than 256 items using PowerApps. Please see the details below on the changes you need to make to your apps to take make use of this support. We are also working on making any new apps generated use these capabilities out of box, so look for that announcement in the near future.

First off, the 256 item limit was raised to 500 items, so even without making use of the new support for paging, you should now be able to get up to 500 items from the list.

With the changes rolled out today, your existing apps should see no difference in behavior. If the list you were connecting to was under 500 items, your apps will continue to work fine without any changes. In these cases, PowerApps was simply retrieving all of the items, and letting you sort/search/filter by any of the columns you had in your list.

For lists that contain more than 500 items, there are some changes you need to make to your app (to the “Items” property of the Gallery control), and once these are done, you should be able to simply scroll to the bottom of a list, and PowerApps will bring in the next set of results from SharePoint to make things work.

Sorting

First off, to display the results that are most relevant to you, you have to decide on what to sort by. SharePoint supports sorting on column types of “Single line of Text”, Choice, Number, Date and Person. In the formula for “Items” in the gallery control of your app, make sure you pick one of the columns of these types. If you pick a column that does not support sorting, you will get a warning with a blue icon telling you that your source list doesn’t support the column you picked for sorting. In those cases, PowerApps will just revert to sorting the first 500 items in your list by ID, so watch out for that. If you are dealing with a list that has more than 5,000 items in SharePoint, you should make sure that you add an index to the column that you want to sort by.

Filtering

Second, if you’d like to filter the set of items that you are showing in the gallery control, you will make use of a “Filter” expression, rather than the “Search” expression, which is the default that existing apps used. With our changes, SharePoint connector now supports “equals” type of queries on columns that support filtering (Single line of text, choice, numbers, dates and people), so make sure that the columns and the expressions you use are supported and watch for the same warning to avoid reverting back to the top 500 items. We will be adding “Starts With” support in the future as well. As with sorting, it is required to have an index on the column that you want to filter your items by, if you are dealing with a SharePoint list that has more than 5,000 items.

Search

We will be working on adding support for “Search” function by connecting to SharePoint Search service in the future.

Some examples

Here are some sample expressions for the “Items” field that show how you can retrieve items from SharePoint that match certain criteria:

Retrieve all items that are “AssignedTo” the current user, and sort by “DueDate”, where these are column names in a SharePoint list named “Issues”:

SortByColumns(Filter('Issues',AssignedTo.Email=User().Email), "DueDate", If(SortDescending1, Descending, Ascending))

Retrieve all items that have “Status” equal to “Pending”, and that match the string added to the search box, sorted by “Created” date in a list called “Helpdesk Requests”:

SortByColumns(Filter('Helpdesk Requests', Status=”Pending”, If(Len(TextSearchBox1.Text)>0, Title=TextSearchBox1.Text, true)), "Created", If(SortDescending1, Descending, Ascending))

Please continue to give us feedback using the Forum and your comments on what other options you would like to see in SharePoint integration, it is very helpful to drive our prioritization as we add new features.