I was going to post this as part of the "hierarchal conditional value lists " thread, but then thought it was different enough for its own thread.

I have found many posts with similiar questions, but none seem to address this specific problem. here goes:

3 files-

invoices

products

items

I am having a hard time concocting a conditional value list schema for entering heirarchical data from products into the items file through a portal in invoices.

sticking with a car analogy, lets say we have fields:

model

make

year

color

I would like to have a value list displaying all models (from products)

the make would be chosen from a value list of all available for that model.

the year, chosen from list of all from that model and make, etc...

now, I cant figure out how to create a relationship invoices::products that would be based on information in items (these lists would be different for each record in the portal), or how to use the relationship items::products to display a conditional value list in invoices.

There has been much talk about using global fields in invoices for this, but I cant see how this would help me for different records in portals.

Am I completely missing something here?

thanks!

All you need is multiline key:

model & PP &

model & "-" & Make & PP &

model & "-" & Make & "-" & Year

and list value based on relationship with it.

Dj

Perhaps my understanding of multiline keys is not as good as I believed. This solution has worked fine for me before by creating the key between the item and products file, but I still dont see how to create it from invoices so as to allow each portal row in invoices to have a different relationship to products.

should the key :

model & PP &

model & "-" & Make & PP &

model & "-" & Make & "-" & Year

be from invoices? if so, how do I get the fields to represent the different values in the portal to items? if not, how can I access the relationship from invoices (where the portal is displayed, and the value lists need to be?) should the fields in the key (make, model, year) be global? text?

I suspect I may have a fundamental conceptual misunderstanding of what is involved here, and am looking for a kick in the rear to get me on the right track. I have been researching this for days now and no light bulb so far.

thanks for everyones patience...

Sorry but I don't see the problem.

Send me those files off list. I'll give tham a look.

Dj

If you wanted to have an heirarchical structure "make, model, year, color" then you'll need to make four databases, one for each level. All are related through cascading relationships

It is not necessary.

You only have to define value lists based on multiline key relationship.

Dj

Ahhh, but Color isn't directly related to Make. One datavase (or two) cannot hold all the data. each colour should be a separate record in a separate database, as should make and model and year. Building value lists and relationships becomes easy with this data design.

This is an typical case where denormalysing DB with an repeating field is the probably best choice. I'd agree with you if there were multi value attributes other than color and year related to make and model. However don't forget that FM relational model is not only table based, ie sometimes using list is as good as creating separated table.

Dj

Dj's method works nicely, and is not complicated at all.

Thanks for all the input!