Archive | November, 2010

The Last Day of the Month, part 1

Posted on November 30, 2010 by

This seems an appropriate day to post this. At the risk of stating the obvious, the first day of a given month is a very easy date to calculate, because the day number will always be 1. So, assuming we want to dynamically calculate the date for the first day of the current month (whatever that month may happen to be), we can simply plug the values in as follows:

      Let ( x = Get(CurrentDate) ;
         Date ( Month ( x ) ; 1 ; Year ( x ) )
      ) // end let

Wouldn’t it be nice if it were that easy to calculate the date for the last day of the month? The problem of course, is that the last day of the month can be 28, 29, 30 or 31.

You might be tempted to use a Case() statement, and test each month individually, but then you’d have to engage in some calculation gymnastics to accommodate Feb 29th in leap years. Here’s an approach I like because it’s both succinct and bullet proof.

      Let ( x = Get(CurrentDate) ;
         Date ( Month ( x ) + 1 ; 1 ; Year ( x ) ) - 1
      ) // end let

In a nutshell, this tells FileMaker to calculate the date corresponding to the 1st of next month, and then to subtract 1 day from that. It even works in December, because FileMaker is smart enough to convert Date(13;1;2010) to January 1, 2011, and of course if we subtract one day from that we get December 31, 2010.

And here’s a tip I picked up from Geoff Gerhard at Creative Solutions: the calc can be further simplified, by removing the “-1″ from the end, and changing the day number to zero, which FileMaker is smart enough to interpret as “the day before the first of the month”.

      Let ( x = Get(CurrentDate) ;
         Date ( Month ( x ) + 1 ; 0 ; Year ( x ) )
      ) // end let

Incidentally, I’ve used Get(CurrentDate) in these examples, and that’s fine for a single-user system. For a multi-user system, it’s a good idea to instead use…

      GetAsDate ( Get ( CurrentHostTimestamp ) )

…which ensures your users will all be on the same page, or date at any rate, regardless of what date their computer thinks it is. Time can be calculated in a similar manner:

      GetAsTime ( Get ( CurrentHostTimestamp ) )

And the nice thing about this is that if you open the file single-user, the above calcs still work since your computer is considered to be the host. That’s what I call all gain and no pain.

Color Coding in the Relationships Graph

Posted on November 29, 2010 by

FileMaker developers have philosophical differences of opinion on many issues great and small, and one of them is a) whether it’s worthwhile to add color to table occurrences (TOs) on the Relationships Graph (RG), and b) if so, what guiding principle(s) one should use.

Needless to say, I have some opinions on the subject, and here they are.

If you’re working on a simple project, it may not be worth the trouble. But let’s assume you’re working on a complex project, or that you’re working as part of a team (and if so, the project will almost by definition be complex).

In either of those latter cases, I believe that color coding is well worth the effort. I have seen various schemes employed, but to cut to the chase, the one that makes the most sense to me is to color code TOs according to their underlying base table.

In the example above, which is a small fragment of a complex project, I can quickly zoom in on the TO I want without actually doing much reading, akin to the way those of us who drive automobiles in the U.S. automatically know to stop when we see a red octagonal sign.

And I can sort the TOs as I wish (in this case by function), rather than grouping TOs from the same table together, as I might feel inclined to do if I weren’t using color coding.

Bottom line: color coding facilitates team development, helps me understand my solutions better, and enhances my productivity.

A Sweet Little ERD Tool for Windows

Posted on November 28, 2010 by

