1 Reply Latest reply on Sep 27, 2009 9:24 AM by FentonJones

    Relationships and Search



      Relationships and Search




      I designed a database in which o hotel sells accommodations to costumers. So I created a main table named 'voucher' with unique IDs.

      I also have another table named 'rooms' which presents information about the rooms sold. They are connected to each other through a relationship (Voucher ID). The table 'rooms' is placed as a portal inside the 'Voucher'. So far it is working fine.


      Now, I have a situation in which a tour operator can buy many rooms at once (to speed up the process). For example: (line No. 1) 2 Superior Rooms, (line 2) 5 Standard Rooms and so on. In this example, the 'rooms' table would have two lines.


      However, I also needed a table that would present each room separately in each line. In this example, I would need 2 records with superior rooms and 5 records with standard rooms. All of this would be done automatically.


      Any suggestions?



        • 1. Re: Relationships and Search

          It's kind of of toss up whether you really need another table, but I think I would also do it that way, just for simplicity's sake. I think this is a situation where FileMaker 10's script triggers will greatly improve the user interface (though it could be done via a dialog also). 


          You'd need the actual multiple Rooms table (one record = one room). You could call the existing table you have (with the quantity) RoomsOrdered or something. That existing table would need to have a unique auto-entered primary ID, such as a serial id (or UUID). This would be used in a relationship to the new Rooms table (which would be children of the RoomsOrdered table).


          The quantity (of rooms) is the field which would need the script trigger attached, onExit. The script would need to take the qty field, compare it to how many rooms you had already, then (if different) go to its rooms (children) and either create or delete rooms records until they matched the quantity. The count is fairly easy,

          Count (relationship::non-empty field)


          One oddity of the "2 tables" structure is that even a quantity of 1 would need a Rooms record created, which is a little redundant. The Rooms record would NOT have the "type" of room, only the ID of the parent RoomsOrdered record. Unless you're willing to do more work. Which you might want to do, in order to run reports from the table; though I can't see why you need to do so, as the RoomsOrdered would do for that.


          [ If the Rooms table is to have anything other than the Qty and the parent ID, such as the room type, you'd have be make sure the parent record HAD the type of room first (the user may have just entered the Qty first). The script trigger would need to be attached to both fields, and a test made that both had values. A RoomType table might be good, to give these room types an ID you could use.]