7 Replies Latest reply on Jun 18, 2016 10:41 AM by jere7my

    Forming multiple relationships at once?


      Hi there. I'm creating a database to reserve rooms (about 80) in various buildings for various people (about 200). I want to be able to note their requested room(s) as well as the room they are eventually assigned. For instance, Alice might request Bampton #1, Longborough #4, and Fieldtown #3 as her preferred rooms, and eventually be assigned Fieldtown #3. Betty might request Pinecones #5, Fieldtown #3, and Longborough #2, and eventually be assigned Fieldtown #3 with Alice.


      This is pretty simple to keep track of with a many-to-many relationship through a join table: PEOPLE to ASSIGNMENTS to ROOMS. (Each person might have multiple requests and each room might have multiple people in it.) The complication arises when people request "anywhere" in a particular building—e.g. "any room in Longborough". I can go through and note each room in the building they want as a separate request, but some buildings have ten or more rooms, which is a lot of repetitive data entry. Is there any way to create multiple matches at once, so I could enter "Longborough" in Clara's requests portal and see Clara's name in the requests portal for every Longborough room, while still being able to enter "Longborough 4" in Alice's requests portal and only see her name in room 4?


      I tried creating a concatenated field that was "Room & ¶ & Building" and using that for the relationship, which worked great for requests; I could select Fieldtown and it would match every room in Fieldtown, or Fieldtown 3 and it would match only room 3. Unfortunately, when it came time to assign rooms, an assignment to any room in Fieldtown would be an assignment to all rooms in Fieldtown, because every room in the building matched that relationship with that person. I also tried making another branch on the relationships tree linking PEOPLE to ASSIGNMENTS 2 to ROOMS 2, with that relationship based on building name instead of room, but that led to some weird behavior like each request showing up multiple times in the portal.


      Any thoughts?

        • 1. Re: Forming multiple relationships at once?

          I would just script this. Allow the user to select "all rooms" or "Bampton Block". Find all appropriate room records, then either import them to the join table or loop over them and create the records individually. The former is faster; the latter is more error-proof.





          1 of 1 people found this helpful
          • 2. Re: Forming multiple relationships at once?

            In the request record you can have a text field holding a list of all the rooms requested.


            If the client wants a specific room in a specific building, this field will hold only one element.


            If the client specifies just the building you will fill the field with all the room ID's of that building.


            If the client just wants *any* room, the field will hold all the Room ID's from all the buildings.


            OF course the client can pick 4 specific rooms scattered over all the buildings; no problem. No need to have multiple requests, just one request with the ID's. ( The client *can* have multiple requests, but the requests will be different - they will have a different time period. )


            You will use this field in a relationship to the rooms.


            And yes, filling it can be easily scripted asMike_Mitchell suggests.

            1 of 1 people found this helpful
            • 3. Re: Forming multiple relationships at once?

              Okay, I think I follow. If Alice selects "Bampton", I script a search for Bampton in the rooms table, loop through the found records, and add the ¶-delimited room names to a list in the join table. That field will then match all Bampton rooms, so Alice's name will appear in the requests portal in all Bampton rooms. (Alternately, I could create new records in the join table for each room.)


              I would have to manually delete or edit the requests if Alice decided she didn't want Bampton, yes? Right now, if I delete Bampton 1 from Alice's portal, that breaks the relationship so Alice doesn't show up on Bampton 1's record. Deleting Bampton won't break that relationship anymore. This might be acceptable, but if there's a way deleting a building name would break or remove all related relationships that would be ideal.


              Thanks much!

              • 4. Re: Forming multiple relationships at once?

                You can script the deletion, too. 

                1 of 1 people found this helpful
                • 5. Re: Forming multiple relationships at once?

                  If Alice selects Bampton you set the key to ExecuteSQL("SELECT RoomID from Rooms WHERE Building = ?";"";"", "Bampton"), no search needed.


                  If Alice wants to change her request, you delete the request and create another one.

                  1 of 1 people found this helpful
                  • 6. Re: Forming multiple relationships at once?

                    As I always preach, think interface first.


                    In my imagination, you've got a client record (showing Alice).


                    On the left, a portal with all the requests from Alice.  the portal only shows things like request date, begin and end date, etc


                    On the right, when you click on a request in the aforementioned request portal, you have another portal listing the detail of Alice's request. It shows data from the rooms table. Some $$Vars get set, like begin and end of reservtion, number of beds needed, parking needed, whatever. They are shown as <<$$ merge fields>>.


                    The single lines of the detail are color-coded. Red, if impossible. Orange, if a collision with an anconfirmed reservation is detected. Green: set it as reservation (definitive) or as unconfirmed yet option (orange).


                    this is (or can be ) the interface.


                    Now, implement it.




                    In Filemaker, you usually can do it, with more or less scripting / plugins / field definitions / autoenters etc. The question is not "can I do it ?" - because you almost always can... but rather "is it speed-effective ? "; "does it help / fit the mental model of / the end user ?"; "does it sell ?"; "if X changes, do I have to trash everything ?" and so on...

                    1 of 1 people found this helpful
                    • 7. Re: Forming multiple relationships at once?

                      Okay, I think I've got it sorted. Thanks to both!