Conditional value list, THEN lookup

March 25, 200619 yr

I wanted to add a conditional value list then a lookup to my database, but I have thus far been unable to get it to work.

Here is the concept:

I have a category value list. After selecting the category, a relational value list is used for the item. I then want a lookup used for description and unit price. However, the lookup happens as soon as I seect the category.

Anyone know a way around this?

March 25, 200619 yr

These are fun. In the data source file, build an index field that concatenates the categories you need like this:

Index Field:

Category 1 & "¶" &

Category 1 & Category 2 & "¶" &

Category 1 & Category 2 & " " & Category 3

In the destination file build a concatenated field like this:

Pointing or Aiming Field:

TrimAll(Category1 & " " & Category 2 & " " & Category 3; 2; 0)

Then create a relationship from the Aiming field in the destination file to the index field in the source file.

Pick lists for Category 1 is straightforward: any existing value in the Category 1 field.

Once a value for Category 1 is entered, that value is matched to all occurances of Category 1 in the index field by the relation you defined. You pick Category 2 from that list.

The pick list for category 2 is taken from the related Category 1 records that have Category 2 entries. The list will be all category 2 values with the same Category one value. The pick list will be all Category 3 values that have the same Category 2 and Category 1 values you selected.

You can extend this method as far as your classification system demands. Only one relationship is used! Eventually, you will come to a unique combination of entries from Category 1 through Category N. Three levels is about right for most situations.

A perfect analogy is the Taxonomic arrangement in Biology: kingdom. phylum, class, order, family, genus, and species (with genus and species usually appearing together). That categorically covers all living things in seven category levels. If you get past four, IMHO, you're gonna confuse people.