Conditional relationship based on a field - is it even possible?

[krabbykabby]

Hi all,

I am working on a participant database to store which participants are involved in our different programs. There are several different participant types, and while most of the information we need is the same across types, there are several pieces of information that are only relevant if a participant is a certain type. To avoid having a ton of fields in my People table that will be empty in some portion of the records, I have an AdditionalInfo table linked by ParticipantID to the People table. In the AdditionalInfo table, I also have Property and Value fields. I have set up a layout (based on the People table) where when you choose a particular participant type, the AdditionalInfo table is automatically populated with records where the appropriate participant type-specific categories occupy the Property field (accomplished by button script) and then the user can enter a Value for those properties from a portal.

Here's my question:

One of the Properties we want to store for "Teacher" participant types is their school. Right now, all schools are stored in a School table and I want users to only be able to pick from schools we have a record for. Since the Value field does not ALWAYS refer to a school, how do I hook this up in the relationship graph? Is there a way to create a conditional relationship (in this case, Value relates to SchoolID only when Property = "School")?

Please let me know if additional info would be helpful in solving this.

[comment]

In the AdditionalInfo table, I also have Property and Value fields.

It's not always the best solution - not only because of the value lists issue, but mainly because all values must be text. Validation poses a problem, too.

Anyway, try the attached.

VLbyType.zip

[krabbykabby]

Thanks for your help. In your example file, the drop-down for Value only appears when Type = "School," which is exactly what I want. Somehow though, I cannot get mine to do that - the drop-down appears when I click on the Value field even when Property ≠ "School". I created a cSchool field in the Schools table where the calculation is "School." I then hooked up one TO of Schools from Schools::SchoolID to AdditionalInfo::Value (equivalent to your Table::Value) and the other TO of Schools from Schools::cSchool to AdditionalInfo::Property (equivalent to your Table::Type). Then I set the layout boxes for AdditionalInfo::Value and AdditionalInfo::Property to be drop-down menus populated by value lists equivalent to those in your file. Am I missing something?