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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

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

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (3,023)