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
HenryARPhillips
Continued Contributor
Continued Contributor

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

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

@alex93jansen 

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

Untitled.png

Drrickryp
Super User II
Super User II

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

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,258)