I have been on a multi-year quest to find a tool that would allow me to easily generate ERDs (entity-relationship diagrams) on the Windows platform. Having tried and been disappointed by a large number of free and paid products, the other day I stumbled on one that is easy to use and is 100% free, called Dia (http://dia-installer.de).

According to the online help…

Dia is an application for creating technical diagrams. Its interface and features are loosely patterned after the Windows program Visio. Features of Dia include multiple-page printing, export to many formats (EPS, SVG, CGM and PNG), and the ability to use custom shapes created by the user as simple XML descriptions. Dia is useful for drawing UML diagrams, network maps, and flowcharts.

One of my favorite things about Dia is how intuitive the interface is. I was able to produce the following in a few minutes without consulting the online help at all.

Note that I’ve chosen “Database” from a drop down menu of diagram types, and as a result, three diagram tools have appeared. I can use the leftmost one to insert tables into the work area.

What’s interesting here is that the four tables represented above aren’t just boxes on a screen. They are “table objects”, and double clicking on them brings up editable properties.

…so, in a nutshell, all you need to do is:

  1. throw some tables onto the work area
  2. specify key fields and attributes
  3. draw lines to connect the tables via the appropriate key fields

…and you’ve built a professional looking ERD.

Hint: set the zoom level to 50% under Page Setup… otherwise a simple diagram will require multiple pages to print.

Ranking Entries in a Summary Report

Posted on November 27, 2010 by

Demo file: 2010-11-21-count-unique.zip (requires FM 10 or later)

Yesterday we looked at a simple method to flag unique entries in a found set. This time, we’re going to look at an additional use for this technique, using the same data set and demo file as last time.

As you may recall, we have a simple table of sales data, and previously we produced a summary report sorted by salesperson, but reordered by total sales, so that the top performing salespeople appeared at the top of the report.

Now it turns out that ranking the sales people isn’t quite as easy as you might expect. You might be tempted to try this:

But it doesn’t work, because @@ indicates the actual record number, and since we’re summarizing multiple records, we end up with:

…which is no good at all. So, we’re going to have to remove the kid gloves, roll up our sleeves, and show FileMaker who’s boss. And believe it or not, to make this happen, we’re going to use the flag_unique field that we discussed last time. Let’s take a look at our raw sales data. Here are just a few records, sorted by salesperson.

If only we could produce a running total of flag_unique… hmm… well, why not, let’s give it a try. What happens if we define a summary field, s_running_count_of_flag unique, like this?

Now what does our data look like? You know what? That’s going to work.

And here’s what our report looks like.

Okay, that’s all well and good, but what if we want to group first by zone, and then rank the salespeople within each zone? We can design a new report, summarized first by zone, and then by salesperson…

… and we can sort it first by zone and then by salesperson, but will our salesperson ranking do what we want?

Not yet. Susan should be #1, not #7; we need the ranking to restart for each zone. Fortunately, there is a way, and it doesn’t require defining a new field… we can just tweak the one we already have. Let’s revisit the definition of s_running_count_of_flag unique, and this time we’re going to check the “restart summary for each sorted group” box.

When that box has been checked, we then need to specify zone as the sort field (this is also sometimes referred to as the “break” field).

And while we’re mucking about, there’s something else about this new report that could use some tweaking. Take a look at the Zone column… we don’t really need to see the zone name repeated over and over again, do we? Let’s go into layout mode and apply some conditional formatting to the zone field.

What we’re saying is: only show the zone name if we’re on the first salesperson record of a given group; otherwise make the text color white (i.e., invisible). And here’s our final report, sorted by zone and by salesperson, with rankings correctly restarting for each zone.

Darn that looks nice, if I do say so myself. Believe it or not, there are some other cool tricks we can do with flag_unique, but we’re going to save those for another day.

s_running_count_of_flag uniques_running_count_of_flag unique

Identifying Unique Records

Posted on November 26, 2010 by

[Update 15 May 2011: see this posting for additional information about this technique.]

Demo file: 2010-11-21-count-unique.zip (requires FM 10 or later)

A question that comes up regularly on various FileMaker forums is some variation on “I have a table of sales data for my organization. For a given found set within that table, it’s easy to produce a report grouped by salesperson showing number of sales, total sales amount, etc…

…and at the bottom of the report, I can easily display grand totals for number of sales and total sales amount…


…but how can I show a count of my sales people?”

There are various ways to solve this problem, and it’s a problem worth solving, because once you have this technique under your belt, you will find plenty of uses for it, above and beyond simply counting group members. I’m going to focus on what I believe is the simplest solution. I first saw this solution in Ray Cologon’s FileMaker 10 Bible, and more recently was reminded of it in a posting by Mikhail Edoshin on the FM Experts list.

One of the reasons I like this solution, is that it doesn’t rely on knowing anything at all about the found set of records. It works with any found set, and as long as that found set is sorted, it works. And at the risk of stating the obvious, the real challenge here is to identify the unique records, right? Because once they’ve been indentified, or as many developers like to say, “flagged”, counting them will be child’s play. We’ll just point a summary field at the flag field, and voila, problem solved.

So, we’re going to define new field, “flag_unique”, which will show a 1 for the first instance of a given saleperson; otherwise it will show nothing.  Incidentally, some developers feel strongly that there should be a zero rather than nothing for the “false” case. Generally speaking if I only care about the “true” case, I use nothing (i.e., "") as the false case. First, it makes it easier to see the data I care about, and second, when it comes time to aggregate these values, I can use a summary count field to accomplish this. Otherwise (if I were using zeros and ones), I would have to use a summary total field. Like many things in the FileMaker world, it ultimately comes down to personal preference.

Here is the definition of flag_unique, which is an unstored calculation field with a number result:

Let ( x = GetNthRecord ( salesperson ; Get(RecordNumber) - 1 ) ;

If ( salesperson <> x ; 1 ; "" )

)   //   end let

In a nutshell, this calc compares the salesperson in the current record with the  corresponding value in the previous record, and returns a 1 if they are different. This works because we’ve sorted on the salesperson field.

The only remaining task is to define a summary field as the count of flag_unique, and we can now determine how many sales people there are in our found set.

And above, highlighted in a shade of green a certain generation of reader will associate with Mr. Yuk, is our salesperson count. We can also use the salesperson count to help determine the number at the very bottom of the report: the average total per salesperson.

Common sense says it’s…

total sales / number of salespeopole

…and since we’ve already got summary fields containing both those values, we can simply define a calculated number field, average_total_per_salesperson as:

s_amount / s_running_count_of_flag_unique

Incidentally, the values in yellow a) require a bit of work to generate and b) build on the technique we’ve been discussing. They will be addressed in a subsequent posting.

