Help

Create separate records with single dates from a date range

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

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:

image

image

7 Replies 7
KVachon
6 - Interface Innovator
6 - Interface Innovator

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
4 - Data Explorer
4 - Data Explorer

Hey Kyle

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

image

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

image

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
4 - Data Explorer
4 - Data Explorer

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 ?

image

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

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

Andrey_Kovalev
8 - Airtable Astronomer
8 - Airtable Astronomer

@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.