If a booking is always made for one cottage for one week, and you already have a record for each cottage x each week, then those records could be bookings as well - all you need to do is to mark them as such.
Alternatively, you could define a calculation field in PriceList (result is Number) as =
not IsEmpty ( Bookings::BookingID )
(any field in Bookings that cannot be empty can be used for this). Format the field to display as Yes/No.
Thanks for this, comment.
I'm not entirely sure it's exactly what I want, though. I'd realized the cottage x week combination could be used for this purpose, but there are some other things to consider.
As it stands a booking will indeed always be for a cottage for a week, but may also be for a cottage for two or three weeks (if we are lucky, :-) ), so a single BookingID may refer to more than one week.
The other thing is that we'd eventually like to get to the situation (haven't got a clue how yet) where we can feed the relevant information from PriceList directly into a matrix type overview - for our own use, and perhaps for publication on our web site, like we do now, by hand. That in itself is not the scope of this question though.
Anyway, I'll fiddle around with the suggestion for the calculation field a bit, and see where it gets me.
I couldn't help thinking there'd be others doing something very much like we are, and hey, you've pointed me to some of them. Haven't really studied the threads you refer to in any detail yet, but they look very helpful. Lots to get on with. Thanks again.
Just as an aside, already now it's very clear Filemaker is awesomely powerful! It does force you to rethink things through, though ...
Hello comment, (or anybody else wanting to help)
I've been trying your suggestion with "not IsEmpty" but I can't get that to work.
What I've done is defined
PriceList::CottageWeek_Booked = If ( not IsEmpty ( Bookings::Booking_ID ) ; "Yes" ; "" )
The tables PriceList and Bookings are linked by
Cottage_ID AND Week_Start_Date
which, if I understand correctly, should only give the calculation result for bookings where there is a match both for the Cottage and the required week. In actual fact nothing happens.
Is CottageWeek_Booked defined as a Calculation field?
Yes, it's an auto enter calculation, marked "Number".
If the calculation is auto-entered, then the field is NOT a calculation field. Change the field's type to Calculation.
Thanks, comment. This works fine. It's a bit confusing to see an auto entered field being calculated which then is not a calculation field. But never mind, this is the way to learn.