SubstituteManyWithCalculation ( _text ; _list ; _varName ; _expression )

Substitutes many terms in a text and apply a calculation to them.

Average rating: 4.2 (34 votes) Log in to vote

Fabrice Nordmann - Show more from this author
1-more-thing
https://www.1-more-thing.com

Sample input:

SubstituteManyWithCalculation ( "abcdef" ; "a¶c¶e" ; "_item" ; "TextStyleAdd ( _item ; bold )" )

Sample output:

"abcdef" where a, c, and e are bold

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*
SubstituteManyWithCalculation ( _text ; _list ; _varName ; _expression )

by Fabrice Nordmann

v1, May 2007

Substitutes every item of _list in _text, while applying a calculation to the item.

e.g.

SubstituteManyWithCalculation ( "abcdef" ; "a¶c¶e" ; "_item" ; "TextStyleAdd ( _item ; bold )" )

will return "abcdef" where a, c, and e are bold.


Parameters :
_text: the text searched
_list: the list of items to substitute
_varName: just the way you want to call an item in the _expression parameter. In the above example, it is "_item", but "SnowWhite" would be fine, since it not elsewhere in the calculation.
_expression: a calculation to be evaluated, generally using the varName.

additionally, a counter is incremented, so that it is possible to differenciate every line calculation :
$$cf_SMWC_counter


*/


Let ([
    _exit = Case ( IsEmpty ( Substitute ( Trim ( _list ) ; ¶ ; "" )) ; 1 )
  ; $result = Case ( _exit ; $$cf_SMWC ; "" )                                           // the result is put here only on last recursion
  ; $$cf_SMWC = Case ( _exit ; "" ; $$cf_SMWC )                                    // initialise $$
  ; _item = GetValue ( _list ; 1 )                                                             // the search string
  ; _exp = Substitute ( _expression ; _varName ; "\"" & _item & "\"" )     // the calculation to be evaluated
  ; $$cf_SMWC_counter =                                                                     // a counter, in case we want to use it in the calculation
         Case ( _exit ; "" ;
               Case ( PatternCount ( _text ; _item ) ; $$cf_SMWC_counter + 1 ; $$cf_SMWC_counter )
         )
  ; _Error = EvaluationError ( Evaluate ( _exp ))                                     // an Evaluation Eror code
];
Case(
    _Error ; TextStyleAdd ( TextColor ( "Evaluation error #" & _Error & " in formula" ; RGB ( 255 ; 0 ; 0 )) ; Bold ) & 
        SubstituteManyWithCalculation ( "" ; "" ; "" ; "" )                            // initialise variables
  ; not _exit ;
    Let ( $$cf_SMWC =
       Substitute (
           Case ( IsEmpty ( $$cf_SMWC ) ; _text ; $$cf_SMWC )
         ; _item
         ; Evaluate ( _exp )
       ) // substitute
    ; SubstituteManyWithCalculation ( _text; RightValues ( _list ; ValueCount ( _list ) - 1 ) ; _varName ; _expression )
    ) // Let
; $result 
) // Case
) // Let

Substitutes every item of _list in _text, while applying a calculation to the item.

UPDATE : I would now recommend the use of CustomList, by Agnès Barouh : http://www.briandunning.com/cf/747

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

SubstituteManyWithCalculation ( _text ; _list ; _varName ; _expression )

Comments

Support this website.

Support this website.