4 Replies Latest reply on Jan 11, 2016 8:34 AM by avgraphics

    Creating a file for Roommate Assignments


      I have two tables - Rooms and Attendees - a one to many relationship.

      This should be really easy, but I'm having a tough time with it -

      I need to assign 2 attendees to each double sleeping room at a conference as well as single room occupancy     .

      Here are the fields included the Rooms Table.


      RoomType (Single or Double drop-down list)



      [I want to use a Drop Down List of the Name field from the Attendees Table, in the Rooms Table - but the name disappears when I go to the next Record.]

      Here are (some of) the fields in the Attendees Table:




      Room Type


      Should I create a third new table called Roomates (and remove the Name1 and Name2 fields from the Rooms Table
      to make all the relationships work?

      Third Table - Roommates Field




      (Still need a dropdown list to enter the names so I don't enter data twice).


      I've spent several hours on this, studying Cris's Lynda.com movies...Feeling really dumb.

      Can anyone help me figure this out please?




        • 1. Re: Creating a file for Roommate Assignments

          A one-to-many would be fine, assuming that each attendee will only ever be in one room.


          You're going about the room selection backwards though. The "one" is your room, and the "many" your attendees, so you want to select the one from the context of your many. This means you should be selecting which room the attendee is occupying from the attendees list, NOT from the rooms list.


          On the rooms list, you can then display a portal showing the one or two joined attendees.


          Also, a simple calc in the rooms table would suffice for a room status, to show you any errors made, EG:


            RoomType = "Single" and count(Attendees::Name) > 1 ; "OVER OCCUPANCY LIMIT" ;

            RoomType = "Double" and count(Attendees::Name) > 2 ; "OVER OCCUPANCY LIMIT" ;

            RoomType = "Single" and count(Attendees::Name) = 1 ; "ROOM FILLED" ;

            RoomType = "Double" and count(Attendees::Name) = 2 ; "ROOM FILLED" ;

            RoomType = "Single" ; "ONE SPACE AVAILABLE" ;

            RoomType = "Double" ; "TWO SPACES AVAILABLE" ;



          • 2. Re: Creating a file for Roommate Assignments

            A third table, with fk_RoomID, fk_AttendeeID and start / end Date could be useful for cases when an attendee stays the whole conference like let's say 5 days - and another one just 2 days, just to make an example. This would allow you to assign another second attendee to the same room for the last 3 days.

            • 3. Re: Creating a file for Roommate Assignments

              OMG - Of course! Thank you so much Mike, and Siplus!

              I thought the one-to-many worked both ways. I started working in Filemaker 5 many years ago, when you had to make new file for what are called tables now, and my old mind hiccups on relationships ever since.


              I had tried both the CASE and the IF operators without success on the attendees side - the Case equation you've shown is invaluable. Thank you Thank you


              Siplus -

              I do have the situation where some attendees are staying for one day and some for 3, so a third table with start and end Date fields will be another step in the right direction.

              Thank you BOTH for your help!

              • 4. Re: Creating a file for Roommate Assignments


                Thank you for your correct reply. I will be adding the start and end date fields