I've been trying to figure this one out. I know creating calendars with portal filters is a well-documented task on line and I've done plenty of research, but basically I'm trying to find a simple solution that uses native functionality, and doesn't rely on having a populated "Dates" table.
At the moment, I've got a "Calendar" table with 1 record. It has a Global ID (Date), and 2 fields that calculate the 1st and last dates in a given month. These 2 fields reference through to my "Events" table.
( Date ( Month ( ID ) + ( 0 ) ; ( 1 ) ; ( Year ( ID ) ) ) )
( Date ( Month ( ID ) + ( 1 ) ; ( 0 ) ; ( Year ( ID ) ) ) )
Relationship between "Calendar" X "Events":
FirstDate ≤ EventDate
AND LastDate ≥ EventDate
Then I've set up my 42 "Events" table portals.
Filtered them by DayName ("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")
Then staggered them
i.e., (Day(ID))>-6 and (Day(ID))≤1, incrementing by 1 etc. up until (Day(ID))>-35 and (Day(ID))≤42
The events are appearing in the correct portals, which is great, so I'm half-way there...
But now, I'm struggling to get the days of the month to display as numbers (either as merge fields, or straight fields) in the corresponding slots.
I found this article:
Creating Calendars with Portal Filters in FileMaker Pro
which was really helpful.
But I didn't understand the part about creating the 42 variables. I mean, I recreated that script in my Filemaker solution, but I didn't understand where/when or in what context the script should run (when I change the Global Date ID?). So I abandoned that method.
Then I tried setting up 42 calc fields in the "Calendar" table. Getting each Calc field to evaluate each possible date of the month to check e.g.,
a) whether it's a Monday
b) whether (Day(ID))>-6 and (Day(ID))≤1
Evaluating each possible date (so 31x) within each Portal calc field. This took about an hour but still didn't work! Plus it seems a very cumbersome method.
If there's a function e.g.,
Portal 01 Calc: find all Mondays in a given month where (Day(ID))>-6 and (Day(ID))≤1
Portal 02 Calc: find all Tuesdays in a given month where (Day(ID))>-5 and (Day(ID))≤2
Alternatively, I'm sure that there must be an easier way to achieve the same result - as from my extensive research, I can tell a lot of people have built calendars (although I appreciate that there are multiple ways of doing it).
Just to re-clarify, I've got the events showing up correctly in the corresponding slots; I just can't get Filemaker to display the day numbers in those slots. My "Calendar" table is a 1-record Global ID table, so the calendar should change dynamically as I change the date.
I'm using Filemaker Pro v16.
Many thanks in advance!