14 Replies Latest reply on Aug 17, 2015 11:14 AM by electon

    Clarifying a filtered relationship lookup calculation?

    appleseed

      This question potentially relates to "filtered relationships" or "filtered calculations". I not sure how to classify.

       

      Scenario

      three tables: Reservation, Trip, Person

       

      A reservation relates to one Trip and multiple Persons

      A trip relates to multiple Reservations & Persons, and has a set of room assignments

       

      I can assign a room to each person, per trip, and that field could be stored in either the Reservation table or the Trip table (not clear on which is better).  However, that data is a room number.

       

      What I'd like to do is show a "Room Assignment Layout" from a given Trip of the people listed by room #. In other words, how do I translate the room number to the name of the person to whom that belongs?

       

      What I'm looking for is some sort of "filtered relationship lookup", but that phrase casts too wide of a net when searches in various Filemaker forums.

       

      In the Trip DB, I've tried making each room field a "filtered calculation", meaning looking through the "TRIP ID" match, show me the person who has room assignment #1:

      e.g. for field "room1"

      If ( Reservations::room assignment=1 ; Reservations::fk_Person ID ; "not assigned" )

       

      but the relationship never matches.

      Also, this is a one-way street and not the ideal approach.

       

      Also, how do I make this a "two-way street"? In other words, I want to be able to change the room # from a Reservation, OR the person's name (primary key actually) while looking at the Room layout.   It seems I need some sort of "join table", but that concept is cloudy to me at the moment.

       

      Can someone steer me in the right direction?

        • 1. Re: Clarifying a filtered relationship lookup calculation?
          erolst

          appleseed wrote:

          What I'd like to do is show a "Room Assignment Layout" from a given Trip of the people listed by room #. In other words, how do I translate the room number to the name of the person to whom that belongs? […]  It seems I need some sort of "join table", but that concept is cloudy to me at the moment.

           

          Yes, you need a join table, because a room is not an attribute of a person, and very likely not one of Reservation, either**

           

          You'd need a join table like Participant anyway, because where would you store the fact that a person (customer?) has participated (or how much she has paid etc.)? Storing it in the person table would only work if you had not a single repeat customer (which I don't hope for you…)

           

          If the concept of a join table is “cloudy", google a bit; it is not limited to FileMaker, and almost every solution needs one or more of them.

           

          Leaving the Reservation table aside (**which I don' understand the role of in this solution), your data model could look like

           

          Trip --< Participant >-- Person


          where room number is simply an attribute of Participant – unless a participant could occupy several rooms during a trip; hard to tell without knowing more about your trips.

           

          You can maintain and/or display the related set of Participant records for a Trip in a simple portal (where you can also display the related name from Person), or on a report layout – or get fancy, display a floor plan with room distribution by overlaying a graphic with single-row portals …

           

          But first get that data model right!

          • 2. Re: Clarifying a filtered relationship lookup calculation?
            appleseed

            Thank you for responding erolst...To clarify...

            Yes, but a room number could be an attribute of a Trip.

             

            I understand. A person might go on more than on Trip.

             

            Reservation is necessary — it contain ‘status’ information of that person on that trip, in the pre-planning stages. It’s somewhat of a “join table” between Trips and People. There’s also Payment table, but that’s doesn’t come into play here.

             

            A reservation represents one person’s spot on a single trip and all the factors that we keep related that person’s individual trip.


            Still not understanding how a join table is the answer.

             

            The graphic floor plan is the final goal — the floor plan with the right person shown in the right “room”.

            • 3. Re: Clarifying a filtered relationship lookup calculation?
              erolst

              appleseed wrote:

              Reservation is necessary — it contain ‘status’ information of that person on that trip, in the pre-planning stages. It’s somewhat of a “join table” between Trips and People. There’s also Payment table, but that’s doesn’t come into play here.

               

              A reservation represents one person’s spot on a single trip and all the factors that we keep related that person’s individual trip.


              Still not understanding how a join table is the answer.

               

              Right; well, then, Reservation probably is your join table, though I don't understand why you say that a reservation record is related to several people. A reservation is (should be) the combination of one trip with one person, and one of its attributes would be a room number (but see below).

               

              Now if you have

               

              Trip --< Reservation >-- People

               

              a Reservation portal on a Trip layout would show a list of related Reservations, with the room number coming from Reservation, and the name from People.

               

              That will give you a list of occupied rooms – but of course no empty rooms.

               

              Now, if you wanted to see a list detailing the occupation status of all rooms of a given set, you'd first have to create such a list, i.e. a Room table and the setup…

               

              Trip --< Reservation >-- People

                                    ^

                                    |

                                Room


              which would make Reservation a three-way join table, combining foreign keys from Trip, People, and Room (replacing the room number; that is now an attribute of a room record.)


              Now all you need is to filter the relationship between Room and Reservation (or a portal into that relationship) by checking if the related Trip is still active; if so, the room is still booked, and that is the current occupation, and the occupant is the name from the related People record. (This is easy as a portal filter, a bit more complicated as a relationship predicate).


              No lengthy calculations necessary; let the data model do the work for you (after you set it up correctly, that is).


              Or select from a list of “historical” trips and use its ID as a filter (or additional relationship predicate) to “re-create historical occupations” …

              • 4. Re: Clarifying a filtered relationship lookup calculation?
                ronhofius

                But if you have two people on a single reservation, and one person on another, etc. all for the same trip, then you would need to add a table.  I often call this something like the Reservations_People table, because its main function would be just to hold all unique combinations of people and reservations. So you would really have two join tables: the reservations table would have a primary key of its own and one record for each reservation made for that trip. Each of those records contains the Trip ID.  The Reservations_People table would have a record for each unique pairing of reservations and people. (Two couples made one reservation each to Tahiti, and three individuals made a reservation. You have 1 record in the trip table, 5 in reservations, 7 in people and 7 in reservations_people).  In this scenario, which I suspect is your real one, you store the room number in the reservations_people table, and follow all the logic above.  The portal on the trip table would show records from reservations_people, including the editable room numbers, plus any associated info from the people table.

                 

                There are a couple of tricky pieces in all of this is that may need some clarification.  The reservations_people table would probably need to have a field for the primary key of the trip as well. You would auto-fill it by calculation when a value is set in the reservation ID. So it has all three keys, thereby serving as a join table between everything and everything else. Because you have only one record in the reservations_people table for each person, you have a direct pointer to the room number stored in the reservations_people table when on a layout based upon the people table.  When on a layout based on the trip, show a portal to the reservations_people table based on trip ID, and display any associated person info and the editable room numbers.

                 

                Clear as mud? It did make me think. It's sort of like Customers, Invoices, Invoice Items, Inventory items. I usually end up auto-filling the customer ID into the invoice items table so it can function as a join table between customers and inventory items.

                • 5. Re: Clarifying a filtered relationship lookup calculation?
                  erolst

                  ronhofius wrote:

                  But if you have two people on a single reservation, and one person on another, etc. all for the same trip, then you would need to add a table.

                   

                  Good catch with one reservation including several people; that must be what the OP meant.

                   

                  Doesn't have any impact on handling room numbers, though …

                  • 6. Re: Clarifying a filtered relationship lookup calculation?
                    ronhofius

                    It does in that the room number would need to be stored in a table doesn't exist in appleseed's current scenario.  I was thinking you'd need the auto-filling trip key in that table in order to get all the way back and forth, but I guess with the trips -> reservations -> reservations_people -> people relationship, you could place a portal to the reservations_people table right on the trips table without it.  I think the key concept that appleseed needs is the addition of the reservations_people table, and it all comes together.

                    • 7. Re: Clarifying a filtered relationship lookup calculation?
                      erolst

                      ronhofius wrote:

                       

                      It does in that the room number would need to be stored in a table doesn't exist in appleseed's current scenario.

                      Right; but I wasn't referring to the current setup, but rather to the one I outlined, which then would be:

                       

                      Trip --< Reservation --< ReservationPeople >-- People, with

                       

                      ReservationPeople >-- Room


                      added – if you want to fill in an existing floor plan.

                      • 8. Re: Clarifying a filtered relationship lookup calculation?
                        appleseed

                        Yes, this whole concept still a bit muddy. Struggling to figure out how to ask the right question.  So this is a bit of thinking out loud.

                         

                        First to clarify. Reservations are not multi-person. One to one Reservation-Person.   But I don't think that detail matters. I agree that there still needs to be some join table, and that there needs to be some sort of concatenated key to combine the Trip + Person + Room.

                         

                        So, since Reservations is where the details of one person's trip go, I would think the Room Assignment field goes there, which means the concatenated key can be formulated there (Trip_ID + Person_ID + room)

                         

                        However, what I'm not getting is how to make this all work in the real world. Typically, rooms are going to be assigned when looking at the whole list of people going on a trip, not by individual reservation.

                         

                        So from the Trip table looking at the list of people going on the trip, I would assign individuals their room (by choosing a number) which would be stored in a field in Reservations.  However, the way we would like to do it is to match a person to a "room slot". And somehow that generates the room number over in Reservations. Is there an automated way to do that, or is it a matter of having a "submit" button?

                        • 9. Re: Clarifying a filtered relationship lookup calculation?
                          erolst

                          appleseed wrote:

                           

                          However, what I'm not getting is how to make this all work in the real world. Typically, rooms are going to be assigned when looking at the whole list of people going on a trip, not by individual reservation.

                           

                          So from the Trip table looking at the list of people going on the trip, I would assign individuals their room (by choosing a number) which would be stored in a field in Reservations.  However, the way we would like to do it is to match a person to a "room slot". And somehow that generates the room number over in Reservations. Is there an automated way to do that, or is it a matter of having a "submit" button?

                          There is a way to semi-automate this process: write a script.

                           

                          Sadly, that way itself cannot be automated …

                           

                          As to your “filling slots”: I suggest you (re-)read my last posts where I outline the idea of having a Room table that gives you a “slot list”, from which you can select a (free) slot and assign it to a Reservation; now Reservation is a three-way join.

                           

                          Have a look into the attached sample file that (hopefully) makes this ideas clearer.

                           

                          Note how now you could take individual portal rows from the room list and put them onto a graphical floor plan to display the respective related customer name.

                           

                          Also note that both Room and Trip are prepared to accommodate a Hotel id  – so you could load the correct list of rooms / floor plan from the related hotel record.

                          • 10. Re: Clarifying a filtered relationship lookup calculation?
                            BruceRobertson

                            Regarding trips and rooms: do different trips have different room-sets?

                            I don't know where your trips are really going but let's say Trip A goes to facility Heartbreak Hotel, in Chicago.

                            (Making up the location and hotel)

                            Heartbreak hotel has 300 rooms, of which 100 rooms have been designated to be available to be assigned to your travelers for the dates you have set up for Trip A.

                             

                            As a completely different offering, you also run trip B, which goes to Disneyland.

                            Let's say that Mousketeer Hotel has 120 rooms and of those, 80 rooms are available to be assigned to your travelers.

                             

                            So you have room-sets that are specific to a particular trip and therefore you need a join table for this purpose.

                            Further, if this trip involves more than one location you need a set of rooms to be available at the first destination; and a set of rooms to be available at the second destination; etc.

                            • 11. Re: Clarifying a filtered relationship lookup calculation?
                              ronhofius

                              I'm wondering if when you say "in the real world" you're looking for a set of tips about techniques to make things happen on a layout once you've established the right set of relationships. I'll offer a few thoughts. I think the data relationships topic has been pretty thoroughly hashed out, though when you talk about basing a relationship on a concatenated field, that's a bit old school. You can base a relationship on two separate fields, which does away with the need for the calculation field concatenating two fields that used to be needed for relationships based on multiple fields.

                               

                              • Script triggers: create a script that runs when the user leaves the field (or enters or changes or other things) that does stuff like set one or more variables, switch to another layout, perform a search, set fields in the other table based on your variable(s), return to original layout. Right-click or Ctrl-click on the field and chose script triggers to invoke these.
                              • Use auto-enter calculations in the field definition options that change the value of a field based upon a calculation that gets triggered when any field used in the calculation changes
                              • Place related fields directly on the layout, whether singly or in a portal to a relationship. So in other words, place the room slot field and potentially the room number field in the reservations portal on the trips layout.
                              • Use the ExecuteSQL function in auto-enter calculations: If you're familiar with SQL select syntax at all, this is a game changer in that it allows you to go grab a value from any table based on a search even when you have no relationship to that table in your current context.  

                               

                              Given the right relationships as discussed above, just the first three things above will accomplish everything you're asking about I think.

                              • 12. Re: Clarifying a filtered relationship lookup calculation?
                                appleseed

                                Thank you for putting all the effort into building an example so I would play around with those relationships. Will need soem time to digest all the tables and relationships -- this is more complex of a task than I first imagined. But I'm sure there are lessons within your example file that I can learn from and extract.

                                 

                                One detail that comes into play with some of your suggestions is that I'm working with Filemaker 10 in this situation. The person I'm helping is set on sticking with v10 and won't upgrade, so I won't be able to implement some of suggestions.  But thank for mentioning script triggers and ExecuteSQL - future projects.

                                • 13. Re: Clarifying a filtered relationship lookup calculation?
                                  erolst

                                  appleseed wrote:

                                  Thank you for putting all the effort into building an example so I would play around with those relationships.

                                   

                                  Sure, but aren't you, like, responding to the wrong guy/gal?

                                  • 14. Re: Clarifying a filtered relationship lookup calculation?
                                    electon

                                    Waiting for the Jive page to load might help.

                                    Sometimes it will jump on you and make you press the wrong buttons.