Today we’ll discuss the issues with using Excel as a database and the Delegation of
PowerApps!
[Intro Video]
Hi, this is Mike Negami, Lean Sigma, Black Belt.
In my previous PowerApps videos, I used Excel files stored in OneDrive as a database.
That’s because I'm used to Excel and it's easy to use it with PowerApps as well.
However, there are issues with using Excel as a database such as the following:
1.
While the Excel file is open, if you use the app, an error will occur.
This problem occurs during an app’s development.
Each time you want to test to save your data to the database from your app, you have to
close the Excel file.
Honestly, it’s very annoying.
2.
An error occurs when multiple apps try to connect to the file at the same time.
It would be ok if there is one user, but when you use the app in a team, this would be a
serious problem.
3.
If the number of data exceeds 2000, the subsequent data cannot be referenced.
We can use only 2000 data!?
It sounds like a joke, but it’s true right now.
That limitation used be 500.
Microsoft is improving this platform everyday.
However, the default value is still only 500, but you can increase it up to 2000 yourself.
Let me show you how.
In the menu bar, select ‘File’, ‘App Settings’.
Then, at the ‘Restrict Data Rows for Queries You Cannot Delegate’, you can increase this
number up to 2000.
Why is there such limitation?
Mobile devices have lower storage capacity and power than PCs, so they can't handle large
amounts of data directly.
That's why Microsoft made this restriction.
However, these days, in the era of big data, there is no use for apps with a data limit
of 2000.
Therefore, there is a technology called “Delegation” for databases.
For example, suppose that there are 2000 numerical data in the database and we want to calculate
their total.
Without Delegation, all 2000 lines of data are brought to your device and your app calculates
the total using the Sum Function.
However, if there are more than 2000 lines, your app can access only the first 2000 lines
and your calculated total will be not accurate.
On the other hand, ‘Delegation’ is a technology that does not bring the data to the device,
but “delegates” to calculate the total on the database side, and brings only the
end result to the device.
This can greatly reduce the burden on the device and communication traffic.
This technology is kind to devices and communication traffic, but it increases the burden on us,
the app makers.
First of all, Excel files do not have this delegation function.
Excel is basically for individual not team use and the database application is an extra
feature.
Another difficulty in ‘Delegation’ is that if you can use the ‘Delegation’ is
depending on databases and functions you use.
Also, how you write formulas will be changed depending on situations.
I’ll talk about those in my future videos.
But still Delegation is a great feature!
Then, which database should we use after all?
Microsoft prepares a variety of databases for us: SharePoint, Common Data Services and
SQL Server have Delegation features.
Among them, you can use SharePoint at no additional cost and since it has many users, there is
plenty of information available, so I would recommend it for PowerApps beginners.
From now on, I’ll make PowerApps videos using SharePoint.
I put a link to the official Microsoft page about delegation in the video description
section below.
Please refer to it.
Thank you very much for viewing.
Please click the ‘Subscribe’ button.
Also click and watch my other related videos.
Thanks.