This file has the purpose to fully explain the possibilities of the Parsec formulas equations. Here you can check the syntax, examples, variable types, operators and a real example.
- Integer
- Float
- String
- Boolean
- Date
- DateTime
NULL
3 + 2
# Result: 5
3 - 2
# Result: 1
(3 + 3) * (5 * 2)
# Result: 60
4 / 2
# Result: 2
-(-1)
# Result: 1
4 ^ 3 + 5^(1/2)
# Result: 66.2361
3^(3^(2))
# Result: 19683
(3^3)^2
# Result: 729
10!
# Result: 3628800
4>3 ? 1 : 0
# Result: 1
3>=2 ? 1 : 0
# Result: 1
3==3 ? 1 : 0
# Result: 1
3!=3 ? 1 : 0
# Result: 0
true == true
# Result: true
false == false
# Result: true
1 == true
# Result: false - numbers and booleans are diffents
0 == true
# Result: false - numbers and booleans are diffents
eav_template_progress.flexible_value == true
# Result: Only results true if flexible_value column is a boolean and your value is true
"Here is a string"
# Result: "Here is a string"
5 > 3 ? "yes" : "no"
# Result: "yes"
"this" == "this" ? 1 : 0
# Result: 1
sqrt(4)
# Result: 2
# Explanation: square root of 4
cbrt(8)
# Result: 2
# Explanation: cubic root of 8
abs(-4)
# Result: 4
# Explanation: absolute value of -4
sum(1, 2, 3)
# Result: 6
# Explanation: the sum of 1, 2 and 3
max(1, 2) + min(3, 4)
# Result: 5
# Explanation: maximum value between 1 and 2 plus minimum value between 3 and 4. That means: 2 + 3 => 5
avg(5.0, 10.0, 15.0)
# Result: 10
# Explanation: the average value between 5.0, 10.0 and 15.0. That means: (5.0 + 10.0 + 15.0)/3 => 10
pow(2, 3)
# Result: 8
# Explanation: 2 raised to the 3rd power
string(5.2)
# Result: "5.2"
# Explanation: Convert the number to a string representation
round(5.236)
# Result: 5
# Explanation: Rounds the number to an integer value
round_decimal(5.236, 2)
# Result: 5.24
# Explanation: Rounds the number to a decimal value, given it's decimal precision
length("abc")
# Result: 3
# Explanation: the number of characters in "abc" string
length(default_value(eav_template_datasheet.empty, ""))
# Result: 0
# Explanation: The usage of default_value is necessary because when a column with the name
# "empty" has no value this length comparison will return an error instead 0 cause
# NULL is not a string.
concat("Hello ", "World")
# Result: "Hello World"
# Explanation: concatenation of "Hello " and "World"
str2number("5")
# Result: 5
# Explanation: transforms the string "5" to the number 5
number("5")
# Result: 5
# Explanation: transforms the string "5" to the number 5
toupper("test string")
# Result: "TEST STRING"
# Explanation: capitalize all letters of a string
tolower("TEST STRING")
# Result: "test string"
# Explanation: lowerize all letters of a string
left("Hello DPMS", 5)
# Result: "Hello"
# Explanation: shows the first 5 letters of a string
right("hello DPMS", 4)
# Result: "DPMS"
# Explanation: shows the last 4 letters of a string
mask("000-000-000", 123456789)
# Result: "123-456-789"
# Explanation: The mask applied to a number
# Observation: You should not use a number that has more than 17 digits as a second parameter due to imprecision issues
link("Google", "www.google.com")
# Result: "<a href=\"www.google.com\">Google</a>"
# Explanation: The HTML href link with the desired name as display
contains("Hello World", "orld")
# Result: true.
# Rationale: The string "orld" IS a sub string of "Hello World").
contains("One Flew Over The Cuckoo's", "Nest")
# Result: false.
# Rationale: The string "Nest" IS NOT a sub string of "One Flew Over The Cuckoo's".
timediff(start_time, end_time)
# Result: The difference (in hours) between two times (start_time and end_time) formatted like HH:MM:SS.
# Examples
timediff("03:30:00", "02:00:00") => 25.5
timediff("02:00:00", "03:30:00") => 1.5
timediff("02:00:00", "02:00:30") => 0.01
null == 0
# Result: true
null == 1
# Result: false
null == false
# Result: false
current_date()
# Result: The today date.
# Example
current_date() => "2019-01-01".
daysdiff(date1, date2)
# Result: The difference (in days) between these two dates.
# Example
daysdiff("2019-01-01", "2019-01-02") => 1
hoursdiff(date1, date2)
# Result: The difference (in hours) between two dates (or two date times).
# Examples
hoursdiff("2019-01-01", "2019-01-02") => 24
hoursdiff("2019-01-01T00:00", "2019-01-02T08:00") => 32
add_days(date, number)
# Result: The addition (in days) between a date and a days quantity.
# Examples
add_days("2019-01-01", 3) => "2019-01-04"
add_days("2019-01-01T08:30", -1) => "2018-12-31T08:30"
You can also use the current_date()
function as parameters of daysdiff()
or hoursdiff()
functions.
daysdiff(date1, current_date())
# Result: The difference (in days) between the date1 and the current date.
hoursdiff(date1, current_date())
# Result: The difference (in hours) between the date1 and the current date.
Calculate the text value as if it was an equation
calculate("2+2+2*4")
# Result: "12"
calculate("(2+2)*4")
# Result: "16"
calculate("2^4")
# Result: "16"
calculate("sqrt(9)")
# Result: "3"
calculate("abs(-50)")
# Result: "50"
calculate("round(1.123)")
# Result: "1"
calculate("add_days(\"2019-01-01\", 3)")
# Result: "2019-01-04"
calculate("daysdiff(\"2019-01-01\", \"2019-01-02\")")
# Result: "1"
calculate("hoursdiff(\"2019-01-01\", \"2019-01-02\")")
# Result: "24"
calculate("3 > 2 ? \"higher\" : \"lower\"")
# Result: "higher"
calculate("3 < 2 ? \"higher\" : \"lower\"")
# Result: "lower"
calculate("concat(\"One \", concat(\"Two\", \" Three\")))"'
# Result: "One Two Three"
calculate("\"One\" // \" \" // \"Two\" // \" \" // \"Three\"")
# Result: "One Two Three"
calculate("number(calculate(\"1 + 1\")) + 1")
# Result: "3"
default_value(eav_template_datasheet.empty, "word")
# Result: "word" or empty column content
# Explanation: the result is "word" when eav_template_datasheet.empty has no value
default_value(eav_template_datasheet.string, "")
# Result: "" (empty string) or string column content
# Explanation: It could beseful when you need to return a string instead a null value when there
# is no value at string column
default_value(eav_template_datasheet.number, 0)
# Result: 0 or number column content
# Explanation: It could beseful when you need to return a number instead a null value when there
# is no value at string number
- matrix_lookup(matrix_id, column, row)
Considering the Matrix (or Spreadsheet) with the specified matrix_id, a lookup search is executed in order to find the cell value which has the column specified on column and the row specified on row.
- Example
Considering the following Matrix.
id:4 | A | B | C | D |
---|---|---|---|---|
1 | 0.1 | 0.2 | 0.3 | 0.4 |
2 | 0.5 | 0.6 | 0.7 | 0.8 |
matrix_lookup(4, "B", "2")
# Result: 0.6
- children_sum(child_column)
This is a function that is used inside a formula equation in the Template for Scoping or Performance.
With this function, you can sum the values of integer
/decimal
/formula
flexible columns from the Estimate Services associated with the Scope.
To create the children_sum()
function, you need to setup a formula flexible column in the Template for Scoping or Performance with an equation like this:
children_sum("eav_template_estimate_service.volume")
In this case, you would need to have the integer
/decimal
/formula
flexible column named volume
in the Estimate Service Template, which will be used to sum their values.
In the case the volume
is a formula flexible column, the equation can only use integer
/decimal
/formula
flexible columns from the Estimate Service template. If the volume
is a formula that uses another formula inside it's equation, that formula can only use integer
/decimal
/formula
flexible columns from the Estimate Service template. This same behavior happens if a formula uses another formula that uses another formula: All of them must use integer
, decimal
or another formula
that uses integer
or decimal
.
- Example
children_sum("eav_template_estimate_service.volume")
formula_a
= 1 + 2formula_b
=formula_a
+ 3formula_c
=formula_b
+ 3eav_template_estimate_service.volume
=formula_c
- xlookup(value, template_id, lookup_column, return_column, operation)
Parameter Description:
- value: Value to be searched for in the
lookup_column
. - template_id: Is the id of the template in which the search will happen. It must contain both the
lookup_column
and thereturn_column
. - lookup_column: This column will be filtered by the value provided in the
value
parameter. - return_column: Contains the object of the search. The
operation
will be applied in this column, after the filter. - operation: It will take the items returned by the filter and perform a predefined action.
Possible types for each parameter:
- value: (string, date, multiline_text, link, datasheet_filter, formula)
- template_id: integer
- lookup_column: (string, date, multiline_text, link, datasheet_filter, lookup) // A flexible column inside the template_id
- return_column: (string, integer, decimal) // A flexible column inside the template_id
- operation: string ('first', 'last' and 'sum')
xlookup()
is a function that is used inside formula equation in any template.
With this function, you can filter data of a datasheet, contract service or request template lookup_column by a value, and make an operation ('first', 'last', 'sum') to return data of another column of the template.
To create the xlookup()
function, you need to setup a formula flexible column in the Template for Template with an equation like this:
xlookup(eav_template_scoping.id_palete, 3812, "some_description", "predicted_weight", "first")
⚙️ In this case, you would need to have a flexible column named id_palete
that is of one of the possible value
types in the template that will use the xlookup()
. You will also need a Datasheet, Contract Service or Request template with id 3812, which must have a column named "some_description" that is of one of the possible lookup_column
types, along with a column named "predicted_weight" that must be of one of the return_column
types.
🚀 On that example, the value of the id_palete
flexible column will be used on the template with id 3812
to filter the items by matching the id_palete
value of the origin template with the "some_description"
value of the target template. After that, the operation "first"
will be applied with the "predicted_weight"
flexible column, by bringing the value of the "predicted_weight"
as a result.
ℹ️: If the value
(1st parameter) is NULL
/null
/""
, the result will be: 0
.
ℹ️: The xlookup()
function just gets an snapshot of the value in the form calculation in order to save it, but it doesn't automatically update once a record in the Datasheet, Contract Service or Request Template is updated.
ℹ️: When using formula as the type in the value parameter, it is important that the formula resolves to a string. Ex: concat("My ", "String"), eav_template_request.decimal > 10 ? "this" : "that"
length(x), concat(x, y), str2number(x), number(x), toupper(x), tolower(x),
left(x, y), right(x, y), pow(x, y), exp(x), round(x), round_decimal(x, y),
min(x, y, ...), max(x, y, ...), sum(x, y, ...), avg(x, y, ...), string(x),
sin(x), cos(x), tan(x), sinh(x), cosh(x), tanh(x), asin(x), acos(x), atan(x),
asinh(x), acosh(x), atanh(x), ln(x), log(x), log10(x), abs(x), sqrt(x), cbrt(x),
real(x), imag(x), conj(x), arg(x), norm(x), sizeof(x), eye(x), ones(x), zeros(x),
daysdiff(x, y), current_date(), contains(x, y), matrix_lookup(x, y, z), children_sum(x).
+, -, *, /, ^
and, or, &&, ||, ==, !=, >, <, <=, >=
CONDITION ? EXECUTED_IF_TRUE : EXECUTED_IF_FALSE
&, |, <<, >>
estimate_services.application_price > 2500 ? "DISCOUNTED RATES" : "NORMAL RATES"
This formula means: If the estimate_services.application_price is greater than 2500, then display DISCOUNTED RATES, otherwise, display NORMAL RATES.
Any formula created to control whether a column is visible or required to be filled in needs the result of the equation to be a Boolean value: True or false.
It is possible to create different formulas to separately control the web visibility and mobile visibility of a column. If a column is mandatory but it is invisible. This column will be visible! Mandatory columns are always visible.
estimate_services.application_price > 2500 ? true : false
This formula means: If the estimate_services.application_price is greater than 2500, then display the column else it will be hidden on setup.
length(default_value(eav_template_datasheet.string, "")) > 0 ? true : false
This formula means: If the eav_template_datasheet.string has any value typed, that is, it is not null then display the column else it will be hidden on setup.
Parameter Description:
lookup_column
.lookup_column
and thereturn_column
.value
parameter.operation
will be applied in this column, after the filter.Possible types for each parameter:
xlookup()
is a function that is used inside formula equation in any template.With this function, you can filter data of a datasheet, contract service, request, scope, or progress template lookup_column by a value, and make an operation ('first', 'last', 'sum', 'max') to return data of another column of the template. The return column can also be the same column that
xlookup
will be used (the only case of return column with type formula).To create the
xlookup()
function, you need to setup a formula flexible column in the Template for Template with an equation like this:To use an integer as value in the function, the number should be between quotes, example:
⚙️ In this case, you would need to have a flexible column named
id_palete
that is of one of the possiblevalue
types in the template that will use thexlookup()
. You will also need a Datasheet, Contract Service, Request, Scope, Progress template with id 3812, which must have a column named "some_description" that is of one of the possiblelookup_column
types, along with a column named "predicted_weight" that must be of one of thereturn_column
types.🚀 On that example, the value of the
id_palete
flexible column will be used on the template with id3812
to filter the items by matching theid_palete
value of the origin template with the"some_description"
value of the target template. After that, the operation"first"
will be applied with the"predicted_weight"
flexible column, by bringing the value of the"predicted_weight"
as a result.ℹ️: If the
value
(1st parameter) isNULL
/null
/""
, the result will be:0
.ℹ️: The
xlookup()
function just gets a snapshot of the value in the form calculation in order to save it, but it doesn't automatically update once a record in the Datasheet, Contract Service, Request, Scope, Progress Template is updated.ℹ️: When using formula as the type in the value parameter, it is important that the formula resolves to a string. Ex: concat("My ", "String"), eav_template_request.decimal > 10 ? "this" : "that"
Parameter Description and possible types: same of
xlookup
custom functionThis function follows the same rules of
xlookup
, with the addition of 2 new rules compared withxlookup
:xlookup
just considers active items,xlookup_all
includes all items, active and inactive. That means this function considers deleted items(inactive items).xlookup_all
withtemplate_id
param equals to the id of this template('a), the evaluation ofxlookup_all
column in an item('b') will only match items older them the item been evaluated('b'). It means newer items will not be considered when evaluatingxlookup_all
for older items.List of all functions: