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."
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.
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).
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.
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.
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).
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.
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
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?
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".