Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 09, 2022 04:09 PM
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:
Nov 09, 2022 04:41 PM
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
Nov 10, 2022 12:19 AM
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
Nov 10, 2022 11:55 PM
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)
}
Nov 11, 2022 03:41 AM
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 ?
Nov 11, 2022 03:58 AM
@Jack_Tranckle Hmm, I do not have this error, the script runs as expected. Can you check what exact field causes the error?
Nov 11, 2022 04:01 AM
@Jack_Tranckle You can run Field List extension to get that info.
Nov 11, 2022 04:11 AM
@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.
Apr 27, 2024 02:36 PM
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.