Create new records from range of start date and end date

[Mike_Campanella]

I am trying to set up an employee scheduling table. The table has links to my events table and to my employee table. The events have a "Start date" and "end date". Is there a way to create new records in the scheduling table pulled from events in the events table for each date in the start to end range? Ideally it would have all the event info in each day of the range. Employees are scheduled for full days so i wont need times, i would just like the ability to be able to add employees to each day of the event. Thank you for any help!

[salvadaor]

Hey Mike - I wrote a script to do this since I couldn't figure out a way to do this using the native automation tools. I created a formula field in the events table called "Duration" that outputs a number that represents the amount of days for the event. The script is set to run when a record is created. I set up a simple form to intake the event name, start date and end date. The "Duration" field is instantly calculated, which is referenced in the script hence why I made the trigger for when a record is created rather than when a form is submitted.

let inputConfig = input.config();

// Create an array to store the individual date values

const dateRecords = [];

//Loop to create date records

for (let i = 0; i < inputConfig.eventDuration; i++) {

// Calculate the date for each day of the event.

const currentDate = new Date (inputConfig.eventStart);
currentDate.setDate(currentDate.getDate() + i);

// Create a new record in the "Event Dates" table.

const newDateRecord = await base.getTable('Event Dates').createRecordAsync({
'Day': currentDate.toISOString().slice(0, 10), // Format the date as YYYY-MM-DD.
});
 dateRecords.push(newDateRecord);
}

output.set("dateRecords", dateRecords);

Other Notes:

The table where the records are being created is called "Event Days". In Event Days, the primary field is called "Day" with a linked field to our other table called "Event"

[Mike_Campanella]

Thank you for your reply! I will try this, it seems like it could work for me!

[dmkirby]

Hey @salvadaor is what you put into the input.config the list of records that have a Duration calculated on the "Event Dates" table? I'm trying to replicate a similar solution for myself but instead of an employee schedule, I'm creating a Program Schedule that is always 56 days. I've already created fields for 'Start Date' and 'End Date' and a 'Duration' formula field.