Creating a month view calendar in FileMaker Part 2

A month view calendar (think iCal or Outlook) within FileMaker 11 would make for a really useful tool, however as developers we're not provided with any product specific templates to create this sort of functionality - that's why I've written this blog. In part one we created the framework for the one month view calendar and as it stands, it'll do a reasonable job but it could be so much better - that's what part two is all about. If you haven't read part one, then I'd recommend that you do so before returning here.


Creating a month view calendar in FileMaker Part 2 Features
1. Shading weekends.
2. All day events.
3. Colour coding events.
4. Events spanning more than one day.


Before we start
I would thoroughly recommend creating a developer layout of the Events table (unless you already have). I tend to name developer layouts Dev : followed by the table name, then I make sure that all of the table fields are on the layout and I'll usually look at it in Table View (much like a spreadsheet). As we create new fields, you'll need to add them to this layout so that you can view what's happening with each record as we create the calendar. Tip : put all of your Dev views in a Developer layout folder, keep it in alphabetical order and hide it from your users - this sort of neatness makes for much quicker scripting, layout selection and so on - remember that consistency is king!


Feature 1 : Shading Weekends
In the 1st blog, the weekends were shaded but I didn't describe how to do it. No doubt you've already worked out how to do that but in case you haven't... making the weekends a different colour can help break the month view up, so select all of boxes that surround the weekend portals and change the fill to just off white (whilst you're at it, grab all of the weekday boxes and give them a pure white fill). You'll now be able to see the weekends much more clearly. Remember that this is only for the boxes that surround the portal, not the portals themselves.


Feature 2 : All Day Events
Some Events need to be marked as being all day, either by choice (i.e. the user decides to make the event last a full day) or by nature (i.e. an event lasts more than a single day). The month view calendar in the first part of the blog didn't allow for this level of functionality so let's address that omission first.

Create a field in the Events table called UserSelectedAllDay and make it a number field. We'll use this field as a checkbox on a layout so that the user can select it in order to signify that this is an all day event. The field will hold a 1 in it if it's an all day event or a 0 (or nothing) if it isn't.

Create a second field in the Events table called cAllDayEvent. Make it an un-stored, numeric calculation field with the following code :

    If ( UserSelectedAllDay = 1 ; 1 ; 0 )

This calculation is overkill at the moment but we'll be adding to it later on so we might as well enter most of the code now. You can use the "Dev : Events" layout we created earlier to alter the UserSelectedAllDay field later on (use a 1 for events that are all day). That's all we need for the moment as we'll put it to good use when we colour code events.


Feature 3 : Colour coding events.
Colour coding events is easy to employ using our good friend, conditional formatting. You'll need to create a text field in your Events table and call it something like Type. You'll use this to store the type of event record (such as meeting, appointment, birthday, holiday, etc). You'll want to assign a single, obvious, colour to each Type so the more Types you have, the harder it will be to find a suitable colour. Once you have your field, select the Events Summary field and the blob-button you placed in each portal and create a conditional formatting rule as follows :

    Interface to Events::Type = "XXXX" and Interface to Events::cAllDayEvent = 1

...where XXXX is the Type - eg Meeting, so you could have Interface to Events::Type = "Birthday" and Interface to Events::cAllDayEvent = 1

Then apply a suitable fill colour (I'd recommend pale, pastel colours as they're less likely to make the event text illegible).

You'll need to add a conditional formatting statement for each Event Type you have eg if you have Events of Type, Birthday, Meeting and Holiday, you'll be needing 3 separate conditional formatting statements (using the code above but adjusted for each of the three types, with 3 different fill colours).

You'll also need two extra conditional formatting statements, one for the weekday portals and one for weekend portals, which will be :

    Interface to Events::__ID = ""

