Our Time registration application has been built very well from blank as Powerapps supports lots of useful features in the front end. We are using Excel as a datasource for my application.
We have rolled out the application for all the users in our organisation and now we are experiencing the Data limitation of 500 rows in excel file using Delegation functions like Filter, Sort, Sum which is making us to complete stopover on this front.
There seems to be no workaround to avoid this limitation as we have already tried 'collection' to store data locally and then used Filter, Sort, Sum function but it failed.
Would you please let us know if you know workaround on this issue or when can be Excel added to the Delegation datasources list.
Have you checked the solution posted by Mr-dang?
There is no ETA that could be shared from my side regarding the delegation of Excel.
Just FYI you cannot use Mr-dang solution on Excel since his solution requires a data source which is delegable to be able to pull in more than 500 records into a local collection.
Since there is no delegation in Excel you cannot work with Excel tables that have more than 500 records. I strongly recommend using Azure Database as for me it seems to work the best with PowerApps and it supports the most functions for delegation.
You are correct, Mr-Dang solution wont work for excel database.
Can you share me the details how to get Azure SQL and will it be included in Office 365 business packages?
Your input would be much helpful for me to get started 🙂
If you already have Microsoft Products in your organization it should be very simple to get Azure Database.
Thanks for all the inputs, we have made connection between SQL to my Application in Powerapps.
Filter/Sort works fine. However, Sum() still has limitations on above 500 rows even though i have filtered the 20-30 records from my Database of 1000 records.
It won't work that way for Excel database as Mr.Dang has mentioned the solution for CDS.
How is your Sum() function setup because in SQL Server the SUM() function is fully delegable since few months ago.
Take a look at this table of what is delegable for what data source:
Below is my formula for Sum:
Coalesce(Sum(Filter('[dbo].[TimeSheet_Entries]',Month= TextBox22.Text,User=TextBox9_3.Text,Year= TextBox25.Text, Task= "Raven"),Hours),0)
Do you think i have to tweak a bit to my formula?
Check it out!
Fill out a quick form to claim your user group badge now!
Find out where you can attend!
Features releasing from October 2019 through March 2020
The largest Power BI, Power Platform, and Data conference in New Zealand