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

    Creating a file for Roommate Assignments

    avgraphics

      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.

      __pkRoomID

      RoomType (Single or Double drop-down list)

      Name1

      Name2

      [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:

      __pkAttendeesID

      _fkRoomID

      Name

      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

      _fkRoomID

      Name1

      Name2

      (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?

       

      Thanks,

      Angie

        • 1. Re: Creating a file for Roommate Assignments
          mikebeargie

          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:

          Case(

            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" ;

             "UNKNOWN"

          )


          • 2. Re: Creating a file for Roommate Assignments
            siplus

            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
              avgraphics

              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
                avgraphics

                Siplus,

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