Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

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

Topic Labels: Automations
3128 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