How to Count Records by the Week they were Created?

Karl Larson

Overall ObjectiveUsers set a target for the number of times they want to workout per week and for how long (e.g. 4 workouts per week for 20 out of 26 weeks). Every time they workout, the post a photo to prove they worked out.

To see if they achieved their target, I need to count the number of workouts every week and compare that to their weekly goal. Then, update the total number of weeks they've reached that goal vs. did not and compare it to their overall target.
DetailsHere is a screenshot of the goals table. [CleanShot 2021-11-05 at 11.21.29@2x.jpg]
I've created a function that gets the id and created time of all a user's photos.
[CleanShot 2021-11-05 at 11.22.50@2x.jpg]
Now, I'm trying to figure out how to get the week from the photo's timestamp, and then update the done_workout_weeks and done_rest_weeks (I'm ignoring done_light_weeks for now).

Any ideas? Thanks in advance for your advice.

Michael Udinski

Hey  - you should be able to accomplish this with the aggregator return type on your query all records function...

Group by: user_id and
created_at (filter: timestamp_week)

Aggregate by:
user_id (or other field that makes sense)
Aggregator: count


[Screenshot_2021-11-05_19-19-56.png]

If you want it to just be an individual user, then I would turn on authentication on and first do a by custom query where photos.user_id = auth ID.... and then you also don't need to group by user id by doing that.