F(x) Expressions

What is F(x)?

F(x) is a powerful expression evaluator integrated within Perigee, designed to enhance data manipulation and computation capabilities. Much like the "Formula" bar in Excel, F(x) allows users to input complex formulas incorporating functions, mathematical operations, column references, and parentheses. However, F(x) goes beyond basic evaluation by offering advanced features:

• Custom Functionality: Users can add custom classes with functions, expanding the evaluator's capabilities to suit specific needs.

• Performance Optimization: Instead of repeatedly tokenizing, parsing, and evaluating expressions, F(x) compiles expressions into Intermediate Language (IL) code. This compilation results in a direct expression tree that can be executed swiftly, making it ideal for processing large datasets efficiently.

How can I use F(x)?

Possibilities are endless, but let's examine a few cool ideas.

• Enable an input box to accept math expressions allowing the user to easily double a value, or figure out what a third of their original input was.

• Execute an expression across an entire DataTable, modifying the values of each row. This allows for easy data manipulation and cross references to other columns.

• Enabling users to create dynamic data output for a report.

• Allow the user to write their own filter on data, tables, report output, or data ingestion.

Examples

Example 1) Math expression with identifiers

In this example, we'll see how to create an Fx class, register functions, compile a formula, and get results. We can see what the identifier callback is and how to use it.

Notice we return FxValue? This special class handles all of the data types and conversions for the evaluator. If you're passing data in or out of Fx, it will be in the form of a FxValue.

PerigeeApplication.App("Fx", (c) => {

    //Declare a new Fx class, and register the default functions shipped alongside it.
    Fx fx = new Fx();
    fx.RegisterRoot<FxDefaultFunctions>();

    //Compile a new expression
    var fnMethod = fx.Compile("abs([a]) * 2.5");

    //Get the results
    FxValue fnResults = fnMethod(
        //the idCallback will be called when an identifier [value] is found and needs to be referenced. 
        (idCallback) =>
        {

            if (idCallback.Identifier == "a")
                return FxValue.From(10m);
            else
                return FxValue.From(1m);
        });

    //prints 25.0
    c.GetLogger<Program>().LogInformation("Result: {v}", fnResults);

    c.ExitApplication();
});

Example 2) Method overrides

Just like last time we'll be compiling an expression, but this time we'll add the method override. This special override allows you inject your own method handing. In this case, we'll inject a "custom" method that we divide the value by 100. If the requested method isn't "custom", then we allow fx to call the methods as it usually would with fx.CallMethod(fn, arg).

PerigeeApplication.App("Fx", (c) => {

    //Declare a new Fx class, and register the default functions shipped alongside it.
    Fx fx = new Fx();
    fx.RegisterRoot<FxDefaultFunctions>();

    //Compile a new expression, with a method override
    var fnMethod = fx.Compile("custom([a]) * 2.5", (arg, fn, token, _) => { 
        if (fn.Equals("custom") && arg.Length > 0)
        {
            return FxValue.From(arg[0].AsDecimal() / 100.0m);
        }
        return fx.CallMethod(fn, arg);
    });

    //Get the results
    FxValue fnResults = fnMethod(
        //the idCallback will be called when an identifier [value] is found and needs to be referenced. 
        (idCallback) =>
        {
            if (idCallback.Identifier == "a")
                return FxValue.From(10m);
            else
                return FxValue.From(1m);
        });

    //prints 0.25
    c.GetLogger<Program>().LogInformation("Result: {v}", fnResults);

    c.ExitApplication();
});

Example 3) Input parameters

Sometimes you need input context when processing methods, the CompileWithInputParameter allows you to do exactly this, pass in anything that conforms to the object type (including a class) and it will be available for you in the callback. This is especially useful when processing lists, tables, or modifying behavior based on the index, row, user, etc.

This time we'll be returning a string value, and prepending "name: " to it. The passed in input parameter allows us to reference our lookup dictionary from within the method override call.

