Linked fields

FileMaker auto-enter capabilities make it possible to create “linked” fields that are directly editable and update each other to keep them in sync.

What I personally like about this technique is they way it works: no buttons to press/scripts to run/events to trigger, but everything “just” changes to keep things linked. To me this makes a very smooth working experience, maybe because it visualizes the internal logic of the application.

Check the sample file I used to prepare two samples I describe.

A simple example

A simplest example would be three fields that describe a chunk of time: Start, End and Duration. Chances are in your application you'll need all the three fields. Before v7 you would have to make one of the fields calculated: for example, you could define the fields so:

or so:

This means one of the fields will always be non-editable.

In FileMaker 7+ you can make all the fields editable and update the values depending on which field has changed.

It helps if we think about what must happen if the user change any of the fields:

If the user changes	Start becomes	End becomes	Duration becomes
Start
End
Duration

First, if the user changes a field directly, the field must assept the change. Thus the main diagonal will always be “same”.

If the user changes	Start becomes	End becomes	Duration becomes
Start	same
End		same
Duration			same

The other rules may vary. We may, for example, respect Start and Duration:

If the user changes	Start becomes	End becomes	Duration becomes
Start	same	Start + Duration	same
End	same	same	End - Start
Duration	same	Start + Duration	same

You can see we never change Start and only change Duration when End is edited directly. Of course, if your application requires different logic you can change the formulas; for example, you might want to respect Start and End:

If the user changes	Start becomes	End becomes	Duration becomes
Start	same	same	End - Start
End	same	same	End - Start
Duration	same	Start + Duration	same

The difference is only in the formulas. Lets put these formulas in auto-enter options.

Since Start never changes, we don't add anything to it. The first statement in other formulas is similar: if the field is changed manually, then we accept the value. To recognize a manual change, we use the Get( ActiveFieldName ) function. I typically define this condition using a separate Let() statement:

Let( changed = Get( ActiveFieldName ) = "End";
  ... )

In this case this may be an overkill, but complex formulas become more readable.

The whole formula for End is that:

Let( changed = Get( ActiveFieldName ) = "End";
  If( changed; End; Start + Duration ) )

And for Duration is that:

Let( changed = Get( ActiveFieldName ) = "Duration";
  If( changed; Duration; End - Start ) )

Here's a movie of the working version:

A more complex example

A more complex sample is taken right from one of applications I made. There was a need to specify who is to do something and “who” can be either “anyone”, or someone selected by their position (“director”), or someone selected by their role in this context (“initiator”, “coordinator” and so on). Typically only one selection is made, but sometimes users may want to select several positions, roles or even both of them.

We made the following widget:

with a rather complex behavior. You can select a position or role or (with the Shift key) any number of them. As you do this, the value list at the left gets updated. The value list can also be changed directly—this is how you can select “anyone”. Choosing “anyone” overrides all other choices in spite of the Shift key state.

The widget is made of 3 linked fields: Performer (the value list of “anyone”, “position” and “role”), Position and Role. Every field is set to auto-enter something and overwrite the existing value:

The formulas are that:

Peformer

Let( [
  changed = Get( ActiveFieldName ) = "Performer";
  anyone is selected = PatternCount( Performer; "anyone" ) ];

  Case( 
    changed and anyone is selected; "Anyone";
    changed; Performer;
    not IsEmpty( Position ) and not IsEmpty( Role ); "position¶role";
    not IsEmpty( Position ); "position";
    not IsEmpty( Role ); "role" ) )

Position

Let( changed = Get( ActiveFieldName ) = "Position";

  If( not changed and ( not Set Contains( Performer; "position" )
      or not IsEmpty( Role ) and not Shift Is Pressed ); "";
    Position ) )

Role

Let( changed = Get( ActiveFieldName ) = "Role";

  If( not changed and ( not Set Contains( Performer; "role" )
      or not IsEmpty( Position ) and not Shift Is Pressed ); "";
    Role ) )

Here the Shift Is Pressed is a custom function that checks whether the Shift key is down; I described it earlier.

The final version of the widget looks like that:

Technorati Tags: FileMaker

Comments

[David Graham]

Are you using a text editor with a FileMaker dictionary or do you manually indent and colorize your code snippets?

[Mikhail Edoshin]

I use SubEthaEdit with a hand-made FileMaker mode. Same for XSLT samples. SEE has a nice option to copy text with highlighted syntax as XHTML. I can share these modes, if someone needs them :)

[David Graham]

