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

[Anonymous]

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.

And experiment what actually works and what does not.

Search

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

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.

Filter

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. 

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. 

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.

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

Or display only the rows created by me.

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

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

Sorting

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.

After this, we can sort by these columns.

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

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.

[CindyZ]

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

[Anonymous]

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

Today, SharePoint supports delegation with Choice and Lookup fields!

https://powerapps.microsoft.com/en-us/blog/sharepoint-delegation-improvements/

[scmlkp]

@Anonymous  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?