of the
SharePoint lists (and now Microsoft Lists) are an easy way to quickly create lists of data for use in a Power App. Makers can quickly create lists, add fields, and populate data for canvas apps.
However, not all apps are equal from a complexity perspective.
If the app is simple and contains data that is not sensitive, Microsoft Lists can be a good choice for data storage for canvas apps.
However, if your app contains many different lists, large amounts of data, complex relationships between tables, and sensitive data for which all of the users of your ap should not have access, then you should consider using alternative data sources for you canvas app.
Here are the top 5 reasons why you should not use SharePoint lists for high-criticality Power Apps:
- It is not a relational database. Each list is independent and SharePoint lists were never intended to be used as a relational database. While SharePoint does have a field type called Lookup, it does not serve as a true relational database. Lookup fields in SharePoint lists are very difficult to work with in canvas apps, and performance with many lists in the same app will be slower than if you have a true relational database.
- Common formulas are not delegatable to Sharepoint: With canvas apps, heavy processing of data is delegated to the data connection. Common functions like if, Collect/ClearCollect are not delegatable in Sharepoint, while they are in other data sources like Dataverse. This means that if you need to return more than 500-2,000 records at a time, it is very difficult to do so with Sharepoint lists. With other connections like Microsoft Dataverse, Collect is delegatable, allowing for a much higher number of records.
- Security: if you share an app built on SharePoint with a user, you also need to share the underlying lists with them. This means if the user should not be able to see all of the records in the table (or update all of the records in the table), they could open up the underlying list and view or modify any of the records, even if you are obscuring the records inside of your app. Sure you can hide the list, but obscurity != security.
- SharePoint lists have limits in how they can be filtered in Power Apps: for example, filtering on yes/no fields does not work. Microsoft Dataverse based apps support complex filtering via views, which can include multiple tables.
- If your app exceeds the capabilities of SharePoint Lists, you need to start over—many apps begin as personal productivity but over time become more critical to an organization. Before you build your app, think through the potential roadmap—if there is a potential that it will increase in data, security sensitivity, or number of users in the future, build it on something other than SharePoint lists.
What is the alternative?
If you want to start with the benefits and ease of use of Microsoft lists without having to purchase premium licenses, start with Dataverse for Teams. Dataverse for Teams is included in the standard licensing for Office and Microsoft Teams.
In Microsoft Teams, click Apps and search for Power Apps, then click Open
Click New App
Select the name of the Team where you want the app to be used, then click Create
This will open the Power Apps Studio inside of Microsoft Teams.
Give your app a name
Click Create new table
Give your table a name
Define your columns in a Microsoft List like experience.
Dataverse for teams table creation has all of the things that are great about Microsoft Lists, without the major downsides.
- It is included in the standard licensing
- It’s easy to use to create and modify tables
- It’s a real relational database—if you choose lookup for field type, it creates a real database relationship between the two tables
- It supports more advanced delegation and filtering.
- It supports real record ownership and security. You can specify different table permissions for users based on their role in the Team
It supports more advanced filtering via Dataverse views
- If you exceed the capabilities of Dataverse for Teams you can upgrade your environment to a full Dataverse environment without having to rebuild your app.
Limitations of Dataverse for Teams
Dataverse for Teams environments are limited to 2 GB of data. For larger environments with more complex security requirements, I recommend Microsoft Dataverse. For a full comparison of Dataverse for Teams vs. Dataverse, see Dataverse for Teams vs. Dataverse – Power Apps | Microsoft Docs.
Thanks to Matt Devaney for contributing ideas to this tip.
Cover photo by Donald Giannatti on Unsplash.
Important stuff this! As MS get better at closing up the loops people try to tie to get around their licensing understanding the limitations of so-called ‘alternative solutions’ is essential to not creating a real mess for the sake of paying for a premium license and getting the connectors you wanted in the first place 😀
Loved this article! One small correction would be Collect is not delegatable to any data source 🙂
You also don’t need to create a collection to retrieve/display records. In my experience it’s actually best practice to avoid collections whenever possible as they can slow things down & introduce delegation issues.
More details on that here:
https://youtu.be/7tTRNgXYBzE
We used to work with a client that made “Business Applications” for their end clients (often government departments in developing nations). We helped them with some general SharePoint consultancy and developing tools to extract the data from these Business Applications for submission to various standards agencies.
Anyway, we tried to explain the benefits of doing this in a model-driven Power App instead (this was before Dataverse for Teams existed) and even gave them some free training. But, while they liked the concept, they and their clients, struggled to get past the fact that they could purchase a few SharePoint licenses (for admins) and then let everyone else in for free as Guest users. It amazes me how genuine sustainable businesses are desperate to do all they can to save a few dollars, when there’s an obviously better way to do it, for not a lot of money. We don’t do work for them any more.
While Dataverse is the better tool… Microsoft markets the Sharepoint approach in the App-in-a-Day… So I’m not surprised that clients insist on using a Sharepoint data source
I enjoyed reading this article. Thanks for the shoutout!
This is a great tip, but as of July 2021 are the licensing requirements the same as way back in February?
MS changes everything monthly it seems. My corp environment includes Lists in the 365 apps pane.
This means if the user should not be able to see all of the records in the table, they could open up the underlying list and view or modify any of the records, even if you are obscuring the records inside of your app.
Choice is delegable by now in sharepoint
Sorry, but I would disagree with this article and it tells me that you really are not aware of the Microsoft 365 ecosystem. Sharepoint is at the core of nearly all things Microsoft 365, including Teams, OneDrive, PowerApps, Dynamics, etc.
In actuality, Sharepoint is not a bad choice if that is your only option. I have built many mission critical applications around this system without a hitch.
Sorry, but you might want to jump into Microsoft 365 today and rethink your recommendations.
I agree this article is garbage.
We’ll, robbing a bank is not a bad choice if that is your only option. This article raised concerns that using multiple Sharepoint Lists as a relational database is by design slower and more difficult to govern that an actual relational database. Did you have arguments to counter that claim?
Depends on your skillset.
Corporations are continually shifting development needs to their managers, offering little else in the way of tech support than a few standard licenses for stuff like Microsoft 365.
These licenses aren’t cheap, and the knowledge isn’t there to engage in traditional development, which itself has produced the bloated, awful software we all hate anyway.
Where people are successful, they used what was available.
This all boils down to dedicated developers being stuck on the ongoing projects that are constantly shifting scope because management can’t make a decision.
Microsoft advertises itself as developing the tools to solve business problems. Well, the primary business problem is its management, so they need to deliver tools that engineer them out of the process.
[…] Tip #1402: Why you should not use Microsoft Lists/SharePoint Lists for business-critical Power Apps […]
There’s now a way to augment SharePoint Lists for Power Apps that resolves many of the issues you mentioned: https://powerusers.microsoft.com/t5/Community-App-Samples/Large-SP-List-Delegation-Workaround-App/td-p/2330721
[…] and sensitive data that should not be accessible to all app users, then you should explore alternative data sources for your Canvas […]