Help

Re: Automation 2 steps on automation 1's toes

1022 1
cancel
Showing results for 
Search instead for 
Did you mean: 

I understand that automations are not exactly speedy. I can live with that. But I’ve noticed a problem that is more serious: If I trigger automation #1, and then (while #1 is being processed) I trigger automation #2, #2 may prevent #1 from running to completion.

Here’s the problem, in full.

.

1, Self-joins not reciprocal

I’ve got a table called Parents. I want to link spouses. So I’ve created a self-join called Spouse. This allows me to click on Spouse field for husband’s record and link him to wife. After which, normally, the table looks like this:

ParentName Spouse
William Porter Joan Porter
Joan Porter ?

That is, while links between tables are automatically reciprocal, self-join links are not. Normally, I’d now have to go to Joan’s record and manually link it to William.

.

2, Automation to the rescue

So I’ve written an automation that creates the reciprocal link, and it works perfectly. Now, when I link William to Joan, a second or two later, the automation back-links Joan to William. Brilliant.

ParentName Spouse
William Porter Joan Porter
Joan Porter William Porter

.

3, And I can correct mistakes!

Of course, sometimes mistakes are made. Horrible mistakes, tragic mistakes, like this one:

ParentName Spouse
William Porter Claire Foy
Joan Porter -
Claire Foy William Porter

So I revised the automation to have conditional triggers. Now it runs a different action if the spouse field is cleared. So now I can disconnect Claire Foy from William (sigh). And THAT works, too. Even more brilliant. And no lawyers required!

.

4, Unless I move too quickly…

But the problem occurs if I clear Claire out of William’s Spouse field, and without hesitating, put Joan into that field instead. In this case, the likely result is that William and Joan will be correctly linked, but, um, Claire will think that she’s still married to William. It’s sad.

image

And now I have — er, I mean William has some explaining to do. If I go to Claire’s record and remove its link to William, the reciprocating “divorce” action (to so speak) mentioned in #3 above runs, and I get this result:

ParentName Spouse
William Porter -
Joan Porter William Porter
Claire Foy -

Oops.

.

5, Is this a bug? Is there a workaround?

I can probably fix the “divorce” action so that, if C’s spouse field is cleared of W, W’s spouse field won’t be cleared unless it’s linked to C.

But the real problem is that the second automation — the one that correctly links W to J — seems to be “stepping on the toes” of the first action (where W’s faulty link to C was removed). As a result, the first action is not allowed to run to completion before the second action starts.

This seems wrong to me. Like a threading error or something. Is there anything I can do about this other than tell my users not to be too quick when correcting this type of error?

5 Replies 5

You have a race condition.

Here are some thoughts:

  • Instead of having a single {Spouse} field that acts as its own backlink, use two same-table link fields and an automation script to manage them. (I sell a script on my gumroad store.)

  • Have a new table for a “family”. Link both spouses to the same family. Then backlinks are used as normal. You can then use rollups and formula fields to show who the spouse is.

  • Build out a system that uses last modified times to check to see if the spouse’s field has been changed after the triggering time, and don’t update in those cases. (I don’t really recommend this method.)

  • Decide that family relationships are really complicated and that the relationships need to be maintained manually in both directions.

Yep, pretty much what I was thinking. To me, that sounds like a bug. Not a problem I ever had to deal with in, say, FileMaker (or even Tadabase or Knack).

Yes have this in back of my mind as a possibility. But it adds a layer of conceptual complexity to the data that I’m worried my users will find problematic. Also can get messy if you start talking about “families” instead of individuals.

Build out a system that uses last modified times to check to see if the spouse’s field has been changed after the triggering time, and don’t update in those cases. (I don’t really recommend this method.)

Hadn’t thought of that. Why don’t you recommend it? This seems promising.

Decide that family relationships are really complicated and that the relationships need to be maintained manually in both directions.

Yep, that’s likely the most conservative approach to take here. Indeed, it’s going to be unavoidable when when it comes to linking siblings, since in many cases there are three or sometimes more siblings and the automation that works for spouses doesn’t work for siblings (although I’m still banging on that one).

Thanks, Kuovonne.

William

