Multiple Value Lists on Same Field

[jlev]

Hello Everyone,

I've got a question about value lists and haven't been able to find a definitive answer searching here or elsewhere. I've got a database for tracking the recruiting of research participants. Each participant gets screened to see whether he or she is eligible for a research study, so there are several tables: a Screening table, with fields for the person's answers to the screening questions, and several tables that allow for multiple answers to a screening question. These related tables are Medications, Medical Conditions, and Illegal Drugs Used.

I'm trying to refine a system for tracking the reasons that a participant may be deemed ineligible for the research study. In a table called "Rule Out Reasons," I've got a field called "Eligibility Criteria not Met," which uses a drop-down list populated by the eligibility criteria for the research study (e.g., "Can't have used illegal drugs in the last 30 days," "Can't have X type of medical condition," etc.). I've got a second field for the specific reason that a participant is deemed ineligible. Ideally, this field would conditionally use three different drop-down lists, depending on the contents of the first field. So if the first field were to say "Medical Conditions," the second field would have drop-down values based on the related medical condition records of the participant; and if the first field says "Can't have used illegal drugs...," the second field would have drop-down values based on the related records from the Illegal Drugs Used table.

I have three value lists, one for each related table, working already. I'm wondering, though, whether there's a way to get FileMaker to select one of these value based on the contents of the first field. The best I can figure out right now is to have three different instances of the second field on my layout, each set to use a different value list, and use conditional formatting to hide the two whose value lists aren't appropriate at any given time.

I would be very grateful for any insight into this that you would be willing to give. Thank you!

My best,

J.L.

FileMaker Pro 11, OSX and Windows XP

[comment]

I am somewhat confused by your description. This should be easy if all the values (for use in the second field) were in the same table - and I am not sure why they are not.

a Screening table, with fields for the person's answers to the screening questions, and several tables that allow for multiple answers to a screening question.

I think there should be ONE table for all answers. If some questions allow multiple answers, then there may be multiple records with the same PersonID and QuestionID.

[jlev]

Comment,

I apologize; I should have been more clear in my description. The different tables each have fields for hugely different types of questions. Each participant (i.e., each record in the Screening table) has about 16 related records in the Medical Conditions table (one for each medical condition about which we ask), 8 or so related records in the Substance Use table (one for each of the substances about which we ask), and a related record in the Medications table for each of the medications the participant is taking. These are all related by PersonID. As some questions have an answer that can be fit into one field and others have answers that span multiple fields (e.g., frequency and dosage of each medication), I put them into separate tables. The Rule Out Reasons table is for metrics purposes, and the data going into that table's second field would actually be the ID number of the related record from the appropriate table (Medications, Medical History, or Substance Use). Does that make sense?

Thank you very much,

J.L.

[comment]

Let's start with a basic fact: there is no way you can attach more than one value list to a single field instance, and there is no way you can define a value list to use values from more than one field - let alone a table.

I believe this reduces your options to:

1. Use the three-field hack (BTW, conditional formatting will not really hide a field - you will need a script to select the correct instance of the field when user tries to enter data);

2. Combine the three tables into one, with some fields inapplicable to some types of records;

3. Create a super-type table to contain fields common to all three sub-types.

The Rule Out Reasons table is for metrics purposes, and the data going into that table's second field would actually be the ID number of the related record from the appropriate table (Medications, Medical History, or Substance Use).

I am not sure what you mean by "metrics". Surely, after you have selected SubstanceUseID #26, you'd want to see if it's "Grass" or "Heroin", wouldn't you? Using the first method, you will need three relationships for this - AND you must either make sure the ID's are unique across the three tables, or have a "constant" Type field in each table.

[jlev]

Comment,

Again, thanks so much for your help. I will experiment a bit more with the three options you outlined to see which will work best for the data with which I'm working. I'll post back here if/when I have more questions.

Gratefully,

J.L.