9 Replies Latest reply on Feb 23, 2010 6:25 AM by comment_1

    Autofill "New/Repeat" field for new or returning guests



      Autofill "New/Repeat" field for new or returning guests


      I'm still quite new to FM (Pro, 10.0v3, on a Mac with Leopard installed), and am running into the following problem.


      When taking bookings from a (prospective) guest, I would like to see the field "New_Repeat" on the booking form autofill, based on a test of whether he/she has booked with us before.


      Bookings are made in a "Bookings" table. Guest details are kept in a "Guest" table. The "Guests" layout has a portal, which shows all bookings a guest has made in the past. Bookings get assigned a Booking_ID, which is one of the fields in the portal along with booking date, cottage, period, etc. Booking_ID in the portal triggers a script to take you to the record for that specific booking.  


      My thinking was to use a calculation for the "New_Repeat" field, as follows:


      If (  not IsEmpty ( Guests::Booking_ID ) ; "Repeat" ; "New" ) 


      hoping that this would refer back to (one or more) previously taken booking(s), with their respective Booking_ID(s), in the portal.


      This does not appear to work. Strangely, this calculation gives exactly the wrong result: "New" for repeaters (and non-repeaters, I should add). When I modify the formula, by deleting the "not", non-repeaters and repeaters alike get assigned "Repeat".


      It'll be my misunderstanding things, but I would say that's not right, but I cannot find why, or how to remedy this.


      Any help is appreciated.


      Jan Meulendijk 

        • 1. Re: Autofill "New/Repeat" field for new or returning guests

          Well, Meulendijk, it seems to me it would serve your needs far better to count the number of times a particular client has booked a cottage and the average number of days per stay. To me, that would appear to be more useful information than "New" or "Repeat" but then, I don't operate the same type of business as you do. 


          In your question, the number of Booking_IDs counted, as in the above paragraph, would determine if any are filled for the particular guest. If count is 0 then you would know if it will be that guests first stay with you. If the count number is 5, then the guest was there 5 previous times. Your field could contain either "Staying for the first time" or "Stayed 5 times previously."







          • 2. Re: Autofill "New/Repeat" field for new or returning guests

            Thanks, TECman.


            Although we are interested in the number of times someone has stayed with us before, it is not necessary to know when taking the booking. We can always find out about that going into the portal for the guest, where all bookings are shown. And if we were to require a report on that, that's where we'd find much more detailed information.


            Where the "New_Repeat" issue comes in is in preparation for the next step in the booking process. If "New", we know we need to take, and will be automatically taken to the field to record, the source of the booking: advertising, web site, search, etc. So it's to provide important information about where to direct our marketing efforts.


            Your suggestion of counting Booking_IDs per guest could probably work, although at this moment I would not have a clue how to set this up, being still quite new to it all.


            Meanwhile, I'm still in the dark as to why the "not IsEmpty" trick does not work.


            I would like to get to the bottom of that as well, to solve the problem in hand, and also to understand better what this function can and cannot do.




            Jan Meulendijk 


            • 3. Re: Autofill "New/Repeat" field for new or returning guests
                 If you create bookings from the Bookings table, you need to assign a GuestID to the newly created booking. If the guest already exists in the Guests table, you can select their ID from a value list. If not, you need to create a record for them first, then enter the new record's ID into Bookings::GuestID.


              Either way, once this is done the guest already has at least one booking (subject to records being committed).



              Meulendijk wrote:

              If (  not IsEmpty ( Guests::Booking_ID ) ; "Repeat" ; "New" )

              This makes no sense: the relationship between Guests and Bookings is one-to-many. Therefore, it is the Bookings table that needs to have a GuestID field - not the other way round.


              • 4. Re: Autofill "New/Repeat" field for new or returning guests

                Thank you for this, comment.


                I had realized the relationship is one-to-many, but my thinking (hope) was that if there is one (or more) existing Booking_IDs for a particular guest, the function would find that one (or more), so return "True", and fill the field "Repeat". Clearly, that doesn't work. No doubt it's down to my inexperience, and not knowing the exact reach of many of the functions. Your clarification is therefore much appreciated.


                As you say, you can also look at it from the other side, and see if there are any bookings with the same Guest_ID attached.


                Indeed, every booking does have its own Guest_ID, so in principle this should be possible. I had given this approach some thought myself, but - perhaps wrongly - discarded the idea.


                That has to do with the way we envisage Filemaker to work for us. We want it to be as close as possible to the way we operate now; only quicker, more easily searchable, etc.


                The process goes as follows:

                Somebody gets in touch, and either wants more information before committing (We call that an Enquiry, with its corresponding record in the Enquiries table, and info sent), or wants to book.

                If the latter, we create a Guest record in the Guests table. If the person had previously enquired, a script will fill a new Guest record with the data from Enquiries. If not, the Guest record will just be a new record, duly completed. Thus, in every case there is a full guest record before the actual booking is made. Making the booking then means completing booking specific info in a Bookings record, which - through a relationship based on the Guest_ID - is linked to the relevant Guest record. This approach is very quick and simple; it works very well.


                But this means that once we start inputting the booking specific information, there is always already a Guest_ID for the guest in question related to a Bookings record. As a result - my thinking went - basing the "New_Repeat" autofill on this criterion would not work, because it would always find an existing "previous" record.


                Again, perhaps here I'm not understanding things completely, so it may be that this approach is not suffering the problem I foresaw. If it is, I'm wondering how to work around it. Maybe make Filemaker look for a combination of Guest_ID and Booking_ID in Bookings, where Booking_ID < Booking_ID of the current record, and if it finds one return "True" with "Repeat" autofill; otherwise "New"? 


                I haven't thought this through, and shall have to play around to see if I can get something like this to work....


                Anyway, that's what I shall try. If you have any tips, or see mistakes in the reasoning, I'd very much like to know.




                Jan Meulendijk 


                • 5. Re: Autofill "New/Repeat" field for new or returning guests

                  I think you're on the right track - the exact choice is mostly a matter of convenience. So you could either count the Bookings from the point-of-view of Guests, or look for previous (or other) Bookings with the same GuestID.



                  BTW, I believe you could have one table for all your contacts (whether guests or just enquiring).

                  • 6. Re: Autofill "New/Repeat" field for new or returning guests

                    I've been trying to look for older bookings with the same Guest_ID for a bit, but don't seem to be getting very far.


                    Have created a self-join Bookings::Booking_ID > Bookings 2::Booking_ID, thinking this would only give me match records in Bookings 2 with older (lower) Booking_IDs (the booking presently being made having the highest auto enter serial no). Format of Booking_ID is B#### (Number); I don't know whether that would make any difference.


                    Calculation now reads:


                    If (  not IsEmpty (Bookings 2::Guest_ID)   ; "Repeat" ; "New" ) 


                    This does not work; every record comes up "Repeat".


                    Have been scratching my head over how to get to older Bookings with the same Guest_IDs otherwise, but cannot come up with anything. 

                    • 7. Re: Autofill "New/Repeat" field for new or returning guests

                      The relationship should be:


                      Bookings::Guest_ID = Bookings 2::Guest_ID


                      Bookings::Booking_ID > Bookings 2::Booking_ID



                      It's best to use an auto-entered serial number as the ID, because in text terms "B9" comes after "B15". Alternatively, you could make the relationship:


                      Bookings::Guest_ID = Bookings 2::Guest_ID


                      Bookings::Booking_ID ≠ Bookings 2::Booking_ID


                      or use the booking date field (asuming you have one) to make the chronological > comparison.
                      • 8. Re: Autofill "New/Repeat" field for new or returning guests

                        This works a treat!


                        And I've picked up a lot about relationships in the process. So thank you very much, comment!


                        I've also changed the Booking_ID into a plain number. But I do have a final question, to satisfy my curiosity: if you make sure to classify an ID field as "Number", and format it "Letter####", does a comparison (<, >, etc.) then still suffer from what you pointed out?


                        Thanks again!


                        Jan Meulendijk 

                        • 9. Re: Autofill "New/Repeat" field for new or returning guests
                             No, but it may suffer from other artifacts. For example, in a relationship using Number fields as the matchfields, "B15" will match "C15" or "D0015" or even "x1y5z".