The following is a complete list of all functions currently available in the system.

For each function, the following information is provided:

•    The full name of the function, plus placeholders (in green) for the required parameters.

•    A brief description of the function.

•    A list of the required parameters (if any), the required format, and any notes about the parameters.

•    The value that is returned by the function, and the format of the response.

DaysBetween(p0, p1)

Counts the number of days that have elapsed between two dates.

P0 (date): First date field

P1 (date): Second date field

Returns (numeric): Number of days between p0 and p1.  If the first date is earlier than the second date, the value will be positive.  If the first date is later than the second date, the value will be negative.

eg. Comparing date 4/22/2014 and 4/24/2014 returns 2.

      Comparing date 4/26/2014 and 4/24/2014 returns -2.

MonthsBetween(p0, p1)

Counts the number of months that have elapsed between two dates.

P0 (date): First date field

P1 (date): Second date field

Returns (numeric): Number of calendar months between p0 and p1.  If the first date is earlier than the second date, the value will be positive.  If the first date is later than the second date, the value will be negative.

eg. Comparing date 2/24/2014 and 4/24/2014 returns 2.

      Comparing date 6/24/2014 and 4/24/2014 returns -2.

YearsBetween(p0, p1)

Counts the number of years that have elapsed between two dates.

P0 (date): First date field

P1 (date): Second date field

Returns (numeric): Number of years between p0 and p1. Difference is calendar years.  If the first date is earlier than the second date, the value will be positive.  If the first date is later than the second date, the value will be negative.

eg. Comparing date 4/24/2012 and 4/24/2014 returns 2.

      Comparing date 4/24/2016 and 4/24/2014 returns -2.

IsDateGreaterThan(p0, p1, p2)

Checks the first provided date to determine if it is greater than the second provided date.  A tolerance can be set to account for minor differences.

P0 (date): First date field.

P1 (date): Second date field.

P2 (integer): Tolerance in milliseconds.  The system date/time format is accurate to a ten-millionth of a second.  Even dates that seem to be calculated at the same time will have a small difference.  If the P0 date is only greater than the P1 date by less than the provided tolerance, it will not be considered a difference and the function will return false.

Returns (text): Returns "True" if the P0 date is greater than the P1 date by more than the P2 tolerance, if any.  Returns "False" if the P0 date is less than or equal to the P1 date, or if it is greater but within the P2 tolerance.

eg. If P0 is 2014-10-28T18:16:12.001-04:00, P1 is 2014-10-28T18:16:12.002-04:00, and no tolerance is specified, returns "False" since P0 is less than P1.

If P0 is 2014-10-28T18:16:12.005-04:00, P1 is 2014-10-28T18:16:12.002-04:00, and no tolerance is specified, returns "True" since P0 is greater than P1.

If P0 is 2014-10-28T18:16:12.005-04:00, P1 is 2014-10-28T18:16:12.002-04:00, and the P2 tolerance is set to 10, returns "False" since P0 is greater than P1, but only by 3 milliseconds when the tolerance is 10 milliseconds.

−    See Identifying When Fields Have Changed After an Integration for a detailed description of change detection structures.

DaysInPeriod(p0, p1, p2, p3)

Similar to the DateOverlap() function, this function compares two date ranges to determine if they overlap, counting the number of days that are common between the two ranges.

P0 (date): Start date of the first date range

P1 (date): End date of the first date range

P2 (date): Start date of the second date range

P3 (date): End date of the second date range

Returns (numeric): Number of days that are included in both ranges.  Note that the start and end dates are included in each range.  Time of day is not taken into account.  If there is no overlap, the function returns 0.

eg. If the first date range is from January 1^st to June 30^th and the second range is from June 15^th to August 31^st, the function returns 17, counting the overlapping days at the end of June.

If the first date range is from January 1^st to January 31^st and the second range is from January 10^th to January 20^th, the function returns 11, since the full second range is included within the first range.

DateOverlap(p0, p1, p2, p3, p4, p5)

Similar to the DaysInPeriod() function, this function compares two date ranges to determine if they overlap, returning one value if they do, and returning a different value if they do not.  If any of the four date fields are missing or invalid, the P5 value (no overlap) is returned.

P0 (date): Start date of the first date range

P1 (date): End date of the first date range

P2 (date): Start date of the second date range

P3 (date): End date of the second date range

P4 (matches type of field): The value to return if there is an overlap between the date ranges

P5 (matches type of field): The value to return if there is no overlap between the date ranges

Returns (any): Either the value entered as P4 or P5.

CurrentDateTime()

Obtains the current date and time from the server.

No parameters.

Returns (date):  The current Date and time in the full system format (2015-10-28T18:16:12.3313239-04:00).

TodayXml()

Obtains the current date from the server.

No parameters.

Returns (date):  The current Date, formatted for use in other functions.

TodayLong()

Obtains the current date from the server.

No parameters.

Returns (text): The current Date, in Long Date format.

eg.  Monday, June 15, 2011

TodayShort()

Obtains the current date from the server.

No parameters.

Returns (text): The current Date, in Short Date format. The date will be formatted according to the browser language settings at the time the field was last calculated.

eg.  15/6/2011 or 6/15/2011

AddHours(p0, p1)

Takes a provided date and adds or subtracts a specified number of hours.

P0 (date): Date field.  The date must be in the system format, such as from a date picker field, the TodayXml() function, or any system dates.

P1 (integer): Number of hours to add (positive values) or subtract (negative values).  Decimal values are not supported.

