9 Replies Latest reply on Nov 24, 2008 8:05 AM by fileseek




      Relationships & globals - how to use for quoting interface?



      I have been studying FileMaker and I have successfully created some tables, relationships, scripts, layouts for a small database.

      Now I want to do something more complex (trying from scratch) and I am stuck. Please help!


      I am attaching a screenshot of my current (test) database.

      I am trying to create a quoting interface (layout and calculations).

      As you can see in the diagram I have a Hotels, Rooms, Periods and RoomRates table.

      These work fairly well with the current relationships, I have a Room Rates layout where I can enter all rates for each hotel.

      [a "roomrate" is unique combination of hotel, period (periods differ for each hotel) and room type (room types differ for each hotel, that's why this setup) ]


      I have also created for now a Quotes table, with global fields.


      Here's where I have trouble:


      Global fields:

      - where do I put them (in separate Quotes table or in existing table)?

         At the moment I don't need to save quotes, just to calculate them and then merge them in letters/emails.


      - how do I use them & what further relationships do I need to setup?


      What I want to accomplish:

      1. User selects hotel (from a drop-down ideally, or a portal)

      2. Rooms of that hotel are shown in a portal

      3. User selects specific room

      4. User enters dates for booking (start/end dates)

      5. Total is calculated (based on the room type & period)

          (other amounts will be calculated too, but if I get one right, then I can do the calculations for the rest)


      How can the user select the hotel & then select the room?

      I sense that the solution relies on globals, but I have been trying things I got messed up.

      If I have a layout showing records from Quotes, the rooms don't work, if I show from RoomRates, something else doesn't work...

      I have looking at this for too long and now it all looks fuzzy!


      Please, any help will be appreciated.


      (I use FileMaker Pro 9 Advanced, on WinXP)


      tables & relationship diagram



        • 1. Re: Relationships & globals - how to use for quoting interface?
          Thank you for your post and screenshot.  A picture tells a thousand words!
          I'm not sure why you want global fields.  A global field displays the same information for every record in a table.  We'll touch on this later.  Let's handle the task at hand....
          > 1. User selects hotel (from a drop-down ideally, or a portal).
          On your layout, you can include the HotelName field from your "Hotels" table.  Pull down the View menu and select "Layout Mode".  Add the field to the layout.  Click on the field so it is selected, pull down the Format menu and select "Field/Control -> Setup..."  This then takes you to a new dialog box.  On the left side, under "Control Style", change the value of "Display as:" from Edit box to Pop-up Menu.  Below that, a new entry will appear stating, "Display values from"...  Click on the pop-up and select "Manage Value Lists..."
          In the next dialog box, name the value list anything you want, and select the option "Use values from field", and then specify HotelName from your "Hotels" table.  Be sure to select all values.  When finished, click OK several times to return to the Layout.  Now, whenever you are in Browse and add a record, you can access the pop-up menu and select the Hotel.
          > 2. Rooms of that hotel are shown in a portal.
          If you create a portal, the HotelName is already selected, which means the kp_hotel_id is active, which means you can select the rooms only associated with that Hotel.  If you haven't set up a portal before, let me know, and I'll help you with that.
          > 3. User selects specific room.
          > 4. User enters dates for booking
          > 5. Total is calculated 
          I'm not sure how you intend to select the room. You could have a button that takes you to another layout for entering the dates of booking, and that would then calculate.
          This should give you a good start and point you in the right direction.
          If you need clarification for anything I covered, please let me know.
          FileMaker, Inc. 
          • 2. Re: Relationships & globals - how to use for quoting interface?

            Hi TSGal, many thanks for your reply.


            I have actually created both the value list with Hotel names (as you described) and the portal to show the rooms of each hotel. And another portal to show the hotel period [ see screenshot below]

            I have a layout based on the Hotels table, with all this on, so it is very easy to create a new hotel and create at the same time its rooms and periods from the same layout (entering the rooms/periods in the portals).


            For my quoting though, I have tried what you suggested and here's what happens:

            - I have a new layout based on Hotels.

            - I have a HotelName field on it, showing a drop-down of the hotels.


            While on this layout I already am on a hotel (the name of a hotel is shown - there are as many records as the hotels created).

            So if I select another hotel on the drop-down list, the name of the current hotel I am on changes to the one selected (the hotel is renamed).

            So selecting the hotel this way tampers with the hotel records.

            How can we avoid this?


            I thought of using globals for this reason (I think I have seen some example databases where they are used for similar purposes)...I am not "wedded" to the idea...I just don't know where to go from here, to do the "select hotel", "select room" steps.


            I appreciate your help.


            hotel portals

            • 3. Re: Relationships & globals - how to use for quoting interface?

              Because you are not creating hotels, but hotel bookings, would it not make sense to create a new table to link all the other tables into a single booking?


              Each drop down menu and portal then links this booking to previously entered data from your other tables.  Although this table only has a few fields, you can pull data through the relationships to make it useful.




              Datechecked in

              Datechecked out



              + your calculation fields


              Hopefully this helps,



              • 4. Re: Relationships & globals - how to use for quoting interface?

                Hi Scott, thanks for helping out.


                I have in the meantime realised that creating a separate table is the only solution.

                Because I don't want to mix up "bookings" (I already have a table for this, but will be used for actual bookings) with quoting (which involves only on-the-fly calculations), I am making use of the Quotes table.

                (I had to change some keys that were set as globals, so now it sort of works.)


                My key obstacle now is this:

                - When I select a room and then I select a period, the correct "rate_id" from the RoomsRates table is not defined (see portal showing from RoomRates below).


                Also, when I create the rooms & periods (via portals on the Hotels layout):

                - when I create a new period, it triggers the creation of a new rate_id in RoomRates

                - when I create a new room, it does NOT trigger the creation of a rate_id in RoomRates


                I am missing something in the relationships here... my rate_id is meant to represent a unique combination of room & period, but how can I populate the rate_ids correctly?


                Many thanks.


                selecting rooms & periods 

                • 5. Re: Relationships & globals - how to use for quoting interface?

                  Help, anyone...please?


                  I will be away for 6 days after tomorrow, I was hoping to lift this roadblock before then.

                  I appeciate any ideas/suggestions.

                  • 6. Re: Relationships & globals - how to use for quoting interface?



                    I'm unsure what key fields constitute the correct rate_id from the RoomsRates table.  There needs to be some key to pull the correct "rate_id".  I'm sure some of this is expected on the date value, but using the date by itself will not select a range.  You can either create a record for each date, or you can have a calculation to take the booking date and turn it into a base date.  For example,


                    Case (Year (Booking Date) = 2009; If (Month (Booking Date) > 6; 1/11/2008; 1/7/2009);

                          Year (Booking Date) = 2008; If (Month (Booking Date) > 10; 1/11/2008; 1/1/2008);



                    That is, it puts the Booking Date back to the beginning of the rate change, and then you can use this field as one of the key fields.


                    This way, you don't need to trigger the creation of a new rate_id in RoomRates.  You can add that at any time, but you will have to modify the calculation.


                    Since the Room is in another table, it should not interfere with any other calculation.  However, if you do start booking this room, you will need to apply a rate somewhere.



                    FileMaker, Inc. 

                    • 7. Re: Relationships & globals - how to use for quoting interface?

                      Hi TSGal, I appreciate your response to my cry for help.


                      Perhaps I am too confused right now, but I can't make sense of what you are suggesting.


                      You refer to a "Booking Date", but I have to work with 2 dates: ArriveDate & DepartDate (check-in/check-out dates).

                      Also, I do need to have all the rate_ids for each rate (combination of room & period), because I need to have available a way to enter (& then display) a full set of rates for all rooms, for the whole year, for each hotel.

                      By entering manually rate ids, I can accoplish this (but of course it is labour-intensive and error-prone, as there can be many periods and many room types for each hotel...)


                      Plus, the periods I have entered in the test start at the first of a month - but in practice the periods are more like 2/3/2009...15/3/2009, 16/3/2009...21/5/2009 etc. (btw, I am using the European date format, day goes first).


                      I have tried creating 2 new TOs, Quotes 2 and Periods 2, to setup a greater than/less than relationship between gArrive, gDepart (dates) in Quotes 2 and StartDate, EndDate in Periods 2, but it hasn't worked out.


                      What I don't get is that when I create a new record in Quotes, a period (the first one) is always "pre-selected".

                      When I enter the gArrive and gDepart dates, I want to pull the correct period (id), but it's not happening.

                      Therefore all my calculations draw rates only from the first period.


                      I know this is complex...

                      Again, I appreciate any help anyone can give me.




                      • 8. Re: Relationships & globals - how to use for quoting interface?



                        Thanks for the clarification.


                        It will probably be easier to use lookups.  A lookup copies data from another table into a field in the current table.  After data is copied it becomes part of the current table.  One of the options for lookups is if there is no exact match, then you have the option to copy the next lower value.  For example, using your sample data, if ArriveDate field contains 3/3/2009, it will not find it in the related table.  However, if you use a lookup and find the next lower value of the range, it will copy over 2/3/2009.  You can then grab the rate at this point.


                        The same would go for the DepartDate, because the check-in and check-out dates may have separate rates or cross over different rates.  You will then need to subtract from the beginning range and ending range dates to obtain the correct totals.


                        To define a lookup, pull down the File menu and select "Manage -> Database".  In the Manage Database dialog box, click the Fields tab.  Create a new field to accept the lower range and click Options.  Click the "Auto-Enter" tab and select Looked-up value.  Here, select the table for the lookup and the key fields and then select options for the lookup.  Here is where you want to select the option for no exact match.  Click OK.


                        This should get you going.


                        If you run into any difficulties, or you need further clarification, please let me know.



                        FileMaker, Inc. 

                        • 9. Re: Relationships & globals - how to use for quoting interface?

                          Hi - I solved it!

                          Many thanks to all who tried to help.


                          In the end - because I am better at it - I used scripts (with loops) & variables, and I have now managed to have correct calculations, even across two or more periods. Few!

                          (thank goodness for the Script Debugger and Data Viewer, they saved me from hiccups along the way)


                          By the way TSGal, I know this is not the place, and I intend to enter is a future feature request, but it would be really great if the Script Manager had collapse/expand capabilites (like Dreamweaver for example).

                          It would be really helpful to be able to collapse some ifs or loops, to have a clearer picture of a (long) script.

                          Don't you think?