# Create a List of specific Relation Items **URL:** \ **Category:** Ask for Help\ **Created:** [May 4, 2023, 12:30am UTC](https://community.glideapps.com/t/create-a-list-of-specific-relation-items/61321 "2023-05-04T00:30:36Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![Matty\_Fenny](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/matty_fenny/32/39419_2.png) [@Matty\_Fenny](https://community.glideapps.com/u/Matty_Fenny)\ **Post date:** [May 4, 2023, 12:30am UTC](https://community.glideapps.com/t/create-a-list-of-specific-relation-items/61321/1 "2023-05-04T00:30:36Z")
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. ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [May 4, 2023, 1:27am UTC](https://community.glideapps.com/t/create-a-list-of-specific-relation-items/61321/2 "2023-05-04T01:27:52Z")
This might be perfect timing. > [@Conditional Relations](https://community.glideapps.com/t/conditional-relations/13818/21): > > Something is coming later this week… > [@new Query column!](https://community.glideapps.com/t/query-column-is-in-staging/61320): > > This request has been brought to life. [https://community.glideapps.com/t/conditional-relations/13818/](https://community.glideapps.com/t/conditional-relations/13818/) See Mark’s video here. [https://www.loom.com/share/a16f6d57e6904c8889715d4dc9266b3e](https://www.loom.com/share/a16f6d57e6904c8889715d4dc9266b3e) ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [May 4, 2023, 2:40am UTC](https://community.glideapps.com/t/create-a-list-of-specific-relation-items/61321/3 "2023-05-04T02:40:57Z")
As Jeff alluded to, there is a new feature coming that will make this sort of thing trivial. But in the meantime: > [@Matty\_Fenny](#): > > 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. - 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 > [@Matty\_Fenny](#): > > //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. 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: ```auto Now + MOD(8-WEEKDAY(Now),7) - 6 - HOUR(Now)/24 - MINUTE(Now)/1440 - SECOND(Now)/86400 ``` ---
**Author:** ![Matty\_Fenny](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/matty_fenny/32/39419_2.png) [@Matty\_Fenny](https://community.glideapps.com/u/Matty_Fenny)\ **Post date:** [May 4, 2023, 10:49am UTC](https://community.glideapps.com/t/create-a-list-of-specific-relation-items/61321/4 "2023-05-04T10:49:30Z")
> [@Darren\_Murphy](#): > > ```auto > Now > + MOD(8-WEEKDAY(Now),7) > - 6 > - HOUR(Now)/24 > - MINUTE(Now)/1440 > > ``` 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. ---
**Author:** ![Matty\_Fenny](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/matty_fenny/32/39419_2.png) [@Matty\_Fenny](https://community.glideapps.com/u/Matty_Fenny)\ **Post date:** [May 4, 2023, 10:53am UTC](https://community.glideapps.com/t/create-a-list-of-specific-relation-items/61321/5 "2023-05-04T10:53:41Z")
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. ---
**Author:** ![Matty\_Fenny](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/matty_fenny/32/39419_2.png) [@Matty\_Fenny](https://community.glideapps.com/u/Matty_Fenny)\ **Post date:** [May 4, 2023, 11:03am UTC](https://community.glideapps.com/t/create-a-list-of-specific-relation-items/61321/6 "2023-05-04T11:03:45Z")
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. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [May 4, 2023, 11:09am UTC](https://community.glideapps.com/t/create-a-list-of-specific-relation-items/61321/7 "2023-05-04T11:09:00Z")
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. ---
**Author:** ![Matty\_Fenny](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/matty_fenny/32/39419_2.png) [@Matty\_Fenny](https://community.glideapps.com/u/Matty_Fenny)\ **Post date:** [May 4, 2023, 11:45am UTC](https://community.glideapps.com/t/create-a-list-of-specific-relation-items/61321/8 "2023-05-04T11:45:49Z")
Ohhh that makes sense hahah Thanks! ---
**Author:** ![Matty\_Fenny](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/matty_fenny/32/39419_2.png) [@Matty\_Fenny](https://community.glideapps.com/u/Matty_Fenny)\ **Post date:** [May 4, 2023, 1:20pm UTC](https://community.glideapps.com/t/create-a-list-of-specific-relation-items/61321/9 "2023-05-04T13:20:00Z")
What would the Math need to be to get the current month? ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [May 4, 2023, 1:35pm UTC](https://community.glideapps.com/t/create-a-list-of-specific-relation-items/61321/10 "2023-05-04T13:35:54Z")
I would use this: ```auto 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. ---
**Author:** ![Matty\_Fenny](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/matty_fenny/32/39419_2.png) [@Matty\_Fenny](https://community.glideapps.com/u/Matty_Fenny)\ **Post date:** [May 4, 2023, 1:51pm UTC](https://community.glideapps.com/t/create-a-list-of-specific-relation-items/61321/11 "2023-05-04T13:51:53Z")
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. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [May 4, 2023, 2:02pm UTC](https://community.glideapps.com/t/create-a-list-of-specific-relation-items/61321/12 "2023-05-04T14:02:34Z")
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? ---
**Author:** ![Matty\_Fenny](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/matty_fenny/32/39419_2.png) [@Matty\_Fenny](https://community.glideapps.com/u/Matty_Fenny)\ **Post date:** [May 4, 2023, 2:03pm UTC](https://community.glideapps.com/t/create-a-list-of-specific-relation-items/61321/13 "2023-05-04T14:03:33Z")
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 ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [May 4, 2023, 2:13pm UTC](https://community.glideapps.com/t/create-a-list-of-specific-relation-items/61321/14 "2023-05-04T14:13:25Z")
Okay, so this is where I would use a [Helper Table](https://community.glideapps.com/t/how-would-you-describe-a-helper-table/54497). 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.