Returns (date):  New date with adjusted time.  The date itself will change if the hours cross into the next or previous days.

AddDays(p0, p1)

Takes a provided date and adds a specified number of days.

P0 (date): Date field

P1 (numeric): Number of days to add

Returns (date):  Date which is the addition of p1 days to date p0. 

AddMonths(p0, p1)

Takes a provided date and adds a specified number of months.

P0 (date): Date field

P1 (numeric): Number of months to add

Returns (date): Date which is the addition of p1 months to date p0. 

AddYears(p0, p1)

Takes a provided date and adds a specified number of years.

P0 (date): Date field

P1 (numeric): Number of years to add

Returns (date): Date which is the addition of p1 years to date p0. 

GetYear(p0)

Takes a provided date and returns just the year.

P0 (date): Date field

Returns (numeric): Returns the year of p0.

eg.  If p0 = 15/10/2011, returns 2011.

GetMonth(p0)

Takes a provided date and returns just the month.

P0 (date): Date field

Returns (numeric): Returns the month of p0, from 1 to 12

eg.  If p0 = 15/10/2011, returns 10.

GetDayOfYear(p0)

Takes a provided date and returns the number of the day, counted from January 1st.

P0 (date): Date field

Returns (numeric): Returns the numeric day of the year from p0. 

eg.  If p0 = 15/02/2010, returns 46.

GetDayOfMonth(p0)

Takes a provided date and returns the number of the day, counted from the first day of the month.

P0 (date): Date field

Returns (numeric): Returns the numeric day of the month from p0. 

eg.  If p0 = 15/02/2010, returns 15.

GetMonthsFromCurrent(p0)

Takes a provided date and compares it to the current date, returning the number of months difference.

P0 (date): Date field

Returns (numeric): Number of calendar months from p0 to the current date.  Dates earlier than the current date will return a positive value, dates later than the current date will return a negative value.

GetDaysFromCurrent(p0)

Takes a provided date and compares it to the current date, returning the number of days difference.

P0 (date): Date field

Returns (numeric): Number of calendar days from p0 to the current date.  Dates earlier than the current date will return a positive value, dates later than the current date will return a negative value.

GetYearsFromCurrent(p0)

Takes a provided date and compares it to the current date, returning the number of years difference.

P0 (date): Date field

Returns (numeric): Number of calendar years from p0 to the current date.  Dates earlier than the current date will return a positive value, dates later than the current date will return a negative value.

DateLong(p0)

Converts a provided date into the long date format.

P0 (date): Date field

Returns (text): Date p0 in long date format.

eg. If p0 = 15/06/2011, returns Monday, June 15, 2011

DateTimeLong(p0)

Converts a provided date and time into the long date and time format.

P0 (date): Date field.  The date must be in the system format, such as from a date picker field, the TodayXml() function, or any system dates.

Returns (text): Date p0 in long date and time format.

eg. If p0 = 2014-12-19T14:45:00.0000000-04:00, returns Friday, December 19, 2014 2:45:00 PM.  The output may display differently due to regional settings.

DateShort(p0)

Converts a provided date into the short date format.

P0 (date): Date field

Returns (text): Date p0 in short date format.

eg. If p0 = Monday, June 15, 2011, returns 15/6/2011

DateTimeShort(p0)

Converts a provided date and time into the short date and time format.

P0 (date): Date field.  The date must be in the system format, such as from a date picker field, the TodayXml() function, or any system dates.

Returns (text): Date p0 in short date and time format.

eg. If p0 = 2014-12-19T14:45:00.0000000-04:00, returns 12/19/2014 2:45 PM.  The output may display differently due to regional settings.

CustomDateFormat(p0,"{0:p1}")

Converts a provided date into a custom date format defined by codes.

P0 (date): Date field.  The date must be in the system format, such as from a date picker field, the TodayXml() function, or any system dates.

P1 (text): String containing formatting codes.

Returns (text): Returns date p0 in format provided in p1.

−    See Custom Date Format Function for a detailed description, a list of codes, and examples.

IsAbnValid(p0)

Validates a text field to determine if the content is a valid Australian Business Number (ABN).

P0 (text): Field to be examined

Returns (numeric): Returns "True" if the field contains a valid ABN, or returns "False" if the value is not valid for any reason (too short, contains letters/special characters, failed encoding check, etc.)

$GetUser()

Obtains the full name of the current user.

No parameters.

Returns (string):  The full first and last name of the user logged in at the time the calculated field is evaluated.

$UserContainsRole(p0)

Checks the current user’s account to determine if it has a specific security role.

P0 (text): Name of security role

Returns (numeric): Returns "True" if current user account has role p0, returns "False" if it does not.

Note:  This function should only be used within policy workflows.  For claims workflows, please use the UserContainsSecurityRole() function below instead.

−    See Verifying User Security Roles for a detailed description of the function.

UserContainsSecurityRole(p0)

Checks the current user’s account to determine if it has a specific security role.

P0 (text): Code of security role

Returns (numeric): Returns "True" if current user account has role p0, returns "False" if it does not.

Note:  This function should only be used within claims workflows.  For policy workflows, please use the $UserContainsRole() function above instead.

−    See Verifying User Security Roles for a detailed description of the function.

$IsNewBusiness(p0)

Checks if a provided submission, identified by transaction id, is new business.

P0 (numeric): Transaction ID number of the submission

Returns (numeric): Returns "True" if given transaction ID is new business, returns "False" if it is not.

Compare(p0, p1, p2, p3)

