Parsec Formulas

dpms_formulas.md

Parsec Formulas 🔢

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.

Variable Types

We support the following types:

1. Integer
2. Float
3. String
4. Boolean
5. Date
6. DateTime
7. NULL

Possibilities

Supports basic arithmetic equations.

3 + 2
# Result: 5

3 - 2
# Result: 1

(3 + 3) * (5 * 2)
# Result: 60

4 / 2
# Result: 2

-(-1)
# Result: 1

Supports advanced arithmetic equations.

4 ^ 3 + 5^(1/2)
# Result: 66.2361

3^(3^(2))
# Result: 19683

(3^3)^2
# Result: 729

10!
# Result: 3628800

Supports the IF...THEN...ELSE operator, also known as ternary operator. ?:

Syntax: CONDITION ? EXECUTED_IF_TRUE : EXECUTED_IF_FALSE

4>3 ? 1 : 0
# Result: 1

3>=2 ? 1 : 0
# Result: 1

3==3 ? 1 : 0
# Result: 1

3!=3 ? 1 : 0
# Result: 0

Supports bollean results

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

Supports string values, not only numbers!

"Here is a string"
# Result: "Here is a string"

5 > 3 ? "yes" : "no"
# Result: "yes"

"this" == "this" ? 1 : 0
# Result: 1

Also supports a large variety of built-in functions!

If you want a customized one, please contact the DPMS team.

Number related functions

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

String related functions

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 comparisons results

null == 0
# Result: true

null == 1
# Result: false

null == false
# Result: false

Date related functions

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 function

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"

Other functions

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

Custom functions

• 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")

1. formula_a = 1 + 2
2. formula_b = formula_a + 3
3. formula_c = formula_b + 3
4. eav_template_estimate_service.volume = formula_c

• xlookup(value, template_id, lookup_column, return_column, operation)

Parameter Description:

1. value: Value to be searched for in the lookup_column.
2. template_id: Is the id of the template in which the search will happen. It must contain both the lookup_column and the return_column.
3. lookup_column: This column will be filtered by the value provided in the value parameter.
4. return_column: Contains the object of the search. The operation will be applied in this column, after the filter.
5. operation: It will take the items returned by the filter and perform a predefined action.

Possible types for each parameter:

1. value: (string, date, multiline_text, link, datasheet_filter, formula)
2. template_id: integer
3. lookup_column: (string, date, multiline_text, link, datasheet_filter, lookup) // A flexible column inside the template_id
4. return_column: (string, integer, decimal) // A flexible column inside the template_id
5. 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"

List of all functions:

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).

Operators

Math standard operators

+, -, *, /, ^

Logical operators

and, or, &&, ||, ==, !=, >, <, <=, >=

Ternary operator

CONDITION ? EXECUTED_IF_TRUE : EXECUTED_IF_FALSE

Bit manipulation

&, |, <<, >>

Real example

IF-THEN-ELSE operator (?:)

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.

visibility and mandatory equations

Expected result

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.

Examples

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.

On the String Related functions:

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".

On the Custom Functions:

children_sum() is a function that is used inside formula equation in Template for Scoping.

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 Template 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.

On the Custom Functions:

xlookup() is a function that is used inside formula equation in any template.

With this function, you can filter data of a datasheet or contract service 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:

Syntax:

xlookup(VALUE, TEMPLATE_ID, LOOKUP_COL, RETURN_COL, OPERATION)

VALUE: (string, date, multiline_text, link, datasheet_filter)
TEMPLATE_ID: integer
LOOKUP_COL: string
RETURN_COL: (string, integer, decimal)
OPERATION: string ('first', 'last' and 'sum')

xlookup(eav_template_scoping.id_palete, 3812,"some_description", "predicted_weight", "first") 

In this case, you would need to have the string/date/multiline_text/link/datasheet_filter flexible column named id_palete in the Datasheet or Contract Service Template with id 3812, which will be used to search it's value filtering the records that have the value in the some_description column and return the first value of predicted_weight column.

PS: The xlookup() function just gets an snapshot of the value in the form calculation in order to save it, but it doesn't automatically updates once that a record into the Datasheet or Contract Service Template is updated.

On the Custom Functions:

• xlookup(value, template_id, lookup_column, return_column, operation)

Parameter Description:

1. value: Value to be searched for in the lookup_column.
2. template_id: Is the id of the template in which the search will happen. It must contain both the lookup_column and the return_column.
3. lookup_column: This column will be filtered by the value provided in the value parameter.
4. return_column: Contains the object of the search. The operation will be applied in this column, after the filter.
5. operation: It will take the items returned by the filter and perform a predefined action.

Possible types for each parameter:

1. value: (string, date, multiline_text, link, datasheet_filter, formula)
2. template_id: integer
3. lookup_column: (string, date, multiline_text, link, datasheet_filter, lookup)
4. return_column: (string, integer, decimal)
5. 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. 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"

Default value

This formula means: If the first parameter is null, empty, return the second parameter as result

default_value(eav_template_datasheet.empty, "word")
# Result: 3
# Explanation: the result is "word" when eav_template_datasheet.empty has no value

Time related functions

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

Parsec Formulas 🔢

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.

Variable Types

We support the following types:

1. Integer
2. Float
3. String
4. Boolean
5. Date
6. DateTime
7. NULL

Possibilities

Supports basic arithmetic equations.

3 + 2
# Result: 5

3 - 2
# Result: 1

