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! Smiley Happy

 

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!

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

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!

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

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 190 members 4,361 guests
Please welcome our newest community members: