Formula Function Reference
Below is a list of all functions available for use with the EDS Formula Evaluator, along with descriptions and expected parameters.
Some functions may behave differently depending on the module that they are being used with. Some functions may not be available in all modules.
Function Definition Notation
Parameter Types
In the function descriptions below, each parameter will be followed by a type in subscript:
- str: Text string.
- int: Integer (whole number).
- fp: Floating point (decimal number).
If the value entered (or returned from a nested function) is not the expected type for the parameter, the Formula Evaluator will automatically convert it to the expected type.
Optional Parameters
In the function descriptions below, parameters surrounded by square brackets [] are optional.
Parameter lists ending in a triple period ... mean an arbitrary number of additional parameters can be supplied.
Text Manipulation Functions
FIND
FIND(haystackstr, needlestr [, instanceint])
Finds the first instance (or 1-based instance, if specified) of needle in haystack, and returns the 1-based character position of the start of needle. If instance is negative, searches backwards from the end of haystack.
Returns 0 if needle was not found in haystack.
The search is case-sensitive.
See also: FINDEND.
Example | Result |
---|---|
FIND("01-CV101-A", "01") | 1 |
FIND("01-CV101-A", "01", 2) | 7 |
FIND("01-CV101-A", "01", -1) | 7 |
FIND("01-CV101-A", "B") | 0 |
Available from: 8.8.1.0.
FINDEND
FINDEND(haystackstr, needlestr [, instanceint])
Finds the first instance (or 1-based instance, if specified) of needle in haystack, and returns the 1-based character position of the first character after the end of needle. If instance is negative, searches backwards from the end of haystack.
Returns 0 if needle was not found in haystack.
The search is case-sensitive.
See also: FIND.
Example | Result |
---|---|
FINDEND("01-CV101-A", "01") | 3 |
FINDEND("01-CV101-A", "01", 2) | 9 |
FINDEND("01-CV101-A", "B") | 0 |
Available from: 8.8.1.0.
LEFT
LEFT(inputstr, countint)
Returns the left-most count characters from the input text string.
Example | Result |
---|---|
LEFT("CV101", 2) | "CV" |
LEFT("CV101", 3) | "CV1" |
LEFT("CV101", 10) | "CV101" |
Available from: 8.8.0.0.
LEN
LEN(inputstr)
Returns the count of Unicode characters in the input text string.
Example | Result |
---|---|
LEN("CV101") | 5 |
LEN("Café") | 4 |
LEN("Кириллица") | 9 |
Available from: 8.8.0.0.
LOWER
LOWER(inputstr)
Converts the input string to lower-case.
Supports text from the following Unicode blocks: ASCII/Basic Latin, Latin-1 Supplement, Latin Extended-A, Cyrillic.
See also: UPPER.
Example | Result |
---|---|
LOWER("ABC") | "abc" |
LOWER("CAFÉ") | "café" |
LOWER("КИРИЛЛИЦА") | "кириллица" |
Available from: 8.8.0.0.
MID
MID(inputstr, firstint [, countint])
Returns a substring from the middle of input, starting from first (1-based character position), and count characters long (or to the end of input if count is omitted).
Example | Result |
---|---|
MID("01-CV101-A", 4) | "CV101-A" |
MID("01-CV101-A", 4, 5) | "CV101" |
MID("01-CV101-A", 8, 5) | "1-A" |
Available from: 8.8.0.0.
REPLACE
REPLACE(inputstr, oldstr, newstr [, instanceint])
If instance is omitted (or zero), replaces all instances of old with new in the input text string.
If instance greater than zero, replaces the specified instance (1-based, from the start of the string) of old with new.
If instance less than zero, replaces the specified instance (1-based, from the end of the string) of old with new.
This function is case-sensitive.
Example | Result |
---|---|
REPLACE("01-CV101-A", "1", "2") | "02-CV202-A" |
REPLACE("01-CV101-A", "1", "2", 2) | "01-CV201-A" |
REPLACE("01-CV101-A", "1", "2", -1) | "01-CV102-A" |
Available from: 8.8.1.0.
RIGHT
RIGHT(inputstr, countint)
Returns the right-most count characters from the input text string.
Example | Result |
---|---|
RIGHT("CV101", 2) | "01" |
RIGHT("CV101", 3) | "101" |
RIGHT("CV101", 10) | "CV101" |
Available from: 8.8.0.0.
SPLIT
SPLIT(inputstr, delimiterstr [, indexint])
Splits a text string into substrings, and returns the substring at the specified index (or a count of substrings if no index was specified).
Leading and trailing spaces in the returned substring will be automatically trimmed.
Parameter | Description |
---|---|
input | The string to split. |
delimiter | A string of one or more characters, that when encountered, signals the end of the preceding substring, and the start of the next (the delimiter string itself will be omitted from the substrings). If an empty string is supplied, substrings will be created from sequences of the same class of character (digit, alphabet letter, or other). |
index | 1-based index of the substring to return. If omitted, the count of substrings is returned. |
Example | Result |
---|---|
SPLIT("01-CV101-A", "-", 2) | "CV101" |
SPLIT("01-CV101-A", "-", -1) | "A" |
SPLIT("01-CV101-A", "-") | 3 |
SPLIT("01-CV101-A", "", 3) | "CV" |
Available from: 8.8.0.0 (empty string delimiter from 8.8.1.0)
STR
STR(numberfp [, precisionint [, widthint]])
Converts a number to a text string, with the specified precision and width.
Parameter | Description |
---|---|
number | The number to convert. |
precision | The number of digits after the decimal point to include in the resulting string. Rouding will be used. Padding zeros will be appending if required. If precision is zero, the decimal point will be omitted. If precision is omitted, all significant digits after the decimal point will be included, and trailing zeros will be trimmed. |
width | If specified, the number of digits (including the decimal point, if present) to include the resulting string. If width is larger than the output, the string will be padded with leading zeros. |
Example | Result |
---|---|
STR(12.3456000) | "12.3456" |
STR(12.3456, 2) | "12.35" |
STR(12.3456, 7) | "12.3456000" |
STR(12.3456, 2, 7) | "0012.35" |
STR(12.3456, 0, 5) | "00012" |
Available from: 8.8.1.0.
UPPER
UPPER(inputstr)
Converts the input string to upper-case.
Supports text from the following Unicode blocks: ASCII/Basic Latin, Latin-1 Supplement, Latin Extended-A, Cyrillic.
See also: LOWER.
Example | Result |
---|---|
UPPER("abc") | "ABC" |
UPPER("Café") | "CAFÉ" |
UPPER("Кириллица") | "КИРИЛЛИЦА" |
Available from: 8.8.0.0.
Aggregation Functions
CONCAT
CONCAT(input_astr, input_bstr [, ...str])
Concatenates the input strings together.
Example | Result |
---|---|
CONCAT("CV", 101) | "CV101" |
CONCAT("01", "-", "CV101", "-", "A") | "01-CV101-A" |
Available from: 8.8.0.0.
JOIN
JOIN(delimiterstr, input_astr, input_bstr [, ...str])
Joins all of the input strings together, separating each with delimiter.
See also: JOINNB, CONCAT, SPLIT.
Example | Result |
---|---|
JOIN(" ", "CV", 101) | "CV 101" |
JOIN("-", "01", "CV101", "A") | "01-CV101-A" |
Available from: 8.8.0.0.
JOINNB
JOINNB(delimiterstr, input_astr, input_bstr [, ...str])
Joins all of the non-blank input strings together, separating them with delimiter.
Example | Result |
---|---|
JOINNB(" ", "CV", 101, "", "A") | "CV 101 A" |
JOINNB("-", "", "01", "", "CV101", "A") | "01-CV101-A" |
Available from: 8.8.1.0.
NONBLANK
NONBLANK(indexint, input_astr, input_bstr [, ...str])
Returns the input parameter at index (1-based), ignoring blank values.
If index is negative, counts back from the end of the input values instead.
See also: JOINNB.
Example | Result |
---|---|
NONBLANK(1, "", "", "A", "", "B", "C", "", "", "D") | "A" |
NONBLANK(2, "", "", "A", "", "B", "C", "", "", "D") | "B" |
NONBLANK(-1, "", "", "A", "", "B", "C", "", "", "D") | "D" |
NONBLANK(-2, "", "", "A", "", "B", "C", "", "", "D") | "C" |
Available from: 8.8.1.0.
Lookup and Reference Functions
LOOKUP
LOOKUP(input_keystr, default_valuestr, key_astr, value_astr [, key_bstr, value_bstr [, ...]])
Searches for input_key amongst key_a, key_b, ..., key_z (case sensitive), and if a matching key is found, returns the corresponding value.
If a match was not found, default_value will be returned.
See also: MAPLOOKUP.
Example | Result |
---|---|
LOOKUP("PT", "Unknown", "FT", "Flow", "PT", "Pressure", "TT", "Temperature") | "Pressure" |
LOOKUP("LT", "Unknown", "FT", "Flow", "PT", "Pressure", "TT", "Temperature") | "Unknown" |
Available from: 8.8.2.0.
MAPLOOKUP
MAPLOOKUP(return_fieldstr, lookup_tablestr, lookup_field_astr, lookup_value_astr [, lookup_field_bstr, lookup_value_bstr [, ...]])
Returns a value from return_field from a record in lookup_table (typically a DBF), where the record is found by matching lookup_value_a to lookup_field_a, lookup_value_b to lookup_field_b, and so on, for all specified fields and values.
The value in return_field may also be a formula itself (in hash pair format), in which case it will be evaluated, and the result returned.
See also: LOOKUP.
Example | Result | Notes |
---|---|---|
MAPLOOKUP("RATING", "TypeCodeToRatingTable.dbf", "CODES", "FT", "CLASSES", "Coriolis") | "Instrument_Flow_Transmitter_Coriolis" | Example usage in IM Import. DBF file found in IMP_IM\ImportMapFiles or MET_IM\ImportMapFiles |
The functionality of MAPLOOKUP varies depending on the process it is being used in:
Process | lookup_table location | Handling of blank fields present in lookup_table | lookup_value matching method | |
---|---|---|---|---|
lookup_field_a | Subsequent lookup_fields | |||
Instrument Manager Import Map File | A DBF file in the IMP_IM\ImportMapFiles or MET_IM\ImportMapFiles folder of the EDS installation. | Map Record Ignored | Treated as a wildcard (*) Will match any corresponding lookup_value. | Exact match preferred, followed by case-insensitive Specmatch. |
Cable Scheduler Import Map File | A DBF file in the CabSched\ImportMapFiles folder of the EDS installation. | |||
Database Editor Compute Values |
| Blanks allowed and treated as blanks. Will only match a corresponding blank lookup_value. | ||
Elecdes/Paneldes Database Import | MAPLOOKUP not currently supported. | |||
Protogen |
Available from: 8.8.0.0 (return_field supporting a formula from 8.8.1.0).
Logical Functions
AND
AND(input_astr, input_bstr [, ...str])
Returns 1 if all of the input parameters evaluate to a representation of "true" (a non-zero number, or a string that matches one of TRUE, T, YES, or Y, case-insensitive).
Otherwise, returns 0.
Example | Result |
---|---|
AND("FALSE", 1) | 0 |
AND("TRUE", "Y", "01") | 1 |
Available from: 8.8.2.0.
EQ
EQ(input_astr, input_bstr)
Returns 1 if input_a is the same as input_b.
Otherwise, returns 0.
A numeric comparison is performed if both input strings can be interpreted as numbers (with no extraneous text). Otherwise, a case-sensitive textual comparison is performed.
Example | Result |
---|---|
EQ("2.0", "02") | 1 |
EQ("2.0", 2) | 1 |
EQ("2 a", 2) | 0 |
EQ("2 a", "2 A") | 0 |
Available from: 8.8.2.0.
GT
GT(input_astr, input_bstr)
Returns 1 if input_a is greater than input_b.
Otherwise, returns 0.
A numeric comparison is performed if both input strings can be interpreted as numbers (with no extraneous text). Otherwise, a case-sensitive textual comparison is performed.
Example | Result |
---|---|
GT("10", 9) | 1 |
GT("10a", "9a") | 0 |
GT("B", "C") | 0 |
GT("FT20", "FT101") | 1 |
Available from: 8.8.2.0.
GTE
GTE(input_astr, input_bstr)
Returns 1 if input_a is greater than or equal to input_b.
Otherwise, returns 0.
A numeric comparison is performed if both input strings can be interpreted as numbers (with no extraneous text). Otherwise, a case-sensitive textual comparison is performed.
See also: IF, EQ, GT, LT, LTE.
Available from: 8.8.2.0.
IF
IF(conditionstr, result_if_truestr [, result_if_falsestr])
Returns result_if_true if condition evaluates to a representation of "true" (a non-zero number, or a string that matches one of TRUE, T, YES, or Y, case-insensitive).
Otherwise, returns result_if_false (or an empty string if result_if_false is not supplied)
See also: EQ, NEQ, GT, GTE, LT, LTE.
Example | Result |
---|---|
IF(1, "On", "Off") | "On" |
IF("Y", "Good", "Bad") | "Good" |
IF("TT", 1, 0) | "0" |
IF("0", "Details") | "" |
Available from: 8.8.2.0.
LT
LT(input_astr, input_bstr)
Returns 1 if input_a is less than input_b.
Otherwise, returns 0.
A numeric comparison is performed if both input strings can be interpreted as numbers (with no extraneous text). Otherwise, a case-sensitive textual comparison is performed.
Example | Result |
---|---|
LT("10", 9) | 0 |
LT("10a", "9a") | 1 |
LT("B", "C") | 1 |
LT("FT20", "FT101") | 0 |
Available from: 8.8.2.0.
LTE
LTE(input_astr, input_bstr)
Returns 1 if input_a is less than or equal to input_b.
Otherwise, returns 0.
A numeric comparison is performed if both input strings can be interpreted as numbers (with no extraneous text). Otherwise, a case-sensitive textual comparison is performed.
See also: IF, EQ, GT, GTE, LT.
Available from: 8.8.2.0.
NEQ
NEQ(input_astr, input_bstr)
Returns 1 if input_a is not the same as input_b.
Otherwise, returns 0.
A numeric comparison is performed if both input strings can be interpreted as numbers (with no extraneous text). Otherwise, a case-sensitive textual comparison is performed.
Example | Result |
---|---|
NEQ("2.0", "02") | 0 |
NEQ("2.0", 2) | 0 |
NEQ("2 a", 2) | 1 |
NEQ("2 a", "2 A") | 1 |
Available from: 8.8.2.0.
NOT
NOT(inputstr)
Returns 0 if the input parameter evaluates to a representation of "true" (a non-zero number, or a string that matches one of TRUE, T, YES, or Y, case-insensitive).
Otherwise, returns 1.
Example | Result |
---|---|
NOT("2.0") | 0 |
NOT("F") | 1 |
Available from: 8.8.2.0.
OR
OR(input_astr, input_bstr [, ...str])
Returns 1 if any of the input parameters evaluate to a representation of "true" (a non-zero number, or a string that matches one of TRUE, T, YES, or Y, case-insensitive).
Otherwise, returns 0.
Example | Result |
---|---|
OR("FALSE", 0) | 0 |
OR("2.0", "N", 0) | 1 |
Available from: 8.8.2.0.
SPECMATCH
SPECMATCH(inputstr, spec_astr [, spec_bstr [, ...str]])
Returns the 1-based index of the first Specmatch spec that matches against input.
If input did not match any spec, returns 0.
Example | Result |
---|---|
SPECMATCH("FT101", "PT*", "FT*", "TT*") | 2 |
SPECMATCH("CV101", "PT*", "FT*", "TT*") | 0 |
Available from: 8.8.2.0.
DWG Manipulation Functions
Note: These functions have intentional side-effects that manipulate aspects of the containing DWG file when they are evaluated, and are thus only available in the specified scenarios.
CL_LAYERON
CL_LAYERON(layer_astr [, layer_bstr [, ...str]])
Available only on Custom Loop templates.
Ensures each specified layer is turned on in the generated loop.
Returns an empty string.
Available from: 8.8.2.0.
CL_LAYEROFF
CL_LAYEROFF(layer_astr [, layer_bstr [, ...str]])
Available only on Custom Loop templates.
Ensures each specified layer is turned off in the generated loop.
Returns an empty string.
Available from: 8.8.2.0.
CL_LAYERDEL
CL_LAYERDEL(layer_astr [, layer_bstr [, ...str]])
Available only on Custom Loop templates.
Deletes each specified layer (and all entities on that layer) in the generated loop.
Returns an empty string.
Available from: 8.8.2.0.
CL_LAYERMOVE
CL_LAYERMOVE(layer_astr, x_translation_afp, y_translation_afp [, layer_bstr, x_translation_bfp, y_translation_bfp [, ...]])
Available only on Custom Loop templates.
Moves all the entites on layer_a by { x_translation_a, y_translation_a } (in DWG units) on the XY plane, and so on for subsequent layers and translations provided.
Returns an empty string.
Available from: 8.8.2.0.
CL_LAYERMERGE
CL_LAYERMERGE(target_layerstr, source_layer_astr [, source_layer_bstr [, ...str]])
Available only on Custom Loop templates.
Moves all the entites on each source_layer onto the target_layer, then deletes the source_layers, in the generated loop.
Returns an empty string.
Available from: 8.8.2.0.
CL_INSERTDWG
CL_INSERTDWG(dwgstr [, Xfp, Yfp])
Available only on Custom Loop templates.
Insert the drawing file named by dwg at the point X,Y relative to the insertion point of the formula on the template (inserts at the formula if X and Y are not supplied).
See Custom Loop drawings CL_INSERTDWG for more information.