【PowerApps】 What does “Delegation” mean? Also, the issues with using Excel as a database
In order to make a robust app, you can’t avoid ‘Delegation’ in PowerApps. This article explains the concept of the Delegation in an easy to understand way with images. Also, it explains the issues with using Excel as a database.
The issues with using Excel as a database
Hi, this is Mike Negami, Lean Sigma, Black Belt.
In my previous PowerApps videos, I used Excel files stored in OneDrive as a database. ⇒”【PowerApps】PowerApps: 4 great tips in preparing the source data in Excel”
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.
Why does PowerApps have this data limitation?
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.
In the menu bar, select ‘File’, ‘App Settings’, ‘Advanced 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.
What is the ‘Delegation’ function in databases?
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.
What does the ‘Delegation’ effect to app makers?
This technology is kind to devices and communication traffic, but it increases the burden on us, the app makers. Only we can do for this is to improve our skill.
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!
Which database should we use after all? SharePoint is recommended.
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. Please refer to it.
＜＜Microsoft Official Page: Understand delegation in a canvas app ＞＞
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview
“See these other popular articles.”
