I want to create certain fields that have drop-down menus (value
lists) that are contextual, based on values already entered in other
fields of the same record.
I’ve had limited success but can’t get it all to work, especially
with more than one level; that is, I ultimately want to have three
tiers, with each value list dependent on what has been selected in
the prior field. I haven’t been able to fully grasp the fundamentals
involved, but I’m aware that what is critical includes (1) how the
ERD relationships are established and (2) how the Value Lists are
defined regarding “include only related values starting from.”
My ultimate goal is to have an EMPLOYEE table in which the values
offered (through drop-down menus) for the fields of COMPANY, DIVISION
of Company, and SUBGROUP of Division are contextual based on what is
entered in the previous fields, when these fields are filled in, in
order.
I have four tables: EMPLOYEE, COMPANY, DIVISION, and SUBGROUP.
For example, for the EMPLOYEE table layout, I create a new record for
John Smith. I go first to the COMPANY field. If there are ten
companies in the COMPANY table, then the drop-down menu for the
COMPANY field should show a choice of ten companies. Assume “XYZ
Company” is selected. (No problem so far.) Then, when the DIVISION
field is entered, I want the drop-down menu to show a list of only
those divisions that are maintained by XYZ Company (having first been
defined by creating records in the DIVISION table, with each record
having a combination of a COMPANY FIELD and a DIVISION FIELD).
Assume “Outboard Division” is one of the divisions at XYZ Company
(and has been defined in the DIVISION table by creating a record with
"XYZ Company" and "Outboard Division") and that name is then selected
as the DIVISION in John Smith's EMPLOYEE table record. Then, when
the SUBGROUP field is entered, I want the drop-down menu choices to
show only those subgroups that are a part of the Outboard Division at
XYZ Company (as have been defined in the SUBGROUP table by creating a
records with "XYZ Company" and "Outboard Division" with as many
subgroups that exist, with a separate record for each combination of
company-division-subgroup).
As mentioned, as a precursor to being able to enter records into the
EMPLOYEE table layout, I first need to define the available SUBGROUP
values for each COMPANY-DIVISION by creating records in the SUBGROUP
table layout. While the subgroup name would have to be manually
entered (this is the process of defining what subgroup names exist
for each company-division), I would like the DIVISION field to be a
drop-down menu, too, again showing only those divisions available for
each company.
Can someone please help me define the proper combination of
relationships among the tables* along the correct value list
structure? I seem to have tried combination after combination but
can never get all contextual tiers to work.
* In experimenting, it seems that they way the tables are linked is
crucial. One approach would be to define a COMPANY FIELD in each
table and link the tables by using the COMPANY FIELD in all tables.
Or, one could link the COMPANY table to the DIVISION table using a
common COMPANY FIELD, but then link the DIVISION table to the
SUBGROUP table using a common DIVISION FIELD, etc. I’m confused as
to the best approach in determining which fields to use for table
linking.
I hope the above is not as confusing as I fear. Perhaps my entire
approach is faulty; if so, can you offer a better alternative.
Thanks !!!
John Eiseman, St. Louis, USA
http://www.safehavengardens.com
------------------------------------------------
