Conditional Value List - with a twist.

[ccaste]

OK. So I have been working on a db that tracks an event that my church puts on every few months. I have the basic structure set up thanks to "comment". And maybe you can help me with this one too. I have a table set up that contains all the people in my db (I called it tblPeople). I also have a table set up for the events (called tblEvents). Additionally I have a table that contains all the registrations (tblRegistrations). This table allows me to chose a specific event for each person to form a registration. This registration also allows me to pick a role that the person who I am registering. We have a role that is called guide and if a person is a guide, they have other people assigned to them for this event.

So I have the structure set up and I have a field in the Registrations table that allows me to pick the person who is the registrants guide. This is where my problem is. I set up a Value List that looks up all the people who are registered to this event and it pulls those names into this form. So I can pick any person who is registered to this event (because I used the Include only related values from: option) What I want to do is to filter out that list to show me only people who are registered for this event AND have a role of guide.

I found something online about conditional value lists, but I am confused. It says that if you can create a portal that shows the list that you want to refer to, then you should be good. Well, I have a portal showing on my registrations table that shows all the people who are registered for the same event and are listed as guides. But I can't figure out how to make that list show up on my value list. I don't know if this makes a difference but the portal actually has all the people registered for the event and I then applied a filter to it so that it.

Thanks for your help!

[bcooney]

Create a self-join relationship:

Registration to Registation~self

eventID = eventID

Constant1 = flag_IsGuide_c

where flag_IsGuide_c = if (RoleID = 100, 1, "")

In my Roles table, ID 100 is a type Guide.