Functions and Variables Reference

Formulas automate tasks of your eLegere Applications. Formulas prescribe to an application how to manage restrictions, permissions, and calculations without the User's intervention. Type Formulas in different Design Mode options to customize the behavior of Applications' parts and who can access or modify the data.

A Formula is a combination of Functions, Variables, and Arithmetic and Logical Operators. The Function takes the user-specified variables as inputs to return an output. eLegere behaves according to the Formula's output.

Abstract

This document lists all the Variables used as arguments and the Functions employed in Formulas.

Referencing Storage and Values¶

In any formula, you can reference a eLegere Storage in two ways:

Using the $CURRENTSTORAGE variable: it refers to the actual Storage in which the formula is running. Use $CURRENTSTORAGE.field_name to reference the field's value of the current row.
Using the hashtag (#) and the Storage's Name to refer to Storages outside the current Storage in the formula (#storage_name.field_name). E.g. #sProjects.projectDate, #userList.Name.

Note

$CURRENTSTORAGE.field_name refers to the specific field's cell value of a row in the current application.

#storage_name.field_name refers to a field column in #storage_name and not to a specific field's cell value of #storage_name.

Arithmetic Operators¶

eLegere supports the following arithmetic operators within formulas.

Operator	Name
`+`	Addition
`-`	Subtraction
`*`	Multiplication
`/`	Division
`%`	Modulo

Note

Any null value (that is any field without value) involved in an arithmetic operation leads to a result equal to null. Therefore a null leads to no result.

In-Line Comments¶

eLegere supports in-line comments for Formulas in the Formula Editor. The comments introduce notes and explanations in formulas.

Single Line Comments¶

If // opens a line, the Formula Editor ignores that line. The Formula Editor will ignore any text between // and the end of the line.

1	`// This is an example of single line comment in the Formula Editor.`

Multi-Line Comments¶

The Formula Editor considers as comment any line between /* */.

/*
    I have employed the formula
    to sum all the column's values.
    Stop.
*/

The Formula Editor will ignore any line enclosed between /* and */.

Logical Operators¶

eLegere supports the following logical operators within formulas.

Operator	Name	Function
`=`, `==`	Equal	`=EQ()`
`<>`, `!=`	Not Equal	`=NEQ()`
`>`	Greater than	`=GT()`
`>=`	Greater or equal than	`=GTE()`
`<`	Less than	`=LT()`
`<=`	Less or equal than	`=LTE()`

Warning

Logical operators are supported for backward compatibility, but it is highly recommended to use functions instead.

Variables¶

Variables are placeholders for values (or lists of values) that an eLegere Application retrieves from a Storage or the system.

Info

This section lists all the Variables and their options available in eLegere. The Variables section arranges the entries in alphabetical order.

$CURRENTAPP¶

The $CURRENTAPP variable takes the information about the application (Name and ID) from the context. The variable retrieves either the name or the ID of the application in which the formula runs. ($CURRENTAPP doesn't work in cases like the Scheduler where there isn't a specific application to refer to.)

$CURRENTAPP.ID The $CURRENTAPP.ID tells the formula to take the ID of the current application as argument.
$CURRENTAPP.LINK The $CURRENTAPP.LINK returns a link to the current app.
$CURRENTAPP.NAME The $CURRENTAPP.NAME tells the formula to take the name of the current application as argument.

$CURRENTDATE¶

The $CURRENTDATE variable tells the formula to take the current Date and Time as argument.

$CURRENTIME¶

The $CURRENTIME variable tells the formula to take the current Time as argument.

$CURRENTDOMAIN¶

The $CURRENTDOMAIN variable tells the formula to take the name of the current Domain as argument.

$CURRENTSTORAGE¶

$CURRENTSTORAGE tells the formula to refer to the Storage of the current application. The User must use it with an option. It does not work without any option.

$CURRENTSTORAGE.field_name After the Storage name, the User must specify after the dot ('.') the field name of reference. For example: $CURRENTSTORAGE.construction_site_status, $CURRENTSTORAGE.riskEvaluation.
$CURRENTSTORAGE.field_name.OLDVALUE Refers to the value recorded before a modification of the entry.
$CURRENTSTORAGE.$DETAILS.$DetailName.field_name $CURRENTSTORAGE.$DETAILS.$DetailName.fieldname refers to entry of a Detail table of the application the User is working on. $CURRENTSTORAGE.$DETAILS.$DetailName.fieldname requires the definition of the following parameters: $Details The parameter tells the formula to refer to the Details of the current application. The User must not change it. $DetailName The parameter tells the name of the Storage of the desired Detail table. The User must replace the expression with the specific name of the Storage; e.g. "employees_storage", "order_details". fieldname The parameter points the field of the Storage. The User must replace the expression with the name of the field desired.
$CURRENTSTORAGE.$MASTER.field_name $CURRENTSTORAGE.$MASTER.field_name refers to a field on the Master table from any formula embedded in a Detail table.

$CURRENTUSER¶

The $CURRENTUSER variable points the information of the current logged User who is using the application.

$CURRENTUSER.CURRENTLOGONDATE Return data with the latest login of the current User.
$CURRENTUSER.DEPARTMENTS Returns a lists containing the departments the User belongs to.
$CURRENTUSER.EMAIL Returns the email address of the current User.
$CURRENTUSER.GROUPIDS Returns a list of IDs of the groups the User belongs to.
$CURRENTUSER.GROUPNAMES Returns the list of the groups the User belongs to.
$CURRENTUSER.LASTLOGONDATE Returns the latest logon date of the current User.
$CURRENTUSER.LOGONNAME Returns a string with Username of the current User.
$CURRENTUSER.NAME Returns a string with the Name of the current User.
$CURRENTUSER.USERID Returns a string with the alphanumeric code identifying the current User (Id)

$FALSE¶

Stands for a FALSE Boolean value type.

$NULL¶

Stands for the null value.

$PI¶

Stands for the Greek Pi value (Pi = 3.1415926535897931)

$TRUE¶

Stands for a TRUE Boolean value type.

Functions¶

Functions perform actions or return values starting from variables and options written in a precise order between the Function's brackets. Functions accept as arguments Variables, other Functions, and other options that the User might have to specify manually.

Info

This section lists all the Functions available in eLegere. The Functions section divides the entries by category. Each category arranges the entries in alphabetical order.

Numeric¶

ABS¶


Category	Numeric
Description	Absolute value of a number
Syntax	=ABS(VALUE `numeric`)
Output	`numeric`
Arguments	VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula takes a number and returns the absolute value of that number. The absolute is a mathematical function that returns the positive value of the specified numeric expression. E.g. The absolute of |-2| is 2.

Examples¶

Calculating the Absolute Value of a Number
The formula takes the VALUE parameter and yields the absolute number of $CURRENTSTORAGE.field_name. Suppose that one entry of the field $CURRENTSTORAGE.NumberExample contains the value "-25".
1
=ABS($CURRENTSTORAGE.NumberExample)
In the case above, the result will be the "25".

AGGREGATEDETAILS¶


Category	Numeric
Description	Executes an arithmetical operation on a Detail's column.
Syntax	=AGGREGATEDETAILS(VALUE `numeric`; OPERATION `string`; CONDITION `boolean` `optional`)
Output	`numeric`
Arguments	VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). OPERATION `string` Operation that `=AGGREGATEDETAILS()` performs on the retrieved rows. `SUM` `AVG` `COUNT` `MAX` `MIN` CONDITION `boolean` `optional` One or more nested conditions that the values must satisfy. The User uses `CONDITION` to manage permissions, filters, and calculated values. `=AND()` `=CONTAINS()` `=CONTAINLIST()` `=CONTAINSTRING()` `=EQ()` `=GT()` `=GTE()` `=ISNOTNULL()` `=ISNULL()` `=LT()` `=LTE()` `=NEQ()` `=NOT()` `=NOTCONTAINS()` `=OR()`

Explanation¶

Aggregates the values of a Detail's column and applies an arithmetical operation on the values: sum, count, average, max or min.

Examples¶