I said earlier that this report was sorted by salesperson, yet when you look at the above example, you may notice that the report has been “re-ordered” by descending total sales. On the off chance you’re not familiar with how this is done, here are the sort settings.


For simplicity’s sake, this example has used the salesperson’s first name. In a real world situation, you might want to see the data sorted by the salesperson’s last name and first name,  and in a large organization, you might even have more than one salesperson with the same name, so you’d want to sort by:

name_last
name_first
id_salesperson

…and the definition of flag_unique would instead be:

Let ( x = GetNthRecord ( id_salesperson ; Get(RecordNumber) - 1 ) ;

If ( id_salesperson <> x ; 1 ; "" )

)   //   end let

Of course in the case of multiple salespeople with the same name, you’d also want to include some other information in your report to differentiate between them.

Note: the material discussed in this post applies to FileMaker 8 and later. However, the demo file requires FM 10 or later, since it is designed to display summary reports in browse mode.

Selective Modification Timestamp

Posted on November 25, 2010 by

Demo file: 2010-11-25-selective-modification-timestamp.zip (requires FM 8 or later)

Have you ever wished for a modification timestamp that would only update if a user manually edited a field? In other words, a timestamp that would not update based on scripted actions, or non-field-based activity like creating, duplicating or importing records? This can be achieved in a variety of ways, but the technique here uses a simple auto-enter calc, which I was introduced to by Nick Orr at Goya Pty Ltd.

Define a timestamp field, ts_mod_selective, with this auto-enter syntax:

Let ( [
   trigger = GetField ( "" ) ; // note the innovative use of GetField
   ros = Get ( RecordOpenState ) ;
   ies = IsEmpty ( Get ( ScriptName ) ) ;
   ts = Get ( CurrentHostTimeStamp )
] ;
   Case (
      ros = 1 ; "" ;
      ros = 2 and ies = 1 ; ts ;
      ts_mod_selective // for FM 9 or later, we could use Self
                     // instead of specifying the field name
   )
) // end let

Note: if you just want the date or time component, you can enclose all of the above in GetAsDate() or GetAsTime(). And of course, this technique can easily be adapted to work with modification account name as well.