Create separate records with single dates from a date range

[Jack_Tranckle]

Hello everyone, thank you in advance for the help. Really appreciated

Script Purpose

When a record is created in first table (with start and end date field), create records with unique dates that go from start to end date in a new table

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


console.log(tableWithDays)
let inputConfig = input.config();

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

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

    await tableWithDays.createRecordsAsync(outputRecords)
}

The trigger for this script, is when Status = “Done”

It is successfully creating records, but the problem is when other records have a “Done” status in the first table. The script outputs all of the records that are “Done”. I am wanting to do this so that the script runs only for the trigger record ( inputConfig.recordID), ignoring all other records in the first table. Any advice on how to do this? Thank you, this will be my first script! :slightly_smiling_face:

[KVachon]

Did you set the trigger to check “When a record matches a condition” or did you set it for “When a record is updated”?

If you set it as the condition, it’ll run for existing records. I’d verify that you set it for “When a record is updated” and make sure you set it to watch your Status field.

Let me know if that helps.
Kyle

[Jack_Tranckle]

Hey Kyle

Thanks for your response, the trigger was set to "“when a record matches conditions”, where conditions is Status = Done.

changing the trigger to when a record is updated or when a record is created outputs the same results annoyingly, but great feedback. I guess it must be the script itself, asking to search the entire table? Im just not sure how to change this

I forgot to show the input variables, let me know if I can share any more details

[Andrey_Kovalev]

Hi, I changed slightly your script and removed unused variable “emlpoyee”. May be this is what you need.

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


//console.log(tableWithDays)
let inputConfig = input.config()

let query = await tableWithRange.selectRecordAsync(inputConfig.recordID)
if (query !== null) {
    var fromDate = new Date(query.getCellValue("Date start"))
    var toDate = new Date(query.getCellValueAsString("Date end"))
    var employee = query.getCellValue("Employee")
    var outputRecords = []
    while (fromDate <= toDate) {
        var useDate = fromDate
        outputRecords.push(
           {
               fields:{
                    "Date":new Date(useDate),
                    "Date range": [{id: query.id}],
                    "Record ID" : inputConfig.recordID,
                    "Employee" : employee,
                }
            })
        //console.log(fromDate);
        fromDate.setDate(fromDate.getDate() + 1)
    }
    await tableWithDays.createRecordsAsync(outputRecords)
}

[Jack_Tranckle]

Thats great Andrey, a massive favor! Ive tried your code with every possible trigger but keep getting the same error. Are you not receiving this ?

[Andrey_Kovalev]

@Jack_Tranckle Hmm, I do not have this error, the script runs as expected. Can you check what exact field causes the error?

[Andrey_Kovalev]

@Jack_Tranckle You can run Field List extension to get that info.

[Andrey_Kovalev]

@Jack_Tranckle I think the error comes from the field which in my script is called “Date range”. In your base it would be “Link to range”. Either rename the field in the script or rename the field in your table.

[John_McGarvey]

I came across this post for the same function I am trying to implement.  I am trying to setup via an Automation when a record enters a view.  The script is working, however for each trigger, it is pulling all records in the view, not just the record that just entered to trigger the Automation.