Compares two source values and returns one value if they match, or a different value if they do not match.  The values are compared as raw text, and only an exact match will return the true value.

P0 (text): The first source value for comparison.

P1 (text): The second source value for comparison.

P2 (text): The value to return if the comparison values are an exact match.

P3 (text): The value to return if the comparison values do not match exactly.

Returns (text): The value for either P2 or P3, depending on whether the source values match or not.

HashAndModifiedOn(p0, p0, p0, ..., p1, p2)

The first time this function runs, it combines the content of all P0 fields, calculates a hash value (a unique value derived from the content), stores the hash value in the P1 field, and stores the date and time of the calculation in the P2 field.

Each time the function runs after the first time, it calculates the hash value for all fields then compares it to the previous hash value stored in P1.  If the hash value has changed, meaning that at least one of the fields was changed since the last time the function ran, the function stores the new hash value in the P1 field and updates the date and time stored in the P2 field.

The date stored in the P2 field can be compared to another date to determine if any of the fields changed within a certain time period.  The IsDateGreaterThan() function is useful for making the comparison.

Note:  The date stored in the P2 field indicates the last time the function identified the changes.  It does not reflect when the changes were made.

P0 (text): All fields to be included in the hash calculation should be provided, separated by commas.

P1 (Text Area): The Code of the field to contain the hash value.  This will be an alphanumeric value with no meaning outside of this function, so the field should most likely be hidden.  This condition is literal and must contain the Code of the field without any square brackets, no placeholders or functions can be used here.

P2 (text): The Code of the field to contain the last date and time that changes were detected. This condition is literal and must contain the Code of the field without any square brackets, no placeholders or functions can be used here.  This should be a text field to ensure the full date and time are stored.

Returns (text): Returns the same hash value stored in P1.

−    See Identifying When Fields Have Changed After an Integration for a detailed description of change detection structures.

$CrossPolicyExactMatch(p0, p1, p3, p3, p3, ...)

$CrossPolicyFuzzyMatch(p0, p1, p2, p3, p3, p3, ...)

Used with a Cross-Policy Data Configuration, these functions take a value from the current transaction and compare it to stored values from other transactions.  The functions evaluate to true if they find an exact match ($CrossPolicyExactMatch()) within the stored data or data that is similar within a certain tolerance ($CrossPolicyFuzzyMatch()).

P0 (text): This identifies the field containing the source value that the function will search for.  The field Code should be provided, enclosed in square brackets.

P1 (text): This identifies the resource containing the stored data.  The Code of the Cross-Policy Data Configuration should be provided, without any brackets.

P2 (numeric): This is the tolerance, ranging from 0 to 100, and is only used for the $CrossPolicyFuzzyMatch() function.  As the function compares the source value to the stored data, it calculates a percentage match score from 0 (no similarity at all) to 100 (exact match).  The percentage must be equal to or greater than the tolerance value for the function to consider it a positive match.

P3 (text): These are optional filters to control what data will be searched within the Cross-Policy Data Configuration.  The source value will only be compared against data that fits within all of the selected filters.

Each filter should be formatted as Filter=Value, and only one filter of each type can be used in each instance of the function, except where noted.

Returns (text):

$CrossPolicyExactMatch() returns "True" if an exact match is found within the stored data.  Returns "False" if no match is found.

$CrossPolicyFuzzyMatch() returns "True" if data is found that has a percentage match score that equals or exceeds the tolerance.  Returns "False" if no data matches closely enough.

Examples:

$CrossPolicyExactMatch([[FieldCode]], ConfigurationCode, Product=Other, Distributor=Current,

PolicyTransactionStatus=Quoted, PolicyTransactionStatus=Bound)

This function will search through the Cross-Policy Data Configuration "ConfigurationCode" for the text found in the [[FieldCode]] field.  It will only search data associated to the current distributor, only within other products, and only within transactions in quoted or bound status.

$CrossPolicyFuzzyMatch([[FieldCode]], ConfigurationCode, 90, Policy=Current, Transaction=Other)

This function will search through the Cross-Policy Data Configuration "ConfigurationCode" for the text found in the [[FieldCode]] field.  It will only search data associated to other transactions within the current policy.  Any similar values must have a percentage match score of 90 or better to produce a positive result.

ValueExists(p0, p1)

Used with Field Indexing, this function takes a value from the current claim and compares it to stored values from other claims.

P0 (text): This identifies the field containing the source value that the function will search for.  The field Code should be provided, enclosed in square brackets.

P1 (text): This identifies the index containing the stored data.  This can be the Code for a single indexed field, or an Alias for multiple indexed fields.  This value must be provided without any brackets.

Returns (text): Returns "true" if a match is found within the stored data.  Returns "false" if no match is found.

Examples:

ValueExists([[FieldCode]], Code)

This function will search through the indexed field "Code" for the text found in the [[FieldCode]] field.

ValueExists([[FieldCode]], AliasName)

This function will search through all indexed fields with the "AliasName" alias for the text found in the [[FieldCode]] field.

IsNull(p0)

Determines if a specific field is empty.

P0 (any): The field to be evaluated.

Returns (boolean): Returns True if the field value is null or an empty string.  Returns False if the field contains any data of any type.

IsNotNull(p0)

Determines if a specific field contains any data.

P0 (any): The field to be evaluated.

Returns (boolean): Returns True if the field contains any data of any type.  Returns False if the field value is null or an empty string.

IsNumeric(p0)

Determines if a specific value is a number.

P0 (any): The value to be evaluated.  This can be a field placeholder, function, or formula.

