How to collect data from a workweek??

[star_EF]

Hi!

I'm new to the Airtable community, I'm still finding my way around. I hope all is well!

Sooooo, Im wanting to create a smoother process for sending out invoices to my sub-contractors for a work week (Mon- Sun). I have come across a bump!

Let's pretend this work week is Feb/12 - Feb/18 (Mon-Sun)

So I have two tables:

1. Booking Details: ignore the entry at the bottom, it's not part of the pretend work week

2. Payout Details 

Now, The info from table 1 contains a job record: who worked it, the price, the date, etc. In this case, all those jobs were worked by "test@gmail.com" (one person). These jobs were done in different days but in the same Work week of Feb.12-Feb.18. 

Table 2 is supposed to put all the jobs together from this work week and combine them into 1 record: who worked them, the names of the jobs... which would go under booking details (name: from table 1, first column), and add the total of jobs completed.

Now, for table 2, the reason I put start date and end date (although I know this is not going to resolve my problem) is because I wanted to show how I want it to appear in the "payout record". I want the email of the person and the work week to appear in the payout record. 

My questions are: What is the formula for this? Do I need to do a roll up? Am I supposed to link something?

Btw, I'm wanting to automate this. I would rather not go and input any info myself... do I need to add an automation?

Thanks!

[TheTimeSavingCo · Accepted solution]

Try this:
1. Create formula fields that'll calculate the start and end of that specific work week based on the 'Job Date' entered
  - There are a bunch of threads on the forums where people go into how to get, say, the previous Monday to a specific date, so you can start there
2. Create a formula field that'll output the text '[PROVIDER EMAIL] | [Start of work week] - [End of work week]', so it'd look like 'test@gmail.com | 02/12/2024 - 02/18/2024', just like you have in your 'Payout Details' table
3. Convert the 'Payout Record' field to a text field
4. Create an automation that'll trigger when the 'Provider email', 'Start of work week', 'End of work week' and the formula field from step 2 are not empty
5. Give this automation an 'Update Record' step, where it'll update itself and paste the value from the formula field from step 2 into the linked field to the 'Payout Details' table

This system will create one record per work week per provider email in the 'Payout Details' table.  Now, you can create a lookup field to display the email and the work week from the 'Booking Details' page

[star_EF]

Hi!! So i stayed up all night looking for formulas in the forums but they weren't exactly what I was looking for. I'm not formula savvy either. I don't think I'm understanding, pls help! I don't know what formulas would be best

[TheTimeSavingCo · Accepted solution]

Ahh, sorry to hear that.  I've built something for you here that should do what you're looking for

It includes the formulas to calculate the start and end of the job week, as well as the automation to link them together.  You can view the formulas once you duplicate the base to your own workspace!