hierarchal conditional value lists

[randy remark]

I am trying to create a database to simplify a selection process to a specific product, which is displayed in a portal

I have managed to make it work to two levels based on a relationship, but i need more scope, well at least 4 levels

Product Type (5 Choices)

Then by Manfacturer (Choices dependent on Product Type Selected)

Then by Colours Available (Choices depend on both manufacturer and product type)

This should narrow the final selction from the product database from 500 items to approx 6

Any suggestions on how to take this further.

Cheers

Mark

[Pupiweb]

Hierarchical value lists are indded the solution:

make 3 global text fields

gType, gManifacturer and gColor

Format gType with a value list taking its values from the Type field of Products

Relate gType::Type and use this relationship to create a relational value list of Manifacturers to format the gManifacturer field

Create a calc field gType & " " & gManifacturer and use this calc to relate to a similar calc in Products (Type & " " & Manifacturer); use this relationship for the relational value list to format the gColor field

Create a Create a calc field gType & " " & gManifacturer & " " & gColor and use this calc to relate to a calc in Products:

Type & "PP" &

Manifacturer & "PP" &

Color & "PP" &

Type & " " & Manifacturer & "PP" &

Type & " " & Color & "PP" &

Manifacturer & " " & Color & "PP" &

Type & " " & Manifacturer & " " & Color

(PP stays for a carriage return; the scope of the calc is to have a multiline key, one line per combination of Type, Manifacturer and Color)

Put a portal from this relationship on the layout and you're done

[randy remark]

Im sorry but you've lost me.

Can you please explain in more detail, ie which file the global fields need to be in, and how to create a calclation valuelist.

Thanks

Mark

[Moon]

Lost me, too. Pupiweb, could you please send your solution again, this time using punctuation and perhaps breaking up your explanation into paragraphs?

[andygaunt]

I posted this in another forum. Might as well add it here.

Hi. OK. Conditional value lists.

OK, so you can select a type and using a relationship of type::type you get those manufacturers that deal in that type of vehicle.

So, you then want to see a list of products for that manufacturer (and for the particular type of vehicle). So, we have Truck and Ford for example.

Now, Ford make other vehicles so you dont want those parts. correct?.

OK, so we need to create a calculation field first in the database we are showing the values from.

This would be _cTypeAndManufacturer (text field)

Type & ":" & Manufacturer

OK, so what this does is for each record combine the type of vehicle and the manufacturer

Right, now, we need to repeat this in our main database from the fields we selected the values from (lets call them _gType & _gManufacturer)

So, a calculation field (result of text again) _cThirdValueList

_gType & ":" & _gManufacturer.

OK, now a relationship using these two calculation fields.

Do you have a headache yet???

OK. So, using this relationship in our third value list we can show only related values of product.

And you can continue this process on down.

The reason this was a bit more difficult is because you have the possibility of a manufacturer supplying products for different types of vehicles.

Anyhow, hope this helps.

[Pupiweb]

Sorry for the "congested" post, I'll give it a second try ...

For the sake of explanation the problem can be split into two parts, how to make a filtered portal and how to attach conditional value lists to the "filtering" fields

To make a "filtered" portal so that you can select products by Type and/or Manifacturer and/or Color you can

- create 3 global text fields gType, gManifacturer and gColor

- create a calc field =

gType & " " & gManifacturer & " " & gColor

- make a relationship between this calc and a calc in Products =

Type & "PP" &

Manifacturer & "PP" &

Color & "PP" &

Type & " " & Manifacturer & "PP" &

Type & " " & Color & "PP" &

Manifacturer & " " & Color & "PP" &

Type & " " & Manifacturer & " " & Color

(PP stays for a carriage return; the scope of the calc is to have a multiline key, one line per combination of Type, Manifacturer and Color)

Now when you enter a value in gType you'll see all products of that type, and if you add a Manifacturer in gManifacturer you'll see products of that type made by that Manifacturer and so on ...

For the value lists part:

Format gType with a value list taking its values from the Type field of Products

Make a relationship between gType in Orders and Type in Products

Use this relationship to create a relational value list of Manifacturers to format the gManifacturer field

Create in Orders a calc field =

gType & " " & gManifacturer

Make a relationship between this calc and a similar calc in Products=

Type & " " & Manifacturer

Use this relationship for a relational value list to format the gColor field

Now if you enter a product type in gType the value list attached to gManifacturer will show ONLY the Manifacturers producing that type of product; once you've chosen a Manifacturer the value list attached to gColor will show ONLY the Colors of the products made by the chosen Manifacturer

Hope this clarifies things a bit ... I had made a 3-file sample but I've just learned that cannot attach .zip files ... :-(

Drop me a note and I'll send the files directly ...