Help

Reciprocal Relationships in a Column

Topic Labels: Formulas
2585 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Ryan_Anderson
5 - Automation Enthusiast
5 - Automation Enthusiast

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

4 Replies 4
Zollie
10 - Mercury
10 - Mercury

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

Screen Shot 2020-05-11 at 6.18.48 PM

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)

Ahhhhh that’s smart. Thank you.

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.

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:

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.