Conditional Value lists? If functions? Plz help!

[gnarlydude]

Is it possible to have a field display 1 of 3(or more) value lists depending on the value list choice in another field?

I have a field named "Company" which is a pop up list of Car Companies for example.

Ford

Chevy

Dodge

I have also defined 3 value lists of the models of each companies cars. For example:

Dodge Value list would include Intrepid, Ram, Caravan, Durango, etc.

I want the corrisponding value list to show up in a field named "Model" depending on which car company I choose from the Company pop up list field. Does this make sence? I have searched this forum and found lots of solutions but didn't find one that seemed to fit my situation. At first I thought maybe I would use a calculation field with an If function. But I couldn't figure out how to make the result choose a value list. Am I totally off on this? What is the best way? Any suggestions?

Thanks

Joe

[LiveOak]

This type of implementation is why FM allows you to base value lists upon a relationship (Use values from a field, only related values). An initial selection of Car Company sets up the related values. The model value list is based upon a relationship based upon the field Car Company and only displays the appropriate models for the company selected.

-bd

[oilman]

I have also been trying to write a conditional value list and have read up on the board about them. I have followed a couple of replies examples but I come to a part that states "choose the related Values" only. Could you tell me where this is done in FM 4.0v1 because I have looked in all the dialog box and have only seen the check box to sort related records. I think I understand the principle.

thanks

[IdealData]

It's not available in FMP 4.x.

Relational value lists only came out with FMP 5.x

Time to upgrade ?

[oilman]

thanks Mark

I lease i know i am not going blind

[gnarlydude]

Brent,

So how would I go about doing this? I am sorry if this seems like a dumb questions but I still don't understand what I should be doing. Could you elaborate a little on your answer?

Thanks

Joe

[Vaughan]

There was a way of getting hierarchial value lists in FMP 4.x but it was a complicated kludge involvind (from memory) scripted replace functions. There used to be a demo of the technique on http://www.databasepros.com/

If you need hierarchical valuelists the upgrade price from FMP 4 to FMP5 is worth it for this feature alone.

[oilman]

Vaughan

thanks for the information -

[gnarlydude]

I want to thank those that tried to help me on this, especially Russ Baker. Unfortunately I think I am just going to give up. Why you can't just have the result of a calculation (for example IF Company = Dodge then Dodge Value list) is beyond me. It's just so counter intuitive (that's the first time I ever said that about Filemaker) to do it the way Filemaker make you do it and it's beyond my newbie capabilities anyway.

Just wanted to say thanks and vent a little. I think I'll go send Filemaker some feedback.

Joe

[danjacoby]

Don't give up!

Create a file, call it "ValueLists.fp5". You'll need two fields, call 'em "Title" and "Item". Then create records for each item. For example, create a record where the Title is "Dodge" and the Item is "Dart" (I'm showing my age!). Then another record with the title "Dodge" and the Item "Neon". Maybe more fields with the title "Oldsmobile" and various items, etc.

Then create a relationship in the main file from the "Company" field to the "Title" field in ValueLists. Finally, create a value list, based on the relationship, using the related items in the field "Item".

By the way, this is one notable exception to the "Never use repeating fields" rule -- you can have the "Item" field be a repeating field, so you only need one record for each "Title" and as many repetitions as you need in "Item".

[Vaughan]

It can be done, but it takes some setting up, that's all.

There is nothing "easy" about hierarchical value lists. It takes a lot of programming to make them work. Having a good data design is the first step.

[uchujin]

Just had a thought

re-reading Live Oaks first response to this thread, I thought of a 1 file solution.

1 relationship selfjoin:

_tUniqueRecordID::_tUniqueRecordID

1 calc field _cList:

Case(_tCategory= "fruit", "apple" & "

[uchujin]

Actually,

change _cList to:

Case(_tCategory= "fruit", _gFruitList,

_tCategory= "vegetable", _gVegetableList)

and enter your lists in the global fields_gFruitList and _gVegetableList. now lists can be imported/edited/scripted

[Allegheny]

Are you serious about this single-file solution to the hierarchical value-list thing? I reada couple of Jonathan Stars articles in FMP Advisor and couldn't adapt his thinking to my own situation. So thanks in advance.

Now for the parts I don't understand:

1) What in the world are "_c" and "_t" and "_g" below? Somthing, Text, and Global?

change _cList to:

Case(_tCategory= "fruit", _gFruitList,

_tCategory= "vegetable", _gVegetableList)

2) To what fields do I apply that Case statement? My first guess is that the value list for "fruit" would get its values from a global field that would contain all different kinds of fruits. Yes? But where do I put the case statement to force "Fruit" to access this "fruits" list?

3) This single-file solution will start to cause problems if I need to change the values in my value lists, right? What can I do to fix that? Or will I be stuck with a relational database situation?

Thanks!

[matmac]

Dear Danjacoby,

Although your entry is old now (June 2000), it was sufficient to assist me in getting the a relationship based value list working. (Judging by the number of posts on the topic generally and the amount of vague responses, I thought that it was going to be a nightmare. Your response was simple and effective).

Thanks for you time and effort.