Electrical Design Software | Elecdes Design Suite by Scada Systems Ltd

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.

The search is case-sensitive.

This function is case-sensitive.

See also: FINDEND.

ExampleResult
FIND("01-CV101-A", "01")1
FIND("01-CV101-A", "01", 2)7
FIND("01-CV101-A", "01", -1)7

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.

The search is case-sensitive.

This function is case-sensitive.

See also: FIND.

ExampleResult
FINDEND("01-CV101-A", "01")3
FINDEND("01-CV101-A", "01", 2)9

Available from: 8.8.1.0.

LEFT

LEFT(inputstr, countint)

Returns the left-most count characters from the input text string.

See also: RIGHT, MID.

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

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

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

See also: LEFT, RIGHT.

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

ExampleResult
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(inputstr, countint)

Returns the right-most count characters from the input text string.

See also: LEFT, MID.

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

See also: CONCAT, JOIN.

ParameterDescription
inputThe string to split.
delimiterA 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).
index1-based index of the substring to return.
If omitted, the count of substrings is returned.
ExampleResult
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.

ParameterDescription
numberThe number to convert.
precisionThe 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.
widthIf 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.
ExampleResult
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.

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

See also: JOIN, SPLIT.

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

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

See also: JOIN, NONBLANK.

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

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

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

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

Processlookup_table locationHandling of blank fields present in lookup_tablelookup_value matching method
lookup_field_aSubsequent 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 IgnoredTreated 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
  • A currently open DBF or table name.
  • A table name within the current (Access/SQL/Excel) database.
  • A relative path (from the source file) to another database.
  • A full path to another database.
Blanks allowed and treated as blanks.
Will only match a corresponding blank lookup_value.
Elecdes/Paneldes
Database Import
MAPLOOKUP not currently supported.
Protogen
  • A relative path (from the protogen DBF) to another DBF.
  • A full path to another DBF.

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.

See also: IF, OR, NOT.

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

See also: IF, NEQ, GTE, LTE.

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

See also: IF, GTE, LT, LTE.

ExampleResult
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: AND, OR, NOT.

See also: EQ, NEQ, GT, GTE, LT, LTE.

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

See also: IF, GT, GTE, LTE.

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

See also: IF, EQ, NOT.

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

See also: IF AND, OR, NEQ.

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

See also: IF, AND, NOT.

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

ExampleResult
SPECMATCH("FT101", "PT*", "FT*", "TT*")2
SPECMATCH("CV101", "PT*", "FT*", "TT*")0

Available from: 8.8.2.0.