Write results of calculations to "adjacent" records
I hope this title (and the following!) makes sense.
I'm still quite new to FM. I have been getting some good advice here, so am making progress in structuring the booking administration for our holiday cottages.
We have a table "Cottage_PriceList", with (a.o.) the fields "Week_Start_Date", "Week_Price" and "CottageWeek_Booked".
When we take a booking for a cottage (normally for a week's period) in our "Bookings" table, a Booking_ID is auto-generated. This is being used as the testfield in a "not IsEmpty" calculation for the "CottageWeek_Booked" field in the relevant "Cottage" table record, so that that shows "Booked" for the week in question. This will subsequently be used to prevent another booking being made for that cottage for the same week.
So far, so good.
Now, we also take bookings for two or three weeks. The "Bookings" record generated for a booking has a Week_Start_Date field, which is used in the relationship to create the "CottageWeek_Booked" entry in the relevant "Cottage_PriceList" table record. A booking for 2 weeks gets assigned "2 weeks" in its "Period" field, and the correct Departure_Date is calculated. At that point the "CottageWeek_Booked" field in the relevant "Cottage_PriceList" table record for one week later should also read "Booked". Likewise for 3-week-bookings, again one week further in the calendar.
I have been playing around forever, creating fields and auto (calculate) filling them for week records, such as "Booked_Previous_Week" and "Period_BookedFor_Previous_Week", to then allow lookups or calculations within one record, to autofill the "CottageWeek_Booked" field if needed. Whenever I set up a calculation that would do such a thing, the fields start returning "?". I'm guessing that is because in essence there is then a "circular" calculation being created.
The other approach I have tried unsuccessfully is to make parallel links as follows: Bookings::Week_Start_Date = Cottage_PriceList::Start_Date_NextWeek, AND Bookings::Cottage_ID = Cottage_PriceList::Cottage_ID, and similar for one week further again. Records in Cottage_PriceList have been given (secondary - help; autocalculated) extra date fields for that purpose. At that point I cannot get a good calculation to replace the simple
If ( not IsEmpty ( Bookings::Booking_ID ) ; "Yes" ; "" )
to catch the situation where there is just the one week to show "Booked" (which works perfectly OK), and the others where there are a second and/or third week to show "Booked" as well.
I have also been thinking of using something like "GetNextRecord", or "GetCurrent+1stRecord" and "GetCurrent+2ndRecord" in the Cottage table, but cannot find anything about this anywhere. Is something like this possible, and if so, how?