I have become a very avid, self-taught Airtable builder over the last 1.5 years. I do not have coding experience so I rely almost exclusively on formulas and automations. I love figuring out ways to solve different challenges in Airtable and am pretty good at it. I know, however, that my “creative” solutions aren’t always the most efficient and may have implications on base performance or future development that I may not understand or appreciate.
I am now at the point, however, where I’m trying to learn more about how to develop solutions with efficiency, performance and scalability/flexibility in mind. With that in mind, I have two questions - one that is quite specific and one that is very broad. Feel free to respond with an answer to either!
I just developed a base for scheduling appointments [same base described below] that uses a Now() field in order to delay one set of automations in a series of automations. I know that Now() can be a drag on performance, and I only need it for newly created records.
If I created an IF statement along the lines of IF {Created Time} is before Today(), then “”. Else Now(),will that reduce the drag on performance?
This fairly complex base runs solely on internal (Airtable) automations. I know there is at least one set of automations that could have been done in a more streamlined fashion using a script, but I’m wondering what the actual practical effects of this are. I imagine, in general, these are two major drawbacks on using “inefficient automations”:
Slow, possibly poor, performance when the automations are being run;
Running out of automations (given the limit per base)
Neither of these seem to be practical issues for this base because, one, I run the primary automations in question weekly at 5am on Saturday, and two, this base is dedicated to just to scheduling and I have plenty of remaining automations.
Are there other general drawbacks I should be aware of or anything else I’m missing? Also, in general, are these the two biggest considerations I should be aware of?
Here’s a description of the base, although that’s not really necessary to this question.
We were looking for a Calendly-like solution for scheduling “intakes” with our clients, among our 4 staff members on that team. We didn’t want to pay for four different staff calendars when we weren’t quite sure it could do all the things we wanted it to do (although, tbh, I don’t know if it could have). We wanted to be able to distribute appointments roughly equally, but also limit who was available based on language spoken and if they wanted an in-person appointment (we have a different person in the office everyday). With Calendly, it seemed like we could do one or the other, so I set out to do all this via Airtable.
I was able to configure a base and a form (or a nice Interface view, but our clients mostly use mobile phones) that allows clients to select language and video/in-person and then select the relevant available appointments. There’s a target number of appointments that each staff member has, and once they reach this target for the week, their appointments are no longer visible until everyone has reached their target. Then the appointments become visible again until the person hits their limit for the week.
The base also syncs with each staff member’s Google Calendar and automations search for conflicts, both when the appointment records are initially added and then when a new event is added to Google.
Creating the appointments for the week, for each staff member, requires multiple automations and also involves the creation of unnecessary records. I know this could have been better done with a script, but I didn’t know how to do that. It seems to be working, though, and again, it happens at 5am on Saturday, so it doesn’t impact performance practically.
I wouldn’t use NOW() to introduce a delay in an automation. The created delay can be wildly inconsistent. And the formula that uses it applies to all of the records in the base, not just the records where you actually need it.
TODAY() has similar performance problems as NOW() in that it recalculates even when there are no changes in input.
There are other ways of introducing delays. The method to use depends on
the duration of the delay that you want
the importance of keeping everything within Airtable
the importance of limiting the number of automation runs
I didn’t realize that about Today()! I’ve used that relatively frequently as a timestamp. (For example, when we move something from one stage to another (using a single select field), I capture the dates for each stage using an automation that pastes the value for Today() when the field is changed to a given status.)
I did see that Now() is inconsistent. I solved that by doing a formula to the effect of If Created Time is more than 10 minutes after Now, then “Yes”. Otherwise, “No”. The "Yes’ is one of the conditions triggering the automation. (I then also added an automation to capture anything on Sunday that doesn’t run properly on Saturday, just in case.)
Regarding your factors that determine the appropriate method:
I just need a slight delay to make sure another (fairly straightforward) automation runs, but also a delay of 10 minutes, or even a day, isn’t really a big deal.
In general, it’s not that important to keep everything within Airtable, but for this one, I’d prefer it.
Right now, limiting automation runs isn’t very important either. (I am more conscious of limiting my operations in Make right now.)
Imagine an automation that only executes when a condition in a view exists. Fire the automation when — only when — a record has entered a view. Once complete, remove the condition.
There are two distinct records that I need to be created before triggering the automation. (It uses one as an input and links to the other.) So I have to figure that out, but I can think through that.
I can probably link the one that is used for input (and was the main trigger initially) to the other new record. Once that is linked, it triggers the cascading automations.
On the TODAY() issue, can I use it on a table with only a few records (permanently) and then just always find that record when I want a field to be updated with “today’s” date (on whatever date the automation runs)?
[As far as I have been able to figure out, there is no other way to dynamically paste today’s date in a record through an automation. Or have I missed something basic?]
Using TODAY() to date stamp things is fairly common. However there are other ways of getting time stamps in automations that don’t involve TODAY() or NOW().
a {Created Time} field for a timestamp default that you might want to change later
a {Last modified time} field that watches only a field of interest that is related to the trigger for the automation
a simple automation script that gets the current time it is run, like the one in my automations helper scripts.
TODAY() probably doesn’t have as much of a performance hit as NOW() since it doesn’t update as frequently, but it still updates when no other inputs have changed.
I like “when record meets conditions” instead of “when record enters a view”.
you don’t have to worry about people changing the view filters and messing up your automation
the logic is spread out across fewer places making the system easier to maintain
when checking dependencies, people often ignore view dependencies when a there are a lot of views, but dependencies in automations are much harder to ignore
I can definitely do the Last Modified Time (related to the field) and use that for the time stamp. (I’ll also try your helper script the next time I am building out a new automation related to this. Little by little, I’m going to learn to script!)
I use conditions for automations all the time (and rarely views, since they generally achieve the same thing - although you can do more complex conditions with views). I’m already using that trigger type for this automation, but then it has to be conditional on the other record too. I think linking to that other record is a good condition to add to the trigger.
The issue here is that both get records get created at the same time, so if I try to link A to B, B may not yet exist and vice versa. But I think I can just do a literal delay for B.
Right now records in A are created starting at a scheduled time. B is created after a specific record is created in A, which is then followed by a series of A records. But instead, for B, I could just schedule it for 15 minutes after A runs, and have it link to all the relevant records in A, which would then start the rest of the automations.