Unable to SUM a Numeric SQL field

[alex93jansen]

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.

[Anonymous]

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.

[alex93jansen]

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.

[Drrickryp]

@alex93jansen 

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. 

[alex93jansen]

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?

[Drrickryp]

@alex93jansen 

Could you post a screenshot of the delegation warning please showing the blue line in your formula?

[alex93jansen]

[Drrickryp]

@alex93jansen 

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.

[alex93jansen]

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?

[Drrickryp]

@alex93jansen 

Yes