Creating a month view calendar in FileMaker Part 1

There are several reasons why a month view calendar would be of benefit to a FileMaker solution and there are an almost equal number of ways of providing that functionality.

I'm going to show you one method for creating a month calendar view, a method which I prefer as it's easy to create and simple to maintain, two of my guiding principles in FileMaker development.

My preferred system incorporates an arrangement of portals, each of which will represent a single day, showing multiple event records. FileMaker's portal filtering facility will be used to filter the records that are shown in each portal as it makes for an easy way of limiting shown records in a portal without resorting to lots (and lots) of relationships. NB Portal filtering was introduced in FileMaker 11 so these instructions won't be useful if you're running pre FM11.

To give you an idea of what the layout will look like in layout mode when it's finished, here's a screen-grab showing 42 portals (one for each potential day in any given month).


A word of caution : although this solution will work in a networked environment, the extensive use of portal filters may result in slow layout refreshes (the same issue is likely when using iPads as I haven't tested this in that environment). If you have a lot of records to display then you may find that this solution doesn't meet your performance requirements. In the third part of this blog series I'll detail a few alternatives that may help to speed things up.

Why 42 portals, after all there's a maximum of 31 days in a month? That's because showing a month view as a calendar requires us to show the days in a grid with 7 columns (a column per day) and 6 rows. 

The standard 1 month view in Outlook or similar application like iCal will show either 28, 35 or 42 days depending on where the 1st of the month occurs during the week and how many days there are in the month.

If you have access to iCal/Outlook, you can see this by looking at February 2010 (28 days are shown), March 2012 (35 days are shown) and April 2012 (42 days are shown) - though this presumes that you start your week on a Monday. iCal/Outlook flick between 28, 35 and 42 days but most people don't notice it. 

Initially we're going to use 42 portals but it wouldn't be difficult to create additional layouts and an appropriate script to replicate iCal/Outlook's functionality in this matter.

Note that in my example I've used, in the British way, Monday as the first day of the week.

What you're seeing in the screen-grab is 42 portals holding a single field. Surrounding each portal is a box and above each portal is, shock horror, a repeating field. Repeating fields have a fairly poor reputation and in most cases, this reputation is very much warranted, but here they make perfect sense - I'll explain why later on.

Below is an image showing an exploded view of one of the 42 portals. To the left you can see the box that surrounds the portal, above you can see the cDisplayDate field that shows what day the portal refers to and on the right you can see the portal complete with a single field and a button (more on the button later).


To create the calendar:

1. You're going to need a table to base the calendar on - I'm going to call this the Events table - and it should store a single record for each event. Make sure that you have plenty of test records in there.

2. You'll also need a table to base your calendar layout on. We tend to create an Interface table in all of our databases as it makes for a handy, central location for storing the majority of the global fields in our FileMaker solutions. Our Interface table never has more than one record in it and we apply restrictions so that additional records cannot be created nor the primary record be deleted.

Developer note : we always create a primary key field in our tables and it is always called "__ID" (that's a double underscore prefix). This ensures that the primary ID is always at one end of the field list when viewed alphabetically. You'll need to create a similar field in the Interface table and, presuming that you haven't done so already, the Event table.

3. The two aforementioned tables will need to be related on a catch all relationship so that the Interface table can see all of the Event records. We tend to set the Primary Key ID in the Interface table (Interface:__ID) so that it always has a value of 1 and then we create a matching Interface ID field in the related table using an auto-enter with a value of 1 (call this matching Events field "_InterfaceID "- that's a single underscore there). This allows us to have a match all relationship between the two tables that can also have creation rights - a handy facility that cannot be applied if we use a standard match all relationship based on random fields. To do this, make sure that you have a table occurrence in your relationship diagram for "Interface" and another for "Events", now create a third called "Interface to Events" and make sure that it is using the Events table as its source. Next join the "Interface" table occurrence and the "interface to Events" table occurrence using "Interface::__ID" (the Interface table's primary ID field) and the "Interface to Events::_InterfaceID" field (that's the field in the Events table that will hold a copy of whatever primary ID is stored in the Interface table). Use the equal (=) symbol as the relationship operator so that you now have all Events being related to the single Interface record. Lastly, make this a Create Relationship by putting a tick in the "Allow creation of records in this table via this relationship" and that's on the Events table side.

4. You will now need to create a few fields in the Interface table : a field representing the month a user wants to see (call it gSelectedMonth) and another representing the year that the user wants to see (call it gSelectedYear). Make both of these fields numerics and globals (that way our calendar can be viewed by more than one user and they won't be restricted to a single viewable month).

There is one caveat to using global fields and portal filtering - FileMaker doesn't like refreshing the filter when a global is in use, consequently every time the user changes the month / year that they're viewing, we'll need to force a refresh of the layout to remind FileMaker to refresh the portals - I'll go into how we do this later.

The third field you'll need to create in the Interface table is the calculation field that each of the portal filters will be based upon (call it cDateOfFirstPortal). Use the following calculation code and make sure that you set the result to a Date and don't store the results (no indexing) :

Let ( [ 

FirstDayOfTheMonth = DayName ( Date ( gSelectedMonth ; 1 ; gSelectedYear ) ) ] ;

Date ( gSelectedMonth ; 2 ; gSelectedYear ) - 

Case (
  FirstDayOfTheMonth = "Monday" ; 1 ; 
FirstDayOfTheMonth = "Tuesday" ; 2 ; 
FirstDayOfTheMonth = "Wednesday" ; 3 ; 
FirstDayOfTheMonth = "Thursday" ; 4 ; 
FirstDayOfTheMonth = "Friday" ; 5 ; 
FirstDayOfTheMonth = "Saturday" ; 6 ; 
FirstDayOfTheMonth = "Sunday" ; 7 ) )

Yes, there are neater ways of doing this but the advantage here is that it's very easy to understand.

What we're trying to do is work out, based on the month and year that the user selects via the two previously created fields, what the date shown in the very first (top left) portal is. We'll use this date in a moment to determine what date to display in the second portal and the third portal and the fourth and so on. 

The next field (call it cCalendarDate) you need is another calculation field (text this time without any storage) based on this calculation:

MonthName ( Date ( gSelectedMonth ; 1 ; gSelectedYear ) ) & " " & gSelectedYear

This will show the name of the month and the year, based on those you select via the date fields.

The last field we need to create is the calculated repeating field I mentioned earlier. This is the field that's going to store the date that's emblazoned at the top of each day in the calendar.

Call the field cDisplayDate and make it a repeating field with 42 repetitions, with a text result (calculation shown below) and don't store the results.

Here's the code it will need to be based upon :

Let ( [
PortalDate = Extend ( cDateOfFirstPortal ) + Get ( CalculationRepetitionNumber ) - 1 ;
TodaysDate = Get ( CurrentDate ) ] ;
If ( PortalDate = TodaysDate ; "Today - " & Day ( PortalDate ) ; Day ( PortalDate ) ) )

First off, what are those Extend commands? Well FileMaker has a whole series of quirks and one of them is that when you reference a specific repetition within a repeating calculation field, if you fail to use the Extend command, you can end up with spurious results.

Okay, but what's the point of this code? What it does is to calculate the numeric value of each of the 42 days we'll need to display above our portals (it even prefixes the current date with the word "Today" if it happens to reside in the particular month / year the user has selected).

We're going to place one instance of this field above each portal on the layout making sure that all of the available repetitions are used (all 42 of them) and that each repetition is only used once.

That's it for the Interface table. The Event table will need to have a StartDate field (that stores the date the event is happening on), possibly a type field (the kind of event this is) and a Summary or Title field. We could have an end date field as well as start and end times, alarms and so on however, we're keeping this calendar nice and simple to begin with.

Now, onto the calendar layout.

5. Make a new layout and base it on your Interface table. You'll need to place the two date selector fields (gSelectedMonth and gSelectedYear) onto the layout. For the moment we'll leave them as vanilla data entry fields but later we'll add plus and minus buttons to allow for rapid date changes. Remember that since we only have a single Interface table record, you'll need to make sure that only a single record exists and that the layout is being viewed in Form view mode (not list or table).

Place the cCalendarDate field somewhere prominent on the layout making it obvious what month an year we've selected.

6. As I mentioned earlier, FileMaker doesn't do a great job of refreshing portals when the portal filter is based on calculations / global fields (remember that our two date fields, gSelectedMonth and gSelectedYear, that we'll use to adjust the displayed date, are globals?) so we'll need to accommodate this. My original method for doing this was to flush the cache via a Refresh Window script step but it's really unnecessary overkill - here's a quicker way to create the same effect.

Create a new text field in the Interface table called matchRefresh and now change the relationship between the Interface and Events table to include an additional relationship criteria where Interface::matchRefresh = Interface to Events::_InterfaceID. Finally create a script and call it PortalRefresh. It will need one step (along with any of your usual script headings, comments and the like) namely :

Set Field [Interface::matchRefresh; 1]

Now presuming that your Interface table has a primary key set to 1 and your Events table has a matching field, then by changing the matchRefresh value to 1 in the PortalRefresh script, we'll be refreshing the Interface to Events relationship on it's own (the Flush Cache script step would refresh everything). Make sure that you remember to run this script every time you change the displayed Month or year and when you open the layout.

Now go back to the two date fields (gSelectedMonth and gSelectedYear) and apply an OnObjectExit Script Trigger that is assigned to this new script. Now, whenever either date field is accessed and exited, the screen will refresh and update.

7. Create a portal on the layout using the relationship from your Interface table to your Events table. You'll want to keep the portal to modest proportions, showing around 4 rows with dimensions of 148 pixels wide by 65 pixels high. Remember that this portal is going to be used 42 times hence the sizing restrictions.

For the moment place a single field on the portal and I'd recommend that it only displays the Summary or Title of the Event. As I mentioned before, I've got a button on the portal - the button isn't assigned to any script, it's there so that I can apply conditional formatting to it (each event record could be assigned to a specific colour coded calendar or have a colour based priority - the button is the blob we'll apply all of that formatting to).

We could use a text object or a field instead but I find buttons to be more forgiving when used in this way. 

Just above the portal, place the cDisplayDate field and use the 1st repetition of it, then surround the portal and the field in a box - all of this is shown in the screen-grab above.

8. We now need to enable sorting of the portal and filtering. For the sorting, go into the portal's definitions and sort on whatever you feel makes sense - I've set mine to the StartDate followed by the TimeStart fields used in the Events table. For the portal filtering, you need to use the following code :

If ( Interface to Events::DateStart = Interface::cDateOfFirstPortal ; 1 ; 0 )

Portals filter based on boolean results (i.e. a portal record will only be shown if it passes a calculation with a 1 result - any other result and the portal record will be hidden). So in this calculation we're saying that if the date of the event matches the date calculated by the cDateOfFirstPortal field, then show it. Each additional portal will have a slightly different calculation, with the second portal's calculation looking like this : 

If ( Interface to Events::DateStart = Interface::cDateOfFirstPortal + 1 ; 1 ; 0 )

Notice the +1 after the Interface::cDateOfFirstPortal? You'll need to increment that value by one for each portal from left to right, top to bottom so the third portal will be +2 and the very last portal will be +41. These increments allow us to see what date each portal should be showing based upon that very first portal.

9. Following this process, I would recommend creating 7 portal arrangements (portal, fields, box and so on) in total, representing the first 7 days of the 42 portals.

For each portal arrangement, remember to change the portal filter (incrementing the +1 for each day) and changing which repetition for the cDisplayDate field you're looking at. Test each of these carefully and once you have a whole row working to your satisfaction, you can duplicate the 7 portal arrangements for the next 7 days and so on, changing each of the portal filters, the value of the cDisplayDate repetition and testing as you go.

10. You should now have a layout with a 6 by 7 portal arrangement grid and two date fields that refresh the layout when they are altered - I've included a finished example below.



There's a lot more that we can do here but for this blog entry, it's enough to get us started. I'll detail lots of improvements to this in a future blog. Ready for Part Two?


Latest Notes

I have adjusted the Portal Refresh script so that it now runs without having to force a Cache Flush via the Refresh Window step (see above). Make sure that you adjust your Portal Refresh script as per section 6 accordingly. I have also clarified the Interface to Events relationship as there has been some confusion here - thanks for your comments!


If you notice anything wrong with this blog, think that you can do it better or just want to discuss it, then please get in touch.