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.