Tag: Get(CurrentHostTimestamp)

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

The Last Day of the Month, part 1

Kevin Frank

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: Beginner, Version: FM 8 or later

Selective Modification Timestamp

Kevin Frank

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.