Reporting with DayBack Calendar

Customers often want to track stats about the resources they’re scheduling in DayBack. They want to answer questions like…

“How many hours do these assignments represent?”

“What is the total cash value of the confirmed bookings this week?”

[ba-box background=”#98e382″ border=”#4b9b55″ textcolor=”#F7F7F7″]

This kind of reporting is now built into DayBack.  Check out the new Calendar Analytics introduced in July of 2018: measure values across your schedule.

[/ba-box]

Stats like these are easy to add to DayBack and this post describes two different methods for calculating them.

Like many developers, I usually think of using relationships first when deriving date-range stats like these. While that works, I’ll suggest that using find requests is much easier and results in code that requires a lot less maintenance as your requirements change. A description of both approaches follows, along with unlocked example files.

In our examples, we’ll total a new field in the events table called “Cash Value” and count the number of events in a given week.

Overview

This short video shows what we’re trying to accomplish and introduces the two different techniques.

Step-by-step instructions for each technique follow below in case you’d like to add this capability to your copy of DayBack. And here are the unlocked example files for each approach…

• Reporting in DayBack Calendar.zip

Using Relationships

The idea here is to construct a date range relationship that reflect what’s going on in the calendar, and then use the SUM() or COUNT() functions across that relationship to arrive at the stats you want. To do this, you’ll need to create a few new fields in the calendar interface table to capture the date range and resource filters currently used in DayBack. These values are cast to global variables while DayBack operates so you just need unstored fields that reflect these global variables. You’ll see the new fields here in blue:

You’ll see a simple global field there as well called “RangeRefresh”. That’s here because FileMaker won’t know to update the values of our calculated stats just because the global variables change. We need a “tickler” field we can modify which will tell FileMaker that something has changed in the relationship and cause our stats to re-evaluate. (If this technique is new to you, here’s the overview… be sure to read the comments: https://www.teamdf.com/blogs/ditch-those-flush-caches-use-cartesian-join-instead/ )

With those fields in place, you can now make a new instance of your events table and create a new relationship to it from CalendarInterface:

If this is starting to seem like a lot of work: you’re right. Not only aren’t we quite done yet, but the relationship shown above only accounts for date ranges and resource filters. If you add new filters to DayBack–which is one of it’s great strengths–you’d have to revisit these fields and relationships for each new filter, adding them to the relationship criterial above. There are even a few more limitations we’ll discuss in the conclusion as well.

But this technique does work, and once the relationship is done we just have to do two more things.

First we need to calculate the stats we’re interested in, and that means creating two new fields in the CalendarInterafce table and adding them to our calendar layout. These are the fields that actually hold our stats:

Then we need to script the refresh of our relationship as we alluded to above when describing the “tickler” field. We want to trigger this relationship to reevaluate every time the calendar redraws, so we can add just one line to the script “Activate Webviewer”:

And with that we have the totals working as shown in the video at the top of the post. Pretty cool, but definitely more work than it should be. And potentially more error prone and harder to maintain than what follows.

Using Finds

The idea behind using finds is that DayBack Calendar is already assembling the records it needs to show on the calendar–it’s doing that with a find request. So instead of building a parallel method of retrieving those records (building relationships), let’s just get the data right after we’ve assembled our found set of records to show on the calendar. This way, we don’t need to worry about new filters or additional criteria being added to the calendar down the road: we’re getting our stats at the same place in the code where we get our events.

Here’s how it works.

We start by creating summary fields for each value we want to sum or count in our events table. This is exactly what you would do if you were making a standard FileMaker report, so you may already have summary fields like these in your table:

And then we just need a place to put our stats: we won’t be using calc fields for them anymore, but we still need a place for them to show up on the layout. So create one global field for each of the stats you’re tracking:

That’s it in terms of fields and relationships. The rest is done in scripts.

The key to this is to gather our stats at the same place as we gather events, and that means editing the “Event Find” script. We’ll add two lines to clear our totals and then two more right after we’ve checked to make sure events were found. The new lines are highlighted in this screenshot:

That’s almost all there is to it. What you’ve done so far will work locally and on hosted files. But DayBack uses Perform Script On Server (PSOS) to do its date range finds: that means the globals you set in the script above will be set for the server’s session. And they won’t be accessible to the user’s session. The easiest way around this is to change DayBack’s settings so it doesn’t use PSOS. Instructions for doing that are here: turning off PSOS.

(If you want to keep using PSOS, you’d need to add the stats to the script result for “Event Find” and that’s a bit complicated. But it is something SeedCode can do for you as part of an implementation package. By the way, making a change like this to an existing script result payload is exactly the kind of thing that native JSON support will make easier: it will be easier to add new objects to an existing payload without breaking the parsing of what’s already there.)

That’s it!

Notice that in the script above we gather our stats after the last perform find. That means any new filters you add, any hard-coded filters, and any ad-hoc find requests you make have already happened. We’re already in the found set that will be rendered on the calendar. This is the perfect place to sum the case value or count the events. Moreover, if you want to go further and say that, for instance, you only wanted the cash value of weekday events, or of events that were unpaid, you could perform additional finds at this point to constrain the found set as we’ve already recorded the information DayBack needs in line 83. Pretty cool.

Conclusions and Caveats

I’d encourage you to add stats like this to your file as it’s great to see these values change as you balance your schedule. Here are some caveats to keep in mind as you go forward.

Using Relationships

• The biggest issue here is that you have to keep updating relationships and fields as you add new filters to DayBack. That means each filter is represented twice and there’s definitely the possibility that you’ll code one instance slightly different than the other.
• Relationships also have trouble with blank values, as we saw with resources in our movie. The answer is to base the resources part of the relationship on a calculated field that adds some literal string like “blank” to the resources for every record. And then in the left-hand side of the relationship, include the string “blank” if $$sc_ResourceList is blank or includes all your resources. That’s just more work.

Using Finds

• The only real issue here is PSOS: if your find script runs on the server then any global fields set there are “on” the server as well. You need to either turn PSOS off in DayBack or pass your stats back in a script result the same way DayBack passes back the events themselves.
• Aside from that, using finds gets your stats “closer” to the source of the calendar’s request for data: that’s good.

Caveats for Both Methods

There are a couple issues to keep in mind that are specific to DayBack or to calendars in general:

• Status Filters –  In order to keep things as fast as possible, DayBack does not perform new find requests when you apply status filters. So you’ll see in these example files that the total cash value and item count don’t change when status filters are applied. So, if you’d like to include status filters in your stats, simply follow our instructions for adding new filters, add your own filter for status, and the use your own status filter instead of ours. DayBack always performs a new find when your own filters change. You can even hide the original status filters using CSS.
• Month Views – Gathering stats for a month is a little different as the found set DayBack builds to draw a month view always includes a few days from the previous or the following month. If you look at a month view for December 2016, for example, the first week includes Monday the 28th of November. So the thing to remember is that the techniques described here gather stats for the dates shown which will often be more than a discrete month. An easy way around this, if you’re using the Finds method, is to include a constrain find right before you set your stats field to narrow the request down to just the month you’re looking for. Week and Day views don’t have this problem.

Hope you’ve found this useful!