How to link linked columns

[pueblo]

I’m trying to pull the content of a linked column within a linked column into a table but cannot figure out how to do it. I will give an example to make it clearer what I mean.

I have a table 1 - this contains a list of items
I have a table 2 - items in here are linked to table 1 (they belong to a category listed in table 1)
I have a table 3 - items in here are again linked to an item (category) in table 2, which basically makes them a sub-category of table 1 by the link already established in table 2

Now using the formula field in table 3, I can pull the contents of columns from table 2 but this doesn’t work with linked columns from table 2 (the one that shows to which category from table 1 the item in table 3 belongs to). I desperately need to have this correlation established though.

Could you please point me in the right direction on how to achieve this. I think in Airtable it is called a ‘lookup’ field!?

Thanks in advance!

Edit: I tried to workaround by converting the linked field to a formula field in table 2 and reference to this one but that also doesn’t seem to work?!

[daniel.pan]

We support indirect link use formula like {A.B.C} . Can you share some screenshots, so we can understand the problem you have?

[pueblo]

Sure. I made a simple setup to illustrate the issue I’m having.

I made 3 tables. First is called ‘Videos’

Second one is called ‘Scenes’. Here I associate with table 1 so each scene belongs to a specific video. This also creates the column ‘scenes’ in table 1 as expected and shown in the first screenshot.

The third one is ‘Shots’. Here I associate with table 2 so each shot belongs to a scene. Again corresponding column is created in table 2 as expected.

Now in table 3 I want to display in a separate column to which video each shot belongs to. The information is already there and could be pulled from table 2 column ‘Belongs to Video’ but it doesn’t allow me to do that. In the formula field under ‘linked column’ it offers me to pull in the information from table 2 ‘Scene Info’ but not from any other field (see screenshot) . Manually entering the formula {Belongs to Scene.Belongs to Video} results in an error message (also seen in screenshot).

I hope this makes my problem easier to understand? Maybe I’m doing this the wrong way but the goal is to have shots linked to videos via the association from the ‘Scenes’ table which is what I understand a typical indirect link. Also when I go into the record detail view the information is actually already there anyways, it just doesn’t allow me to pull it into the table.

Thank you for your help!

[rdb]

Hi there!
You can do it! The formula must describe the entire path to the queried field. Your formula does not do that.
Hence: In {A.B.C}, C is the queried column in target table, A and B designated the linked fields.
In your case, I see the forumla as this: {Belongs to Scene.Belongs to Video.Name}. I am not sure if you have to but the column names in quotes, because the contain spaces. Please try both.

[pueblo]

Fantastic. This worked!

Thank you very much!!!

[rdb]

Great! Glad it worked.

[pueblo]

I’m sorry to bother you again, but I do have a follow-up question

In my example, if a shot belongs to multiple scenes (which can again belong to different videos) it seems that only the first (or one) corresponding video is display/pulled into the table. Is this expected behavior. If so, is there a way to work around this?

[rdb]

Hi Pueblo, that’s a good question - or rather bug report. I found the same issue yesterday. I had hoped no one would find it before it could be fixed. But you were faster!

Here’s the thing: At this point - you are right - the forumula discussed above only returns the first value when the target cell contains multiple values. The developers have been informed.

The support of indirectly linked fields was just introduced in the last version and this was apparently overlooked.

[pueblo]

Thank you for your quick answer. I understand. Is there a chance that this will find it’s way into the pending 1.5 Docker Hub release already? Or do I have to be prepared to wait until a later release in 2021?

[rdb]

It’s possible, but not likely. I would not hold my breath.
I think it is much more likely to be fixed in version 1.6 or 1.7.

[rdb]

@pueblo

Or do I have to be prepared to wait until a later release in 2021?

You had to wait till 2021, but you don’t have to wait much longer. In our test system, the bug has been fixed. We’ll test it thoroughly and then roll-it out. Give us a few more days.

[rdb]

We have fixed the problem. Would you test it again and mark as solved if it works as you expect it?

[pueblo]

I would love to, but unfortunately the stripping of custom sharing permissions does not allow us to upgrade to any version after 1.5. The pricing for the amount of external users that we need to collaborate with is just miles beyond what we are able to afford.

[rdb]

Hi Pueblo, please see my response here.