Automation 2 steps on automation 1's toes

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.”