Conditional value list populated by Record IDs

[bcolburn]

I know this has been solved before. Can someone please direct me to the appropriate thread? I'm having trouble differentiating between what look like lots of similar but not quite identical requests.

Here's the gist of the problem: We collect many different types of documents for each of our patients. Each patient is a record in the main table. I also have a document type table with a linking table to store each record that a patient has a separate record (i.e. a line item). I want to create a value list that will serve to provide users with choices of documents to cite when entering data into the system to make sure we know where it came from. And I want to make sure they can only select from documents that we have actually collected for that patient. So I'm thinking I want to make a conditional value list for each patient that only displays the documents that already exist as a line item (i.e. document types that are linked to that patient's unique ID in the document linker table). Does this make sense? Any ideas?

[David Jondreau]

First you need to make sure you have your relationships set up correctly. If I'm reading the above correctly, you have a Patients table, a Documents table, and a Patient Document Join table. Users are entering data into a layout based on the Patient table occurrence.

If that's the case, you need a related table occurence to Patient Documents Join based on Patient ID and a table occurrence related to the join table by Document ID. You may already have that set up.

Now, create a value list that pulls from both the first field and the second field. The first field will be the Document ID from the related Join table occurence, not the base TO (if they're different, like when using Anchor-Buoy). The second field will be the Document Name from the Documents TO. Check show values from the second field.

Check "Use only related values from..." and choosing the Patient TO your data entry layout is based on.

That should do it.

When you're on a Patient's entry layout and select a field with that value list attached, it looks and pulls only the related Document IDs, and pulls the Document Names from the next TO down the line.

[bcolburn]

What if I wanted a conditional value list that selects from a field not on a layout? For example. If I am selecting a cause of death, there are three tiers.

1.) Cardiac/Non-Cardiac

2.) General categories

3.) Specific pathologies

If I have a field for each of these items, how can I constrain the value list based for the next tier down based on my value in the tier above? I'm still not very clear on how to use the value list window interface to do this, since I can only select values from a field. I also don't really understand what the "Use only related values starting from...." means. Specifically, "starting from" a layout? That is confusing to me. Can you help translate? Thank you so much for your patience!!!