Returns (boolean): Returns True if the value is a number, including 0.  Returns False if the value is null, and empty string, or if it contains any non-numeric characters.

Periods and commas will be recognized as decimal separators, while commas will also be recognized as thousands separators.

NextPolicySequenceNumber()

Retrieves the next sequence number, as it appears in the Next Sequence Number field in the Master Cover - Policy Numbering page.

No parameters.

Returns (numeric): Returns the next sequence number.

GetAndIncrementSequenceNumber(p0)

Retrieves the next sequence number from a sequence number configuration and then increments the sequence number by 1.

P0 (text): The Code of the sequence number configuration.

Returns (numeric): Returns the next sequence number.

If the function is called from a transaction whose master cover is in test mode, the number from the configuration’s Test - Next Sequence Number field is returned.

If the function is called from a transaction whose master cover is in live mode, the number from the configuration’s Live - Next Sequence Number field is returned.

Note:  The function will retrieve and increment the number every time it is evaluated.  Conditional structures, such as an If statement or Field Evaluation Rule, can be used to control when the function is evaluated.

$GetTermStatus(p0)

Retrieves the current status of the provided submission or policy, identified by transaction id.

P0 (numeric): Transaction ID number of the submission or policy

Returns (text): Returns the current status of the submission or policy.

$GetTransactionType(p0)

Retrieves the current type of the provided transaction, identified by transaction id.

P0 (numeric): Transaction ID number of the submission or policy

Returns (text): Returns the current type of the transaction.

$GetTransactionStatus(p0)

Retrieves the current status of the provided transaction, identified by transaction id.

P0 (numeric): Transaction ID number of the submission or policy

Returns (text): Returns the current status of the transaction.

$GetTermNumber(p0)

Returns the sequential term number of the provided transaction.

P0 (numeric): Transaction ID number of the submission.  The [[SubPolSystemID]] placeholder can be used to automatically insert the Transaction ID of the current transaction.

Returns (numeric): Returns the number of the current term.

eg.  The original policy returns 1, the first renewal returns 2, the second renewal returns 3, etc.

$NextAdjustmentNumber([[SubPolSystemID]])

Returns the next adjustment number in line for the term.

[[SubPolSystemID]]: This is a placeholder that automatically inserts the transaction ID for the current transaction.  This parameter should not be changed.

Returns (numeric): Returns the number that will be assigned to the next bound adjustment in the current term.

eg.  In a newly created term with no adjustments, this function returns 1.  If 3 adjustments have been bound in the current term, this function returns 4.

$NextEndorsementNumber([[SubPolSystemID]])

Returns the next endorsement number in line for the term.

[[SubPolSystemID]]: This is a placeholder that automatically inserts the transaction ID for the current transaction.  This parameter should not be changed.

Returns (numeric): Returns the number that will be assigned to the next bound endorsement in the current term.

eg.  In a newly created term with no endorsements, this function returns 1.  If 3 endorsements have been bound in the current term, this function returns 4.

$TotalGrossPremiumAmount()

Returns the total gross premium for the current quote.

No parameters.

Returns (numeric): Returns the combined total of all premium types included in the current quote.

$TotalCommissionAmount()

Returns the total calculated commission for the current quote.

No parameters.

Returns (numeric): Returns the combined total commission from all premium types included in the current quote.

$TotalTaxesAmount()

Returns the total calculated taxes for the current quote.

No parameters.

Returns (numeric): Returns the combined total taxes of all premium types included in the current quote.

$GetTransactionTaxAmount(p0)

Calculates the total amount of a certain tax or fee across all premium types within the current transaction.

P0 (text): The Code of the tax or fee.

Returns (numeric): Returns the total amount of the specified tax or fee for all premium types within the current transaction.  This value is only available once quotes have been calculated and a quote option has been selected.

$GetTermTaxAmount(p0)

Calculates the total amount of a certain tax or fee across all premium types, for all other transactions within the current term.  This does not include the current transaction.

P0 (text): The Code of the tax or fee.

Returns (numeric): Returns the total amount of the specified tax or fee across all premium types, for all other transactions within the current term.  This does not include the current transaction.  Tax and fee details are only available for transactions where quotes have been calculated and a quote option has been selected.

$CreatedByFirstName([[SubPolSystemID]])

Returns the first name of the user who created the transaction.

[[SubPolSystemID]]: This is a placeholder that automatically inserts the transaction ID for the current transaction.  This parameter should not be changed.

Returns (text): Returns the first name of the user who created the transaction.  See the Concatenation section for instructions on combining multiple text results.

$CreatedByLastName([[SubPolSystemID]])

Returns the last name of the user who created the transaction.

[[SubPolSystemID]]: This is a placeholder that automatically inserts the transaction ID for the current transaction.  This parameter should not be changed.

Returns (text): Returns the last name of the user who created the transaction.  See the Concatenation section for instructions on combining multiple text results.

$ModifiedByFirstName([[SubPolSystemID]])

Returns the first name of the last user to make changes to the transaction.

[[SubPolSystemID]]: This is a placeholder that automatically inserts the transaction ID for the current transaction.  This parameter should not be changed.

Returns (text): Returns the first name of the last user to make changes to the transaction.  See the Concatenation section for instructions on combining multiple text results.

$ModifiedByLastName([[SubPolSystemID]])

Returns the last name of the last user to make changes to the transaction.

[[SubPolSystemID]]: This is a placeholder that automatically inserts the transaction ID for the current transaction.  This parameter should not be changed.

