Brian Dunning's FileMaker Custom Functions

DistributeExpression ( Expression ; Variable ; Values ; Separator ; Eval )

This function will apply a specified expression to any list of values, one at a time.

  Average rating: 4.3 (18 votes) Log in to vote

Travis Preister   Travis Preister

Share on Facebook Share on Twitter

  Sample input:
DistributeExpression ( "Lower ( x )" ; "x" ; "Apple¶B¶3" ; "¶" ; 1 )
  Sample output:
apple¶b¶3

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

This function will apply a specified expression to any list of values, one at a time.

Parameters:

1. Expression (text): The calculation we want to apply to each of the Values.
2. Variable (text): A string of characters from inside Expression.
3. Values (text): A list of values (separated by the ¶ character), each of which should be run inside Expression.
4. Separator (text): A string of characters to use between each Expression.
5. Eval (boolean): When true, the output of DistributeExpression is evaluated using FM's built-in Evaluate function.

Notes:

1. Expression, Variable, Values, and Separator are all text, and must be enclosed in quotes.
2. As with all text strings, you must use the backslash character to escape enclosed special characters.
3. The ¶ character and double quotation marks are common examples of special characters in FileMaker.
4. There may be zero, one, or more than one instance of Variable inside Expression.
5. It is often useful to reference a List of related or repeating fields, or an ESQL query, as Values.
6. If you're receiving an error, it's often helpful to examine the output of this function with Eval = 0.

Examples:

DistributeExpression ( "x" ; "x" ; "Apple¶B¶3" ; "¶" ; 0 )
= "Apple" & "¶" & "B" & "¶" & "3"

Evaluate ( DistributeExpression ( "x" ; "x" ; "Apple¶B¶3" ; "¶" ; 0 ) )
= Apple¶B¶3

DistributeExpression ( "x" ; "x" ; "Apple¶B¶3" ; "¶" ; True )
= Apple¶B¶3

DistributeExpression ( "Lower ( x )" ; "x" ; "Apple¶B¶3" ; "¶" ; 1 )
= apple¶b¶3

DistributeExpression ( "Ceiling ( num ) + num" ; "num" ; "0.2¶1¶1.5¶2¶2.1¶3¶3.9¶4" ; ", " ; 1 )
= 1.2, 2, 3.5, 4, 5.1, 6, 7.9, 8

DistributeExpression ( "If ( num >= 3 ; num & \¶ )" ; "num" ; "0.2¶1¶1.5¶2¶2.1¶3¶3.9¶4" ; "" ; 1 )
= 3¶3.9¶4¶

DistributeExpression ( "GetValue ( " & Quote ( "A¶B¶C¶D¶E¶F¶G¶H" ) & " ; x )" ; "x" ; "3¶5¶7" ; "¶" ; 1 )
= C¶E¶G

DistributeExpression ( "Func & \" ( Pi )\"" ; "Func" ; "Floor¶Abs¶Evaluate¶Ceiling¶Cos" ; " & \¶ & " ; 1 )
= Floor ( Pi ) & ¶ & Abs ( Pi ) & ¶ & Evaluate ( Pi ) & ¶ & Ceiling ( Pi ) & ¶ & Cos ( Pi )

Evaluate ( DistributeExpression ( "x & \" ( Pi )\"" ; "x" ; "Floor¶Abs¶Evaluate¶Ceiling¶Cos" ; " & \¶ & " ; 1 ) )
= 3¶3.1415926535897932¶3.1415926535897932¶4¶-1

 

Comments

Log in to post comments.

 

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

Under construction. Email me your wish list for improvements.