cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
webdevguy
Level: Powered On

Excel vs Sharepoint Lists

I come from a .NET background, so I'm used to relational databases in SQL Server, etc.... So, this PowerApps concept is relatively new... but SUPER cool! 🙂

 

I created my first app and used Excel as the back end. Simple table. If I have 10 simultateous users, for example, wil lthey all be able to read/write from the data ok, or will there be locking/conflicts, etc...?

 

Also, is a Sharepoint List better to use than Excel for simple database-like stuff? From what I've read Sharepoint Lists are like database tables??? Which is "best practice" for small businesses?

 

Thanks for the info! Looking forward to being an active member of this community!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Excel vs Sharepoint Lists

Hi @webdevguy

 

Welcome to the community! In terms of supporting simultaneous users and larger data sets, SharePoint is more robust than Excel.

In answer to your question, yes - SharePoint lists are similar to database tables in that you can define columns and also define the data types of the columns in your list. With SharePoint, you can set mandatory fields and create validation rules - something that you can't do in Excel.

One of the most important things to consider when choosing a data source is 'delegation support'. This is important because if you carry out a search or query against a data source using a non delegable operation, PowerApps has to retrieve all the records and perform the query locally. There is a limit to the number of records that PowerApps can retrieve (currently 2000), so the results from a non-delegable query may be incomplete. You can find read more about this here.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list

If you have experience of using SQL Server, I would recommend SQL Server if this is possible. PowerApps supports SQL Server, and you can also connect to SQL databases in your internal network th rough the on-premises gateway. My post here refers to some of the benefits of using SQL Server (Azure and on-premises).

https://powerusers.microsoft.com/t5/General-Discussion/CDS-vs-Azure-SQL-Database/m-p/108940#M39373

 

Good luck!

View solution in original post

Super User
Super User

Re: Excel vs Sharepoint Lists

With SharePoint, you can define 'lookup columns' on a list. The data source of lookup column can come from another SharePoint list so from that point of view, it's possible to do basic relational DB type stuff.

https://support.office.com/en-gb/article/create-list-relationships-by-using-unique-and-lookup-column...


Office 365 won't give you access to SQL Azure.

But if you want a no-cost option, you can install SQL Server Express on your local network.

https://www.microsoft.com/en-gb/sql-server/sql-server-editions-express

You can then use the on premises gateway to connect to your database.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/gateway-reference

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Excel vs Sharepoint Lists

Hi @webdevguy

 

Welcome to the community! In terms of supporting simultaneous users and larger data sets, SharePoint is more robust than Excel.

In answer to your question, yes - SharePoint lists are similar to database tables in that you can define columns and also define the data types of the columns in your list. With SharePoint, you can set mandatory fields and create validation rules - something that you can't do in Excel.

One of the most important things to consider when choosing a data source is 'delegation support'. This is important because if you carry out a search or query against a data source using a non delegable operation, PowerApps has to retrieve all the records and perform the query locally. There is a limit to the number of records that PowerApps can retrieve (currently 2000), so the results from a non-delegable query may be incomplete. You can find read more about this here.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list

If you have experience of using SQL Server, I would recommend SQL Server if this is possible. PowerApps supports SQL Server, and you can also connect to SQL databases in your internal network th rough the on-premises gateway. My post here refers to some of the benefits of using SQL Server (Azure and on-premises).

https://powerusers.microsoft.com/t5/General-Discussion/CDS-vs-Azure-SQL-Database/m-p/108940#M39373

 

Good luck!

View solution in original post

webdevguy
Level: Powered On

Re: Excel vs Sharepoint Lists

Thanks for your reply!

 

If I use a sharepoint list is it possible to do basic relational db type stuff?

 

Also, I have an Office 365 account. Does this include a SQL Server account? In the past my previous company already had a server setup, etc.... 

 

Thanks!

Super User
Super User

Re: Excel vs Sharepoint Lists

With SharePoint, you can define 'lookup columns' on a list. The data source of lookup column can come from another SharePoint list so from that point of view, it's possible to do basic relational DB type stuff.

https://support.office.com/en-gb/article/create-list-relationships-by-using-unique-and-lookup-column...


Office 365 won't give you access to SQL Azure.

But if you want a no-cost option, you can install SQL Server Express on your local network.

https://www.microsoft.com/en-gb/sql-server/sql-server-editions-express

You can then use the on premises gateway to connect to your database.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/gateway-reference

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,260)