PerformEquationOnList ( theList ; equation ; listItemKey ; errorReplace ; option )

Performs a mathmatical calculation on each line item and returns the option

Be the first to rate this function Log in to vote

Paul - Show more from this author
Peak 14 LLC
http://www.peak14.solutions

Sample input:

PerformEquationOnList ( List ( 1 ; 2 ; 3 ) ; "item ^ item"; "item" ; "evalList"; ; "")

PerformEquationOnList ( List ( "1/1/2000" ; "1/1/2099" ; "1/1/2099") ; "GetAsDate ( item ) > Get ( CurrentDate )"; "item" ; ""; "" )

PerformEquationOnList ( List ( -1 ; 1 ; 2) ; "Factorial ( item )" ; "item" ; 0 ; "" )

Sample output:

1 ¶ 4 ¶ 27

2

3

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

 /*
 *  CUSTOM FUNCTION
 *      PerformEquationOnList ( theList ; equation ; listItemKey ; errorReplace ; option )
 *
 *  DESCRIPTION
 *      Performs a mathmatical calculation on each line item and returns the option chosen (default is to sum the list)
 *           Usage Example 1:
 *              PerformEquationOnList (
 *                  theList: List ( 1 ; 2 ; 3 );
 *                  equation: "item ^ item";
 *                  listItemKey: "item";
 *                  errorReplace: "";
 *                  option: "evalList"
 *              ) = 1 ¶ 4 ¶ 27
 *          Usage Example 2:
 *              PerformEquationOnList (
 *                  theList: List ( "1/1/2000" ; "1/1/2099" ; "1/1/2099");
 *                  equation: "GetAsDate ( item ) > Get ( CurrentDate )";
 *                  listItemKey: "item";
 *                  errorReplace: "";
 *                  option: ""
 *              ) = 2
 *          Usage Example 3:
 *              PerformEquationOnList (
 *                  theList: List ( -1 ; 1 ; 2);
 *                  equation: "Factorial ( item )";
 *                  listItemKey: "item";
 *                  errorReplace: 0;
 *                  option: ""
 *              ) = 3
 *
 *  PARAMS
 *      theList: The list to perform the math on
 *      equation: The math to perform on each item on the list
 *      listItemKey: The key that represents the list item in the equation variable
 *      option: If one of the following values, will output those value(s), otherwise will output as normal.
 *          displayList: Will display a list of all the values before evaluated
 *          evalList: Will display a list of all the values after evaluated
 *          +: Will add the list together (default)
 *          *: Will multiply the list together
 *          ^: Will raise each item in the list to the power of the sequential number
 *      errorReplace: If not empty, will replace any error that occurs with this value, or wise will use the error.
 *
 *  RETURNS
 *      A number or list or error (if errorReplace = False)
 *
 *  DEPENDENCIES
 *      --NONE--
 *
 *  CHANGELOG
 *      2020-06-25: Paul McCudden (paul@peak14.solutions): Created
*/

While (

    // initialVariable
    [
        ~count = ValueCount ( theList );
        ~i = 0;
        ~result = "";
        ~operator = Case (
            option = "*" ; "*";
            option = "^" ; "^";
            "+"
        )
    ];

    // condition
        ~i < ~count;

    // logic
    [
        ~i = ~i + 1;
        ~thisItem = GetValue ( theList ; ~i );
        ~thisEquation = Substitute ( equation ; listItemKey ; ~thisItem );
        // Add quotes to the list item in attempt to make it a valid calculation
        ~thisEquation =  If ( IsValid ( Evaluate ( ~thisEquation ) ) ; ~thisEquation ; Substitute ( equation ; listItemKey ; Quote ( ~thisItem ) ) );
        ~calc = Case ( 
            option = "displayList" ; ~thisEquation;
            ( not IsEmpty ( errorReplace ) ) and ( not IsValidExpression ( Evaluate ( ~thisEquation ) ) ) ; errorReplace;
            Evaluate ( ~thisEquation )
        );
        ~result = Case (
            option = "displayList" or option = "evalList" ; List ( ~result ; ~calc );
            Evaluate ( ~result & ~operator & ~calc )
        )
    ];

    // result
    ~result

)

Use this function whenever you have a list of items and want to do a calculation on each item in the list.

Comments

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

PerformEquationOnList ( theList ; equation ; listItemKey ; errorReplace ; option )

Comments

Support this website.

Support this website.