Showing results for 
Search instead for 
Did you mean: 

I am building a PowerApps. Should I store data to a SharePoint list?

You have plenty of options to store your data when building a PowerApps. But in Microsoft's ecosystem typical options are


  • SharePoint list(s)
  • SQL database from Azure
  • Common Data Service for Apps


Let's look SharePoint list option a little bit closer.  When it's usable? And if you cannot use SharePoint list should you use SQL Database or Common Data Service for Apps instead? 


SharePoint list

Yes. Every time we discuss using a SharePoint list, someone raises this topic. Limitations and restrictions related to the item count. Many of us have this mystical figure 5000 in our mind.

Let's try this in practice. First, we create a simple SharePoint list with the following columns.


  • Title
  • OwnID- Number field, the value is the same than list item's internal Id
  • OwnOwner- Text Field, the value is list item's owner's email
  • Choice - Choice column. Possible values: Choice 1,  Choice 2 and Choice 3
  • OwnChoice- Text field, the value is list item's Choice column selected value
  • OwnCreated- Number field, the value is the list item's creation time presented as ticks 


After that, we create 45 000 items on that list. Easily done with Flow.

Screen Shot 2018-10-14 at 14.57.07


And experiment what actually works and what does not.



Let's start with the free word search. Get all rows where the Title column contains the string "Timo".

Screen Shot 2018-10-14 at 13.18.08.png


Unfortunately, the yellow triangle appears on the screen. The search operation cannot be delegated. In practice, the search can be used only if we have a maximum of 2000 lines in our SharePoint list. And the search is always performed locally in our PowerApps.



Filtering is often even more important feature than the search. I really hope that it works...

We can quickly find single list item by using ID. In the picture below the item having ID 27810. 

Screen Shot 2018-10-14 at 13.29.03.png


Similarly, we can find item having "timotestssharepointlist34967isthisok" as a Title.


Filter('SharePoint list limits', Title = "timotestssharepointlist34967isthisok")


StartsWith function seems to work also with larger SharePoint list. 

Screen Shot 2018-10-14 at 13.33.46.png


Usually, we like to filter dataset in different ways. A quick test reveals that we can't filter items by


  • ID range (show all rows having ID between 27,000 to 27,200)
  • Create or modify date (show all lines created max 2 days ago)
  • Author (show all rows created by Timo Pertilä)
  • Choice column (show all rows choice field value is "Choice 2")


All of these examples fail because of a lack of delegation. The solution is to use only text and numeric columns with filtering.

That's the reason we have these columns in our list.


  • OwnID
  • OwnOwner
  • OwnChoice
  • OwnCreated


Using these fields all operations can use delegation.


Now we can filter out the rows having ID between 27001 and 27010.

Screen Shot 2018-10-14 at 13.48.54.png


Or show lines having "Choice 3" as a Choice field value.

Screen Shot 2018-10-14 at 13.50.21.png


Or display only the rows created by me.

Screen Shot 2018-10-14 at 14.01.02.png


We can compare dates also. In the image below there are all lines created since  10/21/2018 10:54:35 (yes, it is 636757052756539000 as ticks).

silter by ticks.png


The filters can of course also be combined. Here are all rows with "Choice 2" selected and having ID greater than 29,000.

Screen Shot 2018-10-14 at 14.03.39.png



A brief experiment again gives a harsh judgment. The lines can only be sorted by ID. When using any other columns, threshold limit error occurs.

Let's add indexes for all columns we like to sort by.

Screen Shot 2018-10-14 at 14.12.02.png


After this, we can sort by these columns.

Screen Shot 2018-10-14 at 14.16.02.png


However, it only works when the list is sorted by a single column (Sort). SortByColumns gives us threshold limits error. 

Screen Shot 2018-10-14 at 14.18.53.png


Presenting the items in the gallery

Wait a minute ... I don't want to display the update time as ticks. Who knows when was 636757052756539000?

Of course, you don't need to. Custom fields are used only inside the solution (while filtering and sorting). For the end user, we show real columns like the owner, creation date, etc.



Summary - SharePoint list

If you don't need a free-text search or multi-column sort, SharePoint list is still a pretty good option. At least with 45 000 lines that we have in our example.

If a free-text search is an absolute requirement, there is still a limit (2000 items) with SharePoint. 


Azure SQL Database

When using a SQL database, you can forget all the constraints we found from the SharePoint list. Search works and there are in practice no limits related to the item count.

As long as you build the required indexes on database.

PowerApps doesn't know whether the indexes are made or not. Missing indexes cause sooner or later terrific slowness.

But there are (compared to SharePoint list) plenty of new challenges.


  • Attachments. A SharePoint list item can contain one or more attachments. With the SQL database, you have to build that feature by yourself.
  • The person column. SharePoint list person field is quite handy on many occasions.  End-user can select the desired user in your organization. Or many users. Or even user group! With the SQL database, you end up to build something by yourself. Again.
  • Row-level access.  A SharePoint list item may have individual access rights (the person/persons and/or group/groups). SQL database supports row-level security, but PowerApps connects to a SQL database with a SQL user account. Not with Azure AD account. Yes. You end up to build something with yourself...


And the list goes on. Version History, version approval, etc...

SharePoint has a lot of really useful features built-in. Also from PowerApps point of view. 


Common Data Service for Apps

Shortly. CDS for Apps contains good parts from both sides: SharePoint list and SQL database.

The only disadvantage is the price ($7/user/month). And that the views created for CDS for Apps can't be used in PowerApps.


Tim, super helpful information and recommendations!  Thanks for taking the time to share. 

And as allways, after writing this things has been chnaged.


Today, SharePoint supports delegation with Choice and Lookup fields!

@TimoPertila  Hi Tim, 


Please would you let me know if there are connectors available to persist PowerApps data with a non Microsoft or Azure database e.g. SAP HANA?

Meet Our Blog Authors
  • PowerApps Community Mentor 2018, Practicing surgeon and former Professor of Surgery, University of Illinois, Chicago.
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 12 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • My name is Timothy Shaw and I create digital solutions using the Power Platform, Office 365, and Azure SQL and handle the IT for a small company in the energy sector. When not building the future :), I enjoy playing guitar, good (or really bad!) sci-fi, Xbox therapy, and hanging with my wife and son. Twitter: @ShortForTim