cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alex93jansen
Helper II
Helper II

Unable to SUM a Numeric SQL field

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.

10 REPLIES 10
Anonymous
Not applicable

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.

Drrickryp
Super User
Super User

@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. 

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
Super User
Super User

@alex93jansen 

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

Untitled.png

Drrickryp
Super User
Super User

@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.

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
Super User
Super User

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (2,750)