Extract Records from a Linked Cell

[Hussein_Akar]

Hi,

I have a furniture company, and I have table with a list of all our furniture items and their individual prices. I want to create another table where I can group various items into sets and calculate the price of that set.

For example:

Say I have these items in a “Furniture Items Table”
Sofa A- $500
Chair B- $100

I want to create a “Furniture Sets Table”:
Furniture Set
3 x Sofa A- $1,500
2 x Chair A- $200
And I want to calculate the “Set Price”- $1,700

So, I created a field (link to another record) called “Furniture Sets” in the “Furniture Sets Table” and I linked it to the “Furniture Items” field in the “Furniture Items Table” allowing linking to multiple records.

So, I can now choose the various items that would make up a set from the “Furniture Sets” field. However, I cannot assign different quantities for the various items because all the items are in a single cell. Also, extracting the individual “Unit Prices” from the “Furniture Items Table” into the “Furniture Sets Table” is proving difficult.

I guess the main problem with my approach is that the “Furniture Sets” field is “grouping” the various “Furniture Items” into a single cell. Is there a way to “ungroup” or extract individual records from this linked cell? Otherwise, should I just change the approach I’m using?

Any suggestions are appreciated…

[Julian_Kirkness]

Hi

You really need a third table called, say, Furniture Set Items which would include a link to the set and the item (and have a quantity field).

In database terms, this is called a Join or Junction table and in the basis of Many to Many relationships. You can read more about this here:

Airtable - Support

A beginner's guide to many-to-many relationships

What sets Airtable apart from ordinary spreadsheet applications is its ability to link related concepts together. Knowing how to represent the relationships between different groups of objects can ...

[Hussein_Akar]

Thanks, I will look into this now.

[bdelanghe]

The trick here is a little strange. A set doesn’t actually contain individual items but rather multiples of items. So let’s create a table for ‘line items’:

This table is linked to both the ‘Sets’ table and the ‘Items’ table. And is then grouped by set. I’ve used formula as the ‘primary field’:

{Lookup Name} & ' (x ' & Qty & ')'

The ‘&’ just joins text. I often do this so that I can join the name with the serial. That way if I know either I can find the record.

Here is the item table:

And the set table is much smaller now. It only has summary level data:

And… I just read what @Julian_Kirkness wrote… which is exactly the same.