(3 + 3) * (5 * 2)
# Result: 60

4 / 2
# Result: 2

-(-1)
# Result: 1

Supports advanced arithmetic equations.

4 ^ 3 + 5^(1/2)
# Result: 66.2361

3^(3^(2))
# Result: 19683

(3^3)^2
# Result: 729

10!
# Result: 3628800

Supports the IF...THEN...ELSE operator, also known as ternary operator. ?:

Syntax: CONDITION ? EXECUTED_IF_TRUE : EXECUTED_IF_FALSE

4>3 ? 1 : 0
# Result: 1

3>=2 ? 1 : 0
# Result: 1

3==3 ? 1 : 0
# Result: 1

3!=3 ? 1 : 0
# Result: 0

Supports bollean results

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

Supports string values, not only numbers!

"Here is a string"
# Result: "Here is a string"

5 > 3 ? "yes" : "no"
# Result: "yes"

"this" == "this" ? 1 : 0
# Result: 1

Also supports a large variety of built-in functions!

If you want a customized one, please contact the DPMS team.

Number related functions

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

String related functions

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 comparisons results

null == 0
# Result: true

null == 1
# Result: false

null == false
# Result: false

Date related functions

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.

Other functions

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

Custom functions

• 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 formula equation in Template for Scoping.

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 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")

1. formula_a = 1 + 2
2. formula_b = formula_a + 3
3. formula_c = formula_b + 3
4. eav_template_estimate_service.volume = formula_c

• xlookup(value, template_id, lookup_column, return_column, operation)

Parameter Description:

1. value: Value to be searched for in the lookup_column.
2. template_id: Is the id of the template in which the search will happen. It must contain both the lookup_column and the return_column.
3. lookup_column: This column will be filtered by the value provided in the value parameter.
4. return_column: Contains the object of the search. The operation will be applied in this column, after the filter.
5. operation: It will take the items returned by the filter and perform a predefined action.

Possible types for each parameter:

1. value: (integer, string, date, multiline_text, link, datasheet_filter, formula)
2. template_id: integer
3. lookup_column: (integer, string, date, multiline_text, link, datasheet_filter, lookup) // A flexible column inside the template_id
4. return_column: (string, integer, decimal, formula) // A flexible column inside the template_id
5. operation: string ('first', 'last', 'sum', 'max')

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:

xlookup(eav_template_scoping.id_palete, 3812, "some_description", "predicted_weight", "first")

To use an integer as value in the function, the number should be between quotes, example:

xlookup("20", 3812, "number_of_something", "another_number", "max")

⚙️ 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, Request, Scope, Progress 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 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"

• xlookup_all(value, template_id, lookup_column, return_column, operation)

Parameter Description and possible types: same of xlookup custom function

This function follows the same rules of xlookup, with the addition of 2 new rules compared with xlookup:

• While xlookup just considers active items, xlookup_all includes all items, active and inactive. That means this function considers deleted items(inactive items).
• When a formula column created in a template('a') is using xlookup_all with template_id param equals to the id of this template('a), the evaluation of xlookup_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 evaluating xlookup_all for older items.

xlookup_all("string", 3812, "type_of_column", "number_of_items", "max")

List of all functions:

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), xlookup(x), xlookup_all(x).

Operators

Math standard operators

+, -, *, /, ^

Logical operators

and, or, xor, ==, !=, >, <, <=, >=

Ternary operator

CONDITION ? EXECUTED_IF_TRUE : EXECUTED_IF_FALSE

Bit manipulation

&, |, <<, >>

Real example

IF-THEN-ELSE operator (?:)

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.

visibility and mandatory equations

Expected result

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.

Examples

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.

• xlookup(value, template_id, lookup_column, return_column, operation)

Parameter Description:

1. value: Value to be searched for in the lookup_column.
2. template_id: Is the id of the template in which the search will happen. It must contain both the lookup_column and the return_column.
3. lookup_column: This column will be filtered by the value provided in the value parameter.
4. return_column: Contains the object of the search. The operation will be applied in this column, after the filter.
5. operation: It will take the items returned by the filter and perform a predefined action.

Possible types for each parameter:

1. value: (integer, string, date, multiline_text, link, datasheet_filter, formula)
2. template_id: integer
3. lookup_column: (integer, string, date, multiline_text, link, datasheet_filter, lookup) // A flexible column inside the template_id
4. return_column: (string, integer, decimal, formula) // A flexible column inside the template_id
5. operation: string ('first', 'last', 'sum', 'max')

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:

xlookup(eav_template_scoping.id_palete, 3812, "some_description", "predicted_weight", "first")

To use an integer as value in the function, the number should be between quotes, example:

xlookup("20", 3812, "number_of_something", "another_number", "max")

⚙️ 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, Request, Scope, Progress 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 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"

• xlookup_all(value, template_id, lookup_column, return_column, operation)

Parameter Description and possible types: same of xlookup custom function

This function follows the same rules of xlookup, with the addition of 2 new rules compared with xlookup:

• While xlookup just considers active items, xlookup_all includes all items, active and inactive. That means this function considers deleted items(inactive items).
• When a formula column created in a template('a') is using xlookup_all with template_id param equals to the id of this template('a), the evaluation of xlookup_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 evaluating xlookup_all for older items.

xlookup_all("string", 3812, "type_of_column", "number_of_items", "max")

List of all functions:

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), xlookup(x), xlookup_all(x).

• 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: