Cascading Delete Self-join or Same-Table relationships are very useful (two table occurrences from the source table related to each other), but be careful! Don't ever turn on the option to allow deletion of related records! If you do, whenever you delete a parent record you may delete more than you want. What happens is that since your related records are also your parent records, deleting records can cascade out of control. I have seen the deletion of a single record cause the deletion of all records in all tables.
Level: Intermediate Version: FileMaker 17 Category: General Tuesday, October 16, 2018
Everyone wants a calendar in their FileMaker solution these days. There's tons of web viewer solutions out there for purchase that will integrate well with your FileMaker solution. There's even plug-ins to integrate with iCal or Google Calendar. What I'm going to give you today is a taste of how to build a calendar with just standard FileMaker tools. That means no web viewer or complex acronym web code (HTML, CSS, PHP, JSON, JAVA, etc). Just good ole FileMaker calculations, scripting and relationships in this article.
Question Everything When your client or boss asks you to build a calendar in FileMaker, the first thing you need to do is ask "why". In fact, you should ask that question regarding just about any programming request that seems out of the ordinary or requires more than an hour of work. It's your duty as a developer to question your client or employer so you can provide the best solution possible. What I find most of the time when clients ask for a calendar is they don't really want a calendar. What they really want is a day or week view but hardly ever a month view. Some don't even need a calendar at all but that's the most familiar concept they could use as an example.
All you need to do find out is what your client or employer wants to do in terms of calendaring and then design something specific to their needs. If you just design or purchase a traditional calendar, you may be doing them an injustice. Most of the time it's just overkill and gets in the way of them efficiently using your solution. I see it all the time with clients who need me to modify a solution I didn't create. "Oh, we don't use this section cause we don't need it". I think to myself, "then why is it there lol". In the end, asking a few questions will save time, money and make a much better solution.
Calendars and Plug-ins for Purchase Before you design anything FileMaker, you really need to look at what's available out there for purchase. Make sure you inspect their functionality, interface, integration and licensing before you decide to create your own calendar. Only if what's out there doesn't meet your needs or is overkill, should you develop your own solution.
My apologies if I missed a calendar product. Enter a URL and company in the comments below and I'll be sure to add it.
Relational Calendar The first thing you want to do is create a relationally sound calendar solution. Yes, you can make a flat calendar that works great but what if you want to search it and produce a list view of hits? If you don't store your events as individual records, that won't be possible. What if you want to produce a subsummary report that is organized by date? If you don't store your dates as records then this won't be possible. Make sure to review the current needs as well as possible future needs before diving in to the development of any project.
That means there are at least two tables in this relational calendar. The first table, called DATES, contains one record for each date of the year you want to be able to display. DATES only needs one field at this time named "date". The second table, titled EVENTS, contains one record for every event occurrence on a particular date. EVENTS should contain the fields you want on each event like name, time start, length, date, etc. You may be worried about creating all these records but it's no big deal. Databases are designed to find, sort and display records efficiently. It's the basic functional specifications of any database to speedily organize lots of records.
There will also be a table called CALENDAR containing a single record. It will act as the interface for the solution, displaying the monthly calendar view. This could also be done with the DATES or EVENTS table but there's very little overhead for a simple table and I can store global interface fields in the CALENDAR table so I don't clutter up the other tables.
The CALENDAR table should contain two global fields. A global number field for the year and a global text field for the month.
Place these fields on a layout based on the CALENDAR table and attach custom value lists to display Years and Month Names. You can base the year popup menu on the contents of the DATES table, if you want, so it's dynamic. All you need to do is create a year calculation field in the DATES table.
Creating the Dates It's best to create the 365 or 366 days for every year you want to display ahead of time. As mentioned above, databases handle records very efficiently so there's no need to be concerned. Besides, it's only 3,650 record for a ten year calendar. That's not even enough to make FileMaker hiccup. And, a year's worth of records can be created in a matter of seconds with a simple looping script.
The first step is to go to a layout based on the DATES table. I usually pick a layout that has no related records, portals or unstored calculations that might slow down the looping script, even with Freeze window employed. I often call these "source tables" so I have a place to simply look at raw data without all the interface. The steps that follow start by showing show all records, going to the last record and then setting a global field to the year plus one (unless there are no records). I'm assuming the years have been created in order so the last record should be the newest.
I could have used a variable instead of a global field to store the year but later on I offer the chance to change the year in a custom dialog. This seems counterintuitive to what I just said in the previous paragraph about creating records in order. However, the reason I offer the ability to change the date is you may not want the initial year to be the current year. Maybe you are creating records for when you will release the solution next year. Or, maybe you want records from the previous years. Who knows.
Next, the script returns to the layout where the button was clicked. There are two reason for this behavior. First, I want the original layout to be shown behind the custom dialog. Secondly, if the user cancels the dialog, I want the script to end on the original layout. I see a lot of developers approaching this type of script in a different way. What they like to do is offer an Else If step where they test for the cancel button and then return to the original layout. This does work but often requires the developer to toss in a Halt Script step which makes the script virtually unusable as a sub-script.
If the user decides to continue, the DATES layout is re-selected and a new record is created with the first date for the selected year. The loop is entered and creates the rest of the records for the year. I decided on the GetNthRecord function because it offered the simplest formula. Otherwise, I would have needed to declare a variable and increment it each time the loop repeated. Less steps isn't always better but a shorter script works well for me in this situation.
FYI: The GetNthRecord is a function that can grab a field value from any record in the current found set.
You may be wondering why I transferred the data from the global xyear field into the variable $year. The answer is simple. Data access from a variable is significantly faster than a global field. Since the year is accessed in the Exit Loop If step, I wanted to increase the speed. In reality, you probably won't notice the difference in speed when creating 365 records. However, it's important to implement best practices whenever possible in order to keep them at the forefront of your thought so when it does matter, you intuitively program with the most efficiency.
Once the script is complete, you will have one record for every day of the specified year. This process should take less than a second to create 365 records (366 for a leap year). Since FileMaker can handle thousands of records with ease, it might be best just to create a decades worth of date records right from the start.
IMPORTANT: Always use the Date function when creating dates. The Date function automatically handles leap years, month and year transitions, and date format differences for different countries.
Month View Creating a month view is a little tricky but one of the most satisfying interface solutions to design. The basic idea is to create 42 portals representing the 42 days on a calendar. Yes, I'm counting correctly. I'm including the days of the week in the previous month before the first day of the current month and the days after the last day of the current month. I like to display these so you can get a full view of the beginning and ending of the month.
Only one relationship is needed to display different dates in each of these 42 portals.
The key fields for the relationship use a custom function to determine the first and last day of the currently selected month Via the global interface fields). This allows the relationship to locate all date records between the two dates.
Users specify the month and year they want to display in the calendar using global fields. I like the month to be specified via a text (i.e. January instead of 1) so as to cut down on the confusion of which month is which number. Therefore, I employ another custom function to convert the month string into a number.
The day_first formula uses the FirstDay custom function to determine the first day of the selected month and then subtracts the day of the week to move the first day to the first spot on the calendar. The day_last formula is a lot easier since the maximum number of days from the next month that can display on any selected month is fourteen days. There's no need to figure out the exact number for each calendar month, just add the maximum number of days. It will become clear later how the extra days at the end of the month are lopped off when you see how the data is displayed on each of the 42 portals.
42 One Row Portals To create the monthly interface, create a portal from the DATES_Monthly relationship on a layout based on the CALENDAR layout. You'll want to mess around with the size of the portal so 42 portals fit evenly on the layout size you desire. Make sure the portal is large enough to accommodate a line at the top for the day of the month and four or five events. Also, the portal should only show a single row and have no scroll bar!
Once you are satisfied with your portal, place the date field from the DATES_Monthly relationship in each portal towards the top. I like mine in the upper right corner but it's up to you and your preferences. I also used a merge field since the date field won't be used for editing or searching. Then format it display the day only using the Data tab in the Inspector.
I like to use the Smart Duplicate feature to multiply my portals and their contents. Once the first portal is just the size you want, duplicate it and without deselecting it, move it with the mouse or the arrow keys (on the keyboard) so it's just on the other side of the first portal. If you haven't deselected the second portal, the next duplicate won't be offset nine by nine points/pixels but the same distance as it was moved from the original duplicated object. You can then duplicate till you have a line of seven portals. Select the seven portals, duplicate them, move them just below the other set of portals and use the Smart Duplicate feature till you have six rows.
If you plan the smart duplication right, you'll never have to create the portals again. Unfortunately, this is never true in real life so plan on doing it several times. For example, did you format your date field correctly? Did you forget to remove some of the formatting from the portal that doesn't go well in your vision for the monthly calendar? Are you happy with the location of the date field in the portal? The point is, you're likely to make a mistake or be dissatisfied and have to start over multiple times.
Instead of starting over with the smart duplicate, you could use the Select Same feature to fix an error to all like objects. The steps to selecting the same object is to select one of the objects and then type Option-Command-A (Macintosh) or Ctrl-Shift-A (Windows). However, this trick only prevents the recreation of the portals and merge field because there are so few objects on the layout. If you try this trick later on in the project, it may be much more complicated, requiring you to use the Object Layering feature. Plan ahead or you could even be stuck creating these portals over and over again as your layout gets more and more complex. Of course, you'll only know the full plan once you are done reading the article so bear with me.
Initial Row Once you have all the portals placed on the layout, you'll need to modify each one individually. In order for each portal to show the correct date from the DATES table, you'll need to change the initial row. Once you are done, each portal should show sequentially numbered initial rows ranging from 1 to 42. Here's an example screen shot of the last day on the first row (Saturday).
Highlighting The next thing I'd like to do is differentiate the days in the currently selected month from the days in the previous and next month. Start by filling all the portals with a transparent fill. That's the easy part. The next step is to provide a way to highlight the portals in the current month, dim the merge field showing dates in the previous and next month and make the current day stand out. Only certain objects can be conditionally formatted and a portal is not one of them. That means we'll need a button.
Draw a button one point/pixel larger on each side than the portal but do not assign a script to the button since it's just going to be used to display fill colors. The idea here is to completely fill the portal with a color so the button needs to be bigger than the portal. However, we also want the button to reside inside the portal. If it's too big then it will not get sucked into the portal and the conditional formatting formulas that follow will not function properly.
FYI: Only certain objects in layout mode can be conditionally formatted. These include fields, buttons, text blocks, charts and web viewers. This explains why a button was chosen instead of a rectangle shape.
The button should have all appearance formatting removed like lines, fills, shadows, hover states, corner radius, etc. via the Appearance tab in the Inspector. That's because this button is going to be used to highlight the current month, highlight the current day and dim the previous and next days. It's not really a button at this point!
Select the button and choose Conditional Formatting from the Format menu. Setup the following conditions in the order shown.
Grey fill for previous and next month: MonthNumber(CALENDAR::xmonth) <> Month(DATES_Monthly::date)
Light Blue fill for current day: Get(CurrentDate) = DATES_Monthly::date
Your final step is to place a copy of each button inside each of the 42 portals and then send the button to the back using the Arrange menu. The button won't go behind the portal since it's contained within the portal. Test to make sure the days highlight properly. Possible issues that could occur include a problem with the formula, the button not residing in the portal or no date records in the DATES table for the specified month.
While we're on the topic of conditional formatting, let's make the date merge fields dim on previous and next months as well as bold on the current day.
Text color of grey for previous and next months: MonthName(DATES_Monthly::date) <> CALENDAR::xmonth
Bold text style for current day Get(CurrentDate) = DATES_Monthly::date
FYI: If multiple conditions are specified for a single object and the format chosen is singular (i.e. fill color), overlapping true conditions will replace the previous formatting.
Creating an Event Creating an event is pretty straightforward. Just ask the user the name, date and time of the event and then create a new record with that information. We'll use a popover to ask the question since it's easy to configure. Add four new global fields to CALENDAR to accommodate the interface.
xname xdate xtime_start xlength
Place these global fields in a popover with a button in the popover that runs the following script.
The script is a standard one for adding records to a related table. Just visit a layout showing records from the related table, add a record, place the values on it using Set Field and then return to the original layout. The date field is used as the key field so the event record relates back to the date record.
It's always nice to initialize the global fields in the popover so they don't have the last value used. Add the following script to run OnObjectEnter on the popover.
Displaying the Event There are many ways to display the events in the portal. You might think ExecuteSQL is a good match since you won't need to create any schema. And, that might be true if you are efficient at writing SQL. I am not so I'm choosing to use the List function. Besides, the List function is easier to program even if you know SQL. In addition, we already have the relationships so there's no need to reduce schema with the use of the ExecuteSQL function.
Since the List function returns multiple field references as a return-separated list, it won't work for our needs. I want two values (name and time) to appear in the portal so I'll need two calculation fields. One calculation will combine the name and time fields in the EVENTS table and the other calculation in the DATES table will use the List function. Here's the first calculation field that combines the event name and start time into a single field in the EVENTS table
It's a little more complicated than you might think due to the time formatting but here's not much that can be done. Once two fields are combined into a single formula, the result is text and can't be formatted as time. BTW, that's a single tab character in-between the quotes.
The second calculation is a simple List function call. While you don't need a relationship to add records to the EVENTS table, one is needed to display them. Create a relationship from DATES_Monthly to a new table occurence based on the EVENTS table called EVENTS_Monthly. Use the "date" field from both tables to complete the relationship.
Place this field in the portal along with some tab formatting and it will display four or five events in most portals. I prefer to have the tab right justified so the name starts at the left side of the portal and the time is shown at the right side of the portal.
Where's the Beef? At this point you should have a complete monthly calendar interface. While I like to provide free content, I also have to pay the bills. If you like where this article is going, I have an entire video series on creating a calendar in FileMaker (see the sample video above). You'll find out how to create a day, week and dynamic list view of events. I'll even delve into visual events like you see in apps like iCal. And don't forget reporting, searching and all the other bells and whistles that go along with a relational calendaring system.
This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!
As always, very nicely done John Mark. I actually had to create a calendar for a client about a year ago and let me say it was not exactly a labor of love. I'm glad I did it for the experience but I would not want to do it again. I only wish your article had been out then - would have saved me a lot of grief. Kudos.