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 |
|
Multi-Line Comments¶
The Formula Editor considers as comment any line between /* */
.
1 2 3 4 5 |
|
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 |
|
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¶
-
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 |
|
Explanation¶
Aggregates the values of a Detail's column and applies an arithmetical operation on the values: sum, count, average, max or min.
Examples¶
-
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.
-
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
. -
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
-
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 |
|
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¶
-
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 |
|
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¶
-
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 |
|
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¶
-
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 |
|
Explanation¶
The formula rounds a float number to the closest and highest approximated number.
Examples¶
-
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 |
|
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¶
-
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 |
|
Explanation¶
The formula count the number of row satisfying a condition in a column.
Examples¶
-
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 |
|
Explanation¶
The formula calculates the value of the exponential of e (2.71828). The formula returns e raised of the chosen exponential.
Examples¶
-
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 |
|
Explanation¶
The formula rounds down a decimal number to the closest and lowest integer number.
Examples¶
-
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 |
|
Explanation¶
The function calculates the Logarithm of a number to a specified base.
Examples¶
-
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 |
|
Explanation¶
The function calculates the Logarithm of a number having base the mathematical constant e = 2.718; i.e. the Natural logarithm.
Examples¶
-
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 |
|
Explanation¶
The formula scans the column and returns the highest value stored.
Examples¶
-
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 |
|
Explanation¶
The formula scans the column and returns the lowest value stored.
Examples¶
-
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 |
|
Explanation¶
The function calculates a number's exponentiation given the power.
Examples¶
-
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 |
|
Explanation¶
The formula takes the value of VALUE
and converts it into radians.
Examples¶
-
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 |
|
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¶
-
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. -
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.
-
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 |
|
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¶
-
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 |
|
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¶
-
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 |
|
Explanation¶
The formula returns the square root of VALUE
. For example: "=SQRT(9)" returns 3 as result.
Examples¶
-
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 |
|
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¶
-
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 withTRUE
value.
TAN¶
Category | Numeric |
Description | The formula calculates the tangent of an angle in radians. |
Syntax | =TAN(STORAGE_NAME numeric ) |
Output | string |
Arguments |
|
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¶
-
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¶
-
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 |
|
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¶
-
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 |
|
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¶
-
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.1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
=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 |
|
Explanation¶
The formula lists the email addresses of all the members belonging to a User group.
Examples¶
-
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 |
|
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¶
-
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 |
|
Explanation¶
The formula returns a list of the elements considered or the elements of a Storage field.
Examples¶
-
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¶
-
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 |
|
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¶
-
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 |
|
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¶
-
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 |
|
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¶
-
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 |
|
Explanation¶
The formula inverts the Boolean value that another formula returns. I.e. TRUE
becomes FALSE
, FALSE
becomes TRUE
.
Examples¶
-
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 |
|
Explanation¶
The formula takes two or more conditions as argument. The formula returns TRUE
Boolean if a value satisfies at least one condition.
Examples¶
-
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 |
|
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¶
-
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.
-
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").
-
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
=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 |
|
Explanation¶
The formula formats the DateTime value provided according to the format in the second parameter STRING_FORMAT
.
Examples¶
-
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"
-
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 |
|
Explanation¶
The formula formats a Numeric type value provided according to the format in the STRING_FORMAT
parameter.
Examples¶
-
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 |
|
Explanation¶
The formula formats a Time type value provided according to the format in the STRING_FORMAT
parameter.
Examples¶
-
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¶
-
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 |
|
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¶
-
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 |
|
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¶
-
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 |
|
Explanation¶
The formula calculates the string length. The string length is the number of total characters.
Examples¶
-
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 |
|
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¶
-
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 |
|
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¶
-
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 |
|
Explanation¶
The formula merges the strings in the list in one String type value. The SEPARATOR
string parameter will divide each of them.
Examples¶
-
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. -
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.1 2 3 4 5 6 7 8 9 10 11
=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".
-
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.
1 2 3 4 5 6 7 8 9 10 11
=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 |
|
Explanation¶
The formula finds one piece of text in the String and replaces it with another text.
Examples¶
-
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 |
|
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¶
-
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 |
|
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¶
-
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 |
|
Explanation¶
The formula compares two string values. If the one string is contained in the other, the formula yields a TRUE
value.
Examples¶
-
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 |
|
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¶
-
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 |
|
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¶
-
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 |
|
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¶
-
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 |
|
Explanation¶
The formula checks if the value is not null.
Examples¶
-
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 |
|
Explanation¶
The formula checks if the value is null.
Examples¶
-
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 aFALSE
Boolean value.
LT¶
Category | Comparison |
Description | Checks if one value is lower than another one. |
Syntax | =LT(VALUE numeric ; VALUE numeric ) |
Output | boolean |
Arguments |
|
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¶
-
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 |
|
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¶
-
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 |
|
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¶
-
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 |
|
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¶
-
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 |
|
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¶
-
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".
-
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.
-
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.
-
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.
-
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 |
|
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¶
-
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 |
|
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¶
-
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.
-
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 |
|
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¶
-
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.
-
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 |
|
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¶
-
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 |
|
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¶
-
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 |
|
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¶
-
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 ; STORAGE_NAME any optional ; PRIMARY_KEY any optional ; APPLICATION_ID string optional ) |
Output | string |
Arguments |
|
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¶
-
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 |
|
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.
Examples¶
-
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 |
|
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¶
-
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 |
|
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.
Examples¶
-
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.
-
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 |
|
Explanation¶
The formula extracts the latitude from a couple of geographical coordinates.
Examples¶
-
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 |
|
Explanation¶
The formula extracts the longitude from a couple of geographical coordinates.
Examples¶
-
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 |
|
Explanation¶
The formula searches a Storage for the first entry of a field satisfying the condition requested.
Examples¶
-
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.
-
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 |
|
Explanation¶
The formula searches a Storage for the all the entries of a field satisfying the condition requested.
Examples¶
-
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 |
|
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¶
-
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).