General, Level: Beginner, Version: FM 8 or later

The Last Day of the Month, part 2

Yesterday we explored a method to calculate the last day of the month, and by way of introduction I said,

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.

This time around let’s look at that method, and those alleged “gymnastics”. They turn out to not be terribly convoluted; in fact, the most time-consuming part of this could be simply defining the rules for what constitutes a leap year.

But first let’s pretend there’s no such thing as a leap year or a leap day. In that case we could write our calculation as follows:

   Let ( [
      theDate = Get ( CurrentDate ) ;
      monthName = MonthName ( theDate ) ;
      yearNum = Year ( theDate )
   ] ;
   Case (
      monthName = "January" ; Date ( 1 ; 31 ; yearNum ) ;
      monthName = "February" ; Date ( 2 ; 28 ; yearNum ) ;
      <etc.>

Okay, that was a pleasant interlude, but now back to reality. Superficially, it appears that all years evenly divisible by 4 are leap years, but it’s a bit more complicated than that. A year is a leap year when:

  1. it is evenly divisible by 400
    or
  2. it is evenly divisible by 4 but not evenly divisible by 100

It’s important to know all the rules, because we don’t want our database breaking on February 29th in the year 2400, do we?

So how do we determine “even divisibility”? We do so by using the Mod() function, which returns the remainder when you divide one number by another. If the result of the Mod() operation is zero, then we know the first number is evenly divisible by the second.

For example, Mod ( 2000 ; 400 ) = 0, which passes test #1 above, and tells us that the year 2000 was a leap year. The year 1900 cannot pass either of the above tests, which tells us it was not a leap year. Bearing in mind that FileMaker uses calculation “short-circuiting” (stops calculating as soon as one of its logical tests evaluate as true), here’s a calc that returns a 1 for leap years and otherwise returns a zero:

   Let ( yearNum = Year ( Get ( CurrentDate ) ) ;
      Case (
         Mod ( yearNum ; 400 ) = 0 ; 1 ;
         Mod ( yearNum ; 100 ) = 0 ; 0 ;
         Mod ( yearNum ; 4 ) = 0 ; 1 ;
         0
      )
   )   //   end let

Okay, now that we’ve gotten to the heart of the matter, we can put it all together and write our calculation.

   Let ( [
      theDate = Get ( CurrentDate ) ;
      monthName = MonthName ( theDate ) ;
      yearNum = Year ( theDate ) ;
      possibleLeapDay =
         Case (
            Mod ( yearNum ; 400 ) = 0 ; 1 ;
            Mod ( yearNum ; 100 ) = 0 ; 0 ;
            Mod ( yearNum ; 4 ) = 0 ; 1 ;
            0
         )
   ] ;
   Case (
      monthName = "January" ; Date ( 1 ; 31 ; yearNum ) ;
      monthName = "February" ; Date ( 2 ; 28 + possibleLeapDay ; yearNum ) ;
      monthName = "March" ; Date ( 3 ; 31 ; yearNum ) ;
      monthName = "April" ; Date ( 4 ; 30 ; yearNum ) ;
      monthName = "May" ; Date ( 5 ; 31 ; yearNum ) ;
      monthName = "June" ; Date ( 6 ; 30 ; yearNum ) ;
      monthName = "July" ; Date ( 7 ; 31 ; yearNum ) ;
      monthName = "August" ; Date ( 8 ; 31 ; yearNum ) ;
      monthName = "September" ; Date ( 9 ; 30 ; yearNum ) ;
      monthName = "October" ; Date ( 10 ; 31 ; yearNum ) ;
      monthName = "November" ; Date ( 11 ; 30 ; yearNum ) ;
      monthName = "December" ; Date ( 12 ; 31 ; yearNum )
   )  
   )   //   end let

One of the nice things about yesterday’s elegant little calculation was that we didn’t have to think about leap year at all. We let the FileMaker calculation engine worry about that for us. But there’s something very different I like about today’s calculation… it’s supremely readable. It’s not elegant, and it’s not succinct, but it gets the job done. And that’s one of my favorite things about FileMaker: there is rarely only one way to solve a problem.

General, Level: Beginner, Version: FM 8 or later

The Last Day of the Month, part 1

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.

General, Level: Any, Version: FM 8 or later

Color Coding in the Relationships Graph

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.

Level: Any, Windows

A Sweet Little ERD Tool for Windows

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 scaling like so under Page Setup if you want your ERD to always fill a single page; otherwise it will likely require multiple pages to print.

2017-07-16_124329

General, Level: Intermediate, Version: FM 10 or later

Ranking Entries in a Summary Report

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.

Continue reading “Ranking Entries in a Summary Report”

General, Level: Intermediate, Version: FM 10 or later

Identifying Unique Records

[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…


Continue reading “Identifying Unique Records”

General, Level: Beginner, Version: FM 8 or later

Selective Modification Timestamp

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.