An expression is a
task on formula calculation. The expression value is a calculation result. The
expression is built out of constants and columns, operators, function callings
and round brackets.
Getting the value of
the column
To get the value of
the column it is necessary to use the name specified in a column properties as
“Name used in formulas”.
For example, if you
would like to multiply the value of the column named in formulas as “Price” by the value of the column named
as “Quantity”, you can use the
following expression:
quantity*price
About types
The typization of
expression calculation result is dynamic. The type is defined upon calculation.
The type of the operation execution result often depends on the types of
arguments, but sometimes also on the values of arguments. For example, the
following expression if(marked, sum, "Not marked") will return either the value of the sum
field or “Not marked’ string depending on the value of the marked
field.
The following basic
types are available:
·
Boolean
·
String
·
Number
·
Date
·
Time
·
Table
reference
·
Nested table
·
Picture
The values of all
listed types can be compared for equality
(=) or inequality (!=).
Types specification
Boolean
The values of the
Boolean type do not have any properties or methods.
The following
operations can be performed over Boolean type values: NOT, AND, OR
The constants of the
Boolean type are declared as true or false.
String
The values of the
String type do not have any properties or methods.
The strings can be
concatenated with the help of “+” operator.
The following
comparison operators are supported: <, >, <=, >=
The constants of the
String type are defined with the help of double quotes: “String”
Number
The values of the
Number type do not have any properties or methods.
The following
operations are supported: unary -, +, -,
*, /, % (remainder of the division)
The following
comparison operators are supported: <,
>, <=, >=
The constants of the
Number type are defined by the digits. A point (.) is used as a decimal
separator.
Date
The date value
acquired from the Date type field
holds only date as such, but during the calculation, if you add a Time type value to a Date type value it is possible to get a
combined Date/Time value.
Properties:
·
day – returns the day of the month of a given date (21.01.2015
12:01 -> 21)
·
month – returns the month of given date (21.01.2015 12:01
-> 1)
·
year – returns the year of a given date (21.01.2015 12:01
-> 2015)
·
date – returns the date, that does not have a time shift
(21.01.2015 12:01 -> 21.01.2015)
·
time – returns the time shift from 0:00 date (21.01.2015
12:01 -> 12:01)
The values of the Date
type do not have any methods.
The possible
operations:
·
addition to
time – results in a date, shifted forward for a given time
·
subtraction of
time from date – results in a date, shifted backward for a given time
·
subtraction of
date from date – results in a time, elapsed from the subtracted date
The following
comparison operators are supported: <,
>, <=, >=
Time
In general is
represented by a time interval. When getting the time value from the Time type field the value can fall
within 00:00:00 to 23:59:59, but during the expression calculation the result
may be more than one day.
Properties:
·
days – returns a number of days in a given time interval
(1d 12:02:15 -> 1)
·
hours – returns a number of hours in a given time interval (1d
12:02:15 -> 12)
·
minutes – returns a number of minutes in a given time interval
(1d 12:02:15 -> 2)
·
seconds – returns a number of seconds in a given time
interval (1d 12:02:15 -> 15)
·
totalhours – returns a
total number of hours of a given time interval (1d 12:01:15 -> 36)
·
totalminutes – returns a
total number of minutes of a given time interval (1d 12:01:15 -> 2161)
·
totalseconds – returns a
total number of seconds of a given time interval (1d 12:01:15 -> 129675)
The values of the Time type do not have any methods.
The possible
operations: unary -, addition to another
time interval, subtraction from time interval
Table reference
Provides access to
table fields through its properties. For example, “reference.column1”.
No methods are
supported.
Nested table
No properties are
available.
The following methods
are supported:
·
getTotal(<function>,
<column name>) – aggregates
the value of a given column (passed as a the second argument) with the help of
a function specified in the first argument.
For example: table.GetTotal(“Sum”, “column1”)
The following
functions are supported:
o
Sum – a sum
o
Min – a minimum value
o
Max – a maximum value
o
Average – an average value
o
Any – at least one of the logical values is true
o
All – all logical values are true
o
Count – a number of non-zero values as well as the values
without logical negation
o
Variance
o
StandardDeviation
Picture
No methods or
properties are available.
When comparing
pictures, it is assumed that they are not equal by befault.
The functions of conditional choice
if (condition, value1, value2) – the 1st parameter should be
of logical type; the 2nd and the 3rd parameters could be of any type. If the 1st parameter is
true, the function returns the 2nd parameter value, otherwise it returns the
3rd parameter value. For example: if(length(column1) > 0, "Yes", "No")
switch (condition[0], value[0], … , value by default) – if the condition[i] is set to
true, the function returns the value[i], if the condition[i] in any i is false,
the value returns by default.
Formatting Functions
format (object, mask), the object is of any
type except child table and table reference, the mask is of string type.
It returns the object’s string presentation according to the mask.
Mathematical Functions
sin(argument) – calculates the argument sine. The argument is of the numeric type, the
calculation result is of the numeric
type.
cos(argument) – calculates the argument cosine. The argument is of the numeric type, the
calculation result is of the numeric
type.
tan(argument) – calculates the argument tangent. The argument is of the numeric type, the
calculation result is of the numeric
type.
atan(argument) – calculates the argument arctangent. The argument is of the numeric type, the
calculation result is of the numeric
type.
sqr(argument) – calculates the argument to square. The argument is of the numeric type, the
calculation result is of the numeric
type.
sqrt(argument) – calculates the argument‟s square root. The argument is of the numeric type, the
calculation result is of the numeric
type.
log(argument) – calculates the argument‟s natural logarithm. The argument is of the numeric type, the
calculation result is of the numeric
type.
exp(argument) – raise E number to the argument degree. The argument is of the numeric type, the
calculation result is of the numeric
type.
sign(argument) – returns:-1 if the argument is negative, 0 – if the argument is 0, 1 – if
the argument is positive. The argument is of the numeric type, the calculation result is of the numeric type.
abs(argument) – returns the argument‟s absolute value. The argument is of the numeric type, the
calculation result is of the numeric
type.
round(argument1, argument2) – rounds the 1st argument value to the symbols amount after the comma,
assigned by the 2nd argument. The 1st argument is of the double type, the 2nd
argument is of the numeric
type, the calculation result is of the numeric type.
Date & Time Processing Functions
getMonth (argument) – returns the string month name by the date passed by an argument.
getNow() - returns the current date.
getDayOfWeek (argument) – returns the string day of week name by the date passed by an argument.
getQuarter (argument) – returns the quarter number of a date specified as an argument.
addDays (argument, value) - adds days to date. The first argument is a value of date type and the second argument is a
number of days specified as numeric
value. The result is a new date.
addHours (argument, value) - adds hours to date or time. The first argument is a value of date or time type and the
second argument is a number of hours specified as a numeric value. The result is a new date or
time.
addMinutes (argument, value) - adds minutes to date or time. The first argument is a value of date or time type and the
second argument is a number of minutes specified as a numeric value. The result is a new date
or time.
addMonths (argument, value) - adds months to date. The first argument is a value of date type and the second
argument is a number of months specified as a numeric value. The result is a new date.
addSeconds (argument, value) - adds seconds to date or time. The first argument is a value of date or time type and the
second argument is a number of seconds specified as a numeric value. The result is a new date
or time.
addYears (argument, value) - adds years to date. The first argument is a value of date type and the second argument is
a number of years specified as a numeric value. The result is a new date.
String Functions
concat (string1, string2) - returns the result of appending one string (string2) to the end of
another string (string1)
indexOf(string1, string2,[start]) - returns the position of the first occurrence of a specified value in a
string; returns -1 if the value to search for never occurs. [start] – Optional
parameter. Default 0. At which position to start the search.
insert(string,position,subString) - inserts the target string (subString) into the source string (string) at
specified (position)
length(string) - returns the number of characters
in the string
padLeft(string,length,char) - adds characters(char) to the left
of a string till a certain length is reached
padRight(string,length,char) - adds characters(char) to the
right of a string till a certain length is reached
remove(string,start,length) - eliminates a range of characters in a string to a specified length (length)
beginning from the (start) position
replace (string, subString, newSubString) - returns a string corresponding to the replacement of a given string with
another in the specified string. Input
string (string), a string to be replaced (subString), a string to replace all
occurrences of substring (newSubString)
subString(string,start,length) - returns a substring of the
specified string. If the (length)
is greater than zero, the function returns a subString starting at character
position (start) with a length
of (length) characters. If the
(length) is equal to zero, the
function returns an empty string. Input string (string), position of the start
of the substring (start), the length of the substring (length)
toLower(string) - returns the specified string converted to lowercase
toUpper(string) - returns the specified string converted to uppercase
trim(string) - returns the given string trimmed of whitespace
trimEnd(string) – returns the given string trimmed of trailing whitespace
trimStart(string) - returns the given string trimmed of leading whitespace
regIsMatch(string,expressionString) - returns the sign of string matching the regular
expression (expressionString)
regReplace(string, expressionString, newString) – replaces a substring matching the regular expression (expressionString)
in a (string) with a (newString)