PerigeeApplication.App("Fx", (c) => {

    //Declare a new Fx class, and register the default functions shipped alongside it.
    Fx fx = new Fx();
    fx.RegisterRoot<FxDefaultFunctions>();

    Dictionary<string, string> NameReferences = new Dictionary<string, string>() { { "0", "bob" }, { "1", "jane" }, { "2", "john" }, { "3", "ruth" } };

    //Compile a new expression, with a method override
    var fnMethod = fx.CompileWithInputParameter<string>("'name: ' & custom()", (arg, fn, token, inp) => { 
        if (fn.Equals("custom"))
        {
            return FxValue.From(NameReferences.GetValueOrDefault(inp, ""));
        }
        return fx.CallMethod(fn, arg);
    });

    //Get the results, this time passing in "0" as the input parameter
    FxValue fnResults = fnMethod("0",
        //the idCallback will be called when an identifier [value] is found and needs to be referenced. 
        (idCallback) =>
        {
            
            if (idCallback.Identifier == "a")
                return FxValue.From(10m);
            else
                return FxValue.From(1m);
        });

    //prints "name: bob"
    c.GetLogger<Program>().LogInformation("Result: {v}", fnResults);

    c.ExitApplication();
});

Example 4) Custom functions

This time we'll add a CustomFunctions class to our project, and allow Fx to call it. We added a square root method called "root". Running this example will call the method and return the value (2.449 ...)

PerigeeApplication.App("Fx", (c) => {

    //Declare a new Fx class, and register the default functions shipped alongside it.
    Fx fx = new Fx();
    fx.RegisterRoot<FxDefaultFunctions>();
    fx.RegisterRoot<CustomFunctions>();

    //Compile a new expression, with a method override
    var fnMethod = fx.Compile("root(6)");

    //Get the results
    FxValue fnResults = fnMethod(
        (idCallback) => FxValue.From(0m));

    //prints  2.4494897
    c.GetLogger<Program>().LogInformation("Result: {v}", fnResults);

    c.ExitApplication();
});

public class CustomFunctions
{
    public static FxValue root(FxValue dbl) => FxValue.From(Math.Sqrt(dbl.AsDouble()));
}

Example 5) Table evaluation

In this example, we'll run over a data table with a 1000 rows. We can supply each columns expression allowing us to perform various operations on each column, sequentially.

We'll also use a partitioned sum to set the Calc column (identical to a SUM(Amount) / group by Type).

Then finally, override the Type column with a concatenation of the Org and Type.

//Generate 1K rows
DataTable DT_Sample = new DataTable();
DT_Sample.Columns.Add("Org", typeof(int));
DT_Sample.Columns.Add("Type", typeof(string));
DT_Sample.Columns.Add("Amount", typeof(decimal));
DT_Sample.Columns.Add("Calc", typeof(decimal));
_FillTable(DT_Sample, 1000);

PerigeeApplication.App("Fx", (c) => {

    //Declare a new Fx class, and register the default functions shipped alongside it.
    Fx fx = new Fx();
    fx.RegisterRoot<FxDefaultFunctions>();

    //Run over the data and set the Calc field to a sum partition, and then reassign Type to a concatenation
    fx.CompileTable(DT_Sample, new Dictionary<string, string>() {
        { "Calc", "SUM([Amount] PARTITION BY [Type])" },
        { "Type", "[Org] & '-' & [Type]"}
    });

    c.GetLogger<Program>().LogInformation("Result 0: {@v}", DT_Sample.Rows[0].ItemArray.ToList());

    c.ExitApplication();
});

void _FillTable(DataTable table, int count)
{
    string[] types = { "A", "B", "C", "D", "E" };

    Random rand = new Random();

    table.BeginLoadData();

    try
    {
        for (int i = 0; i < count; i++)
        {
            int org = rand.Next(1, 1001); // Random integer between 1 and 1000
            string type = types[rand.Next(types.Length)]; // Random type from the array
            decimal amount = Math.Round((decimal)(rand.NextDouble() * 10_000), 2); // Random decimal between 0.00 and 10,000.00

            table.Rows.Add(org, type, amount, 0.0m);
        }
    }
    finally
    {
        table.EndLoadData();
    }
}

Valid types

• Booleans values are supplied as: true, false, yes, no

  • When coercing a String or Number value into a boolean, a 0, 1 will also be accepted

• Numbers are supplied as whole or with a decimal point: 5, 2.5

• Strings are within double quotes or single quotes: "name", 'day'

• Date values are supplied in a string: '01-01-1900'

• Timespan values are supplied variably:

  • 01:10 is 1 hour, 10 minutes

  • 01:10:22 is 1 hour, 10 minutes, 22 seconds

  • 01:10:22:55 is 1 day 10 hours, 22 minutes, 55 seconds

  • 01:10:22:55.110 is is 1 day 10 hours, 22 minutes, 55 seconds, 110 milliseconds

