# Formula Evaluator

Many EDS modules have processes available to extract data from external sources (such as a spreadsheet), or output data to a deliverable (such as a loop diagram). Often, there is a difference between the input and/or desired output format(s), and the EDS storage format. Formulae allow the user to control how this data is converted from one form to another, and this help topic explains how these formulae must be constructed, and how they are evaluated to produce a result.

## Basics

### Formula Syntax

*text_a* #*field_or_expression_a*# *text_b* #*field_or_expression_b*# ...

Unless otherwise specified (on the help page for the process using formulae), formulae must contain one or more pairs of hashes (**#...#**) that wrap around a field name or expression. Text before and after pairs of hashes is treated as fixed text (if present), and concatenated with the values produced by the field names/expressions inside the hashes.

For example, **#PNLNO#--#TAGNAME#/#TERM#** would yield something similar to **JB02--X101/8**. All three hash pairs contain a field name in this example.

When the text inside a pair of hashes contains opening and closing round brackets (preceeded by a function name), it is treated as a formula expression.

For example, **#JOIN("-", [PNLNO], [TAGNAME], [TERM])#** would yield something similar to **JB02-X101-8**. The single hash pair contains a function expression in this example.

To include a hash inside a field name or anywhere inside a function expression, escape it by preceding it with a backslash. For example **#CONCAT("\#", [TERM])#** would yield something like **#8**.

#### Simple Formulae

*text_a* #*field_a*# *text_b* #*field_b*# ...

A formula is considered a *Simple Formula* when all hash pairs contain only field names, ie, no expressions are present.

While most processes evaluate formulae to produce output values, some processes read in output values, and decompose them into their constituent parts, based on formulae. This will only work for formulae that are *Simple Formulae*, as expressions are not reversible.

See IM Importing from a Datasheet, Protogen Updating DBF from Clone DWGs.

### Expression Syntax

The expression syntax expected by the Formula Evaluator is designed to be very similar to that used by Microsoft Excel - a function, that takes ones or more expressions as parameters, some of which themselves may also be functions with parameters, and so on.

#### Function Expression

*function*(*expression_a*, *expression_b*, ...)

Evaluates the specified function, passing one or more expressions as parameters (separated by commas, and surrounded by round brackets), and returning the result.

The expression(s) can be of any type listed here, including other function expressions (thus allowing nesting of functions).

Examples:

Refer to the Function Reference for a list of available functions and their expected parameters.

#### Source Data Expression

[*field_name*]

Returns a text string corresponding to the specified field name from the source data. The field name **must** be surrounded by square brackets **[]** to be used as a parameter for a function.

The field name and source data may represent different things, depending on the context of the operation that is utilising the formula. For example:

- Source data could be a database table, and field name specifies a column to get the value of, for the record being processed.
- Source data could be a drawing, and field name specifies an attribute to get the value of, for the block reference being processed.
- Source data could be the input for an Import, and field name specifies a variable name to get the value of, that was set by a state persistence function (eg SET) in a previously evaluated formula in the Import Map File.

The operation processing the formula is free to interpret the field name how it wishes. Refer to the help page for the specific process for any special considerations given to the field name.

Unless otherwise specified (on the process help page), the field name is case-sensitive.

If the operation could not resolve the field name, an empty string will be returned.

Examples:

**[BLOCK]****[P1:Tagname]**

**Note:** If state persistence functions (eg SET) are supported by the process evaluating the formula, and have been used to assign a value to a named variable, then a Source Data Expression that specifies the variable name as *field_name* will return the value assigned to the variable name, in preference to returning any value corresponding to the *field_name* in the source data.

#### Text (String) Expression

"*text*"

Specify a text string by surrounding it with double quotes.

To include a double quote mark in a string, escape it by preceding it with a backslash.

Examples:

**"01-CV101-A"****"Pressure Transmitter"****"Shows \"Fault\" when circuit is open"**

#### Numeric Expression

*integer*

*integer*.*fraction*

Specify a integer (whole number) as-is, or specify a floating point (decimal fraction) using a period/dot between the integral and fractional parts.

Do not use any other characters, such as thousands-separators or units.

Examples:

**1000****3.142**

#### Math Expression

*number_a operator number_b*

The formula evaluator is capable of evaluating the following mathematical operators in this order:

- Brackets:
**( ... )** - Division:
**/** - Multiplication:
***** - Addition:
**+** - Subtraction:
**-**

Operations are evaluated using double precision floating point arithmetic, and the result is a double precision floating point number.

Use the STR formula function to how the result is converted back to a string (for example, if you need to trimming trailing zeros from the result, or round to an integer).

### Complete Examples

Source Data | Formula | Result | |||
---|---|---|---|---|---|

COL_A | COL_B | COL_C | COL_D | ||

JB02 | X101 | 8 | #COL_A#--#COL_B#/#COL_C# | JB02--X101/8 | |

JB02 | X101 | 8 | Dash Separated: #JOIN("-", [COL_A], [COL_B], [COL_C])# | Dash Separated: JB02-X101-8 | |

01-FT301-A | Loop: #SPLIT([COL_A], "", 4)#, Type: #LOWER(SPLIT([COL_A], "", 3))# | Loop: 301, Type: ft | |||

98 | degrees F | #STR(([COL_A] - 32) * 5 / 9, 2)# #REPLACE([COL_B], "F", "C")# | 36.67 degrees C |

## Processes using the Formula Evaluator

Importing components into Instrument Manager using Import Map Files.

Output diagram/datasheet generation from templates in Instrument Manager.

Bulk drawing generation from a prototype in Protogen.

Drawing a table of data on a drawing in Elecdes/Paneldes.

The Compute Values editing operation in a Data Grid view, present in many modules such as Database Editor and Instrument Manager.