String & Numeric Expressions

String Expressions

Concatenating strings: When you want to make a string value a combination of fixed text and or other string values, use concatenation within the expression:

[StringField1] + '-' + [StringField2] + '-' + [StringField3] + '-' + YEAR([DateField1])

String([NumberField1])

Returns a number value as a string.

Substring([TextField1], StartFrom, NumberOfChars)

Returns part of a string field, where the first parameter is the field being looked at, the second is the starting position, and the the third parameter is the number of characters needed to return
NOTE: if the string is not long enough, an error is returned, so you may need to use Len() first e.g. If(Len([FieldName]) > 50, Substring([FieldName], 0, 50), [FieldName])

Len([TextField])

Returns a numeric value representing the number of characters in a text field. 

For example, in the example below, the value will be the fist 60 characters in the text field [Question].

if(LEN([Question])<60, [Question],Substring([Question], 0, 60))

stringReplace([TextField], 'valueToReplace','valueToReplaceWith)

Takes a string value from a Field and replaces where the text in parameter two appears with the text in parameter three. If the value in [Title] is 'Mark to assess Risk'

stringReplace([Title], 'Mark', 'John') would give 'John to assess Risk'

Using stringReplace() to identify text contained within other text

The string replace function can also be used to identify if any text is contained within a value in another Field

For example, if we want to know if [TextField] with value 'some text' contains 'some'
Then:
if([TextField] = stringReplace([TextField], 'some', 'match'), false, true)
Or case insensitive:
if(toUpper([TextField]) = toUpper(stringReplace([TextField]), 'some', 'match'), false, true)

CreatedBy() : Using this function returns the User ID of the user who created the record. ( Primarily used in Person Fields)

CreatedByUser() : Using this function returns the name of the User who created the record as text.

UpdatedBy() : Using this function returns the user ID for the latest entry in the activity stream. ( Primarily used in Person Fields)

UpdatedByUser() : Using this function returns the name of the User for the latest entry in the activity stream as text.

ToUpper() and ToLower()

ToUpper([FieldName]) , will convert "Joe Bloggs" to "JOE BLOGGS"

ToLower([FieldName]) , will convert "Joe Bloggs" to "joe bloggs"

Numeric Expressions

Round([FieldName]) : Rounds a value to the nearest integer or specified number of decimal places.

e.g. Round([FieldName], 2)

If [FieldName] had a value of 130.34567, the resulting value of Round would be 130.35

Double([StringField]) : Returns the string as a number

Int([StringField]) : Returns the string as an integer (Whole Number)

PoW([NumberField], 2) : Returns the value of the Number Field to the Power of 2

Sqrt([NumberField]) : Returns the square root of a specified number or numeric field

Log([NumberField],10) : Returns the logarithm of a specified number or numeric field

Log10([NumberField]) : Returns the base 10 logarithm of a specified number or numeric field

Truncate([NumberField]) : Returns the Calculates the integral part of a number or numeric field