Valid Operators

• + for addition

• - for subtraction

• * For multiplication

• / for division

• ^ for power

• & for concatenation

• % for modulus

• == or = for equals

• != for not equals

• All < > >= <= comparison symbols for greater/lesser than.

• && Logical AND

• || Logical OR

• ! Logical NOT

Dot Chaining

You may supply functions in a dot chain, which makes readability easier in certain scenarios:

'a'.equals('b', true)

Internally, all . chained function calls are written to take the left hand side of the dot and put them inside the first argument. This allows anything, even custom methods to be dot chained.

'a'.equals('b', true) --> equals('a', 'b', true)
('hello' & ' world').upper() --> upper(('hello' & ' world'))

Default Functions

All of the functions listed below are included in the FxDefaultFunctions class.

Value Conversion

ID (id)

Takes any string input and converts the value into an Fx Identifier, allowing data lookup. id('v') -> [v]

codeFx.Compile("id('value')");

Try Decimal (trydecimal)

Safely converts a value to decimal and returns a fallback, or null, when conversion fails.

codeFx.Compile("trydecimal('$123.45')");

Try Integer (tryint)

Safely converts a value to integer and returns a fallback, or null, when conversion fails.

codeFx.Compile("tryint('42.99', 0)");

Try Date (trydate)

Safely converts a value to a date and returns a fallback, or null, when conversion fails.

codeFx.Compile("trydate('01-01-1900')");

Decimal(decimal)

Returns either a decimal value, or error if the value cannot become a decimal

codeFx.Compile("decimal('1.62')");

Bool(bool)

Returns either a Boolean value if the value, string, or decimal can be converted into a Boolean .

1, true, y and yes all are considered true.

Otherwise false is returned.

codeFx.Compile("bool('true')");

Int(int)

Returns either an int value, or error if the value cannot become an int. Rounding may occur if forcing a decimal into an integer

codeFx.Compile("int('1.12')");

String(string)

Returns the string representation of the value

codeFx.Compile("string(1.12)");

Timespan(timespan)

Returns the timespan representation of the value, or timespan zero.

codeFx.Compile("timespan(1500)"); //1.5 seconds

date(date)

Returns the date representation of the value, or error if it cannot be parsed or converted

codeFx.Compile("date('2050-01-01)"); 

Format / Multi-use

Format (Format)

Formats a date, time span, or decimal according to the specified format. Culture is optional and follows the CultureInfo defined by Microsoft.

 codeFx.Compile("Format(fmtValue, format, cultureInfo = null)");

In the below examle, this would format a string value containing a decimal in France currency with 2 decimal places.

format('12345.234', 'C2', 'fr-FR')
// 12 345,23 €

Default Value (default)

Returns a fallback when the value is null, blank, or an error.

codeFx.Compile("default(trydate('not a date'), 'fallback')");

Number Functions

Absolute Value (abs)

Ensures the output value is always positive.

codeFx.Compile("abs(1)");

Maximum (max)

Returns the larger of two numbers.

codeFx.Compile("max(5, 10)");

Minimum (min)

Returns the smaller of two numbers.

codeFx.Compile("min(5, 10)");

Sine (sin)

Calculates the sine of a given angle (in radians).

codeFx.Compile("sin(0)");

Cosine (cos)

Calculates the cosine of a given angle (in radians).

codeFx.Compile("cos(0)");

Tangent (tan)

Calculates the tangent of a given angle (in radians).

codeFx.Compile("tan(0)");

Log Base 10 (log10)

Calculates the base-10 logarithm of a number.

codeFx.Compile("log10(100)");

Square Root (sqrt)

Calculates the square root of a number.

codeFx.Compile("sqrt(16)");

Round (round)

Rounds a number to a specified number of decimal places.

codeFx.Compile("round(3.14159, 2)");

Places (places)

Rounds a number to a specified number of decimal places without exceeding the original value.

codeFx.Compile("places(3.145, 2)");

Ceiling (ceil)

Rounds a number up to the nearest integer.

codeFx.Compile("ceil(3.14)");

Floor (floor)

Rounds a number down to the nearest integer.

codeFx.Compile("floor(3.14)");

