Defining contents of value list by selection

[jmendel]

I am looking for a way to generate a value list based on the selection of a value from another list.

Here's an example:

Table::VEHICLE INVOICES

Field::Vehicle_Type (Records=Focus; Escape; Ranger, etc)

Field::Vehicle_Model (Records=ZX3 S; FWD XLT; Tremor, etc)

Table::QUOTES

Field::Select_Vehicle_Type

Field::Select_Vehicle_Model

When a user in a QUOTES layout selects a Vehicle Type (eg. Focus) from a value list linked to the VEHICLE INVOICES table, I need the Value List for Select_Vehicle_Model to populate itself from the Vehicle_Model field in the VEHICLE INVOICES table (eg. ZX3 S).

Have I provided enough information for someone to answer this question?

Thanks

[stanley]

Jmendel:

Welcome to the Forums. I'm not sure you've provided enough sense of what's going on, but I'll take a stab at it anyway. There seem to be two choices. One, create a whole new table of Vehicle Types And Models, which would have an entry for each model, with its "parent" vehicle type. The other way, which is more directly in line with what you're asking for, would be to create a relationship between QUOTES and VEHICLE INVOICES based on QUOTES::Select_Vehicle_Type = VEHICLE INVOICES::Vehicle_Type. Then, set your value list for QUOTES::Select_Vehicle_Model to the field VEHICLE INVOICES::Vehicle_Model via the relationship. Now, when you've selected something in QUOTES::Select_Vehicle_Type, the value list in QUOTES::Select_Vehicle_Model should populate correctly.

Hope that helps (and is right - it's Saturday evening and I've already had my first beer)

-Stanley

[preeves7001]

I am wanting to extend this a little further. I have a separate source file that contains a Parent and its Children. In the main file I would like to filter out the Parent and its Children from others families, and then be able to select one of the childern presented. I think I have seen this in Scriptology but cannot remember how it is done.

MAIN::Series (Parent) value list from SOURCE::Series

MAIN::SubSeries (Child)

SOURCE::Series

SOURCE::SubSeries

WEST GIANTS

[merkaba22]

I am very interested in understanding the suggestion made by "stanley" to build a relatioship: " QUOTES::Select_Vehicle_Type = VEHICLE INVOICES::Vehicle_Type".

How do you define a relationship with an equals (=) sign in FM6? It seems like two relationships with a = between them -- I have never seen this .....

I think I could use this as a way to filter a pop-up value list by using a set field for "QUOTES::Select_Vehicle_Type" where the pop-up list would only show: "the value list in QUOTES::Select_Vehicle_Model".

If so, I would really like to get it!

Thanks -- this forum rocks!

[QuinTech]

Hi, merkaba. The convention stanley is using is FILENAME1::fieldName1 = FILENAME2::fieldName2. It's the usual relationship, this convention is just convenient (a convenient convention? almost an anagram) because it allows you to specify the filename right there. It has a little different meaning for FM7, but i see you're using FM6 so i won't get into it.

Once you set this up, don't use the VALUE LIST in QUOTES, use data from a field > related values > from the relationship you created above. (I'll explain my somewhat confusing grammar in more detail if you want, just let me know.)

HTH,

Jerry

[merkaba22]

Hey Jerry!

I recreated a simple database with two files, each with two value lists and two name fields as specified in jmendel's query.

I set up the relationships and could see a pop-up list of all vehicle Models in Quotes -- that's fine.

But I needed to show only those models associated (by record) in the Vehicle_Invoice file, so I changed SelectVehicle_Model to :Vehicle_Model as a pop-up from a value list in Vehicle Invoice via the relationship you suggested.

That was fine to a point -- ie. if there was record for Focus as Vehicle_Type and on the same record ZX3 for Vehicle_Model and another record for Focus as Vehicle_Type and Tremor for Vehicle_Model, then only ZX3 and Tremor would show up as pop-up options when I selected Focus from Vehicle_Type in Quotes.

BUT if I selected one of the two in Quotes, it would change the records back in Vehicle_Invoice and leave me with either ZX3 or Tremor in the pop-up value list depending on the choice I made -- most undesirable:(

How can I stabilize this so that selecting in Vehicle_Model in Quotes no data is changed in Vehicle_Invoices?

[QuinTech]

It sounds like maybe you are using the actual data field as your selection field also. You should have two sets of fields:

ActualVehicleType . . . ActualVehicleModel

SelectTheVehicleType . . . SelectTheVehicleModel

The only entry that should be done in the first set is for vehicle that are actually on your lot. The only entry that should be done in the second set is for the vehicle you want to select, which you can then use in a script to populate the VehicleIndexNumber in your Quotes file.

I think you have the right setup as far as value lists, you're just attaching the value lists to the wrong fields for selection purposes. Does that seem to address the issue, or am i getting it wrong?

J

[merkaba22]

got it -- thanks again. You were great!