Conditional Value Lists across a number of files

[Ngia]

I am having an awful time getting a set of dropdown boxes to display different information depending on the choices in each previous list. This is the same problem solved by the excellent demo at www.nightwing.com. However, I'm afraid I'm just not getting it to work.

Here is my situation, I

[Fenton]

You can use a global field on the left side of a relationship. So you can set up a relationship to another file which could not occur using a data field key.

The global could be set either directly by a user choice, or by script (works with portals, a button, or a "pause/resume if not empty" loop).

It's more versatile to use globals when making choices from a series of "filtered" related value lists.

Sorry if that's a little vague. It's been a long day :-/

[Ngia]

Hah! I got it to work ---- well, sort of...... At least enough to feel like I finally accomplished something. So thanks!

However, I can only get the selections to work if I hard code the global value; meaning, if I enter in the number of the id into a text box set to the global value field.

How do I get the id of whatever dropdown box I'm using to be = to the global ID? I can't set up a relationship because Inventory is the master. I can do the reverse (material_id = global_desc) but that doesn't get me very far.

Isn't there someway just to write a script that says, "When dropdown box y selects value x then global_desc becomes x?"

always learning,

Ngia

[Fenton]

OK, here's my take on it. First, I assume you're doing all these choices to narrow down the list of objects, so that you can choose the correct object to create a record in inventory, which is kind of "transaction" database, ie., object arrived on date, left on date, etc.? And you can't stick an ID on the object itself, being kind of a "no-no" for priceless objects? (I don't know much about museums, obviously.)

There are 2 ways you could set up the value lists. The first, most correct but less aesthetic way is to use the ID, "also show" the Description. That's how I'll set this up, 'cause it's safest.

[if you are positive that every description is unique in its parent file, then you could just use the Description field to show in the value lists. But then you would need a button and script to use the description to set the ID into a global behind the scenes (which is what you're doing manually now).

The first value list would then be the Description field in Material. You could still use the global ID relationships below for the others, but you could show only the Description field. Or you could just use the Description field.

Since the other files, Material, Class, and Type appear to be pretty much value list sources only, i.e., they are not the objects themselves, this may be safe. Description would have to be UNIQUE in its file; and not edited lightly. As I said, it's aesthetic, but only as safe as your data entry in Descriptions.

Sorry to be so convoluted, but this "bends" relational integrity, something not done without full understanding.]

Globals, in Inventory:

_gMaterialID

_gClassID

_gTypeID

Relationships: File is first word, followed by field:

"Class__gMaterialID," _gMaterialID=::MaterialID

"Type__gClassID," _gClassID=::ClassID

Value Lists:

MaterialIDs = Values from a field, Material file, MaterialID, show also Description, sort on Desc.

ClassIDs = Values from a field, only related values, "Class__gMaterialID" relationship, ClassID, show also Description, can't sort (darn!)

TypeIDs = Values from a field, only related values, "Type__gClassID" relationship, TypeID, show also Description, can't sort

You'll notice, and this where it's non-intuitive, is that the relationship to the file for a related value list is for the ID BEFORE or enclosing, not the primary ID of the file (which there'd only be 1 of).

On Layout:

_gMaterialID, MaterialIDs value list

_gClassID, ClassIDs value list

_gTypeID, TypeIDs value list

Once you'd chosen all 3, they could be put together in a concatenated calculation field.

_cgObjectChoices = _gMaterialID & " " & _gClassID & " " & _gTypeID

This could be related to a similar field in the Objects file:

_cObjectChoices = MaterialID & " " & ClassID & " " & TypeID

If you create a relationship, then create another filtered value list based on that relationship, it should narrow down to very few objects, which could then be shown in a portal.

Hopefully the above is correct.