---

Null Handling

Null If Blank (nullifblank)

Returns null when a value is empty or whitespace so it can be handled with isnull, coalesce, or default.

codeFx.Compile("nullifblank('   ')");

Null If (NullIf)

Returns null if the first value equals the fallback value; otherwise, returns the first value.

codeFx.Compile("NullIf(value, fallback)");

Is Null (IsNull)

Returns the fallback value if the first value is null; otherwise, returns the first value.

codeFx.Compile("IsNull(value, fallback)");

---

Conditionals

Let Variables (let)

Creates local variables inside an expression so you can reuse intermediate values and keep complex logic readable.

codeFx.Compile("let([full], trim([Name]), [full] & ' - proces

Case (case)

Evaluates condition and value pairs and returns the first matching result, with an optional final else value.

codeFx.Compile("case(false, 'no', 3 > 2, 'yes', 'fallback')");

If (If)

Returns true if the condition is true; otherwise, returns ifFalse.

codeFx.Compile("if(condition, ifTrue, ifFalse)");

In(in)

Returns true if the value is in the parameters list of values.

codeFx.Compile("in(values, params[] values)");

Coalesce (Coalesce)

Returns the first non-null value from the provided conditions.

codeFx.Compile("Coalesce(value1, value2, value3)");

---

Logical Operations

And (and)

Returns true if all conditions are true; otherwise, returns false.

codeFx.Compile("and(condition1, condition2)");

Or (or)

Returns true if any condition is true; otherwise, returns false.

codeFx.Compile("or(condition1, condition2)");

Not (not)

Inverts the boolean value of the condition.

codeFx.Compile("not(condition)");

If Error (iferror)

Returns the fallback value if the first value is an error; otherwise, returns the first value.

codeFx.Compile("iferror(value, fallback)");

Is Error (iserror)

Checks if the value is an error.

codeFx.Compile("iserror(value)");

Is Number (isnumber)

Checks if the value is a number.

codeFx.Compile("isnumber(value)");

Is String (isstr)

Checks if the value is a string.

codeFx.Compile("isstr(value)");

Is Blank (isblank)

Checks if the value is null or an empty string.

codeFx.Compile("isblank(value)");

Is Boolean (isbool)

Checks if the value is a boolean.

codeFx.Compile("isbool(value)");

---

Financial Functions

Payment (pmt)

Calculates the payment for a loan based on constant payments and a constant interest rate.

codeFx.Compile("pmt(rate, nper, pv, fv)");

Principal Payment (ppmt)

Calculates the principal part of a payment for a specific period.

 codeFx.Compile("ppmt(rate, per, nper, pv, fv, type)");

Interest Payment (ipmt)

Calculates the interest part of a payment for a specific period.

codeFx.Compile("ipmt(rate, per, nper, pv, fv, type)");

---

Date Functions

Any date functions that require a part, you may use any of the below listed keywords.

Keyword(s)	Description
year, yy, yyyy	Extracts the year
month, m, mm	Extracts the month
day, d, dd	Extracts the day
hour, hh	Extracts the hour
minute, mi, n	Extracts the minute
second, ss, s	Extracts the second
ms, millisecond	Extracts the millisecond
microsecond, mcs	Extracts microseconds (approximate)

Today (today)

Returns the current date.

codeFx.Compile("today()");

Now (now)

Returns the current date and time.

codeFx.Compile("now()");

Date (date)

Creates a date from year, month, and day.

codeFx.Compile("date(year, month, day)");

Date Add (DateAdd)

Adds a specified number of intervals to a date.

codeFx.Compile("DateAdd(datePart, number, date)");

Date Difference (DateDiff)

Calculates the difference between two dates based on the specified date part.

codeFx.Compile("DateDiff(datePart, date1, date2)");

Date Part (DatePart)

Get the integer based value based on the specified date part.

codeFx.Compile("DatePart(datePart, date1)");

End Of Month (eomonth)

Returns the last day of the month for a date, with an optional month offset.

codeFx.Compile("eomonth(date(2026, 4, 10), 1)");

---

String Functions

Contains (contains)

Returns a boolean of whether or not the string contains a value

codeFx.Compile("contains(stringToSearch, valueToFind, caseSensitive = false)");

Equals (equals)

Returns a boolean of whether or not the string equals another value, allowing case sensitivity if needed

codeFx.Compile("equals(stringA, stringB, caseSensitive = false)");

Name Parts (name)

Parse a name and return a name part. Useful when a column contains a full name and you need to split it.

Valid name parts:

• title

• first

• last

• middle

• nickname (nickname)

• suffix

• surname (optional middle name(s) and last names)

• fullname (first middle last)

• all (all name parts, just cleaned up and optionally title cased)

codeFx.Compile("name(nameValue, namePart, TitleCaseResult = false)");

Name Format (nameformat)

Just like the name function, it allows you the freedom to format the name result in any way you like. Example: {first}/{last} -> Bob/Jones

Valid name replacement strings:

• {title}

• {first}

• {last}

• {middle}

• {nickname} (nickname)

• {suffix}

• {surname}

• {fullname}

• {all}

codeFx.Compile("nameformat(nameValue, format, TitleCaseResult = false)");

Split and Take (rejoin) (split_take)

Split take works a lot like split, but you can specify a range of results to rejoin.

codeFx.Compile("split_take(value, range, join, separators[])");

An example of this is:

split_take('a,b,c,d,e,f,g', '0,1,5-20', '-', ',')

This would return a-b-f-g. as it takes the first two elements, then elements 5 through 20 if they exist.

Split (split)

Split a string and return a split value

codeFx.Compile("split(value, index, defaultValue, separators[])");

The index is zero based, meaning 0 is the first item. You can use 'first', or 'last' as well to retrieve those values.

defaultValue can be supplied when an index is out of range, or the value is null.

You may supply as many string separators as you need, such as:

split([Notes],'last', 'N/A', ',', '|', '@')

Repeat (repeat)

Repeats a sequence of strings a specified number of times.

codeFx.Compile("repeat(count, seq)");

Concatenate (concat)

Concatenates multiple strings into one.

codeFx.Compile("concat(value1, value2, value3)");

Substring (substr)

Extracts a substring from a string starting at a specified index with a specified length.

codeFx.Compile("substr(text, start, length)");

Left (left)

Returns the leftmost specified number of characters from a string.

codeFx.Compile("left(value, len)");

Right (right)

Returns the rightmost specified number of characters from a string.

 codeFx.Compile("right(value, len)");

Trim (trim)

Removes leading and trailing whitespace from a string.

codeFx.Compile("trim(value)");

Trim with Characters (trim)

Removes specified characters from the start and end of a string.

codeFx.Compile("trim(value, chars)");

Replace (replace)

Replaces all occurrences of a specified substring with another substring.

codeFx.Compile("replace(value, search, replacement)");

Upper (upper)

Converts a string to uppercase.

codeFx.Compile("upper(value)");

Lower (lower)

Converts a string to lowercase.

codeFx.Compile("lower(value)");

Starts With (startswith)

Checks whether a string begins with the provided text, with optional case sensitivity.

codeFx.Compile("startswith('Perigee Software', 'Peri')");

Ends With (endswith)

Checks whether a string ends with the provided text, with optional case sensitivity.

codeFx.Compile("endswith('Perigee Software', 'ware')");

Trim Start (trimstart)

Removes whitespace, or specified characters, from the beginning of a string.

codeFx.Compile("trimstart('   hello')");

Trim End (trimend)

Removes whitespace, or specified characters, from the end of a string.

codeFx.Compile("trimend('hello   ')");

Pad Left (padleft)

Pads the left side of a string to a fixed width using spaces or a supplied character.

codeFx.Compile("padleft('42', 5, '0')");

Pad Right (padright)

Pads the right side of a string to a fixed width using spaces or a supplied character.

codeFx.Compile("padright('42', 5, '_')");

Title Case (titlecase)

Converts text into title case so each word begins with an uppercase letter.

codeFx.Compile("titlecase('the quick brown fox')");

Pascal Case (pascalcase)

Converts text into PascalCase by removing separators and capitalizing each word.

codeFx.Compile("pascalcase('the_quick-brown fox')");

Length (len)

Returns the length of a string.

codeFx.Compile("len(value)");

---

Aggregate Functions

Sum Values (sumvals)

Calculates the sum of multiple numerical values.

codeFx.Compile("sumvals(value1, value2, value3)");

Average Values (avgvals)

Calculates the average of multiple numerical values.

codeFx.Compile("avgvals(value1, value2, value3)");