Reciprocal Relationships in a Column

[Ryan_Anderson]

I’m trying to put together a quick gardening database app that allows me to pull together information about a particular plant, including what companion plants it grows well with and what should be avoided.

I have a list of “companions” and “antagonists” for each record, but I can’t see a way to make those relationships reciprocal. For instance, if carrots have a companion of tomatoes, then it follows that tomatoes should automatically inherit the companion of carrots.

It seems like this is going to require a script, but I don’t know enough about scripting in Airtable to start from scratch, and I can’t find examples of people doing exactly this. If anyone knows of any other ways to handle this or a script I could use as a base, I’d be much obliged.

Thanks,

Ryan

[Zollie]

The scripting block would definitely allow you to construct a better solution. But here’s a formula attempt, which has a few notable flaws.

Linked Companions is a ‘lookup’ field. It’s getting the companions from all linked companions. ‘Linked Companions’ and ‘Companions’ are combined in the ‘Combined Companions’ field. Here’s the code for ‘Combined Companions’:

IF(AND({companions},{linked companions}),{companions} & ", " & {linked companions},{companions} & {linked companions})

Flaws

• It’s only going one level deeper. You’re not getting companions from companions’ companions (or anything further).
• You may end up with duplicates
• It’s text (not linked records)

[Ryan_Anderson]

Ahhhhh that’s smart. Thank you.

[Ryan_Anderson]

Hmm… actually, this doesn’t quite seem to work.

What I would want in that example is for tomatoes and celery to list “Carrot” as a linked companion and mango and avocado to list “celery” as a linked companion. I thought I’d be able to sort through it after you pointed out the obvious addition of the linked companions field, but Airtable keeps tripping me up with some restriction or another every time I think I have a solution.

[ScottWorld]

What you’re requesting is that you want Airtable to automatically create the “reverse links” or “back links” for the records that you have already linked to in other records.

Unfortunately, this automatic functionality ONLY exists when linking to a different table — but it doesn’t exist when you “self-link” to the same table.

I recently created a product suggestion about this last month. You can add your comments to this thread:

Automatic "back links" when self-linking a table — especially useful for org charts Product Suggestions

This thread really highlighted the need for Airtable to automatically create “back links” (or “reverse links”) when you are creating a self-linking linked record field (i.e. a field relationship that links a table back to itself). All other linked record fields in Airtable automatically create these “back links” or “reverse links”, but they don’t get created when doing a self-relationship within a single table. This is especially crucial for using the Org chart block, which REQUIRES that you c…

The only way that you can currently do what you’re requesting is to manually do it some way.

I’m sure that other people have probably figured out some JavaScript that can do this (using Airtable’s new Scripting Block), but note that the script would need to be manually run every time you want to get the reverse links — so it’s still not an automated process.