How to calculate the total number of elements in a Detail column
A Detail table records data and invoices of orders. The User must count the total number of rows in a Detail.
1 2 3 4
=AGGREGATEDETAILS( $CURRENTSTORAGE.$DETAILS.$sPurchaseOrderRows.Id_Purchase_Order_Rows; "COUNT" )
The formula counts the exact number of entries in the Detail. The formula uses the desired Detail field to identify each row.
How to calculate the total number of products that are not discontinued.
The User can use the fourth parameter to decide what values should be included in the count.
1 2 3 4 5
=AGGREGATEDETAILS( $CURRENTSTORAGE.$DETAILS.$products.ID_product; "COUNT"; =EQ(#Products.Discontinued; $FALSE) )
Because the fourth parameter is =EQ(#Products.Discontinued; $FALSE), the formula includes all the "Discontinued" values set on $TRUE.
How to sum all the values in a Detail column
The User has to calculate the total amount of an invoice. Each row of a Detail stands for an item or service price connected to that invoice.
1 2 3 4 5
=AGGREGATEDETAILS( $CURRENTSTORAGE.$DETAILS.$invoiceItems.cost; "SUM"; =EQ(#invoiceItems.paid; $TRUE) )
The formula calculates the total cost of all the elements of the Detail satisfying the condition. The formula considers only the rows with the "paid" field TRUE
How to calculate the average value of a Detail Column
A Detail table records all the info regarding a category of products. The products have a the "discount" Numeric type field. The User has to calculate the average discount of that category of products.
1 2 3 4
=AGGREGATEDETAILS( $CURRENTSTORAGE.$DETAILS.$productCategoryList.discount; "AVG" )
The formula calculates the average discount of the products belonging to that category.

ARCCOS¶


Category	Numeric
Description	The formula calculates the arccosine of an angle
Syntax	=ARCCOS(VALUE `numeric`)
Output	`numeric`
Arguments	VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula calculates the arccosine (i.e. the inverse of the cosine) of the value taken. The formula returns the value in radians (i.e. expression of an angle as ratio between the arc of the angle on a circle and the radius.)

Examples¶

How to calculate the arccosine of an angle
The application must calculate the arccosine of the angle. The storage field working as argument stores the angle as a Numeric type value.
1
=ARCOS($CURRENTSTORAGE.angleValue)
If the angle is 60°, =ARCOS() returns 0.5 as result.

ATAN¶


Category	Numeric
Description	The formula calculates the arctangent of an angle
Syntax	=ATAN(VALUE `numeric`)
Output	`numeric`
Arguments	VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula calculates the arctangent (i.e. the inverse of the tangent) of the value taken. The formula returns the value in radians (i.e. expression of an angle as ratio between the arc of the angle on a circle and the radius.)

Examples¶

How to calculate the arctangent of an angle
The application must calculate the arctangent of the angle. The storage field working as argument stores the angle as a Numeric type value.
1
=ATAN($CURRENSTORAGE.angleValue)
If the angle is 45°, =ATAN() returns 1 as result.

AVG¶


Category	Numeric
Description	The formula calculates the average value of a set of values.
Syntax	=AVG(STORAGE_NAME `string`; VALUE `string`; CONDITION `boolean` `optional`; BOOLEAN `boolean` `optional`)
Output	`numeric`
Arguments	STORAGE_NAME `string` Name of the Storage to consider. Use `#storage_name` as placeholder for a specific Storage. VALUE `string` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). CONDITION `boolean` `optional` One or more nested conditions that the values must satisfy. The User uses `CONDITION` to manage permissions, filters, and calculated values. `=AND()` `=CONTAINS()` `=CONTAINLIST()` `=CONTAINSTRING()` `=EQ()` `=GT()` `=GTE()` `=ISNOTNULL()` `=ISNULL()` `=LT()` `=LTE()` `=NEQ()` `=NOT()` `=NOTCONTAINS()` `=OR()` BOOLEAN `boolean` `optional` The parameter enables to either include null values or not using a Boolean flag. If the parameter is `TRUE`, the formula accepts null values from the column. If `FALSE`, the formula rejects null values.

Explanation¶

The formula calculates the average value of the entries from the selected column field. The User can decide a filter formula as third parameter to filter the column values for the calculation. Also, the User can to include or not null values by inserting a Boolean variable $TRUE as fourth parameter. ($FALSE if the User wants to exclude null values from the calculation).

Examples¶

How to calculate the average of the monthly expenses
Suppose that you have a Personal Budget application to track your monthly expenses. The application must calculate the average money amount spent each month.
1
=AVG(#Budget; #Budget.moneyValue; =GTE(#Budget.Date; =CONVERT(=FORMATDATE($CURRENTDATE; "yyyMM01"); "INT")
The =AVG() calculates the average value of expenses which dates are above the first day of the current month. The function =GTE() in third parameter checks if the Date's day must is equal or above 01.

CEILING¶


Category	Numeric
Description	The formula rounds a float number to the closest and highest integer
Syntax	=CEILING(VALUE `string`)
Output	`numeric`
Arguments	VALUE `string` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula rounds a float number to the closest and highest approximated number.

Examples¶

How to round each value to the highest and closest integer automatically
Suppose that an application must round each value inserted to the closest integer automatically.
1
=CEILING($CURRENTSTORAGE.value)
The formula will round the $CURRENTSTORAGE.value field value to the closest and highest integer. E.g. "3,52 > 4".

COS¶


Category	Numeric
Description	The formula calculates the Cosine of an angle.
Syntax	=COS(VALUE `string`)
Output	`list`
Arguments	VALUE `string` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula calculates the Cosine of an angle. The formula returns the value in radians (i.e. expression of an angle as ratio between the arc of the angle on a circle and the radius.)

Examples¶

How to calculate the cosine of an angle
The application must calculate the cosine of the angle. The storage field working as argument stores the angle as a Numeric type value.
1
=COS($CURRENTSTORAGE.angleValue)
If the angle is 90°, =COS() returns 0 as result.

COUNT¶


Category	Numeric
Description	Calculates the number of rows matching the condition.
Syntax	=COUNT(STORAGE_NAME `string`; STORAGE_FIELD_NAME `string`; CONDITION `boolean` `optional`; BOOLEAN `boolean` `optional`)
Output	`numeric`
Arguments	STORAGE_NAME `string` Name of the Storage to consider. Use `#storage_name` as placeholder for a specific Storage. STORAGE_FIELD_NAME `string` Name of the Storage Field to consider. Use `#storage_name.field_name` as placeholder for a specific Storage field. CONDITION `boolean` `optional` One or more nested conditions that the values must satisfy. The User uses `CONDITION` to manage permissions, filters, and calculated values. `=AND()` `=CONTAINS()` `=CONTAINLIST()` `=CONTAINSTRING()` `=EQ()` `=GT()` `=GTE()` `=ISNOTNULL()` `=ISNULL()` `=LT()` `=LTE()` `=NEQ()` `=NOT()` `=NOTCONTAINS()` `=OR()` BOOLEAN `boolean` `optional` The parameter enables to either include null values or not using a Boolean flag. If the parameter is `TRUE`, the formula accepts null values from the column. If `FALSE`, the formula rejects null values.

Explanation¶

The formula count the number of row satisfying a condition in a column.

Examples¶

How to count the number of new elements in a table.
The Application has a Logical Column working as a counter for the number of items included in a order. The Logical Column keeps track of the number of items in the Detail table connected.
1 2 3 4 5
=COUNT( #sPurchaseOrderRows; #sPurchaseOrderRows.Id_Purchase_Order_Rows; =EQ(#sPurchaseOrderRows.Id_Purchase_Order;$CURRENTSTORAGE.Id_Purchase_Order) )
If inserted in a Logical Column, the formula updates the counter with the number of entries of the Detail table. An Identity field set as primary key must relate the Detail entries to the row in the Master table.

EXP¶


Category	Numeric
Description	Calculates the exponential of a number.
Syntax	=EXP(VALUE `numeric`)
Output	`numeric`
Arguments	VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula calculates the value of the exponential of e (2.71828). The formula returns e raised of the chosen exponential.

Examples¶

How to calculate the exponential of the value automatically
Suppose that the application must take the inserted value and use it as exponential for e (2.71828).
1
=EXP($CURRENTSTORAGE.exponentialValue)
The formula returns e (2.71828) raised by the value of the $CURRENTSTORAGE.exponentialValue field.

FLOOR¶


Category	Numeric
Description	Rounds a float number to the lowest and closest integer number
Syntax	=FLOOR(VALUE `numeric`)
Output	`numeric`
Arguments	VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula rounds down a decimal number to the closest and lowest integer number.

Examples¶

How to round each value to the lowest integer automatically
Suppose that an application must round each value inserted to the lowest integer automatically.
1
=FLOOR($CURRENTSTORAGE.value)
The formula will round the $CURRENTSTORAGE.value field value to the closest and lowest integer. . E.g. "3,66 > 3".

LOG¶


Category	Numeric
Description	The function calculates a number's logarithm.
Syntax	=LOG(VALUE `numeric`; BASE `numeric`)
Output	`numeric`
Arguments	VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). BASE `numeric` The logarithm's base to calculate a value's exponential.

Explanation¶

The function calculates the Logarithm of a number to a specified base.

Examples¶

Calculating the logarithm of a number
An applications has the calculate the exponential of a number starting from a number and its base.
1
=LOG(1000, 10)
The result is 3 since the power to raise 10 to 1000 is 3.

LN ¶


Category	Numeric
Description	The function calculates a number's natural logarithm
Syntax	=LN(VALUE `numeric`)
Output	`numeric`
Arguments	VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The function calculates the Logarithm of a number having base the mathematical constant e = 2.718; i.e. the Natural logarithm.

Examples¶

Calculating the natural logarithm of a number
An applications has the calculate the natural logarithm starting from a specific exponential.
1
=LN(6)
The result of elevating e to the 6th power is 1.79175946923

MAX¶


Category	Numeric
Description	Searches the highest value of the column.
Syntax	=MAX(STORAGE_NAME `string`; STORAGE_FIELD_NAME `numeric`; CONDITION `boolean` `optional`)
Output	`numeric`
Arguments	STORAGE_NAME `string` Name of the Storage to consider. Use `#storage_name` as placeholder for a specific Storage. STORAGE_FIELD_NAME `numeric` Name of the Storage Field to consider. Use `#storage_name.field_name` as placeholder for a specific Storage field. CONDITION `boolean` `optional` One or more nested conditions that the values must satisfy. The User uses `CONDITION` to manage permissions, filters, and calculated values. `=AND()` `=CONTAINS()` `=CONTAINLIST()` `=CONTAINSTRING()` `=EQ()` `=GT()` `=GTE()` `=ISNOTNULL()` `=ISNULL()` `=LT()` `=LTE()` `=NEQ()` `=NOT()` `=NOTCONTAINS()` `=OR()`

Explanation¶

The formula scans the column and returns the highest value stored.

Examples¶

How to check the highest discount available for a list of products
Consider an Application recording all the products of a store. The User must pick up the highest discount above the 50%.
1 2 3 4 5
=MAX( #prices; #prices.discount; =GT(#prices.discount; 50) )
The formula checks all the "discount" field values to find the highest discount above the 50%.

MIN¶


Category	Numeric
Description	Searches the lowest value of the column.
Syntax	=MIN(STORAGE_NAME `variable`; STORAGE_FIELD_NAME `numeric`; CONDITION `boolean` `optional`)
Output	`numeric`
Arguments	STORAGE_NAME `variable` Name of the Storage to consider. Use `#storage_name` as placeholder for a specific Storage. STORAGE_FIELD_NAME `numeric` Name of the Storage Field to consider. Use `#storage_name.field_name` as placeholder for a specific Storage field. CONDITION `boolean` `optional` One or more nested conditions that the values must satisfy. The User uses `CONDITION` to manage permissions, filters, and calculated values. `=AND()` `=CONTAINS()` `=CONTAINLIST()` `=CONTAINSTRING()` `=EQ()` `=GT()` `=GTE()` `=ISNOTNULL()` `=ISNULL()` `=LT()` `=LTE()` `=NEQ()` `=NOT()` `=NOTCONTAINS()` `=OR()`

Explanation¶

The formula scans the column and returns the lowest value stored.

Examples¶

How to pick up the product with the lowest VAT
Consider an Application recording all the services' VAT. The User must find the lowest VAT below the 22% for the service.
1 2 3 4 5
=MIN( #servicePrice; #servicePrice.Vat; =LT(#servicePrice.Vat; 22) )
The formula checks all the "Vat" field values to find the lowest VAT below the 22% for the desired service.

POWER¶


Category	Numeric
Description	The formula calculates the power of a number.
Syntax	=POWER(VALUE `numeric`; POWER `numeric`)
Output	`numeric`
Arguments	VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). POWER `numeric` The exponential to raise the value for the exponentiation.

Explanation¶

The function calculates a number's exponentiation given the power.

Examples¶

Calculating a number's exponentiation.
The application calculate the power of a number.
1
=POWER(2,3)
The formula returns 8, 3rd power of the natural number 2.

RAD¶


Category	Numeric
Description	The formula converts an angle expressed in degrees into radians.
Syntax	=RAD(VALUE `numeric`)
Output	`numeric`
Arguments	VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula takes the value of VALUE and converts it into radians.

Examples¶

How to convert angles from degrees to radians automatically
The application must convert each Numeric type value standing as degrees to a number expressing the same angle in radians.
1
=RAD($CURRENTSTORAGE.angleValue)
The formula returns the angle in radians as result. E.g. "30° > 0.523599".

RANDOM¶


Category	Numeric
Description	The formula generates a random natural integer number within a specified range.
Syntax	=RANDOM(VALUE `numeric`; VALUE `numeric`)
Output	`numeric`
Arguments	VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula generates a random number between a minimum value and a maximum value. =RANDOM(x,y) yields a number between x and y. =RANDOM(x) yields a number between the range of 1 and x. =RANDOM() considers the upper and lower limits included. If no parameters are specified, =RANDOM() returns either 0 or 1.

Examples¶

How to generate a random number between 0 and 1
The application must return either 0 or 1 as random value.
1
=RANDOM()
If no specific parameters are specified for =RANDOM(), the formula returns either 0 or 1 as Numeric type value.
How to generate a random number between 1 and an integer number.
The application must generate a random number between 1 and 20. The random generation's range must include 20 as upper limit.
1
=RANDOM(20)
The formula returns a random Numeric type value between 1 and 20.
How to generate a random number between a specific range of integer numbers.
The application must generate a random number between 10 and 100. The random generation's range must include 10 and 100.
1
=RANDOM(10, 100)
The formula returns a random Numeric type value between 10 and 100.

ROUND¶


Category	Numeric
Description	The formula rounds the number to the closest integer number.
Syntax	=ROUND(VALUE `numeric`; DECIMAL `numeric`)
Output	`numeric`
Arguments	VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). DECIMAL `numeric` The `Decimal` parameter points how many decimal digit to consider for the round. E.g. If you have 3,343789 and Decimal is equal 2, the formula will consider the first two decimal digits (i.e. 0,34)

Explanation¶

The formula returns the Numeric type value rounded to the closest integer number. The function rounds to the lowest if the decimal is below 'x,5' (e.g. 3,4) or to the the highest if equal or above 'x,5' (e.g. 7,9). E.g. If the value is 2,66914343, the formula considers only the first two decimal digits and round to 3.

Examples¶

How to round each value to the closest integer automatically
Suppose that an application must round each decimal value inserted to the closest integer automatically.
1
=FLOOR($CURRENTSTORAGE.value)
The formula will round the $CURRENTSTORAGE.value field value to the closest integer. E.g. "3,66 > 4", "6,3 > 6"..

SIN¶


Category	Numeric
Description	Calculates the sine of an angle.
Syntax	=SIN(VALUE `numeric`)
Output	`numeric`
Arguments	VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula returns the sine of the angle provided as argument. The value is in radians (i.e. expression of an angle as ratio between the arc of the angle on a circle and the radius.)

Examples¶

How to calculate the sine of an angle automatically
The application must calculate the sine of the angle automatically. The storage field working as argument stores the angle as a Numeric type value.
1
=SIN($CURRENTSTORAGE.angleValue)
If the angle is 60°, =SIN() returns 0.866 as result.

SQRT¶


Category	Numeric
Description	The formula returns the square root of the value provided as argument.
Syntax	=SQRT(VALUE `numeric`)
Output	`numeric`
Arguments	VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula returns the square root of VALUE. For example: "=SQRT(9)" returns 3 as result.

Examples¶

Calculate the square of a value automatically
Suppose that an application must calculate the square of each value of a Numeric type field.
1
=SQRT($CURRENSTORAGE.numbers)
The function returns the square value of each value of $CURRENSTORAGE.numbers.

SUM¶


Category	Numeric
Description	The formula yields the sum of all the values of a column.
Syntax	=SUM(STORAGE_NAME `string`; STORAGE_FIELD_NAME `numeric`; CONDITION `boolean` `optional`)
Output	`string`
Arguments	STORAGE_NAME `string` Name of the Storage to consider. Use `#storage_name` as placeholder for a specific Storage. STORAGE_FIELD_NAME `numeric` Name of the Storage Field to consider. Use `#storage_name.field_name` as placeholder for a specific Storage field. CONDITION `boolean` `optional` One or more nested conditions that the values must satisfy. The User uses `CONDITION` to manage permissions, filters, and calculated values. `=AND()` `=CONTAINS()` `=CONTAINLIST()` `=CONTAINSTRING()` `=EQ()` `=GT()` `=GTE()` `=ISNOTNULL()` `=ISNULL()` `=LT()` `=LTE()` `=NEQ()` `=NOT()` `=NOTCONTAINS()` `=OR()`

Explanation¶

The formula yields the sum of all the values of a column. The User can define a CONDITION parameter to filter the values of the column.

Examples¶

How to calculate the sum of all the entries flagged as 'Expenses'
Suppose that a Personal Budget application has an Boolean type field to flag entries as 'Expenses'.
1
=SUM(#Budget; #Budget.moneyValue; =EQ(#Budget.booleanExpense; $TRUE))
The formula returns the sum of all the expenses which entries have the boolenExpense field with TRUE value.

TAN¶


Category	Numeric
Description	The formula calculates the tangent of an angle in radians.
Syntax	=TAN(STORAGE_NAME `numeric`)
Output	`string`
Arguments	STORAGE_NAME `numeric` Name of the Storage to consider. Use `#storage_name` as placeholder for a specific Storage.

Explanation¶

The formula returns the tangent of the angle provided as argument. The value is in radians (i.e. expression of an angle as ratio between the arc of the angle on a circle and the radius.)

Examples¶

How to calculate the tangent of an angle
The application must calculate the tangent of the angle. The storage field working as argument stores the angle as a Numeric type value.
1
=TAN($CURRENSTORAGE.angleValue)
If the angle is 45°, =TAN() returns 0.7853981 as result.

ValueList¶

ALLITEMS¶


Category	ValueList
Description	Returns the list with all the entries of a drop-down list.
Syntax	=ALLITEMS()
Output	`list`
Arguments

Explanation¶

The formula returns a list with all the the entries of a drop-down list. The formula returns all the items of a drop-down list. =ALLITEMS() does not have any argument.

Examples¶

How to make a drop-down list returning all the entries if a User doesn't belong to a group
A Designer wants to display certain results in a drop/down list if the #sUser Storage includes the Id and Email of the current User.
1 2 3 4 5 6 7 8 9
=IF( $CURRENTUSER.EMAIL == LOOKUP( #sUser; #sUser.Email; #sUser.Id_User == $CURRENTUSER.USERID ); =LIST(1;2); =ALLITEMS() )
=LIST(1,2) contains the list of the identifiers of the options to be shown. If #sUser includes the User's Id and email, the drop-down list displays the options with the IDs that =LIST() contains. Otherwise, the drop-down lists shows all the options of the drop-down list.

DISTINCT¶


Category	ValueList
Description	Returns a list of elements taken only once.
Syntax	=DISTINCT(LIST `object list`)
Output	`list`
Arguments	LIST `object list` `LIST` represents any list of items.

Explanation¶

The formula takes a list and yields a String of elements separated by a comma. The formula takes each repeated element of the list only once.

Examples¶

How to pick up all the elements of a list without repetitions
An application manages the list of products to ship. The Designer has to pick up the list of all the products that the column "productToSend" contains only once.
1
=DISTINCT($CURRENTSTORAGE.productToSend)
The formula returns a List of all the elements of "productToSend" taken only once and without repetitions.

FIELDCONTAINS¶


Category	ValueList
Description	The formula filters a field according to a list of values.
Syntax	=FIELDCONTAINS(STORAGE_FIELD_NAME `string`; LIST `object list`; FILTER_LIST_CONDITION `string`; MINIMUM_MATCHING_NUMBER `numeric` `optional`)
Output	`list`
Arguments	STORAGE_FIELD_NAME `string` Name of the Storage Field to consider. Use `#storage_name.field_name` as placeholder for a specific Storage field. LIST `object list` `LIST` represents any list of items. FILTER_LIST_CONDITION `string` The `FILTER_LIST_CONDITION` defines the conditions to retrive the rows. `ALL` `NONE` `ATLEAST` `NATLEAST` MINIMUM_MATCHING_NUMBER `numeric` `optional` If the User uses the `ATLEAST` option for `LIST`, they must include `MINIMUM_MATCHING_NUMBER` as fourth parameter. `MINIMUM_MATCHING_NUMBER` specifies the minimum number of elements of the `=LIST()` that should appear so that the formula returns a `TRUE` value. So, if the parameter is set to 3, at least three elements of the formula `=LIST()` must appear in the field to return a `TRUE`.

Explanation¶

The formula filters a field according to a list of values. The User can choose whether the field values should match all the list elements, none, some or no less than. The User can specify the minimum threshold of elements to match so that the formula returns a TRUE Boolean value. E.g. The User sets the value to 2. The formula yields a TRUE when the elements of the field match at least two elements of the list.

Examples¶

How to check if a Multiselection field contains a certain value

A Storage called #sWorkPackages contains the info of all the employees and work groups. The Designer has to enable only the Users assigned to a project to modify the rows.

=LOOKUPLIST(
    #sWorkPackages;
    #sWorkPackages.Id_WP;
    =AND(
        #sWorkPackages.Id_Project == $CURRENTSTORAGE.Id_Project;
        #sWorkPackages.Id_Status != 5;
        =FIELDCONTAINS(
            #sWorkPackages.Id_Owners;
            =LOOKUPLIST(
                #LKUserList;
                #LKUserList.IdProgressive;
                #LKUserList.emailCompany == $CURRENTUSER.EMAIL);
        "ATLEAST"
        )
    )
)

Designers can employ the formula in the Edit Restriction settings. The formula allows edits under the following conditions: (a) the current User in the Storage #sWorkPackages is assigned to the project through the Multiselection field "Id_Owners" and the company's email; (b) the project status is not number 5 (i.e. Status 5 = Completed).

GROUPUSEREMAILS¶


Category	ValueList
Description	Returns all the email addresses of User group.
Syntax	=GROUPUSEREMAILS(GROUPNAME `string`)
Output	`list`
Arguments	GROUPNAME `string` Name of the group of Users of the Domain.

Explanation¶

The formula lists the email addresses of all the members belonging to a User group.

Examples¶

How to obtain all the email addresses of a User Group
The Designer creates a Custom Action to send an email notification to all the members of a User Group automatically.
1
=GROUPUSEREMAILS("SystemManagers")
The formula works in the To: Formula field of the Send Email custom action. The formula retrieves the list of all the email addresses of the members of "SystemManagers" group.

INTERSECT¶


Category	ValueList
Description	The formula takes all the elements in common between two lists
Syntax	=INTERSECT(ELEMENTS_SET `formula`; ELEMENTS_SET `formula`)
Output	`list`
Arguments	ELEMENTS_SET `formula` Stands for any function returning a List. `=DISTINCT()` `=FIELDCONTAINS()` `=FILTERITEMS()` `=GROUPUSEREMAILS()` `=INTERSECT()` `=LIST()` `=LOOKUP()` `=LOOKUPLIST()` `=SUBTRACT()` `=UNION()` ELEMENTS_SET `formula` Stands for any function returning a List. `=DISTINCT()` `=FIELDCONTAINS()` `=FILTERITEMS()` `=GROUPUSEREMAILS()` `=INTERSECT()` `=LIST()` `=LOOKUP()` `=LOOKUPLIST()` `=SUBTRACT()` `=UNION()`

Explanation¶

The formula takes all the elements in common between two lists. It works like a set intersection between two sets A and B (A ∩ B).

Examples¶

How to pick the elements in common between two lists
The Designer is configuring a Send Email Custom Action. The email must arrive only to Users belonging to both "Developers" and "Product Team" User Groups.
1 2 3 4
=INTERSECT( =GROUPUSEREMAILS("Developers"); =GROUPUSEREMAILS("Product Team") )
The formula works in the To: Formula field of the Send Email custom action. The formula picks up only the email addresses in common between the two User Groups. The email notification will arrive only to addresses in common to both the lists.

LIST¶


Category	ValueList
Description	The formula returns a list of the elements considered or the Storage field.
Syntax	=LIST(ELEMENTS `object list`)
Output	`list`
Arguments	ELEMENTS `object list` `ELEMENTS` stands for either a fixed list of elements or the entries list of a column field.

Explanation¶

The formula returns a list of the elements considered or the elements of a Storage field.

Examples¶

How to list all the elements of a field
The Designer has to list all the elements that the "Surnames" field includes in a Address Book application.
1
=LIST($CURRENTSTORAGE.Surnames)
The formula provides a list of all the surnames recorded in the Storage.

NOITEMS¶


Category	ValueList
Description	Returns an empty list from a drop-down list.
Syntax	=NOITEMS()
Output	`list`
Arguments

Explanation¶

The formula returns an empty list from drop-down list. =NOITEMS() does not have any argument.

Examples¶

How to show nothing from a drop-down list if a condition is not met
A drop-down list must remain empty if the desired condition is not met.
1
=CONTAINSLIST($CURRENTUSER.DEPARTMENTS;=LIST(#Auth.department))
The drop-down list displays all the options if the User belongs to one of the departments of the #Auth Storage. Otherwise, the drop-down list results empty, and the User cannot choose any option.

SUBTRACT¶


Category	ValueList
Description	The formula takes two set of elements and returns the list of the elements not in common.
Syntax	=SUBTRACT(ELEMENTS_SET `object list`; ELEMENTS_SET `object list`)
Output	`list`
Arguments	ELEMENTS_SET `object list` Stands for any function returning a List. `=DISTINCT()` `=FIELDCONTAINS()` `=FILTERITEMS()` `=GROUPUSEREMAILS()` `=INTERSECT()` `=LIST()` `=LOOKUP()` `=LOOKUPLIST()` `=SUBTRACT()` `=UNION()` ELEMENTS_SET `object list` Stands for any function returning a List. `=DISTINCT()` `=FIELDCONTAINS()` `=FILTERITEMS()` `=GROUPUSEREMAILS()` `=INTERSECT()` `=LIST()` `=LOOKUP()` `=LOOKUPLIST()` `=SUBTRACT()` `=UNION()`

Explanation¶

The formula takes two set of elements and returns the list of the elements not in common. It works like the difference between two sets A and B (A - B).

Examples¶

How to list all the elements not in common between two Lists
A drop-down list must display the orders not in common between the current field and a field in another Storage.
1 2 3 4
=SUBTRACT( =LIST($CURRENTSTORAGE.codeOrders); =LIST(#ordersCompleted.codeCompletedOrders) )
The formula displays in the drop-down list the order codes that appear only in the "codeOrders" field but not in "#ordersCompleted.codeCompletedOrders". The list will contain only the orders not completed.

UNION¶


Category	ValueList
Description	The formula takes two set of elements and returns the list of all the elements of both sets.
Syntax	=UNION(ELEMENTS_SET `object list`; ELEMENTS_SET `object list`)
Output	`list`
Arguments	ELEMENTS_SET `object list` Stands for any function returning a List. `=DISTINCT()` `=FIELDCONTAINS()` `=FILTERITEMS()` `=GROUPUSEREMAILS()` `=INTERSECT()` `=LIST()` `=LOOKUP()` `=LOOKUPLIST()` `=SUBTRACT()` `=UNION()` ELEMENTS_SET `object list` Stands for any function returning a List. `=DISTINCT()` `=FIELDCONTAINS()` `=FILTERITEMS()` `=GROUPUSEREMAILS()` `=INTERSECT()` `=LIST()` `=LOOKUP()` `=LOOKUPLIST()` `=SUBTRACT()` `=UNION()`

Explanation¶

The formula takes two sets of elements and returns the list of all the elements of all the sets. It works like the union between two sets A and B (A ∪ B).

Examples¶

How to pick all the elements of two Lists
The Designer must grant the editability of the Master table only to the users belonging to two groups.
1 2 3 4 5 6 7
=CONTAINS( =UNION( =GROUPUSEREMAILS("Accounting"); =GROUPUSEREMAILS("IT Specialists") ); $CURRENTUSER.EMAIL )
The formula works in Design Mode > Settings > Master Table > Restrictions > Edit. The formula allows edits only to the User email address appearing in the global list of either "Accounting" or "IT Specialists" User Groups.

Logical¶

AND¶


Category	Logical
Description	The formula works like the logical operator conjuction.
Syntax	=AND(CONDITION `boolean`; CONDITION `boolean`)
Output	`boolean`
Arguments	CONDITION `boolean` One or more nested conditions that the values must satisfy. The User uses `CONDITION` to manage permissions, filters, and calculated values. `=AND()` `=CONTAINS()` `=CONTAINLIST()` `=CONTAINSTRING()` `=EQ()` `=GT()` `=GTE()` `=ISNOTNULL()` `=ISNULL()` `=LT()` `=LTE()` `=NEQ()` `=NOT()` `=NOTCONTAINS()` `=OR()` CONDITION `boolean` One or more nested conditions that the values must satisfy. The User uses `CONDITION` to manage permissions, filters, and calculated values. `=AND()` `=CONTAINS()` `=CONTAINLIST()` `=CONTAINSTRING()` `=EQ()` `=GT()` `=GTE()` `=ISNOTNULL()` `=ISNULL()` `=LT()` `=LTE()` `=NEQ()` `=NOT()` `=NOTCONTAINS()` `=OR()`

Explanation¶

The formula takes two or more conditions as argument. If all the conditions are satisfied, the formula returns a TRUE Boolean. Otherwise, it yields a FALSE Boolean.

Examples¶

How to restrict editing only to Users belonging to two User Groups
Suppose that you must enable editing on an application to User belonging both the"Master Users" and "Managers" User Groups.
1
=AND(=CONTAINS($CURRENTUSER.GROUPNAMES; "Master Users"); =CONTAINS($CURRENTUSER.GROUPNAMES; "Managers")
You can insert the formula above in Design Mode > Settings > Restrictions > Edit. The formula enables editing if and only if the User's Group List contains both "Master Users" and "Managers".

NOT¶


Category	Logical
Description	The formula works like the logical operator Negation.
Syntax	=NOT(CONDITION `boolean`)
Output	`boolean`
Arguments	CONDITION `boolean` One or more nested conditions that the values must satisfy. The User uses `CONDITION` to manage permissions, filters, and calculated values. `=AND()` `=CONTAINS()` `=CONTAINLIST()` `=CONTAINSTRING()` `=EQ()` `=GT()` `=GTE()` `=ISNOTNULL()` `=ISNULL()` `=LT()` `=LTE()` `=NEQ()` `=NOT()` `=NOTCONTAINS()` `=OR()`

Explanation¶

The formula inverts the Boolean value that another formula returns. I.e. TRUE becomes FALSE, FALSE becomes TRUE.

Examples¶

How to exclude specific User Groups from editing data
Suppose that an application must forbid oner or more User groups (e.g. "Read Only"; "Deactivated") from editing the rows.
1
=NOT(=CONTAINSLIST($CURRENTUSER.GROUPNAMES;=LIST("Read Only"; "Deactivated")))
ou can insert the formula above in Design Mode > Settings > Restrictions > Edit. The formula enablforbidses editing if and only if the User belongs to either "Read Only" or "Deactivated".

OR ¶


Category	Logical
Description	The formula works like the logical operator Disjunction
Syntax	=OR(CONDITION `boolean`; CONDITION `boolean`)
Output	`string`
Arguments	CONDITION `boolean` One or more nested conditions that the values must satisfy. The User uses `CONDITION` to manage permissions, filters, and calculated values. `=AND()` `=CONTAINS()` `=CONTAINLIST()` `=CONTAINSTRING()` `=EQ()` `=GT()` `=GTE()` `=ISNOTNULL()` `=ISNULL()` `=LT()` `=LTE()` `=NEQ()` `=NOT()` `=NOTCONTAINS()` `=OR()` CONDITION `boolean` One or more nested conditions that the values must satisfy. The User uses `CONDITION` to manage permissions, filters, and calculated values. `=AND()` `=CONTAINS()` `=CONTAINLIST()` `=CONTAINSTRING()` `=EQ()` `=GT()` `=GTE()` `=ISNOTNULL()` `=ISNULL()` `=LT()` `=LTE()` `=NEQ()` `=NOT()` `=NOTCONTAINS()` `=OR()`

Explanation¶

The formula takes two or more conditions as argument. The formula returns TRUE Boolean if a value satisfies at least one condition.

Examples¶

How to restrict editing only to Users belonging to either one group or the other
Suppose that you must enable editing on an application to User belonging either the"Master Users" or the "Managers" User Groups.
1
=OR(=CONTAINS($CURRENTUSER.GROUPNAMES; "Master Users"); =CONTAINS($CURRENTUSER.GROUPNAMES; "Managers"))
You can insert the formula above in Design Mode > Settings > Restrictions > Edit. The formula enables editing if the User's Group List contains either "Master Users" or "Managers".

String¶

CONCAT¶


Category	String
Description	The formula joins two or more strings in a single string.
Syntax	=CONCAT(STRING `string`; STRING `string`)
Output	`string`
Arguments	STRING `string` `STRING` represents any String type value. The User can add as many strings as they need to the formula. A semicolon (`;`) must divide each entry in the formula. The User must type between `" "` (Quotation Marks) any string value not stored in a Storage field. For Example: "Last Month", "22nd September 2022". The `STRING` parameter can be a `=LIST()` formula with a series of String type value or a String type Storage field. STRING `string` `STRING` represents any String type value. The User can add as many strings as they need to the formula. A semicolon (`;`) must divide each entry in the formula. The User must type between `" "` (Quotation Marks) any string value not stored in a Storage field. For Example: "Last Month", "22nd September 2022". The `STRING` parameter can be a `=LIST()` formula with a series of String type value or a String type Storage field.

Explanation¶

The formula joins two or more strings in a single string. Consider the Storage $CURRENTSTORAGE.Name.VALUE and the string " My name is ", and suppose that the value of the field is "Stefano". The formula takes the values divided by the semi-colons and merges them in a joint string: "My Name is Stefano".

Examples¶

Join all the elements of a list in one text.
The Designer has to join all the elements of a List in a single text and fill a field.
1
=CONCAT(=LIST($CURRENTSTORAGE.names); " is my name")
The formula merges the value of the field "names" and " is my name" in a single String type value.
Create a text starting from the values of two columns.
The Designer has to fill the "fullName" String type field automatically. The application must generate the value of "fullName" starting from the "Name" and "Surname" String type fields values.
1
=CONCAT($CURRENTSTORAGE.Name;" ";$CURRENTSTORAGE.Surname)
The formula joins the values of "Name" (e.g. "John" ) and "Surname" (e.g."Wong") and divides them with a space (" "). The result is the Full Name (e.g. "John Wong").

Create an ID starting from order number, month, and year of the invoice.

The Designer has fill a String field with a ID code. The Designer must generate the ID code starting from number, month, and year of the invoice.

=CONCAT( 
    "ID-";
    $CURRENTSTORAGE.orderNumber;
    "-";
    =LOOKUP(
        #sOrdersMonth;
        #sOrderMonth.fiscal_month;
        #sOrderMonth.Id_month == $CURRENTSTORAGE.month_Id
    );
    =LOOKUP(
        #sOrderYear;
        #sBudgetYear.Year_Fiscal;
        #sBudgetYear.Id_Year_Fiscal == $CURRENTSTORAGE.Id_Year_Fiscal
    )
)

The formula merges the prefix "ID-", the symbol "-", the order's number, the current month of the order, and the current fiscal year of the order in a String.

FORMATDATE¶


Category	String
Description	Formats the Date according to the format desired.
Syntax	=FORMATDATE(DATE_FIELD `date`; DATE_FORMAT `string`)
Output	`string`
Arguments	DATE_FIELD `date` `DATE_FIELD` stands for the date that the formula must format according to `STRING_FORMAT`.The User must provide a `$CURRENTSTORAGE.field_name` variable as `DATE_FIELD`. `DATE_FIELD` is a DateTime type value. DATE_FORMAT `string` `DATE_FORMAT` represents the desired format for the Date argument provided. `DATE_FORMAT` is a String type value. The formula accepts the following list of formats: `yyyyMMddHHmmss` `yyyyMMdd` `yyyy-MM-dd HH:mm:ss` `yyyy-MM-dd HH:mm:ss` `yyyy/MM/dd` `yyyy-MM-dd` `ddMMyyyyHHmmss` `ddMMyyyy` `dd-MM-yyyy HH:mm:ss` `dd/MM/yyyy HH:mm:ss` `dd/MM/yyyy` `dd-MM-yyyy HHMM-dd` `c (Displays the General Date Format)` `d (Displays the day number using one digits)` `dd (Displays the day number using two digits)` `dddd (Lists all the days of the week)` `w (Displays the number of the week in the month)` `ww (Displays the number of the week in the year)` `m (Displays the month using one digit)` `mm (Displays the month using two digits number)` `mmm (Writes the name of the month using three letters)` `mmmm (Spells the names of all the months)` `q (Displays the current quarter of the year [1-4])` `y (Displays the day of the year)` `yy (Display the last two digits of the year)` `h (Display the hour using one digit)` `hh (Display the hour using two digit)` `s (Display the seconds using one digit)` `ss (Display the seconds using two digits)` `AM/PM (Displays the time using AM/PM after AMPM)` `am/pm (Displays the time using am/pm after AMPM)` `AMPM (Displays the time using the 12 hour clock)`

Explanation¶

The formula formats the DateTime value provided according to the format in the second parameter STRING_FORMAT.

Examples¶

How to show only month and year of a Date
The Designer has to fill a String field with the month and the year of a Date type value automatically.
1
=FORMATDATE($CURRENTSTORAGE.recordDate; "mm yyyy")
The formula takes the Date from "recordDate" and registers in the String field showing only the month and the year. E.g. {27/12/99} "12 1999"
How to show only day, week of the year, and year of a Date
The Designer has to fill a String field with only the day, week, and the year of a Date type value automatically.
1
=FORMATDATE($CURRENTSTORAGE.recordDate; "d ww yyyy")
The formula takes the Date from "recordDate" and registers in the String field showing only the day, week of the year, and the year. E.g. {02/01/22} "2 01 2022".

FORMATNUMBER¶


Category	String
Description	Formats a Number according to the format desired
Syntax	=FORMATNUMBER(VALUE `numeric`; NUMERIC_FORMAT `string`)
Output	`string`
Arguments	VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). NUMERIC_FORMAT `string` `NUMERIC_FORMAT` represents the desired format for the Numeric type value. `NUMERIC_FORMAT` is a String type value. The formula accepts the following list of formats: `#` `0` `.` `,` `%` `$` `\` `'Text'`

Explanation¶

The formula formats a Numeric type value provided according to the format in the STRING_FORMAT parameter.

Examples¶

How to display a number with two decimal digits and the Euro (€) symbol
The Designer must format the numbers in a Numeric field with two decimal digits. The currency Euro (€) symbol must appear before the number.
1
=FORMATNUMBER($CURRENTSTORAGE.price; "€ ####.00")
The formula displays the number with two decimal digits and the Euro (€) symbol.

FORMATTIME¶


Category	String
Description	Formats a Date according to the format desired
Syntax	=FORMATTIME(VALUE `time`; TIME_FORMAT `string`)
Output	`string`
Arguments	VALUE `time` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). TIME_FORMAT `string` `TIME_FORMAT` represents the desired format for the argument provided. `HH:mm:ss` `HHmmss` `HH-mm-ss` `h m s` `h` `hh` `H` `HH` `m` `mm` `s` `ss` `t` `tt` `K`

Explanation¶

The formula formats a Time type value provided according to the format in the STRING_FORMATparameter.

Examples¶

How to show only the hour of a Time type value
A field must display only the hours of a Time type value.
1
=FORMATTIME($CURRENTSTORAGE.timeRecord; "hh")
The formula displays just the hour of the Time. E.g. 15:30:48 "15".

GUID¶


Category	String
Description	The formula generates a Global Unique Identifier (GUID).
Syntax	=GUID()
Output	`string`
Arguments

Explanation¶

The formula generates a Global Unique Identifier. A Global Unique Identifier is a random alphanumeric code that identifies different objects. Each alphanumeric code of the formula is unique. A classic example of a Global Unique Identifier are the codes identifying the orders of a shop.

Examples¶

How to generate a random and unique code
The field must display a String containing a random and unique code.
1
=GUID()
The formula generates a unique code like the following: "645d134d-673f-4597-aa5e-e3fad81836f3".

INDEXOF¶


Category	String
Description	The formula checks a String for a text starting from a position
Syntax	=INDEXOF(STRING `string`; TO_CHECK `string`; STARTING_POSITION `numeric` `optional`)
Output	`numeric`
Arguments	STRING `string` `STRING` represents any String type value. The User can add as many strings as they need to the formula. A semicolon (`;`) must divide each entry in the formula. The User must type between `" "` (Quotation Marks) any string value not stored in a Storage field. For Example: "Last Month", "22nd September 2022". The `STRING` parameter can be a `=LIST()` formula with a series of String type value or a String type Storage field. TO_CHECK `string` The text to check in the String. STARTING_POSITION `numeric` `optional` The indexed position from which the formula begins the search in the String.

Explanation¶

The formula checks a String for a text. If the String contains the text, the formula returns the index of its position in the String. For example, consider the following sentence: "There should be a new feature in eLegere 2.12" If the searched word is "eLegere", the formula returns its indexed position in the sentence, so 35. The indexed position is the place that the characters composing the searched text occupy in the String. E.g. "Fabio is smart", the position of "is" is 7.

Examples¶

How to search a text in a String type value
The Designer wants to check each value looking for the position of a keyword.
1
=INDEXOF($CURRENTSTORAGE.notesString; "eLegere")
If the String value contains "eLegere", the formula returns a Numeric type value representing the indexed position. E.g. If "eLegere" occupies the 23rd place of the String, the formula will yield 23 as value.

LEFT¶


Category	String
Description	The formula extracts a certain number of characters starting from the left side of a String.
Syntax	=LEFT(STRING `string`; NUMBER_CHARACTERS `numeric`)
Output	`string`
Arguments	STRING `string` `STRING` represents any String type value. The User can add as many strings as they need to the formula. A semicolon (`;`) must divide each entry in the formula. The User must type between `" "` (Quotation Marks) any string value not stored in a Storage field. For Example: "Last Month", "22nd September 2022". The `STRING` parameter can be a `=LIST()` formula with a series of String type value or a String type Storage field. NUMBER_CHARACTERS `numeric` The number of characters to take in a String.

Explanation¶

The formula extracts a certain number of characters from a String value. The formula extracts the characters beginning from the left side of the String's text and going to the right. E.g. "Bobo is a good dog!", the formula takes the characters starting from "B", then it takes the "o", and so on.

Examples¶

How to extract the desired number of characters from the left to the right in a text
The Designer aims to extract the first four characters of a String.
1
=LEFT($CURRENTSTORAGE.Notes; 4)
The formula will return the first four characters from left to right. E.g. If the String value contains "Bobo is a good dog!", the formula yields the String "Bobo".

LEN¶


Category	String
Description	Calculates a String length.
Syntax	=LEN(STRING `string`)
Output	`numeric`
Arguments	STRING `string` `STRING` represents any String type value. The User can add as many strings as they need to the formula. A semicolon (`;`) must divide each entry in the formula. The User must type between `" "` (Quotation Marks) any string value not stored in a Storage field. For Example: "Last Month", "22nd September 2022". The `STRING` parameter can be a `=LIST()` formula with a series of String type value or a String type Storage field.

Explanation¶

The formula calculates the string length. The string length is the number of total characters.

Examples¶

How to calculate how many characters a text contains
The Designer has to calculate the number of characters composing a String type value automatically.
1
=LEN("My name is Daniel")
"My name is Daniel" are 17 characters. =LEN() yields 17 as Numeric type value.

RIGHT¶


Category	String
Description	The formula extracts a certain number of characters starting from the right side of a String.
Syntax	=RIGHT(STRING `string`; NUMBER_CHARACTERS `numeric`)
Output	`string`
Arguments	STRING `string` `STRING` represents any String type value. The User can add as many strings as they need to the formula. A semicolon (`;`) must divide each entry in the formula. The User must type between `" "` (Quotation Marks) any string value not stored in a Storage field. For Example: "Last Month", "22nd September 2022". The `STRING` parameter can be a `=LIST()` formula with a series of String type value or a String type Storage field. NUMBER_CHARACTERS `numeric` The number of characters to take in a String.

Explanation¶

The formula extracts a certain number of characters from a String value. The formula extracts the characters beginning from the right side of the String's text and going to the left. E.g. "My name is Mary", the formula takes the characters starting from "y", then it takes the "r", and so on.

Examples¶

How to extract the desired number of characters from the right to the left in a text
The Designer has to return a certain number of characters from a text starting from the right side.
1
=RIGHT($CURRENTSTORAGE.notesString; 4)
The formula yields a String type value. The String contains the first four characters from the right to the left. E.g. If the String value contains "My name is Mary", the formula yields the String "yraM".

SUBSTRING¶


Category	String
Description	The formula extracts a piece of text from a string.
Syntax	=SUBSTRING(STRING `string`; STARTING_POSITION `numeric`; NUMBER_CHARACTERS `numeric`)
Output	`string`
Arguments	STRING `string` `STRING` represents any String type value. The User can add as many strings as they need to the formula. A semicolon (`;`) must divide each entry in the formula. The User must type between `" "` (Quotation Marks) any string value not stored in a Storage field. For Example: "Last Month", "22nd September 2022". The `STRING` parameter can be a `=LIST()` formula with a series of String type value or a String type Storage field. STARTING_POSITION `numeric` The indexed position from which the formula begins the search in the String. NUMBER_CHARACTERS `numeric` The number of characters to take in a String.

Explanation¶

The formula extracts text from a String starting from the desired index. The index is the position of a character in the string. For instance, the formula with index 12 from the string "Welcome to eLegere" will extract the string "eLegere".

Examples¶

Extracting a Text beginning from the Desired Position
The Designer has to extract a piece of text from a String starting from a precise position. For instance, the field must record the last 5 characters of an alphanumeric code taken from another field automatically.
1
=SUBSTRING($CURRENTSTORAGE.codeId; 10; 5)
Supposing that a code is 15 characters long, the formula yields a String containing the last 5 characters of the code.

TEXTJOIN¶


Category	String
Description	Merges multiple strings in a single string
Syntax	=TEXTJOIN(SEPARATOR `string`; LIST `object list`)
Output	`string`
Arguments	SEPARATOR `string` The symbol that divides the elements of a list. LIST `object list` `LIST` represents any list of items.

Explanation¶

The formula merges the strings in the list in one String type value. The SEPARATOR string parameter will divide each of them.

Examples¶

How to have a list with all the elements divided by a separator
The Designer has to join all the Full Names contained in a Storage in a single String. A semicolon (;) must divide each entry and the String must include only the active users.
1 2 3 4 5 6 7 8
=TEXTJOIN( ","; =LOOKUPLIST( #sPersonalData; #sPersonalData.FullName; =EQ(#sPersonalData.IsActive;$TRUE) ) )
The formula joins the values of the "FullName" field and divides the names with a semicolon. The formula includes only the entries with TRUE "IsActive" Boolean field.

How to have a list of all the elements of a Multiselection field divided by a separator

The Designer wants a String containing all the values chosen by the User in a Multiselection. A semicolon (;) must divide each entry. The Multiselection field contains the members assigned to a project.

 =TEXTJOIN(
    ";";
    =LOOKUPLIST(
    #PersonalData;
        #PersonalData.FullName;
        =CONTAINS(
            $CURRENTSTORAGE.projectMembers;
            #PersonalData.Id_Progressive
        )
    )
)

The formula joins the values of the field "FullName" that the "projectMembers" Multiselection field contains. The formula picks up only the IDs of the Users included in "projectMembers".

How to Include a List of Email Addresses in an Email's Body

A Multiselection field in the Application records the working groups assigned to a project. The email must include the email addresses of the Team Leaders of each working group involved in the project within the Body.

=TEXTJOIN(
    ", ";
    =LOOKUPLIST(
    #sDepartments;
    #sDepartments.teamLeaderMail
    );
    =CONTAINS(
            $CURRENTSTORAGE.workingGroups;
            #sDepartments.Department_Code
        )
    )

The formula works between squared brackets ([ ]) in the Body of the Send Email custom action. The formula joins the email addresses of the groups's Team Leaders involved into a String. The formula takes the Team Leaders' email addresses starting from the values of the Multiselection "workingGroups". From the value of the "workingGroups", the formula takes the info from the #sDepartments Storage and the "teamLeaderMail" field.

TEXTREPLACE¶


Category	String
Description	Replaces a text within a String.
Syntax	=TEXTREPLACE(STRING `string`; TO_CHECK `string`; REPLACEMENT `string`)
Output	`string`
Arguments	STRING `string` `STRING` represents any String type value. The User can add as many strings as they need to the formula. A semicolon (`;`) must divide each entry in the formula. The User must type between `" "` (Quotation Marks) any string value not stored in a Storage field. For Example: "Last Month", "22nd September 2022". The `STRING` parameter can be a `=LIST()` formula with a series of String type value or a String type Storage field. TO_CHECK `string` The text to check in the String. REPLACEMENT `string` Value that replaces the Storage's value if null.

Explanation¶

The formula finds one piece of text in the String and replaces it with another text.

Examples¶

How to replace each occurrence of a text with another piece of text
The Designer has to replace a piece of text in each entry of the field automatically. The field "phoneNumber" is a String type field containing phone numbers of the customers. A "phoneNumber" value is the number plus the "00XX" international prefix (e.g. Italy "0039", Denmark "0045", France "0033"). Suppose that the Designer wants to replace "0039" within each phone number with "+39".
1
=TEXTREPLACE($CURRENTSTORAGE.phoneNumber; "0039"; "+39")
The formula checks the whole String. If "0039" occurs, the formula replaces that piece of text with "+39". E.g. "003935645612" "+3935645612".

Comparison¶

CONTAINS¶


Category	Comparison
Description	Checks a list looking for an element.
Syntax	=CONTAINS(LIST `object list`; VALUE `string`)
Output	`boolean`
Arguments	LIST `object list` `LIST` represents any list of items. VALUE `string` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula checks if an element is included in a list. If the element is in the List, the formula yields a TRUE Boolean value.

Examples¶

How to check if a User belongs to a list and can edit the table rows
The Designer must restrict who can edit the Master table rows according to their User Group.
1
=CONTAINS(=LIST($CURRENTUSER.GROUPNAMES);"Managers")
The formula works when inserted in Designer Mode > Settings > Master Table Name > Restrictions > Edit. The formula allows only the users of "Managers" group to edit the rows in the Application. The =CONTAINS() formula verifies if the List of the User's groups includes the "Managers" group.

CONTAINSLIST¶


Category	Comparison
Description	The formula compares two lists
Syntax	=CONTAINSLIST(LIST `object list`; LIST `object list`)
Output	`boolean`
Arguments	LIST `object list` `LIST` represents any list of items. LIST `object list` `LIST` represents any list of items.

Explanation¶

The formula compares the elements of two different lists. If one element of a list appears in the other list, the formula yields a TRUE Boolean value.

Examples¶

How to check if a list contains one or more elements from another list
A Storage has the "authorized_staff" Multiselection field. The Application must enable editing on a Detail table only for the Users inserted in "authorized_staff".
1 2 3 4 5 6 7 8
=CONTAINSLIST( $CURRENTSTORAGE.authorized_staff; =LOOKUPLIST( #Maintenance; #Maintainance.ID_worker; #Maintainance.staff == $TRUE ) )
The formula works when inserted in Designer Mode > Settings > Detail Name > Restrictions > Edit. The formula compares the IDs of "authorized_staff" with the list of IDs with a TRUE "staff" Boolean field. If the IDs of =LOOKUPLIST() appear in the "authorized_staff" list, those users can edit the Detail table.

CONTAINSTRING¶


Category	Comparison
Description	The formula compares two strings.
Syntax	=CONTAINSTRING(STRING `string`; STRING `string`)
Output	`boolean`
Arguments	STRING `string` `STRING` represents any String type value. The User can add as many strings as they need to the formula. A semicolon (`;`) must divide each entry in the formula. The User must type between `" "` (Quotation Marks) any string value not stored in a Storage field. For Example: "Last Month", "22nd September 2022". The `STRING` parameter can be a `=LIST()` formula with a series of String type value or a String type Storage field. STRING `string` `STRING` represents any String type value. The User can add as many strings as they need to the formula. A semicolon (`;`) must divide each entry in the formula. The User must type between `" "` (Quotation Marks) any string value not stored in a Storage field. For Example: "Last Month", "22nd September 2022". The `STRING` parameter can be a `=LIST()` formula with a series of String type value or a String type Storage field.

Explanation¶

The formula compares two string values. If the one string is contained in the other, the formula yields a TRUE value.

Examples¶

How to check if a alphanumeric code contains a certain group of characters
The Application must check if a String type field contains a specific text. If the "productSerialCode" String type field contains the second parameter, the formula must record a specific value in the table cell.
1 2 3 4 5 6 7 8
=IF( =CONTAINSTRING( $CURRENTSTORAGE.productSerialCode; "shipWorld" ); "Worldwide Shipping"; "EU Shipping Only" )
The formula search the text "shipWorld" in the product serial code. If the String contains "shipWorld", the Application records in the cell "Worldwide Shipping". Otherwise, the formula records "EU Shipping Only".

EQ ¶


Category	Comparison
Description	Checks if two values are equal
Syntax	=EQ(VALUE `any`; VALUE `any`)
Output	`boolean`
Arguments	VALUE `any` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). VALUE `any` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula checks if the values of the function are equal. The formula returns a TRUE Boolean if the two values are equal.

Examples¶

How to display only the entries with the current date
Suppose that an application must enable editing to the user John Doe exclusively.
1
=EQ($CURRENTUSER.EMAIL; "j.doe@vesenda.com")
The formula works when inserted in Designer Mode > Settings > Master Table Name > Restrictions > Edit. The application will allow editing only to the User who has the email j.doe@vesenda.com.

GT ¶


Category	Comparison
Description	Checks if one value is greater than another one.
Syntax	=GT(VALUE `numeric`; VALUE `numeric`)
Output	`boolean`
Arguments	VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula checks if one value is greater than the other. The formula returns a Boolean value TRUE if the first parameter is greater than the second parameter.

Examples¶

How to recalculate only entries of the last 15 days.
The application must enable recalculation only for entries h.
1
=GT($CURRENTSTORAGE.BudgetDate;=CONVERT(=FORMATDATE($CURRENTDATE; "yyyyMMdd");"INT") - 15)
The formula works when inserted in Designer Mode > Settings > Master Table Name > Restrictions > Recalculate. The formula enables recalculation for entries who has a Date greater than the $CURRENTDATE value minus 15 days.

GTE¶


Category	Comparison
Description	Checks if one value is equal or greater than another one.
Syntax	=GTE(VALUE `numeric`; VALUE `numeric`)
Output	`boolean`
Arguments	VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula checks if one value is equal or greater than the other. The formula returns a TRUE Boolean if the first parameter of the field is equal or greater than the second parameter.

Examples¶

How to view only the entries of the last month.
The application must enable recalculation only for the entries h.
1
=GTE($CURRENTSTORAGE.BudgetDate;=CONVERT(=FORMATDATE($CURRENTDATE; "yyyyMM01");"INT") - 15)
The formula works when inserted in Designer Mode > Settings > Master Table Name > Restrictions > View. The application shows only the rows with da Date equal or greater the first day of the month.

ISNOTNULL¶


Category	Comparison
Description	The formula checks if the value is not null.
Syntax	=ISNOTNULL(VALUE `any`)
Output	`boolean`
Arguments	VALUE `any` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula checks if the value is not null.

Examples¶

How to disable recalculation for entries that haven't values.
Suppose that the application must permit recalculation of entries that haven't null values only.
1
=ISNOTNULL($CURRENSTORAGE.Total)
The formula works when inserted in Designer Mode > Settings > Master Table Name > Restrictions > Recalculation. The formula permits the recalculation of rows which $CURRENSTORAGE.Total is not null.

ISNULL¶


Category	Comparison
Description	The formula checks if the value is null.
Syntax	=ISNULL(VALUE `any`)
Output	`boolean`
Arguments	VALUE `any` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula checks if the value is null.

Examples¶

How to insert a value automatically if a field is null.
Suppose that an application must replace each null value of a Boolean field with a FALSE Boolean value.
1
=IF(=ISNULL($CURRENTSTORAGE.statusApproved); $FALSE)
The Formula works in Design Mode > Formula > Calculated Field. If the value inserted for $CURRENTSTORAGE.statusApproved is null, the formula replaces it with a FALSE Boolean value.

LT ¶


Category	Comparison
Description	Checks if one value is lower than another one.
Syntax	=LT(VALUE `numeric`; VALUE `numeric`)
Output	`boolean`
Arguments	VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula checks if one value is lower than the other.The formula returns a TRUE Boolean if the first parameter of the field is lower than the second parameter.

Examples¶

How to make a cell red if the inserted money amount is negative
Suppose that a Personal Budget application must highlight of red a cell if the value is negative.
1
=IF(=LT($CURRENTSTORAGE.moneyValye; 0); "red"; "gray")
The formula works in Design Mode > Layout > Cell BG Color > Set Formula. The cell switch color from gray to red if the money amount is below 0.

LTE¶


Category	Comparison
Description	Checks if one value is equal or lower than another one.
Syntax	=LTE(VALUE `numeric`; VALUE `numeric`)
Output	`boolean`
Arguments	VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). VALUE `numeric` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula checks if one value is equal or lower than the other. The formula returns a TRUE Boolean if the first value is equal or lower than the second value.

Examples¶

How to convert a value equal or below zero to a positive value
Suppose that the application must record a Numeric value without the minus (-) sign if the value is negative. E.g. The product's number of items available in a warehouse.
1
=IF(=LTE($CURRENTSTORAGE.itemsNumber; 0); =ABS($CURRENTSTORAGE.itemsNumber))
The formula works in Design Mode > Formula > Calculated Field. The formula records the absolute of the value whenever $CURRENTSTORAGE.itemsNumber is lower or equal to 0.

NEQ¶


Category	Comparison
Description	Checks if two value are NOT equal.
Syntax	=NEQ(VALUE `any`; VALUE `any`)
Output	`boolean`
Arguments	VALUE `any` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). VALUE `any` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula checks if the values of the function differ and aren't equal. The formula returns a TRUE Boolean if the two values are not equal.

Examples¶

How to permit a Send Email action only when a project is not closed.
Suppose that you have a Project Management application. The application manages projects and permits to send email reminders to members added to the project. Entries have a Lookup cell with different project statuses that a manager can assign (e.g. "To Review"). The application must forbid the Custom Action Send Email if the Lookup has the status "Is Closed".
1
=NEQ($CURRENTSTORAGE.lookupProjectStatus; 7)
The formula works in Execution restriction for Send Email Custom Actions in Design Mode. The application disables the Send Email if $CURRENTSTORAGE.lookupProjectStatus has the ID 7. In the Lookup's options' storage, ID 7 corresponds to "Is Closed".

NOTCONTAINS¶


Category	Comparison
Description	Checks if an element is not included in a list.
Syntax	=NOTCONTAINS(LIST `object list`; VALUE `variable`)
Output	`boolean`
Arguments	LIST `object list` `LIST` represents any list of items. VALUE `variable` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula checks if a list does not include an element. If the list does not include the element, the formula yields a TRUE value. Otherwise, the formula returns a FALSE value.

Examples¶

Checking if a List doesn't contain the specified element
The Application must guarantee the editability of the Master table rows exception made for the User Group "View Only".
1
=NOTCONTAINS(=LIST($CURRENTUSER.GROUPNAMES);"View Only")
The formula works when inserted in Designer Mode > Settings > Master Table Name > Restrictions > Edit. If "View Only" doesn't appear in the current User's group list, the User can edit the table rows. Otherwise, the User cannot edit the table rows if they appear in the "View Only" users list.

Conversion¶

CONVERT¶


Category	Conversion
Description	Converts one data type into another one
Syntax	=CONVERT(VALUE `string`; TYPE `string`; FORMAT `string` `optional`)
Output	`string`
Arguments	VALUE `string` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). TYPE `string` The parameter `TYPE` specifies the data type for the conversion. `DATE` `DECIMAL` `INT` `TEXT` FORMAT `string` `optional` `FORMAT` points the the desired format for the argument provided. The formula will format the outut of the Function according to the `FORMAT` parameter.

Explanation¶

The formula converts a Date, Numeric o String value from one type to another type. For Date values, the formula complies with the ISO 8601 standard.

Examples¶

How to show a Date in another date format
An Application must convert and display the Date date in the custom format desired.
1
=CONVERT($CURRENTSTORAGE.dateRecord; "DATE"; "dd-mmm-yyyy")
The formula converts any Date data in the custom format "dd-mmm-yyyy".
How to convert a float number to an integer
An Application must convert and display any float number as an integer number.
1
=CONVERT($CURRENTSTORAGE.numeric; "INT")
The formula converts any float number stored in the "numeric" Numeric type field into a integer number.
How to convert an integer number to a decimal number
An Application must convert and display any integer number as an decimal number.
1
=CONVERT($CURRENTSTORAGE.numeric; "DECIMAL")
The formula converts any integer number stored in the "numeric" Numeric type field into a decimal number.
How to convert a DateTime value to a custom format
An Application must convert and display a DateTime type value according to the following format: "yyyy-mm-dd AMPM am/pm".
1
=CONVERT($CURRENTSTORAGE.dateTimeRecord; "DATE"; "yyyy-mm-dd AMPM am/pm")
The formula converts any DateTime record into a date with a 12-hour clock.
How to convert a String to a Numeric type value
An Application must convert and display a String type value containing a number as a Numeric type value:
1
=CONVERT($CURRENTSTORAGE.stringNumbers; "INT")
The formula converts the String type value containing a number to a Numeric type value. E.g. "34" 34

Date¶

DATEPART¶


Category	Date
Description	The formula returns a part of a Date, Date Time or Timespan value.
Syntax	=DATEPART(VALUE `datetime`; KEY `string`)
Output	`numeric`
Arguments	VALUE `datetime` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). KEY `string` Points what part of the date to return. `Dates` `Timespan`

Explanation¶

The formula returns a part of a Date, Date Time or Timespan value. E.g. The day (dd) of a Date value like dd-mm-yyyy.

Examples¶

How to show the number of hours of a DateTime value
The Application must show from a DateTime data stored in the field and save it. In particular, the application must show the hours taken from a DateTime value.
1
=DATEPART($CURRENTSTORAGE.dateTimeRecord; 'hour')
The formula extracts the hour from a date from a DateTime value. For example, =DATEPART() from the value {02/07/22 05:30:22} returns "5" as Numeric value.

DATEADD¶


Category	Date
Description	Adds an amount of time to a Date, DateTime or Timespan
Syntax	=DATEADD(VALUE `string`; AMOUNT `numeric`; QUANTITY_TIME_UNIT `string` `optional`)
Output	`date`
Arguments	VALUE `string` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). AMOUNT `numeric` The units of time to add to the total value. QUANTITY_TIME_UNIT `string` `optional` The unit of the quantity of time to add to a Date. E.g. year, months, days, hours `y` `years` `m` `months` `d` `days` `h` `hours` `mi` `minutes` `ss` `seconds` `$CURRENTSTORAGE.Timespan`

Explanation¶

The formula adds a certain amount of time to a Date, DateTime or Timestamp. For instance 2 hours, 1 day, 1 month or 20 seconds.

Examples¶

How to add 5 days to a Date value
The Application must add 5 days to the Date type value. E.g. 20/07/23 25/07/23
1
=DATEADD($CURRENTSTORAGE.dateRecord; 5; 'days')
The formula adds 5 days to the Date type value.
How to add 1 month to a DateTime value
The Application must add 1 month to the DateTime type value. E.g. 01/08/22 10:22:39 01/09/22 10:22:39
1
=DATEADD($CURRENTSTORAGE.dateTimeRecord; 1; 'm')
The formula adds 1 month to the value of the DateTime type field.

DATEDIFF¶


Category	Date
Description	The formula calculates the time difference between two dates or two timespans.
Syntax	=DATEDIFF(VALUE `date`; VALUE `date`; UNIT_FORMAT `string`)
Output	`numeric`
Arguments	VALUE `date` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). VALUE `date` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). UNIT_FORMAT `string` Points the format for the time difference value. `Dates` `Timespan`

Explanation¶

The formula calculates the time difference between two dates or two timespans. The formula accepts Date, Datetime, and Timespan values. If you want to insert a date manually, use the =CONVERT() function to convert the date in a formatted string (e.g. "12/12/24") to a Date or Datetime data.

Examples¶

How to calculate the difference in days between two Dates
The Application must calculate the number of days between two Date type values.
1
=DATEDIFF($CURRENTSTORAGE.openingDate; $CURRENTSTORAGE.closingDate; "d")
The formula compares the two Dates and calculates a Numeric type value with the difference in days.
How to calculate the difference in hours between two Timespans
The Application must calculate the gap in hours between two Timespans.
1
=DATEDIFF($CURRENTSTORAGE.timeSpan1; $CURRENTSTORAGE.timeSpan2; "h")
The formula compares the two Timespans and calculates a Numeric type value with the difference in hours.

GETDATE¶


Category	Date
Description	The formula returns the date from a DateTime value.
Syntax	=GETDATE(VALUE `datetime`)
Output	`list`
Arguments	VALUE `datetime` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula extracts the date from a DateTime value. The extracted value is the date expressed as a numeric value. E.g. 22/09/2022 05:00 in numeric value is "20220922050000". =GETDATE() extracts the digits corresponding to the date "20220922".

Examples¶

How to display the date extracted from a DateTime with the American Date Format
The Application must take a date from a DateTime and display the date with the American Date Format "MM-dd-yyyy" (e.g. "09-22-2022").
1
=FORMATDATE(=GETDATE($CURRENTSTORAGE.DateTime); "MM-dd-yyyy")
=GETDATE() extracts from the DateTime the digits representing day, month, and year of the date. The =FORMATDATE() function formats the result of =GETDATE() according to the second parameter.

GETTIME¶


Category	Date
Description	The formula returns the time from a DateTime value.
Syntax	=GETTIME(VALUE `datetime`)
Output	`datetime`
Arguments	VALUE `datetime` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula extracts the time from a DateTime value. The extracted value is the time in hours, minutes, and seconds expressed as a numeric value. E.g. 22/09/2022 05:00 in numeric value is "20220922050000". =GETTIME() extracts the digits corresponding to the date "20220922".

Examples¶

How to display the extracted Time from a DateTime with the 12-hour format (AM/PM)
The Application must take the time from a DateTime and display the time with the 12-hour AM/PM format. (e.g. "03:20 PM").
1
=FORMATTIME(=GETTIME($CURRENTSTORAGE.DateTime); "hh:mm tt")
=GETTIME() extracts from the DateTime the digits corresponding to the hours, minutes, and seconds. Then =FORMATTIME() formats the result of =GETTIME() as a 12-hours clock.

Link¶

GENERATEAPPLINK¶


Category	Link
Description	The formula generates a link to an application.
Syntax	=GENERATEAPPLINK(APPLICATION_ID `string`; FIELD_KEY `string` `optional`; FILTER_VALUE `any` `optional`)
Output	`string`
Arguments	APPLICATION_ID `string` Stands for the Id of the application to be linked. FIELD_KEY `string` `optional` The Storage field name between `' '` (apostrophes). E.g. 'Price', 'isActive'. The formula will take the `FIELD_KEY` to filter the rows according to its value. FILTER_VALUE `any` `optional` The function filters the rows according to this value.).

Explanation¶

The formula generates a link to an Application in a eLegere's environment starting from an application's ID. The User can point a chosen value for a field to filter the application's rows.

Examples¶

Share a Link to an Application showing the rows added today
The Designer is configuring a Send Email custom action. The notification must include a link to the Application. The Application displays only the rows with the "recordDate" equal to today's date.
1 2 3 4
=GENERATEAPPLINK( "b87e99b5-8757-4443-b61b-ad04de55bb32"; "recordDate"; $CURRENTDATE)
The formula works in the body of Send Email custom action betweeen squared brackets ([ ]). When the receiver clicks on the link, the Application displays all the rows with the "recordDate" equal to today's date.

GENERATEROWLINK¶


Category	Link
Description	This formula generates a link to share the current row of an Application
Syntax	=GENERATEROWLINK(STORAGE_NAME `any` `optional`; PRIMARY_KEY `any` `optional`; APPLICATION_ID `string` `optional`)
Output	`string`
Arguments	STORAGE_NAME `any` `optional` Name of the Storage to consider. Use `#storage_name` as placeholder for a specific Storage. PRIMARY_KEY `any` `optional` The Storage field selected in the application as the primary key. E.g. `$CURRENTSTORAGE.ID_Order` APPLICATION_ID `string` `optional` Stands for the Id of the application to be linked.

Explanation¶

The formula generates a link to share the current row of an Application. The User can define a filter to pick up only certain rows. The fourth parameter supports the system variable and related option $CURRENTAPP.ID in place of a string containing the application's ID.

Examples¶

How to share an Order through an Email from another Application
The Designer is configuring a Send Email Custom Action for the Master table. The Custom Action must send an email notification with a link to an order when clicked.
1 2 3 4 5 6
=GENERATEROWLINK( #sPurchaseOrder; #sPurchaseOrder.ID_Order; #sPurchaseOrder.ID_Order; "b87e99b5-8757-4443-b61b-ad04de55bb32" )
The formula works in the body of the Send Email custom action in the Row Context. The formula generates a link showing the row with the order from another application. The formula picks the order's row starting from the ApplicationID and the field chosen as Primary Key in the application.

Conditional¶

IF ¶


Category	Conditional
Description	The formula returns a specific value when the condition is satisfied.
Syntax	=IF(CONDITION `boolean`; IFTRUE `any`; IFFALSE `any`)
Output	`object`
Arguments	CONDITION `boolean` One or more nested conditions that the values must satisfy. The User uses `CONDITION` to manage permissions, filters, and calculated values. `=AND()` `=CONTAINS()` `=CONTAINLIST()` `=CONTAINSTRING()` `=EQ()` `=GT()` `=GTE()` `=ISNOTNULL()` `=ISNULL()` `=LT()` `=LTE()` `=NEQ()` `=NOT()` `=NOTCONTAINS()` `=OR()` IFTRUE `any` The value returned if a value satisfies the condition. IFFALSE `any` The value returned if the condition is not met.

Explanation¶

The formula returns the value of IFTRUE parameter when the condition is satisfied. If the value does not satisfy the condition, the formula returns the IFFALSE parameter. If you must compare only one field with multiple values to yield other values in return, use the =SWITCH() function instead: it's easier and shorter to use. Use the =IF() function when you have more complex conditions to be verified. Learn about the differences between =IF() and =SWITCH() here.

Examples¶

Changing the color of a Table Cell according to the value
Suppose that you have an application tracking your personal finance. You want to setup a formula in Design Mode > Layout > Cell BG Color to change the cell color according to the value.
1 2 3 4 5 6 7 8 9
=IF( =LT($CURRENTSTORAGE.BudgetValue; 0); "red"; =IF( =GT($CURRENTSTORAGE.BudgetValue; 0); "green"; "gray" ) )
If the value of your budget is lower than 0, the application will color the cell of red. If the value is positive, the cell will be green. Otherwise, the application will color the cell of gray if the value is 0.

IFNULL¶


Category	Conditional
Description	The formula replaces a null value with the chosen value.
Syntax	=IFNULL(CONDITION `boolean`; REPLACEMENT `any`)
Output	`any`
Arguments	CONDITION `boolean` One or more nested conditions that the values must satisfy. The User uses `CONDITION` to manage permissions, filters, and calculated values. `=AND()` `=CONTAINS()` `=CONTAINLIST()` `=CONTAINSTRING()` `=EQ()` `=GT()` `=GTE()` `=ISNOTNULL()` `=ISNULL()` `=LT()` `=LTE()` `=NEQ()` `=NOT()` `=NOTCONTAINS()` `=OR()` REPLACEMENT `any` Value that replaces the Storage's value if null.

Explanation¶

The formula checks if the argument is a null value. If null, the formula replaces the argument's null value with a default value different than null. The returned type value depends on the Storage field type.

Examples¶

Fill a field with a default value if the field is left empty
Suppose that you have a Boolean field that can null as value. If left null, the application should fill the Boolean field with the FALSE value automatically.
1
=IFNULL($CURRENTSTORAGE.boolean; $FALSE)
Placing the formula in Design Mode > Formula > Calculated Field forces the application to change the Boolean field's value from null to FALSE.

SWITCH¶


Category	Conditional
Description	The formula checks multiple conditions on a Storage field.
Syntax	=SWITCH(VALUE `string`; =CASE() `any`; =CASES() `any` `optional`; =DEFAULT() `any`)
Output	`object`
Arguments	VALUE `string` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula). =CASE() `any` `=CASE()` has in turn two parameters: `COMPARISON`, the value that the function uses for the comparison; `Output`, what `=CASE()` returns if the `COMPARISON` is equal the value. `=CASE()` does not accept other formulas as arguments. =CASES() `any` `optional` As alternative, the User can use `=CASES()` if the value is a List. =DEFAULT() `any` `=DEFAULT()` represents the output when no value satisfies one of the conditions

Explanation¶

The formula checks a value through multiple conditions yielding different outputs. If the value does not satisfy any of the conditions, the formula returns the DEFAULT parameter. Learn here how the =SWITCH() function differs from the =IF().

Examples¶

How to change the color a table cell accordingly to the value inserted
The cell of an application must change color according to the value stored. If the value is "FP", the cell becomes green. If the value is "SP", the cell becomes yellow. If the value is neither "FP" nor "SP", the cell remains gray.
1 2 3 4 5 6
=SWITCH( $CURRENTSTORAGE.Product_Type_Code; =CASE("FP"; "green"); =CASE("SP"; "yellow"); =DEFAULT("gray") )
The formula works in Design Mode > Layout > Configuration > Cell Bg Color > Set Formula. The table cell changes color according to the product type code.
How to display the currency of an order according to the selected regional area
The Application must display in a Logical Column the order's currency according to the code of the geographical area.
1 2 3 4 5 6 7 8
=SWITCH( $CURRENSTORAGE.regionalCode =CASE(1;"€"); =CASE(2;"$"); =CASE(3;"£"); =CASE(4;"CHF"); =DEFAULT("-") )
The Lookup compares the value of the field "regionalCode". According to the value of "regionalCode", the Logical Column displays a different currency symbol. If the User hasn't chosen any value for "regionalCode", the column displays the symbol "-".

SpatialGeometry¶

LATITUDE¶


Category	SpatialGeometry
Description	The formula extracts the latitude from a couple of geographical coordinates.
Syntax	=LATITUDE(VALUE `string`)
Output	`string`
Arguments	VALUE `string` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula extracts the latitude from a couple of geographical coordinates.

Examples¶

Extracting the Latitude from a geographical position
The function extracts the latitude from a field (e.g. Coordinates) containing geographical coordinates.
1
=LATITUDE($CURRENTSTORAGE.Coordinates)
Suppose that the couple of coordinates is Latitude: 0.41887 and Longitude: 94.89008 (0.41887; 94.89008). The function yields a String type value with the Latitude: 0.41887.

LONGITUDE¶


Category	SpatialGeometry
Description	The formula extracts the longitude from a couple of geographical coordinates.
Syntax	=LONGITUDE(VALUE `string`)
Output	`string`
Arguments	VALUE `string` The values that the function takes either as argument or as part of a parameter. It can be either a `$CURRENTSTORAGE.field_name` or ANY value (depending on the type of formula).

Explanation¶

The formula extracts the longitude from a couple of geographical coordinates.

Examples¶

Extracting the Longitude from a geographical position
The function extracts the longitude from a field (e.g. Coordinates) containing geographical coordinates.
1
=LONGITUDE($CURRENTSTORAGE.Coordinates)
Suppose that the couple of coordinates is Latitude: 0.41887 and Longitude: 94.89008 (0.41887; 94.89008). The function yields a String type value with the Longitude: 94.89008.

Lookup¶

LOOKUP¶


Category	Lookup
Description	The formula searches a Storage for the first entry of a field satisfying the condition requested.
Syntax	=LOOKUP(STORAGE_NAME `string`; STORAGE_FIELD_NAME `string`; CONDITION `boolean`)
Output	`object`
Arguments	STORAGE_NAME `string` Name of the Storage to consider. Use `#storage_name` as placeholder for a specific Storage. STORAGE_FIELD_NAME `string` Name of the Storage Field to consider. Use `#storage_name.field_name` as placeholder for a specific Storage field. CONDITION `boolean` One or more nested conditions that the values must satisfy. The User uses `CONDITION` to manage permissions, filters, and calculated values. `=AND()` `=CONTAINS()` `=CONTAINLIST()` `=CONTAINSTRING()` `=EQ()` `=GT()` `=GTE()` `=ISNOTNULL()` `=ISNULL()` `=LT()` `=LTE()` `=NEQ()` `=NOT()` `=NOTCONTAINS()` `=OR()`

Explanation¶

The formula searches a Storage for the first entry of a field satisfying the condition requested.

Examples¶

How to pick up the year of the chosen car model in a drop-down list
The Application must show the year of the chosen car in a drop-down list as the only choice.
1 2 3 4 5
=LOOKUP( #car_models; #car_models.ID_car; =EQ(#car_models.year;$CURRENTSTORAGE.yearLookup) )
The formula picks from the Storage the first car produced in the year of "yearLookup". The formula compares the ID of the car with the current value of the "year" field.
Fill a drop-down list field with the name of the current user when they create a new row.
The application has a drop-down list containing a list of active users. (Another application working as registry stores the active users' list.) The drop-down list records the name of the user who is inserting the entry in the table. The application should fill the drop-down list with the current user's name automatically by default.
1 2 3 4 5
=LOOKUP( #sUserList; #sUserList.Id_User; =EQ(#sUserList.Email;$CURRENTUSER.EMAIL) )
You should place the formula in Lookup drop-down list's Default Value (Design Mode > Formulas > Default Value > Create default value formula). When a user creates a new entry, the entry's drop-down list takes the name starting from the logged user's email automatically.

LOOKUPLIST¶


Category	Lookup
Description	The formula searches a Storage for the all the entries of a field satisfying the condition requested.
Syntax	=LOOKUPLIST(STORAGE_NAME `string`; STORAGE_FIELD_NAME `string`; CONDITION `boolean` `optional`)
Output	`list`
Arguments	STORAGE_NAME `string` Name of the Storage to consider. Use `#storage_name` as placeholder for a specific Storage. STORAGE_FIELD_NAME `string` Name of the Storage Field to consider. Use `#storage_name.field_name` as placeholder for a specific Storage field. CONDITION `boolean` `optional` One or more nested conditions that the values must satisfy. The User uses `CONDITION` to manage permissions, filters, and calculated values. `=AND()` `=CONTAINS()` `=CONTAINLIST()` `=CONTAINSTRING()` `=EQ()` `=GT()` `=GTE()` `=ISNOTNULL()` `=ISNULL()` `=LT()` `=LTE()` `=NEQ()` `=NOT()` `=NOTCONTAINS()` `=OR()`

Explanation¶

The formula searches a Storage for the all the entries of a field satisfying the condition requested.

Examples¶

How to display all the car models produced after the year 2001 in a drop-down list
The Application must show in a drop-down list only the cars produced after the year specified in the field "yearLookup". Suppose that the User has selected 2001 as year.
1 2 3 4 5 6 7 8
=LOOKUPLIST( #car_models; #car_models.ID_Car; =GTE( #car_models.year; $CURRENTSTORAGE.yearLookup ) )
The formula displays in the drop-down list only the cars produced in the year recorded in "yearLookup" and after. If the value of "yearLookup" is 2001, the drop-down list show only the cars produced since 2001.

Timespan¶

TIMESPAN¶


Category	Timespan
Description	The formula calculates a quantity of time in milliseconds
Syntax	=TIMESPAN(DATE_TIME_NUMBER `numeric`; DATE_TIME_NUMBER `numeric`; DATE_TIME_NUMBER `numeric`; DATE_TIME_NUMBER `numeric`)
Output	`time`
Arguments	DATE_TIME_NUMBER `numeric` The integer number representing either Days, Hours, Minutes or Seconds in a Timespan formula. What the parameter represents depends on the position in the formula. DATE_TIME_NUMBER `numeric` The integer number representing either Days, Hours, Minutes or Seconds in a Timespan formula. What the parameter represents depends on the position in the formula. DATE_TIME_NUMBER `numeric` The integer number representing either Days, Hours, Minutes or Seconds in a Timespan formula. What the parameter represents depends on the position in the formula. DATE_TIME_NUMBER `numeric` The integer number representing either Days, Hours, Minutes or Seconds in a Timespan formula. What the parameter represents depends on the position in the formula.

Explanation¶

The formula yields a Timespan type data in milliseconds starting from four integer numbers representing the number of days, hours, minutes, and seconds respectively. The first parameter is the integer representing the days. The second parameter is the integer representing for the hours. The third parameter is the integer representing the minutes. The fourth parameter is

Examples¶

How to calculate the number of milliseconds starting from day, hour, minutes, and seconds.
The Application must calculate the number of milliseconds starting from integers representing the day, the hour, and the time.
1
=TIMESPAN(3; 5; 2; 50)
The formula yields how many milliseconds 3 days, 5 hours, 2 minutes, and 50 seconds are (277 370 000 milliseconds).