Help with database setup
I'm new to FMP 10 and am setting up a database to manage booth rentals at a trade show. I have the following tables:
Exhibitor Contact Info (basic contact fields and some summary fields to calculate costs when more than one booth is rented, etc.)
Booth Data (has booth number and cost)
Booth Rental Records (each record has the Exhibitor ID and the booth number to tie the two together)
I want to set up a conditional value list so that when I go to set up a booth rental record, I only see booths that are available (ie not already rented). I've set up conditional value lists before using related records, but was unsure how to do this so that when a booth is selected on a given record, it is then "removed" from the list of available booths. I know I could do some sort of calculated field, but I'd rather do this as a conditional list so that I could select the list in a portal on the Exhibitor Contact Info layout and then when it was selected, a new record was created in the Booth Rental Records table and the booth was then no longer available for rental. (Likewise, I'll need to be able to cancel this and make the booth available again if someone cancels.)
Any insights on how to structure this would be appreciated.