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

Displaying result of a SQL query

I have a SQL Server data source named [dbo].[tmtrak_dev_records] with the field 'activityHours'. I'd like to display the result of the following query as a Label:

 

SELECT SUM(activityHours)
FROM dbo.tmtrak_dev_records
WHERE activityDate = '2021-02-22' AND submittedBy = 'user@email.com'

 

Where the activityDate value is the result of:

 

Today() - (Weekday(Today(), StartOfWeek.Monday) - 1)

 

and the submittedBy value is the result of:

 

User().Email

 

We intend to use this PowerApp as a Time-Tracking tool for our department. This query result would display how many hours a user has logged for for each day of the week (Monday in this example).

 

I'm struggling however with getting this to work, and figuring out if it can be done at all. I have tried various things unsuccessfully, and am just hitting a wall with this.

 

Any help is greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
alex93jansen
Helper I
Helper I

Nevermind, I found the solution myself. The following code retrieves all record for Monday (of the current week) for the current user, Sums the activityHours field and displays it as "x.xx / 8.00 hours logged"

Concatenate(
    Text(
        Sum(
            Filter(
                '[dbo].[tmtrak_dev_records]',
                submittedBy = User().Email,
                activityDate = Today() - (Weekday(Today(), StartOfWeek.Monday) - 1)
            ),
            activityHours
        )
    ),
    " / 8.00 hours logged"
)

View solution in original post

2 REPLIES 2
alex93jansen
Helper I
Helper I

Nevermind, I found the solution myself. The following code retrieves all record for Monday (of the current week) for the current user, Sums the activityHours field and displays it as "x.xx / 8.00 hours logged"

Concatenate(
    Text(
        Sum(
            Filter(
                '[dbo].[tmtrak_dev_records]',
                submittedBy = User().Email,
                activityDate = Today() - (Weekday(Today(), StartOfWeek.Monday) - 1)
            ),
            activityHours
        )
    ),
    " / 8.00 hours logged"
)

View solution in original post

v-bofeng-msft
Community Support
Community Support

Hi @alex93jansen :

Glad to hear that you have solved this problem.

Please consider marking your solution to a "Solution" to help others.Thanks.

Best Regards,

Bof

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (44,077)