Hi. I’m not a coder and I’ve only been using Airtable for a few months. I’ve mostly used chatgpt when I’ve come up against something I can’t figure out on my own (which is mostly anything that requires coding) and it’s mostly done OK with a little back and forth. For my current problem it keeps giving me bad info so I’m turning to you all. 

I’m building a database for my company to track various data points about the projects we manage. I have a table called ProjectsList for this. For each project, we get many associated elements and I try to capture various data points on each element. This is done in ElementLog table. When we receive a schedule for a project, I log ScheduleStart and ScheduleEnd. In a normal world, every schedule would be a 5-day workweek and have the same holiday days off, etc. and it would be easy to set up fields that count work days/weeks between schedules, can do countdowns from today to start date, or tally days remaining in the schedule if we’re within the scheduled dates. The problem is that we have schedules from all over the world, so holidays are different, and we work in an industry where work days can be any five or six (or sometimes 7) days in a week (not just M-F), and there can be occasions where the schedule may be mostly a 5-day M-F schedule but with a 6th day Saturday thrown in, or any other possibility. 

I’m searching for a solution to be able to deal with this variability between schedules. Chatgpt suggested I make a new table called ScheduleDates where every record is a date within a schedule linked to that schedule draft in ElementLog, linked to the project in ProjectsList the schedule is for. And in ElementLog where I track the schedule I create a multi-select field to indicate the “standard” off days in the schedule, and then in ScheduleDates I can pull through the off days to indicate days not worked within the schedule timeframe, and have other fields to indicate if the date is a holiday for that particular schedule, and can override off days in another field. And then I can use these records to do the counting and tracking functionality I described above. 

If we have to create every record in a span every time we get a new schedule in, this is not a workable solution. So chatgpt says this can be automated with a script to run for a new record, or when a record is updated using the following script (FYI, my current trigger is “When a record is updated, in ElementLog, looking to ScheduleStart and ScheduleEnd): 

// Get trigger record from the automation input
let config = input.config();
let recordId = config.recordId;
let startDate = new Date(config.startDate);
let endDate = new Date(config.endDate);

let scheduleDatesTable = base.getTable("ScheduleDates");

let currentDate = new Date(startDate);

// Loop through the date range
while (currentDate <= endDate) {
    // Format as YYYY-MM-DD for Airtable date field
    let dateStr = currentDate.toISOString().split('T')[0];

    await scheduleDatesTable.createRecordAsync({
        "Date": dateStr,
        "ElementLog": [{ id: recordId }]
    });

    // Move to next day
    currentDate.setDate(currentDate.getDate() + 1);
}

but when I run this nothing happens in ScheduleDates. I don’t get any new records. Going back and forth with CGPT it seems like the start and end dates are not getting passed correctly, but CGPT keeps telling me to “configure the script input variables” which doesn’t seem to be a thing in my airtable (Teams paid version).

1 - Can anyone help adjust this code or overall automation so it will produce new records in ScheduleDates for each date within the start/end dates of each element? 

Or 2 - can anyone here think of a better way to do what I’m ultimately trying to do without this ScheduleDates table that will just quickly have thousands of records as I get multiple schedule drafts for multiple projects and this system works by needing to specify work days for each individual date in each calendar draft.

Thanks for all your insight! Sorry this is so long...