concat(text1, text2, ...)

Concatenates up-to four specified String values into a single String.

e.g. concat([FirstName], " ", [LastName]) - combines the values in [FirstName] and [LastName] with a space in between.

concat([FirstName], " ", [LastName])  

This expression combines the values in [FirstName] and [LastName] with a space in between.

substring(text, startIndex, length)

Extracts a substring from the current String (text) that begins at a specified character position (startIndex) and ends after a defined number of characters (length). The starting character position is a zero-based and therefore the first character in the string is at index 0, not index 1.

e.g. substring([Field1], 0, 5) - extracts the first 5 characters of the value in [Field1].

Substring([Pol.ref],4,10) 

This expression could be used to extract the common Policy reference where the policy reference changes for each transaction type for a single risk.

replace(text, oldText, newText)

Returns a new String in which all occurrences of a specified String (oldText) in the current String (text) are replaced with another specified String (newText).

e.g. replace([Field1], "£", "") - replaces all instances of '£' in [Field1] with blank text, thus removing it from the value.

Replace([Underwriter Name], “Wil”, “William Blake”)

toLower(text)

Converts the current String (text) to lower case.

e.g. toLower([Field1])

toLower([Street Name])

toUpper(text)

Converts the current String (text) to upper case.

e.g. toUpper([Field1])

toUpper([Country])

indexOf(text, searchText)

Returns the zero-based index of the first occurrence of the specified String (seachText) in the current String (text), starting from position 0 and searching from left to right.

e.g. indexOf([Field1], "hello") - returns the start position of the String "hello" in [Field1].

indexOf([TaxID],”-“) 

This expression could be used where characters after “-“ in a Tax reference may be useful in identifying a particular risk characteristic such as Large or Medium enterprise or Province where the Risk is based.

lastIndexOf(text, searchText)

Returns the zero-based index of the first occurrence of the specified String (seachText) in the current String (text), starting from the last character in the current String and searching from right to left.

e.g. lastIndexOf([Field1], "hello") - returns the start position of the String "hello" in [Field1].

lastIndexOf([Pol.Ref], “/”) 

This expression may be used where transaction type is appended to the end of a policy reference in a bordereau after “/”.

startsWith(text, searchText)

Determines whether the beginning of the current String (text) matches the specified String (searchText).

e.g. startsWith([Field1], "hello") - returns True if the value in [Field1] starts with "hello", otherwise False is returned.

startsWith([InsuredName], “Gr”) 

This expression could be used where all projects covered for a particular Insured is written under a stand-alone section

endsWith(text, searchText)

Determines whether the end of the current String (text) matches the specified String (searchText).

e.g. endsWith([Field1], "hello") - returns True if the value in [Field1] ends with "hello", otherwise False is returned.

endsWith([Address2], “Harris”)

trim(text)

Removes all leading and trailing white-space characters from the current String (text).

e.g. trim([Field1]) 

length(text)            Returns the number of characters in the current String (text).

e.g. length([Field1])

Trim([policy reference])

length(text)

Returns the number of characters in the current String (text).

e.g. length([Field1])

Length([postcode])

if(expression, valueIfTrue, valueIfFalse)

Returns a value based upon an Expression (expression) being True or False. If the Expression is True, the valueIfTrue value is returned, otherwise the valueIfFalse is. Please note that valueIfTrue and valueIfFalse must be the same datatype. Selecting for valueIfTrue and valueIfFalse fields with different datatypes may therefore require use of casting functions string(), float() or int() to ensure the expression parses.

e.g. if(equals([Field1], [Field2]), [Field1], [Field2]) - this would return the value of [Field1] if [Field1] = [Field2], otherwise the value of [Field2] would be returned.

if(equals([Retail Brokerage Am], “0”), “25", "20")

This expression can be used for Coverholder Commission where 25% is given when business is written directly and 20% when business is written using a Retail Broker.

equals(value1, value2)

Returns a Boolean value based upon the comparison of the supplied Values. If both values are the same, True is returned otherwise False. 

e.g. equals([Field1], "hello") - this would return True if [Field1] = "hello". 

Equals([Risk Country], [Insured Country])

and(expression1, expression2)     

Returns a Boolean value based upon the comparison of the supplied expressions. If both expressions are True, True is returned otherwise False.

e.g. and(equals([Field1], "hello"), equals([Field2])) - this would return True if [Field1] = "hello" and [Field2] = "goodbye".

if(and(equals([Risk Country], “United States”), equals([Risk Code], “B3” )) , “25","20")

This expression could be used for a calc field where 25% Coverholder Commission is given for US business written under the B3 risk code and 20% for all other business.

or(expression1, expression2)

Returns a Boolean value based upon the comparison of the supplied expressions. If one of the expressions is True, True is returned otherwise False.

e.g. or(equals([Field1], "hello"), equals([Field2])) - this would return True if [Field1] = "hello" or [Field2] = "goodbye".

if(or(equals( [Country__1], "US"),equals([Country__1], "Canada") ),"North America", "Rest of World")

This expression populated the calculated field with “North America” on all rows where the field Country__1 contains either ‘Canada’ or ‘US’.

not(expression) 

Returns a Boolean value based upon the supplied expression. If the expression is False, True is returned otherwise False.

e.g. not(equals([Field1], "hello")) - this would return True if [Field1] is not equal to "hello".

if(not(equals([Brokerage], "0")),"Indirect", "Direct")

int(value)

Converts the current Value (value) to the data type Integer.

e.g. int([Field1]) 

string(value)            Converts the current Value (value) to the data type String.

e.g. string([Field1])

Int([Admin Fee %]) + Int([Commission %])

This expression adds the Admin Fee % and the Commission %. Note that as these numeric values are stored in DA SATS as string datatypes at this stage of Bordereau processing, they must be explicitly cast to ‘float’ datatypes.

string(value)

Converts the current Value (value) to the data type String.

Concat(String([inception]), “ “ , [Risk Country])

float(value)

Converts the current Value (value) to the data type Float (this should be used to convert values to decimals).

Float([Tax1 Amount]) + float([Tax2 Amount])

This expression adds the Tax Amounts of Tax 1 and Tax 2. Note that as these numeric values are stored in DA SATS as string datatypes at this stage of Bordereau processing, they must be explicitly cast to ‘float’ datatypes.

bool(value)

Converts the current Value (value) to the data type of Boolean (True or False).

If(bool([Coverage A inclusion Flag]), “Scheme A”, “Scheme B”)

add(value1, value2)

Sums two specified Numeric (Float or Integer) values (value1 and value2).

Add(float([Installment1],

[Installment 2])

sub(value1, value2)

Subtracts two specified Numeric (Float or Integer) values (value2 is subtracted from value1).

Sub(float([Total Deductions Amount], [Lead Admin Fee Amount])

utcNow()

Returns the current Date/Time, expressed as the Coordinated Universal Time (UTC) timezone.

utcNow()

+

Sums Numeric (Float or Integer) values.

e.g. float([Field1]) + float([Field2]) + float([Field2])

-

Subtracts Numeric (Float or Integer) values.

e.g. float([Field1]) - float([Field2]) - float([Field2])

/

Divides Numeric (Float or Integer) values.

e.g. (float([Field1]) / float([Field2])) / float([Field2])

*

Multiplies Numeric (Float or Integer) values.

e.g. (float([Field1]) * float([Field2])) * float([Field2])