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.

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.

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

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

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

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.