Update: How to Create Calendars in FileMaker with Monday Start of Week

I admit, designing a calendar in FileMaker is an involved process. Nonetheless, it's a common requirement, so I’ll walk you through the process step by step. I'm going to piggyback on our previously published post, but for this calendar, we're going to specify Monday as the start of the week as opposed to Sunday.

Of course, there are several different ways that we can structure a calendar. One option would be to create 42 separate relationships and portals to represent events for each calendar day. Why 42? There are seven days in a week and we need six rows to cover the number of potential weeks in a month, 7 * 6 = 42. Also, you’d have to create 42 fields to store the calendar days that would then be used to set up the relationships between the calendar and event tables. This approach can make managing edits or updates cumbersome for developers because of the volume of records.

Luckily with FileMaker 18, and as early as FileMaker 11, we can use filtered portals to create calendars. There are performance issues to be aware of when applying portal filters, particularly when using related record data as part of filter calculations. However, in this case, we'll be using global variables as our filter criteria so that dramatically decreases the amount of structure needed to create the calendar view. Take a look!

Relationship graph for 42 days

The relationship graph required for the filtered portal technique

Calendar Layout

Users typically expect to view calendars in a grid or chart format, so that's what we'll do. Unlike the calendar we designed previously, our calendar is going to be based on the week starting on Monday. Do note that FileMaker considers Sunday to be the first day of the week.

Let’s take a look at July 1, 2019, which was a Monday, so it falls nicely into the first cell of the first row. After seven days, we start a new week/row and after 31 days we have five rows.

You may be wondering why we need to create six rows instead of five. Let’s take a look at December 2019. December 1 falls on Sunday, the last day of week/row 1. And, the last day of the month December 31, falls on Tuesday, the second day of week/row 6. A month could pass through six weeks if it starts on a weekend. Tricky, right?

Not all of the 42 cells are relevant for a given month. However, we like to include all 42 cells because that allows us to customize the presentation of the calendar. We can gray out the unnecessary cells or we can display the days of the preceding and/or proceeding months.

Pick Your Date

To specify a month, we'll go through the same process outlined in our original post. We choose a date (in the field datePicked), for example, December 5, 2019. To see a calendar for December, we need to figure out the first and last days of the month. The first day of the month is easy; based on datePicked, we use the same month and year and set the day to the first:

Date ( Month ( datePicked ) ; 1 ; Year ( datePicked ) )

Now we have to determine the last day of the month. We'll back into that by finding the first day of the following month and then subtract one from that day:

Date ( Month ( datePicked ) + 1 ; 1 ; Year ( datePicked ) ) - 1

The results are 12/1/2019 and 12/31/2019. Next, we have to figure out how the days in December fall within the grid. We know that the first day of the month falls in week/row 1 so we can use this formula:

Div ( date - 1 ; 7 ) * 7 + 1

If you’d like to look into the logic of the calculation or change the calendar to start on any other day, please read our post about how to find the preceding Sunday or Monday or what have you.

December 1 falls on Sunday and the Monday that starts that week is November 25. We start there and fill in each of the 42 cells using global variables.

$$day01 = 11/25/2019

$$day07 = 12/1/2019

$$day08 = 12/2/2019

$$day36 = 12/30/2019

$$day37 = 12/31/2019

$$day38 = 1/1/2020

$$day42 = 1/5/2020

At most, 37 of the 42 portals contain days for a given month. It's up to you to decide if you want to display the dates for other months or not. For our purposes, we're going to gray out the unnecessary cells.

We use a script to dynamically generate the global values to display the days of the month because who has time to create 42 variables manually?

Let’s take a closer look at the $garbage variable, which is used to generate the global variable and store the value:

Let ([varName = If ( Left ( $varName ; 1) ≠ "$" ; "$" ) & $varName ;varType = GetValue ( FilterValues ( "Text¶Number¶Date¶Time¶Timestamp" ; $varType ) ; 1 ) ;varValue = $varValue ;

f = "Let ( ~varName~ = ~varValue~ ; \"\" )" ;fGetAs = "Let ( ~varName~ = GetAsXX ( ~varValue~ ) ; \"\" )" ;formula = If ( IsEmpty ( $varType ) ; f ; Substitute ( fGetAs ; "XX" ; $varType ) )] ;

EvaluationError ( Evaluate (Substitute (formula ;[ "~varName~" ; varName ] ;[ "~varValue~" ; Quote(varValue) ]) // close Substitute) ) // close Evaluate

) //close Let

This is a complex calculation but fortunately you can copy and paste it between files.

DESIGNING THE CALENDAR LAYOUT

I find designing the overall look and feel of the calendar layout to be the most challenging part. I’ve created enough calendars to have picked up some tricks to share with you.

• Select or create a theme for the calendar layout. I don’t recommend using the Classic theme.
• Style all objects. You can create new styles and save them to the theme or use existing styles. For more information on themes and styles check out our FileMaker Naming Styles blog post. Why use styles? Let's imagine you created the 42 portals and each portal contains a text box displaying the event title. You specify the text color to be green and you create a style accordingly and name it PortalText. Later, you decide you want to change the text color to black. All you have to do is update the saved PortalText style and all of your portal text objects are styled with black text automatically. In earlier versions of FileMaker, before styles were available, we would have to manually select each of the 42 text boxes and change the text color. It was such a tedious process; I still have nightmares.
• Before adding all 42 portals and additional objects, stop and think about how you want the calendar event to display, specifically what information you want to show in the portal row. And then there are a number of other decisions to make – whether or not to display the portal scrollbar, enable or disable alternate portal row state, display the numerical dates in the upper right corner or upper left corner of the cell, create a button to allow users to click to view the related event details, etc. Proper planning saves you time. I tend to focus on one table event occurrence first. When I’m satisfied with the look and feel of the first event occurrence, I copy and paste the objects, align them properly and update the filters to use the specific global variables for that instance. This is my personal preference, but you could manually add each event portal and add all objects individually.

Now that we have our global variables, we can place them as merge text on the layout and arrange them accordingly.

Since we're only going to display the days of the specific month, we apply conditional formatting to the merge field if the cell is empty. Let’s start with <<$$day01>>.

We use portal filters for each event to display the events based on the global variables. Let’s add the portal to show records from the events table by double-clicking on the portal and selecting “Filter portal records” option.Set the filter criteria for the first portal filter:

events: : date = $$day01

The other portals include the same filter criteria; however, you reference the global variable associated with the respective column/row, i.e., $$day02, $$day03, all the way to $$day42.

Let’s take our calendar a step further and add the ability to filter calendar events based on calendar type. This allows us to filter events by different criteria such as holidays, birthdays, personal, etc. We must create a table to store calendar type and also update our relationship graph.

By the way, if we had decided to create 42 separate relationships, we would have to update each of those relationships to include the new calendar type filter.

Finally, we go back to the calendar layout and set up conditional formatting on the title field. I like to specify different color formats based on the calendar type to help users visually distinguish between the various calendar types.

We’ve added all the calendar portals, conditional formatting and filters so we're ready to view the calendar!

It would be nice to allow the user to select the calendar event to display the related event details. I recommend using a Card window to view the event information.

You can do this by using a simple Go to Related Record script step and selecting the “Show in new window” option.

Tip: When closing the Card window, make sure to include a Refresh Window [Flush cached join results] script step to refresh the calendar record. This ensures that the latest and greatest results are displayed.

Feel free to use this filtered portal calendar sample file to create calendars in FileMaker yourself.

Learn more FileMaker platform design tips such as how you use placeholders and Material Design icons.