- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 03, 2023 09:45 AM
Thank you for considering to help me! Here's the process I'm trying to create:
- A new record is created via a form which will have a start and end date (i.e. 7/3-7/28)
- Based on the timeframe (above), I would like to set up an automation where additional new records are created (within the same table) for each week within that timeframe. (i.e. a new record with the dates 7/3-7/7; another record with the dates 7/10-7/14; another one for 7/17-7/21 and the last one for 7/24-7/28). There will be other fields that I'd like to copy from the original record as well.
I'd like to avoid a script and just use formulas and automations, but am open to a script (although will need help writing one!) if that's the best/only option.
Thank you!
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 12, 2023 09:28 PM - edited Jul 12, 2023 09:30 PM
I took a crack at solving this without a script in the latest episode of the BuiltOnAir podcast: S15, E01. In short, you'll need one automation and two "helper" formula fields
https://www.youtube.com/live/G2zGaPN98zU?feature=share&t=2586
The formulas for the two helper fields discussed in my implementation:
⚙️Clamped End Date
The following formula will get the Friday of whatever week "Start Date" is in.
IF({Start Date},DATEADD({Start Date}, 5-WEEKDAY({Start Date}), "days"))
⚙️ Next Start Date
Te=he following formula will simply add one week to the "Start Date" if the "Clamped End Date" is before the "End Date". Essentially, if the time span is longer than a week, this field tells the automation that a new record needs to be created, and this date will be its "Start Date".
IF({⚙️ Clamped End Date} < {End Date},DATEADD({Start Date}, 1, "week"))
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 12, 2023 09:28 PM - edited Jul 12, 2023 09:30 PM
I took a crack at solving this without a script in the latest episode of the BuiltOnAir podcast: S15, E01. In short, you'll need one automation and two "helper" formula fields
https://www.youtube.com/live/G2zGaPN98zU?feature=share&t=2586
The formulas for the two helper fields discussed in my implementation:
⚙️Clamped End Date
The following formula will get the Friday of whatever week "Start Date" is in.
IF({Start Date},DATEADD({Start Date}, 5-WEEKDAY({Start Date}), "days"))
⚙️ Next Start Date
Te=he following formula will simply add one week to the "Start Date" if the "Clamped End Date" is before the "End Date". Essentially, if the time span is longer than a week, this field tells the automation that a new record needs to be created, and this date will be its "Start Date".
IF({⚙️ Clamped End Date} < {End Date},DATEADD({Start Date}, 1, "week"))
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 17, 2023 09:53 AM
Wow, @Kamille_Parks !! I'm honored that you used my question on your podcast, THANK YOU! this solution is brilliant - exactly what I was looking for. I'll work on testing it and will let you know if I have nay questions. THANK YOU SO MUCH!