Concatenating names from unlimited number of parent rows

Topic Labels: Base design
609 2
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Hello, I would like to store the location of electronic components in my workshop. As I have mixed furniture, boxes in boxes, drawer cabinets, etc, I thought a simple “Location” table with parent row would suffice:

ID     Parent ID      Name
1                     Red Cabinet
2      1              Drawer A1
3      2              Front Compartment

Now I can record that a component is stored in location ID 3. However showing only “Front Compartment” is meaningless without parent names. How can I concatenate an unlimited number of parent’s names into a string for each row? I would like a field storing the string “Red Cabinet, Drawer A1, Front Compartment” for ID 3, for example.

I have tried using a formula to create a Display Name field concatenating parent Display Name fields however it throws a circular reference error. This sounds like a common problem but I can’t think of the keywords to find the right solution, please can someone point me in the right direction?

2 Replies 2

Welcome to the community, @Alex8!

It seems like you are moving to Airtable from the traditional world of databases! Airtable handles things very differently than traditional database systems.

In Airtable, you don’t need to use ID’s because Airtable manages that under-the-hood for you. You can simply make your primary field in your Locations table the “Location Name” field itself.

Then, in your products table, you would use a “linked record” field to link to its Location Name from the Locations table, and the Location Name will automatically show up in the linked record itself.

However, to bring in any additional fields based on the link, you would then create a lookup field based on your linked record field.

Also, since you are brand new to Airtable, you would probably greatly benefit from taking my free Airtable training course:

Hey @Alex8, it’s not the prettiest solution, and might not be feasible if you’ve got a ton of nests, but this was my workaround for a similar situation heh

Screenshot 2022-09-13 at 10.01.09 PM

Base link