Changing a fields value list options depending on entry in another field

[GarthT]

Hi hope someone can point me in the right direction.

I am a surgeon trying to create a database where the options available in field B change depending on the choice made in field A

For example:

Field A has a pop-up list of limbs (right arm, left arm, left leg, right leg)

I want Field B to be a list of available anatomical areas (Shoulder, elbow, forearm, wrist, hand, fingers) if "arm" is chosen in Field A or (Hip, thigh, knee, leg, ankle, foot) if "leg" is chosen in Field A

Thanks for any help

[jamesducker]

First of all you need a table called "anatomical areas" that has all the A and B options in it, for instance:

Area ---------- Subarea

Arm ---------- Elbow

Arm ---------- Forearm

Arm ---------- Shoulder

Arm ---------- etc

Leg ---------- Hip

Leg ---------- Knee

Leg ---------- Thigh

Leg ---------- Ankle

Leg ---------- Foot

Leg ---------- etc

Your value list for input field A of the patient record should be built using values from field anatomical areas::area. Select the 'all values' option.

Then you need a relationship from patient record::input field A, to 'area' field on the 'anatomical areas' table. If 'leg' is chosen on input field A on the patient record, the relationship will return all the values matching the same area, ie the 'subareas' hip, thigh, knee, leg etc.

Your value list for input field B of the patient record should therefore be built using values from field anatomical areas::subarea. Select the 'show related values' option (from 'anatomical areas', starting from 'patient record') so that only the relevant ones are chosen.

Hope that helps.

James

Edited June 15, 2009 by Guest

[GarthT]

Hi again James - thanks for your input

I am probably being very dense - but need a bit of clarification.

I have created at table called, "anatomical areas' but I am not sure what you mean next.

Should I create two fields within the anatomical area table, one called "area" with a value list of rt arm, lt arm, rt leg, lt leg and one called, "subarea" - with a value list elbow, forearm, shoulder etc.

Or do you mean something else

sorry.....

G

[jamesducker]

Yes, that's exactly what I mean -)

Then create a value list called "all areas", built using values from field anatomical areas::area. Select the 'all values' option. Apply this value list as a drop-down menu to field A.

Then create a relationship from patient record::input field A to anatomical areas::area.

Experiment with the relationship by putting a button on your layout, near field A, which does a "Go to related records (in new window)" action through this new relationship. See that when you select Arm in field A the button takes you to elbow, shoulder etc ... when you select Leg in field A the button takes you to knee, ankle etc.

Once you have tested all that you can build the value list for input field B: build it from on anatomical areas::subarea but select 'show only related values' option (from 'anatomical areas', starting from 'patient record') so that only the relevant ones are chosen.

Hope this helps.

James

[GarthT]

Hi James

thank you for your help - I have had to work for it but have finally sorted it out!!

What I did - for others as dim as me:

1. Create a table "anatomical areas"

2. Create two fields in anatomical areas called "Area" and "Subarea"

3. In browse mode went to layout "anatomical areas" and created several new records with all the possible combinations, eg. Area = arm, subarea = shoulder; area = arm, subareas = elbow etc until I had records for all the possible combinations of area and subarea

I did not understand this from your first email and kept trying to create "value-lists"

4. Create two fields in the patient layout called "A" and "B"

5. Went to layout - then Manage value lists and created two new value-lists one called, "all areas" and one called "all subareas"

6. On the relationship diagram dragged a relationship from field "A" to "area" on the "anatomical area" table - This is "many to many" - does this matter?

7. Then just followed your first set of instructions - a button was not necessary

Thanks for your help

[jamesducker]

No problem, glad it worked out. Sorry not all my instructions were 100% clear.

I would have thought you'd have had to do step 6 before step 5, to make the 'subareas' value list work correctly? To work as intended the value list must be 'related values only' which can only be done once the relationship was in place.

The 'go to related records' button is indeed not necessary, I suggested it simply to prove the concept ie what records are returned by the relationship when you select different values for 'area'.

Cheers

James

[GarthT]

Thanks James

you are right 5 & 6 were the other way round.

One more thing.............

Each patient can have more than one limb injury.

I created a portal (4 rows - because max four limbs!) in the patient record and dragged field A onto the portal. Unfortunately every time I change the limb value in field A on the first row - all the other rows change to the same limb value.

Any ideas?

Thanks

Garth