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: 53 - 2
# Result: 1(3 + 3) * (5 * 2)
# Result: 604 / 2
# Result: 2-(-1)
# Result: 14 ^ 3 + 5^(1/2)
# Result: 66.23613^(3^(2))
# Result: 19683(3^3)^2
# Result: 72910!
# Result: 36288004>3 ? 1 : 0
# Result: 13>=2 ? 1 : 0
# Result: 13==3 ? 1 : 0
# Result: 13!=3 ? 1 : 0
# Result: 0true == true
# Result: truefalse == false
# Result: true1 == true
# Result: false - numbers and booleans are diffents0 == true
# Result: false - numbers and booleans are diffentseav_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: 1sqrt(4)
# Result: 2
# Explanation: square root of 4cbrt(8)
# Result: 2
# Explanation: cubic root of 8abs(-4)
# Result: 4
# Explanation: absolute value of -4sum(1, 2, 3)
# Result: 6
# Explanation: the sum of 1, 2 and 3max(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 => 5avg(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 => 10pow(2, 3)
# Result: 8
# Explanation: 2 raised to the 3rd powerstring(5.2)
# Result: "5.2"
# Explanation: Convert the number to a string representationround(5.236)
# Result: 5
# Explanation: Rounds the number to an integer valueround_decimal(5.236, 2)
# Result: 5.24
# Explanation: Rounds the number to a decimal value, given it's decimal precisionlength("abc")
# Result: 3
# Explanation: the number of characters in "abc" stringlength(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 5number("5")
# Result: 5
# Explanation: transforms the string "5" to the number 5toupper("test string")
# Result: "TEST STRING"
# Explanation: capitalize all letters of a stringtolower("TEST STRING")
# Result: "test string"
# Explanation: lowerize all letters of a stringleft("Hello DPMS", 5)
# Result: "Hello"
# Explanation: shows the first 5 letters of a stringright("hello DPMS", 4)
# Result: "DPMS"
# Explanation: shows the last 4 letters of a stringmask("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 issueslink("Google", "www.google.com")
# Result: "<a href=\"www.google.com\">Google</a>"
# Explanation: The HTML href link with the desired name as displaycontains("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.01null == 0
# Result: truenull == 1
# Result: falsenull == false
# Result: falsecurrent_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") => 1hoursdiff(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") => 32add_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 valuedefault_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 columndefault_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_columnand thereturn_column. - lookup_column: This column will be filtered by the value provided in the
valueparameter. - return_column: Contains the object of the search. The
operationwill 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 : falseThis 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 : falseThis 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.
On the Custom Functions:
Parameter Description:
lookup_column.lookup_columnand thereturn_column.valueparameter.operationwill 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 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:In this case, you would need to have a flexible column named id_palete that is of one of the possible
valuetypes 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 possiblelookup_columntypes, along with a column named "predicted_weight" that must be of one of thereturn_columntypes. These columns will then be used by filtering the values provided by the id_palete column in the some_description registers, so that the operation can be performed (in this case, to return the first value in the predicted_weight column where some_description matches the value in id_palete).ℹ️: 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"