Loop sum expression

[Pejme]

I have a timesheet app. The users can record multiple shifts per day. This will be captured in the 'Shift' table. I would like to be able to see how much overtime each user has worked per day.

My current expression is an overtime column in the Shift table with an expression that calculates how many, if any, overtime hours have been worked at the time of the shift being recorded. I.e if a 2-hour shift is recorded, then the expression checks how many hours have already been recorded that day, and then decides if the 2 hours are within the standard 8-hour work day, or if they should be considered overtime because 8 or more hours have already been recorded in previous shifts. However,  this way of calculating the overtime hours is not good enough as it limits the possibility to edit/delete previous shifts.

I currently have another table called Pay review, which sums up the shifts for each user every week. I think I need an expression or Behavior that will sum up the total hours each day for each user in a sort of loop, but I don’t know how to do it. Maybe some sort of “Data execute an action on a set of rows” behavior? But I am a bit lost.

Ideally, a behavior expression similar to the below for the Pay review table that goes in a loop. Obviously, the “[Date]=Same date” is not right, but something that gets the app to check the sum of Hours worked that are included in the same Pay review on the same day

SUM(
SELECT(
Shift[Hours worked],
[_THISROW].[Id]=[Pay review],
Date]=Same date))

Shift table:

Pay review table:

Help, please 🙂 

[Steve]

I could imagine adding a column for each day of the pay period (Day 1, Day 2, etc.) to the Pay review table. Create an Automation that responds to all changes in the Shift table and (re)computes the corresponding pay period's daily totals.

For Day 1:

SUM(
  SELECT(
    Shift[Hours worked],
    ([_THISROW].[Id] = [Pay review]),
    (Date] = ([THISROW].[Pay period start] + 0))
  )
)

For Day 2:

SUM(
  SELECT(
    Shift[Hours worked],
    ([_THISROW].[Id] = [Pay review]),
    (Date] = ([THISROW].[Pay period start] + 1))
  )
)

And so on.

[SkrOYC]

I'm sorry, must be something I missed, but I don't see the complexity of it.

Again, sorry if I missed something

[Pejme]

@Steve Of course! I can't believe I didn't think of that. You are right, @SkrOYC , that's not complex at all 🙂

I guess I was so focused and sure that there would be some type of loop I could create that I didn't even think about writing an expression that covers each day.

As much as @Steve 's solution resolves my described problem, it is still manual, with limitations. The pay review period needs to be weekly for it to work. What if I would like the pay review period to be decided by the user?

I am just trying to learn, and was hoping that I would learn something that I could then also apply to other scenarios 🙂

[Steve]

---

@Pejme wrote:

What if I would like the pay review period to be decided by the user?

---

You'd need to use loops. Much more complex.

[Pejme]

@Steve So loops are possible? Could you please point me in the right direction on where I can learn more about this?

[Steve · Accepted solution]

Take a look at these search results:

Search - Google Cloud Community

[Pejme]

@Steve Love how the first search result starts with "Kinda burnt out and just wanna get this out", written by yourself 😂

Thanks for sharing what to search for and creating that sample app. Excited to look into it and learn more 🙂