FileMaker 13 One Month Calendar
Some time ago, I wrote a 3 part blog on how to create a one month calendar view within FileMaker, similar in presentation to the Mac OS Calendar app, iCal. The blog gave detailed instructions on how to create a calendar within FileMaker whilst keeping it easy to bolt onto a current database solution. Should you be so inclined, you can view the first part of the blog here.
It proved to be rather popular, based on our web statistics and the number of emails I received; however it was complicated stuff, at least for a novice and I had more than one email request to help people with their system when they had become stuck. I also had requests to make it faster, especially on a network or on an iPad or (gasp) over IWP (Instant Web Publishing) where it performed badly. I had never intended it to work in this sort of environment - the methods which made it easy to bolt onto a current database made it slow over a network. The reason behind this lack of ‘network speed’ was due to the extensive use of portal filters which are nice and easy to integrate as, in a one month calendar, they only require a single relationship to be created. But 42 portals using different filters cannot be sped up - that’s simply too much to expect from FileMaker.
That’s enough history. We’re moving with the times here and that means FileMaker 13. With FileMaker 12 we had a host of new features - largely geared towards enhancing the graphical aspects of FileMaker. With this latest version of our favourite database software, the FileMaker team have managed something truly extraordinary - Web Direct.
Web Direct has made this project possible as it allows a developer to take a database and, with minimal effort, have it running in a standard web browser whilst retaining nearly all of the features and speed you would expect from FileMaker software. This is a remarkable achievement and it really is hats off to the FileMaker team for not only understanding the importance of this groundbreaking development but also having the skill to implement it.
So, as a thank you to FileMaker and in answer to some of the requests we’ve had here at igeek, here is the all new one month calendar.
THE USUAL CAVEATS
1. Although we’ve managed some significant improvements in speed in this new version of the calendar, it is geared towards making it easy to add to your current database and, as such, there are still some speed issues. I’ll give you an example: hosting the original database on a remote server gave a calendar refresh speed of around 70 seconds with 1000 records - that was way too slow, hence our warning about not using the old calendar in a network. With our new approach provided here, we’re looking at less than 10 seconds with 2500 records; that’s more than twice the number of records in a fraction of the time. In my book, that’s a huge improvement but that 10 second refresh will still give some people cause for disappointment. Saying that, stand alone use is very fast (with the 2500 records, there’s less than a 1 second delay).
2. I’m not going to break everything down here like I did in the previous blog. Instead, you’ll be happy to hear, we’re providing a copy of the database file for you to download and integrate with your own solution (though I’ll still go into some detail to give you an idea why we took one development route over another).
3. This is still what I’d call an intermediate project for FileMaker developers so if you’re completely new to FileMaker, database design and / or software development, you may want to proceed with caution. Unfortunately we really don’t have the necessary time to assist in integrating this solution with your database but we’ll be more than happy to discuss you becoming one of our clients (you can contact us here).
4. This blog gives you one solution for how to create a one month calendar view within FileMaker 13 but it must be understood that there are calendar features which have not been included here. However we may be able to add them to a future blog or you may be able to extrapolate from the framework we have provided and create them yourself.
5. I won’t cover how to host the file on FileMaker server, use the file via Web Direct or how to access the file on an iPad as there are guides available covering these subjects.
6. Lastly..... Backup. Backup. Backup. If you’re thinking about using this solution with a live database, please don’t. Instead, take a copy of your database and test it on that first.
We’ve provided the FileMaker 13 file for the calendar and you can download it here: Download File.
The file will automatically login with the full access 'admin' account (no password required).
We’re expecting that you’re opening the file in FileMaker 13 but FileMaker 12 will suffice though there will be aspects of this blog which may not make sense unless you are familiar with FileMaker 13.
Upon opening the file, you’ll be taken to the one month calendar view and the month / year will be set to the current month / year. We’ve populated the database with spurious data so that you have some records to view. You can use the plus and minus buttons to increase / decrease the current month, you can jump to today’s view by clicking TODAY and you can jump to a specific month / year by typing a value into the month / year fields. Here’s a screenshot to give you a better idea :
Presuming that you have navigated to a month where there are event records, you should see a collection of events. Days are colour coded based on the Event Type (Meetings, Internal and Timesheets). You may notice that if an event lasts more for than one day or the All Day Event field has been checked then the colour coding fills the event background, otherwise you’ll just get a blob of colour to the left of the event. If there are more than 4 events for a given day, then you’ll see a description of how many more events they are for that day.
You can click on a specific event and you’ll be taken to a popup window showing the relevant event details (if you click on the description for how many more events there are, then you’ll be taken to the same popup and you’ll have access to all of the events for that day). Here’s another screenshot to give you an idea.
THE DATABASE STRUCTURE
There are two tables in the file; Interface and Events.
The calendar view is based on the Interface table which holds 42 global fields, each of which has 5 repetitions. We need 42 fields, because that’s how many days you can see when viewing the one month calendar and we need 5 repetitions so that we can show up to 5 event titles for each day. These fields are globals so that the calendar will work in a multi-user environment. The interesting thing I’ve done here is to set the 42 global repeating fields here as containers even though they only ever hold text (the event title). The reason behind this is because when you click into a container field, you can bounce out of it far more seamlessly than if it was a text field (text fields expand to show their contents when clicked on which you will soon see is not a favourable action).
The Events table holds the Event records. I’ve kept the Events table very simple so that it’s easy for you to merge it with your own database. You’ll almost certainly want to make a number of changes here (for example I’ve set the primary From / To fields as date fields rather than timestamps in order to keep the complexity of the system to a minimum. I’ve also used a simple auto-enter serial number for the Event IDs - no doubt you’ll be using something more stable in your database but if not, random UUIDs would be a preferable solution.
In the relationship diagram (screenshot below) you can see the two primary table entities; Interface and Events (I’ve squidded Events to the Interface primary table occurrence).
There are only a couple of necessary layouts, “One Month View” and “One Event View”, the others are more like window dressing and a nod to standard FileMaker database development standards.
Finally, I’ve laid the scripts out in a reasonable fashion and I believe that the commenting should help you see how I’ve arrived at a particular solution.
HOW DOES IT ALL WORK?
Whenever the one month calendar layout is refreshed (read ‘refreshed’ as ‘changing the month or year’), the days headers are updated via the “Calendar : Set The Date Headers” script. The script determines what the date is for the very first day cell in the calendar and from that, what all of the remaining 41 day headers should be. The script is actually a loop that sets the dates within a repeating field (which is in the Interface table - gDisplayDate).
A second scripts runs which determines what each of the 5 repetitions for each of the 42 days should hold and it does this via a looping ExecuteSQL statement. SQL is part of the key behind keeping this refresh process as fast as possible and I’ve written it so that it’s reasonably portable between databases. A word to the wise, if after integrating the calendar with your database you end up with a series of question marks instead of event titles, I’d head straight over to the ExecuteSQL statement which almost certainly no longer matches the field names in your database. The SQL statement grabs Event records where the date for the current cell falls between the From and To dates stored in the Events table. These records are grabbed in a sort order based upon whether the Event is for the whole day or not (so whole day events tend to line up with each other at the top of the day cell).
THE ONE MONTH CALENDAR LAYOUT
Keeping the calendar as swappable between databases as possible, I have come up with a 4 layer approach for the main calendar layout which you can see in the screenshot below. In browse mode, when you click into an event, a script trigger kicks off the “Calendar : Process Event Button” script which opens the event popup window. By using a script trigger rather than a button, we get better portability and it’s easier to develop the layout.
ADDING THE CALENDAR TO YOUR DATABASE
You probably have your own version of the Interface table I’ve used here so you can easily copy the Interface fields straight into it. You’ll need some way to link this table with your Events / calendar table but you’ll need two methods to create the link; one which allows access to all of the Event records and one which only allows access to a select few (the select few being the events the user clicks on in the calendar view).
As I mentioned earlier, I’ve kept the Events table very simple so you will almost certainly want to make adjustments to it. Aside from that, either copy the Events table to your solution or make sure that the fields used in the scripts / on the layouts exist in your database.
I would probably import the scripts one at at time to your database, checking as you go that there are no import errors (it’s usually easiest to create the missing fields, delete the script and then re-import it). As far as the creating the layouts goes, I’d probably leave these till last, that way you will already have checked that all of the essential fields exist within your database before you have to start remapping anything on the layouts.
NETWORKING, IPADS AND WEB DIRECT
Since the file is already designed to work within a multi-user environment, there should be no problems hosting it via FileMaker Server (though you may want to create some user accounts for full testing).
If you already have FileMaker Server 13 running, then you should find that the database works in a web browser without requiring any additional work (certainly the only script steps which might cause a problem are the resize window steps).
If you have FileMaker Go running on an iPad then you should find that the main calendar window, though not designed for an iPad, will still display without any problem. I've hidden the status bar so that everything fits nicely (the status bar is automatically shown if you click on an Event and there are several Event records in the found set).
FILEMAKER 13 VS FILEMAKER 12
There are numerous new features which FileMaker 13 has brought to the developer’s toolbox. Here are just a few of my favourites.
Web Direct is an obvious favourite. Instant Web Publishing (IWP) would fail to cope with a lot of this calendar database and let’s not mention the speed aspects! Web Direct has opened doors to entirely new possibilities for FileMaker databases and this calendar is just one example of that. We’re very much looking forward to making use of this wonderful feature in the future.
Padding. The ability to shift the contents of a field object up, down, left or right much like setting page margins, has proven to be very useful.
Popover Buttons could have been used, though they’re beyond the scope of this blog. You would need to add a button to each Event in place of the script triggers and you’d be able to do away with the Event popup window. It would take a little time to create but it would be a very nice touch to the calendar which wouldn’t have been possible in previous versions of FileMaker.
In order to keep the calendar quick to integrate with a current database whilst keeping the refresh lag to a minimum, I have used certain techniques which might be improved upon if you’re after pure speed. For example, you could extract all of a month’s Event records via an ExecuteSQL statement (which will be very fast) and extrapolate from there. On the other hand, you could make the file even more portable by using global variables in place of the global repeating fields (you could also use this technique if you were concerned about the lifespan of repeating fields within the FileMaker product range).
Unfortunately we cannot help in debugging your database or interfacing the calendar with a live system. However, if you notice anything wrong with this blog, think that you can do it better or just want to discuss some other element of it, then please get in touch.
I hope that this blog proves to be useful to you and wish you luck if you decide to integrate it with your own database.
We had a request to highlight the days which weren't part of the current month so the file has been adjusted to accommodate this. You might need to refresh this page to download the latest version of the file which should read as v1.2 on the splash screen.
An error was discovered by one of our eagle-eyed friends - we've now corrected the bug. You might need to refresh this page to download the latest version of the file which should read as v1.3 on the splash screen.