The fill colours for weekday events should be set to white and the weekend ones set to grey. What's the reasoning behind this? In another blog we'll be adding a clickable button to every portal so that we can click on a portal row (whether there's a record present or not) and open a popup window (either onto the day view or onto a blank record view).

To make sure that this button is available regardless of how many Event records are present in the portal, we need to ensure that the relationship from the Interface table to the Events table is a create relationship, if it isn't then the clickable button won't be present where there are no portal records.

We created the relationship last week but didn't make it a create relationship so go ahead and enable that now. We need different conditional formatting for the weekdays and weekends because the boxes that surround each portal has a different fill colour (white or grey).

So why go to all this trouble when you could just change the colour of the blob-button in the first place? Because if we change it so that it matches the box colour, it'll be that much harder to see on the layout whilst we're developing the calendar.

The only problem with this is that the blob-buttons won't be showing their true colours so to speak, they should all be adjusted so that each of the conditional formatting definitions based on Event Type shouldn't have :

    and Interface to Events::cAllDayEvent = 1

as part of the conditional formatting definition - they should look more like this

    Interface to Events::Type = "XXXX"

Make sure that you change the XXXX for each Event Type. Why do all of this twice? Well you'll already have the exact colours assigned to both the blob-button and the event which makes things easier and it won't take long to grab all of the weekday blob-buttons, apply the conditional formatting and then do the same with the weekend blob-buttons (hold down shift and grab select each block of blob-buttons with the mouse).

Personally I would prefer my blob-buttons to merge with the Event Summary if it's an all day event but to remain separate when it isn't, so I've moved my portal items around a bit, like this:

View of portal

Feature 4 : Events spanning more than one day.

In the previous blog calendar events would only show up once in the month view (on the day that they occurred), regardless of how many days they might actually go over.

To create a month view where events can span more than a single day, we need to have an EndDate field in the Events table (you'll need to create a suitably named date field if you haven't already done so). First off, adjust your Event records via your Dev : Events layout so that they all have an end date. If you're using a dummy database, then I'd use Replace Field Contents to set all of the end dates to the start date, perhaps changing a few to being two or three days later.

We'll need a list of the Event dates (ie the start date, end date and all of the dates in between for each Event - including repetitions). FileMaker doesn't readily allow us to calculate this so we'll need a custom function to do so.

Create a Custom Function called InBetweenDays

InBetweenDays will need three parameters : StartDate, EndDate and ResultList. The base code is :

If ( StartDate ≥ EndDate ; EndDate &¶& ResultList ; InbetweenDays ( StartDate + 1 ; EndDate ; Startdate &¶& ResultList ) ) 

Here's a screenshot of what it should look like:


We'll need to put this new custom function to good use in the Events table, so create a new calculation field called cListOfDates (don't store the text results). The code it needs is :

InbetweenDays ( DateStart ; DateEnd ; "" )

cListOfDates will now hold a list of the Event record's dates (from start to end and everything between).

Now for the tedious bit. Each portal will need changing so that the portal filter now looks for the start and end date of events, and we do that by changing each portal filter to :

    If ( PatternCount ( Interface to Events::cListOfDates ; Interface::cDateOfFirstPortal ) > 0 ; 1 ; 0 )

This is for the first portal but for every other portal (from left to right, top to bottom), you'll need to increment the calculation by 1 so for the second portal it should look like this :

    If ( PatternCount ( Interface to Events::cListOfDates ; Interface::cDateOfFirstPortal +1 ) > 0 ; 1 ; 0 )

The third portal will have +2 instead of +1 and the fourth +3 and so on. So how does this work? Well the boolean calculation returns a 1 if the date assigned to the portal matches or exceeds the start date of the event AND matches or is less than the end date of the event.

The last thing we need to do is to change the cAllDayEvent calculation. It should now read :

    If ( UserSelectedAllDay = 1 or DateEnd - DateStart > 0 ; 1 ; 0 )

This means that if the user sets the event to an all day event (by placing a 1 into the UserSelectedAllDay field) or the event has a start date that differs from the end date, then the conditional formatting we've applied to each portal event summary will highlight in the required colour.


The End Result
Phew. That's quite enough for this blog. Presuming that you've followed everything discussed so far and, you should have a calendar that looks something like this, It's not quite Outlook or iCal but it's quite close!

Full calendar view


Moving on and what's next?
We still haven't looked at how to deal with days that have more events than the portal can hold - we will next time. Also a view showing a single day and a view for a single event would be nice so how about a couple of popup layouts? Calendar event type filtering? Back and forward date selectors? Jumping to today's date? I'll cover all of these and more in future blogs so keeping checking back for further updates.


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.

Now, read on - see part 3.

Blog entry is closed for new comments.