Write a post
Published Jul 14, 2015Last updated Feb 09, 2017

Adding Auto-cleanup to Multiple Fields

When defining fields in FileMaker Pro, it is always possible to write some cleanup code as part of the field's auto-calc definition. However, if you need to do it for a large number of fields, or if you want to add the functionality to an existing set of field definitions, this may not always be practical. In which case, a generic trigger based cleanup, per field, may be the answer.

Use a trigger script instead

Say you have a layout with 40+ fields on it, and you need to cleanup the data entry for it; people are cutting and pasting into the fields, and making a mess. The trick is to attach a script to all the fields in one go, probably on the OnObjectValidate trigger.

Create a new script, call it something like "Field Cleanup Trigger Script" and add the following script steps:

Set Variable [$rep; Value:Get ( ActiveRepetitionNumber )]

Set Variable [$fieldName; Value:If(
  $portalRow ;
    Get ( ActiveFieldTableName ) & "::" & Get ( ActiveFieldName );
    GetFieldName( Evaluate( Get( ActiveFieldName ) ) )
  ) & "[" & $rep & "]"]

Set Variable [$oldValue; Value:GetField( $fieldName )]

Set Variable [$newValue; Value:Trim(TextFontRemove( $oldValue ) )]

Set Field By Name [$fieldName; $newValue]

Script explanation

Steps 1 & 2 get the field repetition we might be in, and the fully qualified name of the field itself. This means we can write the script without knowing the field(s) we'll be working on in advance.

Step 3 saves the old value into a variable.

Step 4 cleans up the value; Here, I just want to get rid of any extraneous fonts, and spaces at the beginning or end of the field, but keep the styles (bold, italic, underline etc.) as well as any returns in the body of the text.

Step 5 just puts the value back into the field.

Getting rid of trailing returns

If the cleanup in Step 4 isn't strong enough for your tastes, you can beef it up to remove prefixed and postfixed returns using the following calculation:

 Trim(TextFontRemove(Let(
    [
        stripped = Substitute ( $oldValue ; "ΒΆ" ; "" );
        firstChar = Left ( stripped ; 1 );
        lastChar = Right ( stripped ; 1 );
        fcPos = Position ( $oldValue ; firstChar ; 0 ; 1 );
        lcPos = Position ( $oldValue ; lastChar ; 0 ; PatternCount ( $oldValue ; lastChar ) )
    ];

    Middle ( $oldValue; fcPos; lcPos - fcPos + 1 )
)))

Essentially, this calculation trims out all the returns from the value; it then pulls out the first and last non-return characters of the value; it calculates the positions of these characters in the original value; finally it returns the appropriately trimmed string.

Adding the triggers

Having created the script, go to your layout, switch to layout mode, and select the fields you want to apply the cleanup to. From the Format menu, choose Set Script Triggers..., check the box next to OnObjectValidate, and select your new "Field Cleanup Trigger Script" as the script that gets called. Click OK to close the dialog, and you're done.

Back in Browse Mode, you should find that entering text into any of the fields you've modified causes the script to trigger, and cleanup the values for you.

This technique is useful in many situations; it simplifies the field definitions for a table, and provides the appropriate functionality with the minimum of side-effects.

Discover and read more posts from Richard Dyce
get started
Enjoy this post?

Leave a like and comment for Richard