We are working on developing a tour guide database for our student workers that will track information about each tour, tour guide, and the tours they gave. The problem or request that I am trying to work through is that when creating an assignment to a tour, they only want the value list to display those tour guides who are currently active for the term. Their activity can and will change throughout the course of the year and the relationship needs to be dynamic while also not losing the tour history feature for each tour guide. I am continuing to research how to adapt a conditional value list to work in my scenario, but any help would be greatly appreciated. I am attaching what I have so far.

Tour_Guides.zip

You could do it this way:

In your Tour Guides table, create a stored calc field:

active_bc - if (guide_status = "Active"; "1"; "")

Then in Daily Tours, create another stored calc field:

one_const_c = "1"

Make a relationship between new table occurrences of Daily Tours and Tour Guides. I'd call the table occurrences:

DT__

dt_TG~ActiveOnly

Make a relationship between one_const_c and active_bc.

Then make a value list ActiveTourGuides:

use first radio button option "Use values from Field", then click button.

Use Values from: dt_TG~ActiveOnly::full_name

Include only related values starting from DT__

This will be a dynamic list of active guides.

Then of course, use this value list on your layout.

Hope this helps.

Marek

Tour_Guides_Modified.zip

Hi. Tours involve a number of parameters and I doubt there's any easy way to avoid a fairly simple, normalised database.

e.g. try a table called

"availabilities"

Fields:

availabilityID

GuideID

Startdate

Enddate

conditionsID

ReasonID

I hope you get the idea; the tour itself has a start and end date. From there you work towards a drop-down list of "availabilities" for that date.

Hope this helps.

Steve