You need two table. The first one with the dates does obviously exist already.
But you need a second table for the calendar. Each record one day. A relationship connects your table::date with calendar::date. You always look at lists or portals from calendar table. The data of your date table is shown as related field. Mutliple dates on one day can be handled via the list()-function.
As time proceeds you can create new records in the calendar table. A more sophisticated approach would be to create only as many records as you want to see simultaniously. If you want to see one month you create 31 records, a global _calendar_start_date, an ID field and you get the date as _calendar_start_date + ID.
This tricky setup allows to change the viewed month by simply changing _g_calendar_start.
Alternately, your second table can have just one record in it, but with either 7 (if you only want a week view) or 42 (if you want a month view) calculated globals that calculate the Monday Of (a global date field), the Tuesday Of (same global date field), Wednesday of (same global date field), etc.
Then create 7 or 42 relationships from this table to your data table.
Your layout will be done on the new table with portals to the 7 or 42 related dates.
You'll have to do some fun clock arithmetic to get the calculated fields, but that's the fun part.
Thanks jensteich for the help. If I understand correctly the new Calendar table is used to drive the layout (report) as it has a record for each day of the month. First I would find the first day of the month, followed by the previous Monday (in my example I display the last two days of September when displaying October) then use the found record list from Calendar to select the data from my data table TimeBookings.
At first this seems complex and it is quite different to how I have implemented it in SQLite and RealBasic as the FileMaker method seems to require that the stored data do the work rather than the code behind the data.
jsalzer, thank you for posting your ideas. Again if I have understood your solution has the month of interest created whenever it is required, then this month of records is used to select records from the table TimeBookings.
I think that you have it.
If you're setting up your calendar with Monday starting the week (as it sounds like you're doing), keep in mind that you need to take into account that everything shifts if the first day of the month is a Sunday. To compensate, your calculated globals would be (for example):
One of these:
gDate (A global date field in which you can enter any date to get to that corresponding month)
Six of these:
gDay1OfMonth = Date(MiniCalMonth; 1; MiniCalYear)
gDay8OfMonth = Date(MiniCalMonth; 8; MiniCalYear)
gDay22Plus7OfMonth = Date(MiniCalMonth; 22; MiniCalYear) + 7
gDay22Plus14OfMonth = Date(MiniCalMonth; 22; MiniCalYear) + 14
And 42 of these:
gMonday1 = Case ( DayName ( MiniCalDay01) = "Sunday"; GetAsDate(Int(MiniCalDay01 / 7) * 7- 6); GetAsDate(Int(MiniCalDay01 / 7) * 7+1 ))
Then set up your 42 relationships, and you're good to go.
If you start your week on your calendar with Sundays, you no longer need to do the "case" to offset as shown in the 42.
Hope that helps!
Thank you again. I have just downloaded the trial version of FMP V9 and have just started to create my application. It could be an interesting few days!