Create a List of specific Relation Items

1

Hi,

Im going to try and explain this the best I can but I have been trying for ages and my head is about to fall off…

My app is a Jobsheets App where engineers fill it out when completing a job and then submit a custom form.

The Job Tab has all of the job information and the ones that are important in this case are:

Completed Date
Completed By
Job Price

In my User Sheet, I have a Relation where User Name matches JobTab/Completed By. I then have a rollup of the Job Prices in that relation that calculated the sum of all of the jobs completed. All works fine!

//Total Earned by engineer TODAY
I now want to be able to calculate the sum of all of the jobs completed in a certain time frame.
So in the Relation for Completed by, I need to check if the Completed Date is Todays Date and only have Those jobs in a Rollup.

//Total Earned by engineer THIS WEEK
Then if possible, have another Rollup, same concept as above but if the Completed Date is within This Week.

hopefully that makes sense.

Thanks,

Matty.

2

This might be perfect timing.

2 Likes
3

As Jeff alluded to, there is a new feature coming that will make this sort of thing trivial. But in the meantime:

  • Create an if-then-else column in your Jobs sheet:
    – If Completed Date is within today, then Job Price
  • Now you can rollup that column through your existing relation

Same thing here, except you’ll need a math column to pre-calculate the date/time at the start of the week, and then compare that to the Completion Date. Assuming that your week starts on Monday, then the following could be used:

Now
+ MOD(8-WEEKDAY(Now),7)
- 6
- HOUR(Now)/24
- MINUTE(Now)/1440
- SECOND(Now)/86400
2 Likes
4

The Total Earned Today worked perfectly Thankyou for much for that!

You’ve lost me a little on the This week one if I’m honest.

5

Ive managed to do the math column and then check if Completed Date is on or after the math column, but how to I check if its within Monday to Sunday?

Thanks,

Matty.

6

Okay, relising things as I go along… I just made a math column and calucated (Start of the Week + 6) and it gave me the Sunday. So now I can add another Case after checking if Completed date is on or after Start of the Week, and check if Completed Date is on or Before End of the Week.

7

You don’t actually need to do that, unless you have completion dates in the future. And I assume that’s not the case?

The math column should return a date, which represents midnight on the most recent Monday. So any row with a completion date after that date must be in the current week. Therefore, your if-then-else column is simply:

  • If completion date is after math date, then Job Price.

And then you rollup on that column.

8

Ohhh that makes sense hahah Thanks!

9

What would the Math need to be to get the current month?

10

I would use this:

Year(Now) * 100
+ Month(Now)

That will give you a number that looks like 202305 (for May 2023).
Do the same thing for your Date column in the Jobs table, then you can do a direct numerical comparison.

11

Great thats working thankyou!

What would be the best way to store these values? When the month changes, the values will go back to 0 until another job is completed so I need a way of storing each day, week, month and years takings.

12

Store for what purpose?

The data is there, so you can dynamically recalculate for any time period whenever you need to.

But lets say you were to calculate these and store them in a separate table somewhere - what would you use that for?

13

For finance report to easily keep track:

January 2023 - We took this much money
February 2023 - This much

etc…

Once a new week starts, and a new month starts, the taking for the previous weeks wont exist anymore

14

Okay, so this is where I would use a Helper Table.

Have a read through that thread, and it should give you the general idea. But essentially you’d just need a separate table with 12 rows - one row for each month of the year. Then you build relations from that table and do rollups through those relations. That’s a gross over simplification, but once you get the hang of it you can use this approach to dynamically generate any sort of report for any time period that you need.

2 Likes