Returns (text): Returns the last name of the last user to make changes to the transaction.  See the Concatenation section for instructions on combining multiple text results.

Count(p0)

Counts the number of records in a range of fields.

P0 (any type): Any mix of fields and grid columns.  See Defining Multiple Fields in a Function for details.

Returns (numeric): Number of records in collection p0.

Sum(p0)

Calculates the sum of a range of fields.

P0 (numeric collection): Any mix of numeric fields, grid columns, and policy details.  See Defining Multiple Fields in a Function for details.

Returns (numeric): The sum of elements in collection p0.

Avg(p0)

Calculates the average of all values in a specified range of fields.

P0 (numeric collection): Any mix of numeric fields and grid columns.  See Defining Multiple Fields in a Function for details.

Returns (numeric): The average value in collection p0.

Multiply(p0)

Multiplies all values in a specified range of fields.

P0 (numeric collection): Any mix of numeric fields and grid columns.  See Defining Multiple Fields in a Function for details.

Returns (numeric): The multiplied total of all values in collection p0.

Round(p0, p1, p2)

Takes a provided numeric value or field, and rounds it to the nearest value, according to the selected mode.

P0 (numeric): Value to be rounded

P1 (Positive Integer): Precision or nearest value.  Rounds to the appropriate multiple of the provided value.

P2 (Integer): Rounding mode:

−    0 = Rounds to the nearest multiple of P1, with the exact midpoint rounding up.

−    1 = Rounds up to the next multiple of P1.

−    2 = Rounds down to the next multiple of P1.

Returns (numeric): Round off p0 parameter to the appropriate multiple of p1 according to the round mode p2

eg.             Round(10.7, 1, 0) will round to the nearest 1, resulting in 11.

                  Round(10.7, 1, 2) will round down to the nearest 1, resulting in 10.

                  Round(1100, 500, 1) will round up to the nearest 500, resulting in 1500.

Trim(p0)

Processes a string value and returns the string with any leading or trailing spaces removed.

P0 (string): String value to be trimmed.

Returns (string): The string value without any leading or trailing spaces.

Note:  Data that has been entered manually in workflow fields are automatically trimmed by the UI.  This function is for data that may have been imported from external systems.

Length(p0)

Evaluates a string value and returns the total number of characters.

P0 (string): Source string value.

Returns (integer): The number of characters in the source string.

eg.             Length("Test String") will return 11.

Mid(p0, p1, p2)

Evaluates a string value and returns a range of characters.

P0 (string): Source string value.

P1 (integer): Starting character position.  This is the numerical position of the first character to be returned, starting at 1.  If the starting position is beyond the end of the string, nothing will be returned.

P2 (integer): Number of characters to return, counting from the starting position and including the starting position.  If the string ends before the number is reached, the existing characters will be returned.

Returns (string): The portion of the string defined by the range values.

eg.             Mid("Test String", 6, 4) will return "Stri".

                  Mid("Test String", 6, 8) will return "String", as there are only six characters from the starting point.

                  Mid("Test String", 1, 4) will return "Test".

UCase(p0)

Converts the letters in a string of text to upper case.

P0 (text): This identifies the text to be converted.  This can be a placeholder, function, formula or literal value.  Numbers and symbols can be included in the text and will be unaffected by the conversion.

Returns (text): Returns a string of text with all the letters in upper case.

eg.

If the value of p0 is “Policy”, the function returns “POLICY”.

If the value of p0 is “a1b2c”, the function returns “A1B2C”.

LCase(p0)

Converts the letters in a string of text to lower case.

P0 (text): This identifies the text to be converted.  This can be a placeholder, function, formula or literal value.  Numbers and symbols can be included in the text and will be unaffected by the conversion.

Returns (text): Returns a string of text with all the letters in lower case.

eg.

If the value of p0 is “POLICY”, the function returns “policy”.

If the value of p0 is “A1B2C”, the function returns “a1b2c”.

$Exp(p0, p1)

Calculates exponential values.

P0 (numeric): Base value

P1 (numeric): Exponential value.

Returns (numeric): Returns the value of p0 to the power of p1.

      Exceptions:  0 or any positive base number to the power of 0 will return 1.

                              0 base to the power of any negative number will return 0.

                              Any negative base number to the power of any decimal value will return 0.

eg.  $Exp(2, 3) will return 8.  $Exp(2, 0) will return 1.  $Exp(0, -3) will return 0.  $Exp(-2, 3.5) will return 0.

$InterpolatedLookup(p0, p1, p2)

Calculates interpolated or extrapolated information.  When provided with a range of known values in an (x, y) grid, this function will take a specific x-value and determine its y-value using the existing data.

P0 (text): The Code of the table containing the data.  The first column of the table must be a numeric column to be used as the y-values.

P1 (text): The name of the column containing the x-values.  This must be a numeric column with unique values.

P2 (numeric): The x-value for which to calculate the y-value.

Returns (numeric): The interpolated or extrapolated value of y.

      Exceptions:   If the P2 value exactly matches an x-value in the P1 column, the function returns the associated y-value.

If the table only contains one row of data, interpolation/extrapolation is not possible.  The function will return the single y-value.

If the P0 lookup table is empty, the function will return 0.

−    See Interpolation/Extrapolation for a detailed description of the function.

AdjustedMonths(p0, p1, p2)

Divides one number by another, and adds 1 to the total if the remainder exceeds a specific amount.  This function is commonly used to determine how many months are in a specific number of days, possibly including a partial month.

P0 (numeric): The dividend, the number to be divided

