Early this year I wrote a script1 to help a user organize some sequential events, where one event would start when the previous event ended. Rather than continue to post updates to that original thread, I felt it would be best to start a new one.
Change durations and re-run the script to re-calculate the relevant start and end times (no need to clear fields first!)
Choose as many fixed start times as you wish, and those times will remain untouched while the others calculate based on the end time of the previous record
Tip: use record coloring to mark records with fixed start times, then hide the checkbox field for a cleaner view
Download the latest version of the script here:
A version that will work in an Airtable automation is coming soon…
1 While I used to develop scripts like this for free from time to time when I was becoming acquainted with Airtable’s development environment, custom development now comes with a price tag. I’ll continue to offer free support for this script as best I can, but if you’re looking for any kind of custom script development, please message me directly to discuss my current rate.
WOWOWOWOWOW!!! This is a GAME CHANGER for planning events. Thank you so so so much @Justin_Barrett!! I just put this into my run of show base, and hours of tedious/error-prone work just got reduced to a press of a button. I am so grateful!
Welcome to the community, @Niruban! Thanks for reaching out! I saw that you also pinged me directly via my website, so I’ll reserve the more direct part of my reply to an email, while also trying to cover your general question:
There are two main ways that the script could auto-execute:
Trigger an automation to run a variation of the script
Develop a custom app that responds to data changes and updates the relevant records.
The latter is definitely possible, but would take some significant development time.
The former is also possible, but aside from the requirement that your base must be in a Pro-plan workspace or higher (required to run automation script actions), the tricky part is the trigger mechanism. You mentioned two options for triggering a refresh, both of which have their own unique problems:
Every time a row (record) is added. This is the “When record created” trigger, and the issue here is that it fires immediately when the record is created. In other words, it doesn’t care whether the record has any data or not. If a record is added, that new record instantly triggers the automation, which runs on whatever data may (or may not) be present in the new record. If you can guarantee that new records are always created by a user submitting a form, this will work fine because the form submission adds all field data immediately during creation, and the automation won’t have any problems. However, if you (or your users) manually add new records in a grid view, this won’t work.
When event times are changed. This would correspond to the “When record is updated” trigger. Similar to the other trigger I mentioned, this is quite literally true to its name. The instant that any change is made to a field, the trigger fires. That often means that the very first character typed when adding or changing field data will trigger the automation. Again, it’s not “When the user is finished editing a field”, it’s “When record is updated,” and that includes the smallest of updates. To work around this, I’ve developed a method that will wait for the user to finish editing (sorta-kinda), but it’s not going to be ideal for every situation. I’m planning on sharing this workaround in a separate thread (probably in the next week or so) if you want to consider that.