# 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 <mark style="color:blue;">**`Fx`**</mark> class, register functions, compile a formula, and get results. We can see what the identifier callback is and how to use it.&#x20;

Notice we return <mark style="color:blue;">**`FxValue`**</mark>? This special class handles all of the data types and conversions for the evaluator. If you're passing data in or out of <mark style="color:blue;">**`Fx`**</mark>, it will be in the form of a <mark style="color:blue;">**`FxValue`**</mark>.&#x20;

```csharp
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 <mark style="color:red;">`"custom"`</mark> method that we divide the value by 100. If the requested method isn't <mark style="color:red;">`"custom"`</mark>, then we allow fx to call the methods as it usually would with **`fx.CallMethod(fn, arg)`**.

```csharp
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.

```csharp
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 ...)

```csharp
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.&#x20;

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.

```csharp
//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&#x20;

## Dot Chaining

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

```clike
'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.

```clike
'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 <mark style="color:blue;">**`FxDefaultFunctions`**</mark> class.

## Value Conversion

### ID (`id`)

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

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

### Try Decimal (`trydecimal`)

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

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

### Try Integer (`tryint`)

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

```csharp
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.

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

### Decimal(`decimal`)

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

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

### Bool(`bool`)

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

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

Otherwise false is returned.

```csharp
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

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

### String(`string`)

Returns the string representation of the value

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

### Timespan(`timespan`)

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

```csharp
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

```csharp
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](https://learn.microsoft.com/en-us/dotnet/api/system.globalization.cultureinfo.name?view=net-9.0#system-globalization-cultureinfo-name) defined by Microsoft.

```csharp
 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.

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

### Default Value (`default`)

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

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

## Number Functions

### Absolute Value (`abs`)

Ensures the output value is always positive.

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

### Maximum (`max`)

Returns the larger of two numbers.

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

### Minimum (`min`)

Returns the smaller of two numbers.

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

### Sine (`sin`)

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

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

### Cosine (`cos`)

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

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

### Tangent (`tan`)

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

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

### Log Base 10 (`log10`)

Calculates the base-10 logarithm of a number.

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

### Square Root (`sqrt`)

Calculates the square root of a number.

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

### Round (`round`)

Rounds a number to a specified number of decimal places.

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

### Places (`places`)

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

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

### Ceiling (`ceil`)

Rounds a number up to the nearest integer.

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

### Floor (`floor`)

Rounds a number down to the nearest integer.

```csharp
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`.

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

### Null If (`NullIf`)

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

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

### Is Null (`IsNull`)

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

```csharp
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.

```csharp
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.

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

### If (`If`)

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

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

### In(`in`)

Returns `true` if the value is in the parameters list of values.&#x20;

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

### Coalesce (`Coalesce`)

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

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

***

### Logical Operations

### And (`and`)

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

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

### Or (`or`)

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

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

### Not (`not`)

Inverts the boolean value of the condition.

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

### If Error (`iferror`)

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

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

### Is Error (`iserror`)

Checks if the value is an error.

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

### Is Number (`isnumber`)

Checks if the value is a number.

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

### Is String (`isstr`)

Checks if the value is a string.

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

### Is Blank (`isblank`)

Checks if the value is `null` or an empty string.

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

### Is Boolean (`isbool`)

Checks if the value is a boolean.

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

***

## Financial Functions

### Payment (`pmt`)

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

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

### Principal Payment (`ppmt`)

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

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

### Interest Payment (`ipmt`)

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

```csharp
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.

```csharp
codeFx.Compile("today()");
```

### Now (`now`)

Returns the current date and time.

```csharp
codeFx.Compile("now()");
```

### Date (`date`)

Creates a date from year, month, and day.

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

### Date Add (`DateAdd`)

Adds a specified number of intervals to a date.

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

### Date Difference (`DateDiff`)

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

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

### Date Part (`DatePart`)

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

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

### End Of Month (`eomonth`)

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

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

***

## String Functions

### Contains (`contains`)

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

```csharp
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

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

### Name Parts (n`ame`)

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

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)

```csharp
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}`

```csharp
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.&#x20;

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

An example of this is:

```csharp
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

```csharp
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:

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

### Repeat (`repeat`)

Repeats a sequence of strings a specified number of times.

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

### Concatenate (`concat`)

Concatenates multiple strings into one.

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

### Substring (`substr`)

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

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

### Left (`left`)

Returns the leftmost specified number of characters from a string.

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

### Right (`right`)

Returns the rightmost specified number of characters from a string.

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

### Trim (`trim`)

Removes leading and trailing whitespace from a string.

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

### Trim with Characters (`trim`)

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

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

### Replace (`replace`)

Replaces all occurrences of a specified substring with another substring.

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

### Upper (`upper`)

Converts a string to uppercase.

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

### Lower (`lower`)

Converts a string to lowercase.

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

### Starts With (`startswith`)

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

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

### Ends With (`endswith`)

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

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

### Trim Start (`trimstart`)

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

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

### Trim End (`trimend`)

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

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

### Pad Left (`padleft`)

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

```csharp
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.

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

### Title Case (`titlecase`)

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

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

### Pascal Case (`pascalcase`)

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

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

### Length (`len`)

Returns the length of a string.

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

***

## Aggregate Functions

### Sum Values (`sumvals`)

Calculates the sum of multiple numerical values.

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

### Average Values (`avgvals`)

Calculates the average of multiple numerical values.

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