P1 (numeric): The divisor, the number to divide by

P2 (numeric): If the remainder equals or exceeds this value, the result is increased by 1

Returns (integer): The whole number resulting from the division and remainder.

eg. This function can be used to convert a number of days into months, with any remainder meeting or exceeding 16 days counting as an additional full month.  Enter the number of days, which can come from a field or other function, divide by the average length of a month, 30 (for example), and set the remainder threshold to 16.

AdjustedMonths([[DaysField]], 30, 16)

If [[DaysField]] equals 130, the number of months is 4 with a remainder of 10.  Since 10 is less than the remainder threshold, the final value is 4.

If [[DaysField]] equals 78, the number of months is 2 with a remainder of 18.  Since 18 is more than the remainder threshold, the final value is 3.

Min(p0)

Identifies the lowest value in a specified range of fields.

P0 (numeric collection): Any mix of numeric fields and grid columns.  See Defining Multiple Fields in a Function for details.

Returns (numeric): The minimum value in collection p0.

Max(p0)

Identifies the highest value in a specified range of fields.

P0 (numeric collection): Any mix of numeric fields and grid columns.  See Defining Multiple Fields in a Function for details.

Returns (numeric): The maximum value in collection p0

SetMin(p0, p1, p2)

Identifies the lowest value in a specified range of fields, and compares it to a provided minimum or maximum value.

P0 (numeric collection): Any mix of numeric fields and grid columns.  See Defining Multiple Fields in a Function for details.

P1 (numeric): The minimum or maximum value returned, depending on the Mode.

P2 (numeric): Mode

−    0 = P1 is a minimum value

−    1 = P1 is a maximum value

Returns (numeric):

−    Mode 0 = Lowest value of p0, unless it is lower than p1, in which case p1 is returned.

−    Mode 1 = Lowest value of p0, unless it is not as low as p1, in which case p1 is returned.

SetMax(p0, p1, p2)

Identifies the highest value in a specified range of fields, and compares it to a provided minimum or maximum value.

P0 (numeric collection): Any mix of numeric fields and grid columns.  See Defining Multiple Fields in a Function for details.

P1 (numeric): The maximum or minimum value returned, depending on the Mode.

P2 (numeric): Mode:

−    0 = P1 is a maximum value

−    1 = P1 is a minimum value

Returns (numeric):

−    Mode 0 = Highest value of p0, unless it is higher than p1, in which case p1 is returned.

−    Mode 1 = Highest value of p0, unless it is not as high as p1, in which case p1 is returned.

TakeFirst(p0)

Returns the first value of a grid field.

P0 (any): The grid field containing the values.

Returns (any): Returns the first value of the grid field, based on the row numbers.  The result is not affected by sorting or filtering the grid.

TakeLast(p0)

Returns the last value of a grid field.

P0 (any): The grid field containing the values.

Returns (any): Returns the last value of the grid field, based on the row numbers.  The result is not affected by sorting or filtering the grid.

MatchAny(p0, p1)

Searches a set of values to determine if any value matches a provided search value.

Warning:  This function should not be used in any formula.  This function can appear in analysis features when a trigger is converted into a formula, and is described here for troubleshooting purposes.

P0 (any): Any mix of fields and grid columns.

P1 (any): The search value being matched against the fields.

Returns (boolean): Returns True if any value in the set matches the search value.  Returns False if none of the values match.

MatchAll(p0, p1)

Searches a set of values to determine if all values match a provided search value.

Warning:  This function should not be used in any formula.  This function can appear in analysis features when a trigger is converted into a formula, and is described here for troubleshooting purposes.

P0 (any): Any mix of fields and grid columns.

P1 (any): The search value being matched against the fields.

Returns (boolean): Returns True if all values in the set match the search value.  Returns False if even a single value does not match.

$Parent(p0)

For use with multi-level parent and child grids.  This function is used within a child grid to search all parent grids for the specified column, then returns the value from that column correlated to each entry in the child grid.

P0 (text): The field (column) Code to be found among the parent grids.  This condition is literal and must contain the Code of the field, no placeholders or functions can be used here.

Returns (any): The correlated value from the selected field in the parent grid.

Note:  This function is no longer required, but is still supported for backwards compatibility.  Using a normal placeholder to reference a field from a parent grid will now return just the data associated to the current child record.

$Child(p0)

For use with multi-level parent and child grids.  This function is used within a parent grid to search all child grids for the specified column, then returns all values from that column correlated to each entry in the parent grid.

P0 (text): The field (column) Code to be found among the child grids.  This condition is literal and must contain the Code of the field without any square brackets, no placeholders or functions can be used here.

For example:  $Child(FieldCode)

Returns (any): The correlated values from the selected field in the child grid.  Multiple values are returned separated by pipe characters ( | ).  The results can be displayed directly using a text field, but this function is primarily intended for use within other grid related functions such as Sum(), Count(), and Avg().  Since this function can return multiple values, it cannot be used in calculations or as a condition in functions that expect a single input value.

Note:  This function is no longer required, but is still supported for backwards compatibility.  Using a normal placeholder to reference a field from a child grid will now return just the data associated to the current parent record.

Replace(p0:Label, p1, p2)

When pulling data from a grid, field, or function that returns multiple values, the results are received in a delimited format where the values are separated by the pipe ( | ) character.  This function removes the pipe characters and replaces them with characters defined in the parameters.  This allows the results to be displayed on-screen, or in generated e-mails and documents, in a clean and legible format.  Missing values are skipped without adding an extra separator.

