Generate Value List using all available info?

[orbix]

Question for all of you-

I'm building an inventory database to keep track of pieces of A/V equipment for the media department of a university, and I'm having a bit of trouble with some more complicated value list issues. There's one entry per piece of equipment, each having the following relevent fields:

Category (Broad equipment classification - Video, Projector, Audio, etc.)

Type (Specific classification within category - Data Projector, Overhead Projector, Slide Projector, etc.)

Manufacturer

Model #

Serial #

Right now, users can use drop-down lists to select the category. After selecting a category, the type list is filtered to show only the types relevant to the noted category. This continues down the list, keeping the number of options small for each drop-down list.

The problem arises when the user tries to enter a manufacturer directly, rather than selecting the category and type first. Due to the nature of the lists that I'm currently using, unless the user works straight down the list, the pull-downs don't work at all.

Here's what I'm wanting to do: The user can make a selection from any field in any order, still with the help of the drop-downs. For example: I select JVC from the Manufacturer list. At this point, I'd like to be able to click on the 'category' field and get a list of all categories of equipment made by JVC.

Is there a way to do this?

Thanks!

[jodibee]

Yes Orbix there is a way to do this. You will need to build a separate database for all your potential categories of equipment. A field for category, type, manufacturer,

model # etc. You could potentially use the item serial number is its ID or Key as it will be unique to each product. Then in your inventory database create relationships

between your P_key and the P_key in your new database, then relate the category fields between the two in another relationship.

You then use value lists based upon fields in your new database. Category will be a value list based on values from the category field in your new database using

all values. Then for Type you use value list based upon the field Type in your new database using the Category relationship, this will only show the item types listed

with that Category value, only choose related values from the Type field. This approach should get you where you need to be.

orbix said:

Question for all of you-

I'm building an inventory database to keep track of pieces of A/V equipment for the media department of a university, and I'm having a bit of trouble with some more complicated value list issues. There's one entry per piece of equipment, each having the following relevent fields:

Category (Broad equipment classification - Video, Projector, Audio, etc.)

Type (Specific classification within category - Data Projector, Overhead Projector, Slide Projector, etc.)

Manufacturer

Model #

Serial #

Right now, users can use drop-down lists to select the category. After selecting a category, the type list is filtered to show only the types relevant to the noted category. This continues down the list, keeping the number of options small for each drop-down list.

The problem arises when the user tries to enter a manufacturer directly, rather than selecting the category and type first. Due to the nature of the lists that I'm currently using, unless the user works straight down the list, the pull-downs don't work at all.

Here's what I'm wanting to do: The user can make a selection from any field in any order, still with the help of the drop-downs. For example: I select JVC from the Manufacturer list. At this point, I'd like to be able to click on the 'category' field and get a list of all categories of equipment made by JVC.

Is there a way to do this?

Thanks!