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
alex93jansen
Helper II
Helper II

So now I actually want to display, in a chart by %, how much time a user has been spending on each activity for a set date range. For date ranges that exceed 6 months, this will likely involve SUM'ing more rows than can be processed locally (i.e. >2000), so the fact this formula is non-delegable will become an issue.

 

I guess I'm just bringing up this topic again hoping someone has a different solution that doesn't involve the VALUE-function.

 

 

Helpful resources

Announcements
Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

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

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.

<
Users online (4,338)