P0 (source): Field or function that returns multiple values.

:Label (optional): When pulling data from a field that uses an option list (checkbox groups, drop downs, include / exclude, multi selects, and radio buttons), the default [[Placeholder]] format will return the codes for the selected options.  Using [[Placeholder:Label]] will return the labels for the selected options instead of the codes.

If the option list is of Type: Static and Resource Type: Custom, alternate language versions may be defined for the labels.  The appropriate version will be returned according to the browser language settings.

P1 (text, optional): The text to be replaced.  Note that P1 and P2 must both be used, or neither should be used.  If both P1 and P2 are left out, the function defaults to just replacing the pipe character.

P2 (text, optional): The text that will replace the P1 text.  Note that P1 and P2 must both be used, or neither should be used.  If both P1 and P2 are left out, the function defaults to a comma followed by a space.  The comma, round brackets ( ), and double-square brackets [[ ]] should not be used.

Returns (text): Text string with all pipe characters or P1 text replaced by a comma and a space or the P2 text.

eg. Replace([[StatesField]])

If the [[StatesField]] column has three states, the results would be "New York, Florida, California" (without the quotes).

Replace([[OptionsList:Label]], R-, Region )

If the [[OptionsList]] field is a multi-select with "Canada R-1", "US R-1", "Europe R-2", and "China R-6" selected, the results would be "Canada Region 1|US Region 1|Europe Region 2|China Region 6".  Note the space after "Region " in the parameters.

Replace(Replace([[OptionsList:Label]], R-, Region ))

With the previous function included in a standard replace without P1 and P2, the results can then have the pipes replaced.  The results of this function would be "Canada Region 1, US Region 1, Europe Region 2, China Region 6".

Notes:  This function changes the data to a single string of text.  Even if the source was a grid column, the results of this function cannot be used in a repeater to build a table in a document or e-mail.

If the P2 parameter includes any mathematical symbols, such as +, -, and /, the String: prefix should be used to return the results as text and prevent the system from trying to resolve the results as a mathematical equation.

SaveToField(p0, p1)

Saves a specific value to a specific field in the workflow.

P0 (text): The code of the target field to receive the data, not enclosed in brackets.  The code can be entered directly, retrieved from a field by a placeholder, or calculated with conditional logic.  Any method must return the code of the target field as straight text.

P1 (any): The value to be saved in the target field.  This can be a literal value, a placeholder, or a calculated value.

Returns (any):  The calculated field itself will display the last value that was saved to the target field.

eg.             SaveToField(TargetField, [[SourceField]]/2) will take the value in field [[SourceField]], divide it by 2, and save the result in field TargetField.

                  SaveToField(TargetField, "Does not apply") will save the text Does not apply in field TargetField.  This function will always save the same data in the field, so it would most likely be used within a conditional structure to only save the data under certain circumstances.

SaveToGrid(p0, p1_SequenceNo, p2, p3, p4, p3, p4, …)

NewGrid(p0, p1_SequenceNo, p2, p3, p4, p3, p4, …)

Adds one or more rows to a grid, or edits a single row within a grid.  The SaveToGrid() function adds new rows to a grid that may have existing data, while the NewGrid() function deletes all rows from the grid before adding new rows.

P0 (text): Code of the target grid.  This must be a static code, not enclosed in brackets or quotes.

P1 (text): This parameter is optional, and is used in conjunction with P2 to update a single row in the target grid.  The format is the Code of the target grid followed by "_SequenceNo", such as GridCode_SequenceNo.  This must be a static code, not enclosed in brackets or quotes.

P2 (integer): This parameter is optional, and is used in conjunction with P1 to update a single row in the target grid.  This is the row number to be updated.  This must be an integer, and can be a static value, the result of an embedded formula, or a placeholder to retrieve the value from another field.

P3 (text): Code of the recipient field in the target grid.  This must be a static code, not enclosed in brackets or quotes.  There can be multiple recipient fields, but each P3 must be followed by an associated P4 parameter.

P4 (text): Value to be inserted into the recipient field.  This can be a static value or a placeholder to retrieve the value from another field.  Each P4 must follow an associated P3 parameter.

Returns (text): The row number for each row added or edited.

−    See Functions to Enter Data Into Grids (Grid Mapping) for a detailed description of the function.

ClearGrid(p0)

Clears all data from a provided grid and any associated child grids

P0 (text): Code of the grid to be cleared.  Child grids are cleared automatically, and do not need to be specified.

Returns (): N/A

Where(Value1 operator Value2)

This function applies a condition to the subject that directly precedes it.  This can be a placeholder, another function, or a single parameter of another function.

Value1: This is the first value for comparison.

operator: Operator to determine how the values should be compared

Value2: This is the second value for comparison.

Returns (): N/A

−    See Conditional Selection of Grid Rows - the Where() function for a detailed description of the function.

GetOFACScan(p0)

Takes a provided field and compares the text to standard OFAC lists of names and places under government sanctions.

P0 (text): Text field to be scanned

Returns (numeric): Match value from a standard OFAC scan based on the input parameter p0.  The returned value is from 0 to 100, 100 being a perfect match, 0 being no match.

Compliance(p0, p1, …)

Takes a provided field and compares the text to one or more lists of names and places under sanctions.

P0 (text): Text field to be scanned

P1 (text): Code of the first list the data should be checked against.  At least one list must be included, but additional lists can be provided, separated by commas.  See Compliance() Function Parameters for all available lists and their codes.

