Is it possible to put multi-table info into 1 line item?

[hillborne]

I'm still very new to FMP and just ran into a little snag when trying to figure out how to to information from multiple tables into a single line item.

Taking a look at the red tables in my ERD:

I want to create an Offering of some furniture products to a group of customers. This seemed to me like it would be a classic line item sort of deal. So I have one portal showing all the customers getting this offer and another portal showing all the products.

But then I realized that each product can have many different finishes!

At first I was thinking that I'd just have repetitive line items for each product and each one of their finishes but this is redundant and not needed because 1 quote will cover all the costs for however many finishes. Ideally, I'd have the product listed once, along with all of its finishes.

So then I was wondering if it's possible do a portal-in-a-portal... So that looking from the Offering table, you have a portal showing each product on a line and a portal on each line showing the finishes that product comes in.

Is this possible and if so, is this even a good idea?

How else should I do it?

I won't have access to FMP until Monday to try it but I would like to start the week with the best plan, forum-gods willing.

Thanks in advance!

Edited July 28, 2006 by Guest

[Ender]

Greetings voyeur!

Pretty advanced stuff for a 7 year old, eh?

While it's not possible to create a portal within a portal, there are some techniques that can accomplish a similar thing. However, I don't think that structure would be appropriate here.

Instead, I'd recommend either of these, depending on your business rules:

1. If it's important to know which Finishes are kept in Inventory, then the Product Surface should really become the thing that's tracked (rather than Product.) This would mean changing the relationships to point to the Product Surface table instead of the Product table. When entering a Product on a Line Item, you'd really be entering the Product Surface ID (or the ProductID with the Surface#).

2. If the Finishes are only important to the Offering, then simply make the Finish an attibute of the Offering Line Items (as a field with a value list of choices).

[hillborne]

Greetings voyeur!

Hail! Space Oddity.

Pretty advanced stuff for a 7 year old, eh?

It's how old I feel when using FMP, heh. If I get this problem sorted out I'll be 8 and that would be great.

Instead, I'd recommend either of these, depending on your business rules:

1. If it's important to know which Finishes are kept in Inventory, then the Product Surface should really become the thing that's tracked (rather than Product.) This would mean changing the relationships to point to the Product Surface table instead of the Product table. When entering a Product on a Line Item, you'd really be entering the Product Surface ID (or the ProductID with the Surface#).

Hmmm...The situation is this: I'd like to give 5 customers a list of products. Beside each product would be it's price, size, etc., as well as all the possible finishes it could come in (1-15 finishes and double that if it's a two-tone stain) This list is going to be mostly look up fields to capture a moment in time.

So you're suggesting that the Offering Line Item table should relate to the Product Surface table...but since there are multiple surfaces, what has changed? I'm quite dense fyi.

2. If the Finishes are only important to the Offering, then simply make the Finish an attibute of the Offering Line Items (as a field with a value list of choices).

The Finishes are important in other tables like the Sample Request table and others not yet built.

Could I use some script that runs when a product ID is entered into Offering Line Item that builds a list of it's surfaces? Could you give me a hint as to what it could look like in general?

Or! Can you capture all the items in a related value list into one field? That might work if it's possible to do.

Thank you so very much for the quick reply.

Edited July 28, 2006 by Guest
Thought of value lists

[Fenton]

I'm not quite sure what a "surface" is. Is it simply a "reference" of the possible surfaces? That you would then have a SurfaceID foreign key in each Product record? Seems likely. Not that a product can have multiple surfaces?

You can produce much the same effect as a "portal-within-a-portal", at least as text. You can use ValueListItems(). Create a Value List, using related values, the relationship from Product to Finishes, show only related values, starting at Product; name it "Product_Finishes".

Then create an unstored calculation field, in Products:

Substitute ( ValueListItems (Get(FileName), "Product_Finishes"); ¶; ", " )

It will be a comma-separated list of that product's finishes, in alphabetical order. Notice that the value list name is in quotes. It is a hard-coded value, so don't later change the value list name or the above will be blank.

[P.S. I see you say "at a moment in time." The above is dynamic. If you want to capture it, you would have to put the value into a text field, in Offering Line Item. I don't see why you would however.]

Edited July 28, 2006 by Guest

[hillborne]

I'm not quite sure what a "surface" is. Is it simply a "reference" of the possible surfaces? That you would then have a SurfaceID foreign key in each Product record? Seems likely. Not that a product can have multiple surfaces?

Yes, you are right, it is just a list of all the possible surface attributes a piece of furniture can have.

You can produce much the same effect as a "portal-within-a-portal", at least as text. You can use ValueListItems(). Create a Value List, using related values, the relationship from Product to Finishes, show only related values, starting at Product; name it "Product_Finishes".

Then create an unstored calculation field, in Products:

Substitute ( ValueListItems (Get(FileName), "Product_Finishes"); ¶; ", " )

It will be a comma-separated list of that product's finishes, in alphabetical order. Notice that the value list name is in quotes. It is a hard-coded value, so don't later change the value list name or the above will be blank.

YESSS!!!

That was it! I'm so happy that I was thinking along the right lines as you were writing it. With your and Ender's help, I am now 8!

Thank you both very much!