I've long dreamed of the day that we will have a fully functional code-writing engine in FileMaker. With your FileMaker mode does it offer the range of features one would expect (e.g., indenting, bracket matching, autocomplete, functions pop-up)?

[Mikhail Edoshin]

Not quite. The editor is relatively simple so it matches brackets and has an auto-complete feature, which I have never used, but doesn't offer anything else. I don't use it to write FileMaker formulas, only to highlight the syntax before adding snippets to the blog, because this looks nice. You can see the mode isn't quite correct; for example, in one of the snippets it highlights the Position field because it misinterprets it for the Position() function.

I use the editor and my XSLT mode to write XSLT.

[Emile]

Mikhail,

This is a tremendously useful technique, though I have two questions:

If you wanted to include the start field in your initial example, because a user may actually change the start value once they read the duration, how would you go about doing so?

And secondly, what exactly is FM doing when it reads:
changed = Get( ActiveFieldName ) = "End"

I understand that you are setting changed to GetActive... but then what happens in the rest? Are you setting changed to "end"? Why not just put changed = "End" ??

Cheers!

[Mikhail Edoshin]

Start field is already included, i.e. the other two fields "know" about it and update when it changes. It doesn't have any auto-enter formula because there's no situation (in these samples) when it may be updated automatically. We can put a dummy calculation there like:

Let( changed = Get( ActiveFieldName ) = "Start";
If( changed, Start,
Start ) )

i.e. if the field is changed, then use the new value, if it isn't use the old value, but since the result in both cases is Start such a complex formula is not necessary.

When FM reads the line

changed = Get( ActiveFieldName ) = "End"

it evaluates it in the following manner:

variable = expression

where variable is "changed" and expression is Get( ActiveFieldName ) = "End". This is a boolean expression and it may evaluate to True or False, which are represented as 1 and 0. You can see that later it's used in this logical sense:

If( changed, "true-expression", "false-expression" )

If we set it to simply:

changed = "End"

the formula will be valid, but it won't work as we need it to, because in this case the "expression" will always be same and we'll have no way to see which field is active.

[Alexander Zueiv]

Good job! The only confusing thing is hardcoded field names - Get( ActiveFieldName ) = "End"... Here is how you can get rid of it using global variables:

End (auto-enter calc, replace existing) =
If ( $$_DURATION_HAS_BEEN_ENTERED ;
Let ( $$_DURATION_HAS_BEEN_ENTERED = "" ; Start + Duration ) ;
Let ( $$_END_HAS_BEEN_ENTERED = 1 ; End )
)

Duration (auto-enter calc, replace existing) =
If ( $$_END_HAS_BEEN_ENTERED ;
Let ( $$_END_HAS_BEEN_ENTERED = "" ; End - Start ) ;
Let ( $$_DURATION_HAS_BEEN_ENTERED = 1 ; Duration )
)

The trick is that FMP first calculates manually modified field.

Enjoy!

Alex_Z

[Mikhail Edoshin]

Thanks, Alex, this is more elegant :) Besides, it makes it possible to write different scenarios depending on which field has changed.

[Serge Lavigne]

Hi Mikhail
I like your "Link Fields", a very interesting approach. I prefer not using a variable. So I rerote the formula in your example.....

//Let( changed = Get( ActiveFieldName ) = "Duration";
// If( changed; Duration; End - Start ) )
If (Get(ActiveFieldName) = "Duration" ; Duration ; End - Start)

It is a bit simpler to understand for a newby.

Regards

Serge

[Mikhail Edoshin]

Yes, this is simpler; I probably should start with this and then show how to use a variable. Thanks :)

[Matt Petrowsky]

Very cool Mikhail! Yet another clear presentation of some great coding. I'm always amazed when I see your stuff and have to hit myself in the forehead, saying "Oh, duh, you've done that in PHP or Perl, why not FileMaker!"

I'm referring to the order of operations regarding the assignment of the changed variable. Yes, a bit difficult for a newbie to understand, but it makes perfect sense! Thanks again for a great tip. If you don't mind, I'll have to give my viewers a sneak peek into this great idea.

[Mikhail Edoshin]

Thanks Matt! Sure, I don't mind; I'm happy you're going to tell your readers about this tip. This means I'm among the best :)

[Guy]

Cool indeed. It helped me solve some problems that desperately needed this. Strange though that it doesn't seem to work with container fields(?), e.g. to create true drag and drop (source field emptying itself). I thought I had it working for a few tries, but then I could never get it to work again... Anyway, a big thanks to all! :-)