Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Script to read two dates from record and then create a record for each dates between those dates

Topic Labels: Automations
3832 7
cancel
Showing results for 
Search instead for 
Did you mean: 
Jack_Tranckle
4 - Data Explorer
4 - Data Explorer

Hello all, I am very new to scripting and trying to find a way to create separate records for all dates within a range of two dates, a start and an end. I found this code online but it is not working, I am completely new and just wondering what needs to be added before in order for this to work with Airtables

var fromDate = new Date(inputData.from);
var toDate = new Date(inputData.to);
var output =[];
var i = 1;
do {
    var useDate = new String(fromDate.toISOString())
    output.push(useDate);
    console.log(fromDate);
    fromDate.setDate(fromDate.getDate() + 1);
    i++
}
while (fromDate <= toDate);
console.log(output);
return{output};
7 Replies 7

Hi @Jack_Tranckle ,

The code you have is missing sort of Airtable specific input and output. I kept your original code in the middle and added example how you could get that data.

I have renamed the variable output to outputRecords because output is an existing object in Airtable used to print output to screen.

I hope this will give you some idea!

const tableWithRange =base.getTable("Date range");
const tableWithDays = base.getTable("Days");

const ranges = await tableWithRange.selectRecordsAsync({fields:["Date start","Date end"]})

for (let range of ranges.records){
    var fromDate = new Date(range.getCellValue("Date start"));
    var toDate = new Date(range.getCellValue("Date end"));
    var outputRecords =[];
    var i = 1;
    do {
        var useDate = fromDate
        outputRecords.push(
           {
               fields:{
                   "Name":new Date(useDate),
                    "Link to range": [{id: range.id}]
                }
           });
        console.log(fromDate);
        fromDate.setDate(fromDate.getDate() + 1);
        i++
    }
    while (fromDate <= toDate);
    console.log(outputRecords);

    await tableWithDays.createRecordsAsync(outputRecords)
}
Jack_Tranckle
4 - Data Explorer
4 - Data Explorer

Amazing, thank you so much. How would the “days” table work in this case? Would it be a copy of the “Date range” table where the new records would be created?

Thanks a bunch!

I was working on something very basic:
image

Jack_Tranckle
4 - Data Explorer
4 - Data Explorer

Thank you! its working now. One thing that is confusing me, I am trying to add more fields for the scrip to copy over to the new records. Ive tried adding a Quantity field, but keep getting an error back. The goal is to add Employee and the parent ID over.

image

@Jack_Tranckle, you are missing a comma:
image

Jack_Tranckle
4 - Data Explorer
4 - Data Explorer

So I have added the additional fields successfully, with only issue remaining. The scrip is creating a record for ALL of the records in the “Date range” table. I was hoping to only create records of unique dates related to the trigger record ID. How do I limit the script to only look at the record ID input.config.ID

image

Jack_Tranckle
4 - Data Explorer
4 - Data Explorer

Any thoughts on how to only create records from the triggered record ID? I ve been playing around with it every night but still no luck. I am currently thinking it should be in the “while” condition but its not giving the right result