It isn’t a bug per se. At least not a bug in Airtable. It is just something that happens and that you have to take into account in the systems you build. The issue of race conditions is not unique to Airtable.

Because it is complex to maintain. It involves setting up logic that isn’t obviously apparent and clear to whoever needs to maintain it months from now, even if that person is yourself.

And depending on the purpose of the base you can get into complex family relationships like step families, half siblings, and other relationships that are not symmetrical.

@WilliamPorterTech

The underlying problem here is that Airtable simply needs to show backlinks for the same table.

People have asked for this feature countless times, but we continue to be ignored. You can throw your hat into the ring by writing support@airtable.com.

I come from the world of FileMaker too, and I am reminded everyday that FileMaker is infinitely more capable than Airtable, and most importantly, it is created by people who actually have a deep understanding of database systems plus a deep care for their users’ needs.

Okay, thanks. Will think about this before writing. I’m not entirely sure that this is a mistake in Airtable, given its basic approach to relationality.

Imagine you have a single table named PEOPLE with everybody in it. Now, I’m linking spouses, and in my scenario (described earlier in this thread), reciprocality makes sense. In the US anyway, or in a monagamous culture, if George is married to Martha, Martha must be and can only be married to George.

But while intertable joins are almost always thought of as reciprocal, self-joins do not have to be, and I think this makes sense in Airtable. Say you want to link children to parents, or employees to supervisors, or soldiers of lower rank to their superior officers. If we link Sgt Bilco to Capt Crunch, and it’s automatically back-linked (that is, if it’s automatically reciprocal), then what’s the relationship called? Hard to use self-joins in that case to create a family tree.

NOT a problem in FileMaker because the relational design is in some ways more flexible (although at the price of making us work a little harder). In FileMaker we have the ability to define and use multiple key fields. So if I want a family tree table in FileMaker, I have of course a default primary key field (I call this “ID”), but I’ll also define fields for SpouseID and ChildID. But one thing we cannot do in FileMaker is a direct self-join. In the relationship graph in FileMaker we don’t work with tables, we work with “table occurrences”. And you can’t link a table occurrence to itself, because that would create a loop. Well, technically you can drag the relationship line from one field in a table occurrence to another in the same table occurrence: but FileMaker will automatically create a new instance of the table occurrence.

And say I’m using anchor-buoy to model my relationships. To relate myself (ID = 11) to my wife Joan (ID=12), I put Joan’s primary key “12” into the SpouseID foreign key field in my record. Now if I look at my record, it’s clear that I’m married to Joan. But if I look at her record, it does not show her as married to me, because my ID (“11”) is not in her SpouseID field.

So they’re not reciprocal in FileMaker either.

.

I come from the world of FileMaker too, and I am reminded everyday that FileMaker is infinitely more capable than Airtable, and most importantly, it is created by people who actually have a deep understanding of database systems plus a deep care for their users’ needs.

I bet if you and I ever have lunch together we’ll quickly find we’re pretty much on the same page about a lot of stuff. But I think “infinitely more capable” is too complimentary to FileMaker, and suggesting that the folks running Airtable do NOT have a “deep understanding of database systems” and DON’T care about their users needs, strikes me as a tad harsh.

I’ve spent 20+ years banging on FileMaker (with detours occasionally into things like 4D, which is another kettle of fish). I know some of the folks on the development team at FileMaker and they’re really smart. But they’ve been held down by management (and I think indirectly by Apple) for years and they’re now behind the curve. So in spite of the fact that I know how to do most of this stuff in my sleep in FileMaker and I have to think hard to figure out how to do it in Airtable — and in spite of the undeniable fact that in some responses respects Airtable’s a less flexible tool than FileMaker — I’m moving my work out of FileMaker and into Airtable.

The proof that Airtable has the advantage in this dog fight now can be seen in the fact that FileMaker—now renamed Claris—is introducing the Claris Cloud and the Claris Studio and Claris Connect, all of which seem to be borrowing ideas from cloud-based tools like Airtable.

Every tool I’ve ever used has caused me to curse at times. But it’s also true that even the ones I didn’t care for much (Panorama, Bento, Helix Express, etc) had unique strengths. I wake up in the morning and say to myself, “Let the compromises begin.”