Automation triggering incorrectly or structured incorrectly?

[Megan_Woolston]

I am trying to set up an automation to change the status of a project from “currently active” to “past” if the date entered in the end date field is before today. Yet when I create a new record with an end date in the future, the automation I’ve currently set up still changes the status to past and I have to manually change it back to currently active. I have turned off the automation for now until I can figure out what’s wrong, but would appreciate help figuring out why it’s not working correctly. Here are my parameters in the attached images. Thanks for any help you can provide!

Update: I’ve determined there’s a difference in how the automation responds based on whether I start manually typing in an end date, or whether I use the field’s calendar to click ahead to the date and select it directly. Starting to type anything in the date field automatically makes it assume the date will be in the past, whereas selecting a future date with a click instead doesn’t trigger the automation. Any idea how to stop this from happening? Most of our users tend to type dates in rather than scroll through calendar.

[ScottWorld]

The problem is that Airtable doesn’t trigger automations properly. It doesn’t wait until you have finished typing in the field to trigger the automation… it triggers as soon as you BEGIN typing in the field. Therefore, as soon as you type even one single number into the date field, Airtable sees that as a date that is earlier than today. (Alternatively, when you choose a date from the pop-up calendar, the entire date gets pasted in at once, which is why doing that avoids this problem.)

You can see that this erratic behavior makes very little sense, and you can see why this frustrates & confuses almost everybody who tries to use automations. But the more you work with Airtable, the more you’ll realize that almost nothing in Airtable has been fully thought-out properly, so using Airtable simply becomes a matter of working around all the “gotchas” like this on a regular basis.

Typically, the simplest way to workaround this landmine in any of your automations is to simply use a checkbox field to trigger automations. In your situation, you could make your trigger based on TWO conditions: (1) the date is earlier than today, AND (2) the checkbox field is checked. And then, don’t check the checkbox until you’ve finished typing in the date.

Another way to workaround this Airtable flaw is to delay your automation so that it doesn’t happen immediately. You can create a formula field that keeps track of the number of minutes that have passed since you last edited the date field (keeping in mind that even typing in one character into the date field counts as the last time you edited the field), and then have the automation trigger when this formula field is greater than the number 1 or 2. The problem with this is yet another Airtable flaw: Airtable only updates its internal clock every 10 minutes or so while the base is open, so this can take up to 10 minutes to trigger if your base is open. If you close your base, it can take an hour or two to trigger. (For quicker & more reliable scheduling, you can use a professional automation tool like Integromat which triggers every 1 minute or instantly, depending on how you set it up,)

I’m sure that there are many other workarounds to this Airtable problem as well. For example, you could use a button to run a script that asks users what the end date is, and then the script can paste in the entire date at once.

Others will hopefully chime in with other workarounds too! :slightly_smiling_face: As I mentioned above, I’m sure that there are many other ways to workaround this flaw, and I’d love to learn about everyone’s ideas on this.