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 - Show more from this author

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)

/*
/  DistributeExpression ( Expression ; Variable ; Values ; Separator ; Eval )
/  
/  Created by Travis Preister (version 1.2, 9/28/2016)
/  
/  http://www.briandunning.com/cf/1913
/  
/  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
*/

If ( GetAsBoolean ( Eval ) ; Evaluate ( DistributeExpression ( Expression ; Variable ; Values ; Separator ; 0 ) ) ; 

// Else If: 
Let ( [ 
  Separator = " & " & Quote ( " & " & If ( Separator <> "" ; Quote ( Separator ) & " & " ) ) & " & " ; 
  Prepend_ = "Substitute ( " & Quote ( Expression ) & " ; " & Quote ( Variable ) & " ; Quote ( \"" ; 
  Append_ = "\" ) )" 
] ; 

Evaluate ( Prepend_ & Substitute ( "•" & Quote ( Values & "•" ) ; [ "•\"" ; "" ] ; [ "\¶" ; Append_ & Separator & Prepend_ ] ; [ "\\" & Append_ & Separator & Prepend_ ; "\\\¶" ] ) & Append_ ) 

) )

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

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

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

Comments

Support this website.

Support this website.