Consecutive Drop Down Selections Results in Applicable List of Foriegn Key Selection

[dmontano]

Once again, new territory for a novice. Any help in easy-to-understand language is appreciated.

 

My tables are:

Vendor Companies

Equipment

Vendor Equipment Association (Join Table)

 

In the join table I have the two neccessary fields to establish the join:

1. vendor_company_fkey

2. equipment_fkey

 

This is functioning, but certainly unusable, because..

 

Selecting a vendor from the drop down list is simple for any User to understand (I am displaying the vendors name, but the field value will be the "vendor_company_fkey".

 

However, when it comes to selecting the "equipment_fkey" to establish the second part of the join, I am having a problem getting the User to the right equipment to select.

 

The reason this is unusable is because the equipment list is large and has a varied amount of equipment types. I am displaying in this "equipment_fkey" field a concatenation of some of the equipment fields.

 

So, I thought if I build a few drop down fields that a user selects in consecutive fashion to parse down the available options before they get to the drop down to select the "equipment_fkey" - that would be great.

 

I have set this type of value list up, but did so for the "Equipment" table in order to make the creation of an equipment record easier and to maintain consistancy. But, I have not done so in the context of a join table. I am confused and perhaps going about this the wrong way.

 

I can not just copy and paste these "drop down list selector" I created in the "Equipment" table over to the join table and inherit that functionality for the join table. So, I started to build a duplicate set of fields in the join table to replicate that functionality that I have in the "Equipment" table.

 

This seems incorrect to me - or maybe it is just new territory. Either way, I have stopped to ask the question here if this is the "right" way to do this.

 

Thanks again!

[Weetbicks]

Can you supply an example file? This is the kind of thing thats easier to show than explain.

[dmontano]

Hi Weetbix,

 

Thanks for the offer to help. I wish I could easily post a sample file. I would have to strip out a lot of info - and I have "files" that are related and they would have to be stripped out too.

 

I have learned that building the foundation FIRST is a wise idea. So I have somewhat tied myself down in terms of flexibility. I am paying the price now.

 

However, I did get the drop down to behave as I wanted. I am using one TO for each drop down. With each added subsequent TO, I increased the match criteria by 1 field to filter the choice down. Do not know if this is correct or not - but it is working.

 

There are two negative side effects that I have encountered trying this approach. After a User makes consecutive selections for each drop down, I can get to the correct result: an EquipmentID that "shows" a concatenated value to the user. So now it is more User friendly - so far so good.

 

But, if I change one of the values in a drop down, the values in the other fields remain "as-is" and allows the record to be created - even if they are not "filtered" correctly due to the step of changing a previous drop down.

 

I thought field validation, but I recall someone in a different thread mentioning that field validations are something he/she avoids and validates through calcs or scripts. How could I ensure that the data selected in these drop downs is "aligned" with its proper "subchoices" and "superchoices" to avoid the wrong selection for being accepted? If you go through the steps in order - everything is fine or filetered correctly. It is when any choice is altered, the filtering is not rejecting what should be an invalide choice. Hope this makes sense.

 

Second, I can not search for a record unless I start at the top, and then work through the drop down menus. This makes sense becasue of the filetering is sequential. But, a User may not "know" what the suprtype is (how it is categorized), but may want to search by an item lower in the filtering sequence.

 

Maybe I "stacked" the fields and assign different value lists in each one and designate one to be enterable in "browse" mode, and the other field to be enterable via "find" mode. Seems like there may be a simpler or cleaner way - or this is evedence that I am taping this solution together incorrectly.

 

Any thoughts?

[dmontano]

I have attached a jpg of a section of my relationship graph that shows what I have done. I do not know if this helps at all, but here it is.

 

I am seriously doubting that this is the right way, the Red TOs on the far right are the same basic structure as the Red TOs on the far left.

 

I created the TOs on the far right to provide the drop downs in the Equipment table to properly "categorize" the equipment when the record is being added.

 

Then I created the TOs on the far left to provide the same "categorical" drop down structure when I want to associate or assign "equipment" to a "vendor".

 

I am grasping in the dark...