Creating a relationship based on a value list of related records

September 19, 200718 yr

Hi there,

I'm trying to figure out the best way to do this:

I have a "Passengers" table on a trip. Each passenger must choose several options from a list of "vendorServices" related to a "Vendor" table (for example, there are 5 vendors on this trip, each offering 3 meal choices. Every passenger must choose one option from each of the 5 vendors). The process of selecting the options is currently in place and when a selection is made, a related record is created for that passenger in a "Passenger Options" table. When completed successfully, each passenger will have 5 line items in the related "Passenger options" table, one from each vendor.

On the layout I have 4 portals. The first is a list of the passengers. The second is a list of the 5 vendors. The user clicks on the passenger name, then clicks on the first of the 5 vendors which then populates the 3rd portal with the available options for that vendor. The user then clicks on that passenger's selection from the 3rd table which then populates the 4th portal, which is the "passenger Options" table. The user continues stepping through each of the 5 vendors and repeats the process until that passenger has made all 5 selections.

What I want to do is to use conditional formatting to change the color of each passenger in the portal list whose 5 meal selections have been completed. I also want to change the color of the "Vendor" in the vendor portal when a passenger has made a selection from that vendor's choices.

In other words, when we're looking at the list of passengers in a portal, those who have made all 5 selections (1 from each of the 5 vendors) are colored red. Passengers who have made fewer than all 5 choices are green. Addidionally, when you click on a passenger in the passenger portal, the list of the 5 vendors in the second portal are likewise colored to indicate which of the 5 vendors is still awaiting a selection from that passenger.

Again, all my selection portals are in place. I only need to know how to create the relationship that confirms which customers have completed the selection proces. I'm sorry for the long and convoluted explaination.

Any ideas?

Thanks in advance for your help!