Jul 31, 2020 08:10 AM
I have a script I run that manages task due dates.
Using self linked table to setup predecessor tasks with manual start date and manual lead time fields, it calculates the end due date.
The script pulls the predecessors end date and checks if the task start date matches or not. If not it will change the start date.
My problem is right now is that when I press the button to run the script, it runs through the script once, showing me a result of all the tasks/dates it updated. Because at the time of running the script these are the only dates it found mismatching.
But as a result of these updates, I have to run the script again to update any further tasks/dates that were affected. Then again and again until there is no more dates needing to update.
What I want to do is have the script pause at the end, then run the script again on its own. I want it to continue this loop until there is really no more tasks/dates to update.
Is there any way to do this?
Solved! Go to Solution.
Jul 31, 2020 08:36 AM
Hi @matt_stewart - I had exactly the same issue - tasks and dates. It can be done. Here’s the script I used. On its own, it won’t mean much as it works with a specific base set up, but hopefully you can get a sense of what it is doing and copy the structure:
let tasksTbl = base.getTable('Tasks');
output.text('Updating project dates - please wait');
let status = 1;
while (status > 0) {
status = 0
let tasks = await tasksTbl.selectRecordsAsync();
for (let record of tasks.records) {
let actualEndDate = record.getCellValue('Actual end date');
let actualEndDateCopy = record.getCellValue('Actual end date copy');
if (actualEndDate != actualEndDateCopy) {
status ++;
}
}
// console.log(status)
for (let record of tasks.records) {
let actualEndDate = record.getCellValue('Actual end date');
let actualEndDateCopy = record.getCellValue('Actual end date copy');
if(actualEndDate != actualEndDateCopy) {
await tasksTbl.updateRecordAsync(record, {
"Actual end date copy": actualEndDate
})
}
}
}
output.text('Project dates updated');
There’s a couple of trick in this.
while
loop and set some check that determines if it should run againwhile (status > 0) {
status = 0
let tasks = await tasksTbl.selectRecordsAsync();
note that the selectRecordsAsync
is inside the while loop.
The structure I used was:
At this point because my checker is non-zero, it will run again, setting the checker back to zero, getting the records again and so on.
If at any point the line status++
returns zero, then there’s nothing to do and the while loop will finish.
I can see some improvements in the script, so maybe don’t take it verbatim, but hopefully this helps some. You can also use a do-while
loop which has at least one run built into it.
More here on that
Jul 31, 2020 08:20 AM
It sounds to me like you’re looking for automation.
The new automation beta can trigger a JavaScript based on a new record in view. That would definitely help you out. You can contact support@airtable.com about getting involved in that beta program.
Even better, you can automate tasks on a schedule with Integromat, which is my preferred method.
Jul 31, 2020 08:36 AM
Hi @matt_stewart - I had exactly the same issue - tasks and dates. It can be done. Here’s the script I used. On its own, it won’t mean much as it works with a specific base set up, but hopefully you can get a sense of what it is doing and copy the structure:
let tasksTbl = base.getTable('Tasks');
output.text('Updating project dates - please wait');
let status = 1;
while (status > 0) {
status = 0
let tasks = await tasksTbl.selectRecordsAsync();
for (let record of tasks.records) {
let actualEndDate = record.getCellValue('Actual end date');
let actualEndDateCopy = record.getCellValue('Actual end date copy');
if (actualEndDate != actualEndDateCopy) {
status ++;
}
}
// console.log(status)
for (let record of tasks.records) {
let actualEndDate = record.getCellValue('Actual end date');
let actualEndDateCopy = record.getCellValue('Actual end date copy');
if(actualEndDate != actualEndDateCopy) {
await tasksTbl.updateRecordAsync(record, {
"Actual end date copy": actualEndDate
})
}
}
}
output.text('Project dates updated');
There’s a couple of trick in this.
while
loop and set some check that determines if it should run againwhile (status > 0) {
status = 0
let tasks = await tasksTbl.selectRecordsAsync();
note that the selectRecordsAsync
is inside the while loop.
The structure I used was:
At this point because my checker is non-zero, it will run again, setting the checker back to zero, getting the records again and so on.
If at any point the line status++
returns zero, then there’s nothing to do and the while loop will finish.
I can see some improvements in the script, so maybe don’t take it verbatim, but hopefully this helps some. You can also use a do-while
loop which has at least one run built into it.
More here on that
Jul 31, 2020 08:37 AM
Integromat is yet another paid service, so id like to avoid that. Its one of my qualms with Airtable that so many things rely on these paid 3rd party services as opposed to enhancing the internal paid features of my pro or enterprise plan.
Automation beta I wasn’t aware of so ill have to contact and see how that works.
But to clarify, I don’t want to trigger this automatically necessarily. I just want the script to repeat until there is nothing left to update. The first time it runs and stops, its because there is nothing left to update from the moment the script started. But there IS now new tasks/dates to update as a result of the last script run.
So essentially once I press the run button, I just want it to repeat until it runs once with zero tasks updated.
Jul 31, 2020 08:40 AM
this looks like exactly what I need, thanks!
ill review and work it through, and followup as solved or questions afterwards.
thanks so much!
Jul 31, 2020 08:45 AM
Integromat does have a free tier, which may work for some basic needs. I used the free tier for several months before upgrading to the lowest paid tier, which I’ve used for the past nine months or so. Thanks to the scripting action beta that @ScottWorld mentioned above, I’ve been able to bring almost all of my automation back into Airtable, so I might be able to drop back to the free tier.
Jul 31, 2020 09:08 AM
Your solution work like a charm. I use a similar method that uses boolean values
let shouldContinue = true
while (shouldContinue == true) {
...
actualEndDate != actualEndDateCopy ? shouldContinue = true : shouldContinue = false
}
Jul 31, 2020 09:14 AM
As @Justin_Barrett mentioned, Integromat’s free tier is EXTREMELY generous and works perfectly for the majority of clients that I’ve done consulting & programming for.
Jul 31, 2020 09:53 AM
Yes, nice. To be fair, boolean is probably a better/more logical choice at this point as I’m not using the fact that there are X rows to update, just that there are some.