cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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
Highlighted
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

Highlighted
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
Highlighted
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

Highlighted
Helper II
Helper II

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!

Highlighted
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
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (3,312)