Tag: Get(RecordOpenState)

Level: Intermediate, Version: FM 9 or later

Separation Aggregation Aggravation

Kevin Frank

7 June 2015: This technique has been substantially revised to work with FileMaker 14; see FM 14: Separation Aggregation Aggravation revisited.

FileMaker developers take it for granted that calculated sub-total and total fields will update automatically when portal rows are created, edited or deleted. This becomes problematic when one a) uses the separation model, and b) either intentionally or unintentionally holds the parent and related child records open (uncommitted) while editing, but still expects to see these aggregates update in real time.

7-3-2013 2-57-02 PMToday’s demo file is called aggregates-and-separation, and for demonstration purposes it has interface elements in both the Data file and the Interface file. Normally of course, in a separated solution, you wouldn’t have interface elements in the Data file (since that would defeat the purpose of separation). At the left, we are looking at a newly created parent record in the Data file, with a couple newly created portal rows, and the three aggregate calc fields below the portal are tracking the changes in real time. Continue reading “Separation Aggregation Aggravation” →

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.