Victorcorcos/dpms_formulas.md

## dpms_formulas.md

      
    Raw
  

              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:


Integer
Float
String
Boolean
Date
DateTime
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")

formula_a =  1 + 2
formula_b = formula_a + 3
formula_c = formula_b + 3
eav_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 the return_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"
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.