Calendar View

I’m going to show you how to create a calendar view, like this:

A Ffenics aspect laid out as a calendar

We’re all familiar with this type of layout, and see calendars in Outlook, on our smart phones, and desktops, and even the odd printed one on the kitchen wall!

Here’s the Windows one from my XP desktop, showing the same month:

The standard XP calendar

Calendars may have the following elements:

  1. They are laid out in columns of days of the week. Each month shows the same left-hand start day of the week (Monday in this case).
  2. Optionally, as in both my and Windows version, they only show the days that actually belong to the current month, so there are ‘gaps’ at the beginning and end for the days of the previous and next month.
  3. They are laid out in up to 6 rows, depending on how the days at the start and the end lie. This allows for months like this:

    Most months will use five rows, and February can very occasionally use just four.
  4. Computerised versions usually highlight the current date.

All these and more can be achieved with Ffenics and DataEase.

I’ve added a field to show a count of ‘items’ that ‘occur’ on a given date. For example, this could be a count of appointments each day.

I also have a set of buttons to navigate to other months, and to jump straight back to the current month.

Make A Date

This calendar will cover any date from Jan 1776 (the earliest date DE-Ff can cope with), to December 9993 (the latest):

That’s a gap of over 3 million days! So do I have 3 million date records hanging around in the background? You bet I don’t!

In fact, I only use 42. That is, seven days across times six weeks down.

I’m using several different techniques to bring this all together, of which perhaps the most important involves a set of virtual fields that allow me to handle that enormous date range.

Oh, and my infamous Looper dataset.

In case you’ve not heard me go on about Looper before, here’s the elevator pitch. Looper is a set of integers in order, in a field I call LoopNo. Because they are in order, I can use each Looper record to stand in for a number of situations where the next value is ‘one’ more than the previous one – including letters of the alphabet, time slots and dates.

So to get a set of 42 days from today, all I need is a virtual derived as:

Current date + ( LoopNo – 1 ) 

That bit, I hope, is easy and obvious. The problems I have to solve now, though, are:

  1. How to start these 42 records, not from today’s date, or even from the first of the month, but from the first Monday BEFORE the month started (or the first day of the month if that is a Monday).
  2. How to be able to change the ‘current date’ to cover a different month and year.

The first problem requires a set of virtual date fields. Date manipulation can be a bit clumsy, so a set of fields lets me use some of them like functions, where I can plug the result from one field’s more complicated derivation as a single value into the derivation of the next.

For the second, I use the get/setglobal function pair to store a ‘marker’ date. For no particular reason, I opted for global 200, and derive my place marker date in a field called vCurrDateOrG200 thus:

if ( getglobal ( 200 ) = blank , current date , 
      date ( midc ( getglobal ( 200 ) , 4 , 2 ) ,
             midc ( getglobal ( 200 ) , 1 , 2 ) ,
             midc ( getglobal ( 200 ) , 7 , 4 ) ) )

So if the value has not been set, go with today’s date, otherwise use global 200.

Note that because the get/setglobal storage is in a text string, I have to reconstruct it as a valid date.

This now makes it easy to work out the date of the first of the month, in my vFirstMonth field:

date ( month ( vCurrDateOrG200 ) , 1 , year ( vCurrDateOrG200 ) )

And this derivation for vFirstMonday completes the set of calculations to work out the start date for my 42 records:

vFirstMonth - ( weekday ( vFirstMonth ) - 1 )

Finally, I can get the actual date for each record in vCalendarDate with a variation of the first derivation I mentioned above:

vFirstMonday + ( LoopNo - 1 )

Note that, for the first three virtuals (vCurrDateOrG200, vFirstMonth and vFirstMonday), each record will have the same value. It is only the final virtual that creates different dates.


So I need now to display the data 7 records across and 6 rows down. I’d also like a single value to show the month and year for the calendar ‘page’, which suggests that the actual calendar should be a subform.

But a subform of what? Well, I use Looper again! I create a relationship from Looper to itself, with NO match fields, as shown here:

I will now add vYearCurrDate, to show the month and year, using this derivation:

concat ( firstc ( spellmonth ( month ( vCurrDateOrG200 ) ) , 
if ( month ( vCurrDateOrG200 ) between 6 to 7 or
month ( vCurrDateOrG200 ) = 9 , 4 , 3 ) ) , " " , year ( vCurrDateOrG200 ) )

The ‘if’ statement lets me choose the first four characters for the months June, July and September, as this reads better.

What’s On This Date

I’ll quickly look at how the items for each date are being selected.

In my example I have a form called Appointment, with an AppointmentDate field.

I can then create a relationship from Looper to Appointment, matching on vCalendarDate and AppointmentDate:

I can use a virtual value because I will always be matching from the one Looper (one record per date) side to the many Appointments for that date. AppointmentDate needs to be indexed.

At this point, you should be aware that this causes 42 separate searches of the related SeeAppointments data, and that you should play with larger sets of data over your network to determine whether performance will be acceptable. Contact me directly if you need more information on this.

Hide the Day

You may have wondered how I get the ‘gaps’ at the beginning and end of the calendar range for each month. This involves another virtual, and a bit of scripting.

A virtual on Looper called vCalMonthIsCurrMonth is derived:

if ( month ( vCurrDateOrG200 ) = month ( vCalendarDate ) , "Y" , "N" )

This is included on the subform, but set to ’hide’. I can still script against its value, though, with a valueloaded script that reads:

if vCalMonthIsCurrMonth.value = "N" then 
  parent.hide () .
else () .

The parent of a field is a record object. When we hide the record, we hide all the fields and other objects on it.

Current Date

I'm also highlighting the current date, if it falls during the month on display.

Another flag virtual field vIsToday indicates if the date is today:

if ( vCalendarDate = current date , "Y" , "N" )

And another bit of scripting colours the background to highlight that date on the calendar.

Here we hit a small issue, and it is one that is difficult to explain if you don’t know anything about object oriented programming.

All DE-Ff objects have the ‘parent’ property that points to the object that ‘contains’ them. Each field is on a record object, each record on a form object, and so on up to the main form object.

A visual object on a DE-Ff document inherits from a class called VObject. If you open up the script editor, you’ll see VObject in the list of classes, and if you highlight it, you’ll see its set of properties and methods:

These are properties and methods that all DE-Ff visual objects have in common. Note that the list does NOT contain anything to do with fill, but that the methods do have both show and hide.

As I’ve just described, a parent can be a number of different object types. In order to do this, the code treats the parent as the more generic VObject, and not the specific sub-class that was derived from this base class.

Accordingly, I can reference the common properties and methods, such as ‘show’, but not anything that belongs to the sub-class, such as ‘fill’.

If you’re still with me, congratulations to both of us, especially me for having described it in a way that you can follow. More likely I have failed in that mission, so please forgive me and pretend you understand!

Anyway, that’s a long way of explaining why I have put a particular rectangle object behind the record. I can now add a ValueLoaded script for my hidden vIsToday:

define "tTodayRed"      number . 
define "tTodayGreen" number .
define "tTodayBlue" number .

define "tOtherRed" number .
define "tOtherGreen" number .
define "tOtherBlue" number .

tTodayRed := 183 .
tTodayGreen := 55 .
tTodayBlue := 0 .

tOtherRed := 174 .
tOtherGreen := 196 .
tOtherBlue := 124 .
if vIsToday.value = "Y" then
  rectToday.Fill.Color.Red := tTodayRed .
  rectToday.Fill.Color.Green := tTodayGreen .
  rectToday.Fill.Color.Blue := tTodayBlue .

  vDayMonth.Fill.Color.Red := tTodayRed .
  vDayMonth.Fill.Color.Green := tTodayGreen .
  vDayMonth.Fill.Color.Blue := tTodayBlue .

  rectToday.Fill.Color.Red := tOtherRed .
  rectToday.Fill.Color.Green := tOtherGreen .
  rectToday.Fill.Color.Blue := tOtherBlue .

  vDayMonth.Fill.Color.Red := tOtherRed .
  vDayMonth.Fill.Color.Green := tOtherGreen .
  vDayMonth.Fill.Color.Blue := tOtherBlue .
end () .

Oh, I forgot to mention one other utility field: vDayMonth. This simply derives the day of the month, from 1-31, for the date. This needs a fill on the document, because without it, it appears in the same colour as the form background, not the record, for some reason. Therefore, I also need to change its fill colours.

See that I can use the call at the very end. If you show the parent, you refresh all the child objects at the same time, so it’s a useful shortcut.

Reeling in the Years

The final touch is the set of buttons that change which month is displayed.

In my version, these move you forward or backward a month or a year, or jump you straight back to the current month.

The current month ‘Now’ button’s clicked script reads:

define "t" number . 
t := setglobal ( 200 , concat ( "" , current extended date ) ) .
t := clearselectionfilter () .

ClearSelectionFilter basically will refresh the data, including the count of items.

For the other buttons, the manipulation is a bit more complex. Here’s the ‘next month’ clicked script:

define "tDate" date .
define "tDateStr" text 255 .
define "t" number .

if getglobal ( 200 ) = blank then
  tDateStr := concat ( "" , current extended date ) .
  tDateStr := getglobal ( 200 ) .
tDate := date ( 1 * ( midc ( tDateStr , 4 , 2 ) ) + 1 ,
                midc ( tDateStr , 1 , 2 ) ,
                midc ( tDateStr , 7 , 4 ) ) .
t := setglobal ( 200 , concat ( "" , tDate ) ) .
t := clearselectionfilter () .

As previously mentioned, because the get/setglobals store text values, we have to convert to and from a date.

All the other three ‘change month’ buttons work on variations on the above script.

Finishing Details

I set the document properties to prevent data-entry, and add a filter to the AllLoopers subform to restrict the data traffic to the first 42 Looper records. You may like to include all the appointments within the calendar as a subform, but just like a real calendar, there is not a lot of space, especially on our typically landscape screens. There is a number of other ways to show the detail of the appointments, and I show a few ideas to the demo. In the meantime, look at your iPhone or Outlook calendar for ideas!

And there’s a lot more filtering options – such as for a specific employee – that could be included here, and which again I will leave to your imagination.