Extract Individual Records from a Linked Cell


#1

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”:

        **Furniture Items Table**

Serial Furniture Items Unit Price
1 Sofa A $500
2 Chair B $100

I want to create a “Furniture Sets Table”:

                                                                **Furniture Sets Table**

Serial Furniture Sets Item1 Qty1 Price1 Total1 Item2 Qty2 Price2 Total2 Set Price
1 Sofa A,Chair B Sofa A 3 $500 $1,500 Chair B 2 $100 $200 $1,700

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

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” this cell or extract the individual records from this cell? Otherwise, should I change the approach I’m using?

Any suggestions are appreciated…