My staff and I are working on a room reservation database and we're trying to figure out the best way to show people the available rooms for a given start/end time. An event name is created in one table, a related table holds all the instances of that event, and a third related table holds the locations for each instance. Right now we've got a portal that shows all the instances (Tues 2-3, Wed 9-11, etc.), and what we'd like to do is next to it have a portal to the locations table that shows which rooms are available based upon which instance/portal row they choose. Our problem is that our search for available rooms is handled by finding all rooms that have conflicts (i.e, are NOT available), and then showing the omitted records. We can do this with New Window, but people are saying it's too cumbersome to have to open up a new window and select the room for each of 10 or more instances. If we can put it in a portal that displays available rooms automatically, we can just give them a checkbox next to each record to select the one they want.

Thanks for any help anyone can provide.

This is a little tricky because it requires an auxiliary relationship. Add a gDate (global, Date) field to the Events table and define a relationship to a new occurrence of the Instances table as:

Events::gDate = Instances 2::Date

Next, define a calculation field cLocationIDs (result is Text) =

List ( Instances 2::LocationID ) & "¶0 "

and a relationship to a new occurrence of Locations:

Events::cLocationIDs [color:red]≠ Locations 2::LocationID

Selecting an instance should set the gDate field to the date of the selected instance. A portal to Locations 2 will show the locations available on that day. You'll probably want to end your script with a Refresh Window [Flush cached join results] step.

This need has come up a couple of times in the past. This thread and the file by CobaltSky (Ray Cologon ) stuck out in my mind.

HTH

Lee