Calendar Dates

Discussion created by roryduffy on Mar 10, 2018
Latest reply on Mar 11, 2018 by siplus


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.,

Portal 1:
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!