Returns (numeric): Match value based on the input parameter p0.  The returned value is from 0 to 100, 100 being a perfect match, 0 being no match.  If more than one list is provided, the highest value is returned.

−    See Compare Data Against Sanction or Watch Lists for a detailed description of the function.

CheckComplianceHit(p0, p1, p2, p3, p4, p5, p6)

Takes a provided field and compares the text to one or more lists of names and places under sanctions, and stores the results in provided fields.

P0 (text): The field or static value identifying the type of scan to perform.  The value must be one of the following options:

−    Company = Scans against company names.

−    Country = Scans against country names.

−    Person = Scans against names of individuals.

−    Unknown = If the type of data being scanned is unknown, this option will scan against all list types.  This option will take longer to process.

P1 (text area): The field to receive the reasons for any failed scans.  This should be a Text Area field, as the results could be extensive.  This will include the matching percentage score, entity type, and entity name, plus additional information provided by the scan.

P2 (any): The field to receive just the matching percentage scores for any failed scans.  The resulting values will be from 0 to 100.

P3 (text): The field to receive the list names where the scans failed.

P4 (text): The field to receive the combined results of the scan.  This will be a pipe-separated list showing each list name followed by the associated score in brackets.  Example:  List 1 (90%)|List 2 (80%)

P5 (text): The field containing the data to be scanned.

P6 (text): The field identifying lists to scan against and the score thresholds for each.  The contents of the field should be formatted as: List Code:Score|List Code:Score|…

Multiple lists can be specified, separated by a pipe | character.  Each list and score pairing must be separated by a colon :.

List Code: The code of a sanction list to scan against.  See Compliance() Function Parameters for all available lists and their codes.

Score:  The numeric score threshold for the associated list, from 0 to 100.  If the scan results in a score equal to or greater than the threshold, it will be reported as a fail.

Note:  When scanning against multiple lists, the P1, P2, and P3 fields may contain multiple result values.  In a single text field, the results will be separated by pipe characters |.  The field can be used in grids, where the data will be separated by rows containing associated values.

Returns (boolean):  Returns "True" if any scan fails, or returns "False" if all scans passed.

$Lookup(p0, p1, ..., p10)

Takes one or more condition fields and checks a lookup table for the value that matches the conditions, returning that value.  Lookup tables are configured in the Lookup Tables option in the Product Design menu.  For details about configuring lookup tables, see Lookup Tables.

P0 (any): Code of the lookup table to be checked

P1 (any): The first condition to be used in identifying the appropriate value.  Additional conditions can be added, separated by commas.  The number of conditions in the function should match the number of conditions in the target lookup table.  If the function includes more conditions than exist in the table, the function does not return any value.  If the table includes more conditions than are used in the function, the function returns the first value that matches the conditions.  There is a maximum of ten conditions.

Returns (text): The function checks the Effective Date of the current transaction, and looks for any versions of the lookup table that are valid for that date.  If more than one version is valid for the date, the most recent table is used. The function returns the first value in the table that matches all of the conditions.  If the lookup table does not have a version valid for the date, the function does not return any value.

If the function is used to lookup numerical values, the function can be used as-is.  If the values include text or any non-numerical characters, the String: prefix must be used (detailed here).

$If(p0, p1, p2)

Evaluates a trigger and returns one value or another.  For details on working with the $If() functions, see Conditional Results.

P0 (trigger): Code of trigger to be evaluated

P1 (matches type of field): The value to return if the trigger evaluates as true

P2 (matches type of field): The value to return if the trigger evaluates as false

Returns (matches type of field): Parameter 1 or 2, depending on the result of the trigger.

If Value1 operator Value2 then TrueValue else FalseValue endif

This is an alternate version of the $If() function, where the conditions are built into the function instead of using a trigger.  For details on working with the If functions, see Conditional Results.

Value1: First value to compare

operator: Operator to determine how the values should be compared

Value2: Second value to compare

TrueValue: The value to return if the trigger evaluates as true

FalseValue: The value to return if the trigger evaluates as false

Returns: TrueValue or FalseValue, depending on the result of the conditions.

Trigger(p0)

Evaluates a trigger configuration and returns the boolean result (True or False).

P0 (text): The Code of the trigger to be evaluated.

Returns (boolean): Returns True if the trigger evaluates as true.  Returns False if the trigger evaluates as false.

Note:  This function can be used on its own, but is particularly useful for If/Then conditional structures.

MapValue(p0, p1, p2, p1, p2, …, p3, p4)

Performs a series of value comparisons until it finds a matching value, then returns a specific value for that result.

P0 (text): Placeholder for the target field to be evaluated.

P1 (any): Each P1 parameter must have a matching P2 parameter.  These are the paired case conditions and return results.  The condition can be a static value, placeholder, or another function.

The condition value must match the value of the target field exactly, or the function will move on to the next condition.

P2 (any): Each P2 parameter must have a matching P1 parameter.  These are the paired case conditions and return results.  The return result can be a static value, placeholder, or another function.

P3 (text): The P3 parameter must have a matching P4 parameter.  This parameter is optional, and should contain the text "Default".  This condition will be used when all other listed conditions fail.  If the P3 and P4 parameters are not included, the function will return null if all other listed conditions fail.

P4 (any): The P4 parameter must have a matching P3 parameter.  This parameter is optional, and returns the default value if all other conditions fail.

Returns (any): The P2 value of the first condition that matches the target field value, or returns the P4 value or null if none of the conditions match the target field value.

−    See Sequential "Case" Conditions - the MapValue() function for a detailed description of the function.