Skip to main content

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…

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:

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:


Thanks, I will look into this now.

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.

Terms & ConditionsAccessibility statement