Auto create new records for every unique date within a date spread

Megan_Jennings · ‎Apr 05, 2022

I have a base my team uses for resource planning and time tracking. I assign tasks and hours in the Gantt view, tasks sometimes span days and weeks (there is a start date, end date, and duration field). My employees then add their actual hours to the existing records as a time sheet.

The problem I’m running into is that when assigning multi day tasks only one assignment record is created for the first day of the task, and all assigned hours are put on that task. I need an automation that will create a new record for every unique date between the start and end date of a task, duplicating all other attribute information and assigning the appropriate daily hours according to a formula.

Is this possible within Airtable or do I need to use a different service?

Thanks in advance for any thoughts or advice!

kuovonne · ‎Apr 05, 2022

This is possible with Airtable. This can be done with or without scripting.

To do this with scripting, you need a custom script, but you would not need to create any other fields.

To do this without a script, you need automations and some additional fields.

Setup a formula field to calculate the number of days. Use DATETIME_DIFF() and your start and end dates.
Have a rollup field that counts the number of linked days.
Have a rollup field that shows the last day of all the linked days. Use MAX(values).
Have a formula field calculates the next day to be created. If there are some days, but not enough, the value is the day after the date in the rollup showing the last date. If task takes only one date or if all the dates are created, the formula field is blank.
Have one automation that kicks off creating the first date record.
Have a second automation that creates any additional dates. The trigger for this is watching for changes to the formula field that calculates the next date to be created. When the value changes, if there is a date in the field, create a new linked date record with that value.

See Solution in Thread

kuovonne · ‎Apr 05, 2022

This is possible with Airtable. This can be done with or without scripting.

To do this with scripting, you need a custom script, but you would not need to create any other fields.

To do this without a script, you need automations and some additional fields.

Setup a formula field to calculate the number of days. Use DATETIME_DIFF() and your start and end dates.
Have a rollup field that counts the number of linked days.
Have a rollup field that shows the last day of all the linked days. Use MAX(values).
Have a formula field calculates the next day to be created. If there are some days, but not enough, the value is the day after the date in the rollup showing the last date. If task takes only one date or if all the dates are created, the formula field is blank.
Have one automation that kicks off creating the first date record.
Have a second automation that creates any additional dates. The trigger for this is watching for changes to the formula field that calculates the next date to be created. When the value changes, if there is a date in the field, create a new linked date record with that value.