I have the following formula in my PowerApp:
Sum( Filter( '[dbo].[tmtrak_dev_records]', submittedBy = userEmail, activityDate = Date(2021,3,1) ), activityHours )
Where "dbo.tmtrak_dev_records" is a SQL data source and "activityHours" has data type NUMERIC(4,2).
The formula above doesn't display any errors, but returns a blank value. When I wrap "activityHours" in a VALUE() function it correctly displays the result.
Since VALUE() isn't a delegated function however this isn't a workaround solution for me. Any thoughts?
The one odd thing I found is that in the Filter table result, "activityDate" has a value of "2021-03-01, 12:00:00 a.m." even though it has data type DATE in SQL (i.e. "2021-03-01"). When "activityDate" is left out from the formula, the SUM is correctly calculated.
For me I would use the Power of SQL and get everything present and correct at source and then make use of in your Power App. So what I like to do where possible is for example create a view in SQL based off of the Table, with desired logic and then make use of said view within the Power App.
This sounds like a huge limitation of PowerApps though. Essentially, comparing against a Date in SQL while preserving the original data types isnt possible?
I have tried changing my SQL datatypes to DATETIME, DATETIME2 and to various numeric ones for "activityHours" but it always requires wrapping it in a VALUE-function to get it to calculate correctly.
Instead of Value() you can multiply activityhours by 1. This is a short cut in PowerApps that I use all the time to make my formulas more readable.
The following formula still throws a delegation error though:
Sum( Filter( '[dbo].[tmtrak_dev_records]', submittedBy = userEmail, activityDate = mondayDate ), activityHours * 1 )
Am I missing something here, or is this merely an alternative to Value() without specifically addressing the delegation warning?
A delegation warning is only a warning not an error. If you are summing more than 2000 rows, it can be a problem but if you are not dealing with such large numbers, you can ignore it.
So my entire dataset ("dbo.tmtrak_dev_records") will have many thousands of records, but the table result of the Filter function ("submittedBy = userEmail, activityDate = mondayDate") will have at most a couple of records.
Since the Sum function is only applied to these few records, does it mean I can ignore the delegation warning?