dynamically change "using value list" on a field?

[wootten]

i have created 2 fields to lookup up a product id.

1.) product_id that uses a product value list

2.) lookup_field contains a value list of "suppliers", "customers", "supplier_category" ,"customer_desc".

if lookup_field is "suppliers" i want the value list for product_id to be supplier_products. if lookup_field is "customers" i want the value list for product_id to be customer_products.

if lookup_field is "supplier_category" i want the value list for product_id to be supplier_categories.

if lookup_field is "customer_desc" i want the value list for product_id to be customer_descriptions.

the reason i need this is so we can use different fields and sort conditions depending on lookup_field.

any help would be greatly appreciated.

[CobaltSky]

You can create conditional value lists by using a relationship to a special related 'values' file. This works well in some instances, but does not allow use of the 'Edit' or 'Other' feature in lists and menus for editing on the fly...

If this approach suits your purposes, you may be interested in a basic demo of conditional value list functionality which you can download free from the following URL:

http://www.nightwing.com.au/FileMaker/demos/ConditionalValues.zip

Alternatively, if you want to use editable custom value lists but still have them operate dynamically (ie change according to other values in the database) there is a technique I developed for a client I was working with some time back, which would enable you to achieve this.

To implement it, what you need to do this is to create two (or more) value lists, and then place multiple copies of the same field on your layout, one with each value list assigned to it.

Make sure that the fields are formatted with opaque fill, that "Allow entry into field" is turned off for all of them, that they are adjacent in the tab order (tab order from back to front) and then stack them one on top of the other on your layout.

Then you will need a script which is attached to the top field in the stack, which references the field(s) you wnat to base the value list selection on, and works forward to the appropriate copy of the field and selects it (forcing it to the front and popping its value list.

Once a value is selected, the field will jump back out of view, but the selected value will be displayed.

There is also a demo of this technique available for download from my web site if you are interested. The URL for this one is:

http://www.nightwing.com.au/FileMaker/demos/AutoListSelect.zip

Neither of the demo files I've mentioned are password protected, so you will be able to examine the layouts scripts and field definitions etc.