CleanUp Via Triggers

Friday 4th April, 2014

It may not always be practicable to write the cleanup code into a field’s auto-calc definition. In which case, a trigger based cleanup, per field, may be the answer.

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 the fields in one go, probably on the OnObjectValidate trigger. Here’s the script to call:

Set Variable [$portalRow; Value:Get ( ActivePortalRowNumber )]

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( TrimReturns( TextFontRemove( $oldValue ) ) )]

Set Field By Name [$fieldName; $newValue]

It works by identifying which field (repetition, portalrow) you’re in, and then setting that field with the cleaned-up value. It works for any field on the layout (provided you have write permission of course!)

Here, I just want to get rid of any extraneous fonts, and pesky returns 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.

In case you’re wondering, the function is just the Kieren MacMillan’s TrimReturns() from Brian Dunning’s custom function site without the debugging variable defns:

/* TrimReturns( text ) */
Let(
    [
	    stripped = Substitute ( text ; ‚Äú∂‚Äù ; ‚Äú‚Äù );
	    firstChar = Left ( stripped ; 1 );
	    lastChar = Right ( stripped ; 1 );
	    fcPos = Position ( text ; firstChar ; 0 ; 1 );
	    lcPos = Position ( text ; lastChar ; 0 ; PatternCount ( text ; lastChar ) )
    ];

Middle ( text; fcPos; lcPos - fcPos + 1 )
)