Arithmetic Expressions

Calculated Fields Reference Guide
Version 1.6

Introduction

Guide

Expressions

Operators

Arithmetic Expressions

Expressions with Placeholders

Policy References

Premiums

Limits

Deductibles

Taxes

Term Premiums

Referrals

Insuring Conditions

Functions

Calculate the Time Between Two Date Fields

Discovering User Specific Roles

Functions Specific to Grids

Specify a Time Before or After a Date Field

Full Function List

Introduction

Welcome to the Bridge Calculated Fields Reference Guide. This document is intended to provide a functional overview of Calculated Fields within Bridge and provide explanations of using basic expressions as well as advanced functions.

Guide

Click a link to jump to the desired section.

Expressions / Details how operators are used, how to build arithmetic expressions, and how to build expressions with system reference placeholders.
Policy References / Details how to use a calculated Field to reference and leverage policy related information.
Functions / Details of specific functions included within Bridge to perform calculations against data in submission Fields.

Expressions

The following section describes how operators are used, how to build arithmetic expressions, and how to build expressions with system reference placeholders within calculated fields.

Operators

Bridge recognizes common arithmetic operators as follows:

v1.6.0BRIDGE: Calculated Fields Reference Guide

+Plus

-Minus

*Multiply

/Divide

(Open bracket

)Close bracket

$Denotes a Function name

v1.6.0BRIDGE: Calculated Fields Reference Guide

Arithmetic Expressions

Within calculated fields you can build most standard arithmetic expressions for numerical output using the operators described above.

For example:

3 x 6 ÷ (5 + 15 – 0.3) x 0.6

Would be input into a calculated field as:

3*6/(5+15-.3)*.6

Expressions with Placeholders

Calculated fields allow you to build arithmetic expressions using numerical data,either manually input or generated by Bridge, by inserting placeholders that refer to Fields within a submission.Placeholders are built using double square brackets around the System Reference for any given Field.

For example:

100 x The Number of Trucks – 50

Would be input into a calculated field as:

100*[[NumberOfTrucks]]-50

Where, NumberOfTrucks denotes the specific “System Reference” name for the Field “The Number of Trucks”.

Notes: A calculated field cannot be used as a placeholder within another calculated field.

In order for any field to be used within a calculated field, it must be included in the workflow with the calculated field.

Policy References

This section describes how to use a Calculated Field to reference and leverage policy related information within Bridge.

Premiums

If you desire to work with Premium related data then you will need to use a Calculated Field to retrieve the relevant values.

For example:

The following placeholders will return the amount of premium for a premium type 1. In other words, the first premium type listed in the products section:

[[PremiumInfo[1PremiumType=’Name’]/Amount]]

[[PremiumInfo[PremiumTypeId=’#’]/Amount]]

Where Name is the full name or # is the Id number of the required premium type. See Retrieving Premium IDs below for instructions on identifying the Id number of the required premium type.

Similarly, the numerical reference can be changed to retrieve other premium amounts, such as the nth premium type amount:

[[PremiumInfo[n]/Amount]]

It is also possible to return the commission amounts associated to the various premium types:

[[PremiumCommissionList/PremiumCommissionInfo[1PremiumTypeId=’#’]/CommissionAmount]]

Where # is the Id number of the premium.

Should it be desired to work with the Net or Gross premium amounts, then one of the following placeholders can be used:

[[PremiumCommissionList/PremiumCommissionInfo[PremiumTypeId=’#’1]/NetPremium]]

[[PremiumCommissionList/PremiumCommissionInfo[PremiumTypeId=’#’1]/GrossPremium]]

In all cases, changing the premium type name or numerical reference Id [1] to another [n], within the function will retrieve the associated premium information as in the first examples above.

Limits

If you desire to work with Limit related data then you will need to use a Calculated Field to retrieve the relevant values.

For Example:

[[LimitList/Limit[n]/Description]]will return description of limit for nthpremium type.

[[LimitList/Limit[n]/LimitType]]will return the type of Limit (e.g. Per Occurrence) for the nthpremium type.

[[LimitList/Limit[n]/Amount]]will return the limit amount for the nthpremium type.

[[LimitList/Limit[n]/Currency]]limit currency for nth premium type

Deductibles

If you desire to work with Deductible related data then you will need to use a Calculated Field to retrieve the relevant values.

For Example:

[[DeductibleList/Limit[n]/Description]]will return the description of limit for nth premium type.

[[DeductibleList /Limit[n]/DeductibleType]]will return thetype (e.g. Per Occurrence) for the nthpremium type.

[[DeductibleList /Limit[n]/Amount]] will return the limit amount for nth premium type.

[[DeductibleList /Limit[n]/Currency]] will return the limitcurrency for the nthpremium type.

[[DeductibleList /Limit[n]/Max]] will return the maximumlimit for the nth premium type.

[[DeductibleList /Limit[n]/Min]] will return the minimumlimit for the nth premium type.

[[DeductibleList /Limit[n]/Percentage]] will return the limitpercentage for the nth premium type.

Taxes

If you desire to work with Taxes related data then you will need to use a Calculated Field to retrieve the relevant values.

For example:

[[TaxDefinition[n]/TaxName]]returns the tax name of the nth premium type.

[[TaxDefinition[n]/CalculatedAmount]]returns thecalculated tax amount for the nth premium type.

Term Premiums

If you desire to work with Term Premiums related data then you will need to use a Calculated Field to retrieve the relevant values.

For example:

[[PremiumInfo[nPremiumType=’Name’]/TermAmount]]returns the term premium amountfor the nth Name premium type.

Retrieving Premium IDs

When calling placeholders related to premiums, the premium will need to be identified. For the PremiumType placeholder, the name of the premium or the premium ID can be used. For other premium related placeholders, such as PremiumCommissionInfo, the premium ID should be used.

The order number can also be used in either case, however the order may change in various ways. It is recommended to use the name or ID to ensure the correct premium type is used.

To retrieve the ID numbers of the Premium Types you want to work with, open the Product Design menu and select Premium Types. The Premium Type List opens with all available premium types.

Point the mouse cursor at the hyperlink in the Name column to display the URL in the status bar at the bottom of the browser window. The URL will resemble the one below:

Take note of the numerical value after “id=”, underlined in red in the above example.

If the URL does not display, or the status bar is not visible, clicking the link for the premium type will open the Premium Type Management page. The URL will be displayed in the address bar, ending with the ID number.

If the status bar and address bar are hidden, please check the instructions for your browser to display one or the other.

Functions

The following section describes the use of specific functions within Bridge to perform calculations against data in submission Fields. In all cases a Function is denoted by a $ at the beginning of the string.

Note:Certain functions require a specific number of reference parameters in order to evaluate correctly. Different functions may have a different number of required parameters, or none at all.

Calculate the Time Between Two Date Fields

For any instance where a submission contains more than one Date Field, it is possible to calculate the number of (i) days, (ii) months, or (iii) years between any two (2) of the date fields.

For example:

The number of days between: $DaysBetween([[Date1Field]],[[Date2Field]])

The number of months between: $MonthsBetween([[Date1Field]],[[Date2Field]])

The number of years between: $YearsBetween([[Date1Field]],[[Date2Field]])

In the above three (3) examples, a function is called (denoted with the $ symbol). The name is not case-sensitive, so $daysbetween would also be acceptable. These particular functions require two (2) reference parameters in order to evaluate correctly, which *must* be date fields, and separated by a comma.

Discovering User Specific Roles

In the event that a value or Trigger depends on a specific User Role, the following function can be used:

$UserContainsRole(UserRole)

Where UserRole denotes the specific system name for the User Role you desire to identify, such as Underwriter Supervisor.

The slightly unusual thing about this function is it returns a “1” for true, and “0” for false. So please take care when setting up your Triggers.

Functions Specific to Grids

Grid Panels within Bridge allow users to input multiple selections for the same Field(s). To use a calculated field to get the sum or count from a column/Field in a grid use the following functions:

$Sum([[columnname]])

$Count([[columnname]])

Where columnname is the “System Reference” of the column/Field you’d like to count or sum.

Specify a Time Before or After a Date Field

A calculated Field can also be used to generate the number of days, months, or years before or after a specified Date Field within a Submission.

Examples:

Here is how to output a date 30 days after an existing date field:

$DateShort($AddDays([[MyDateField]],30))

Outputs a date 30 days afterMyDateField’s date in Short Date format. (eg. 6/1/2009)

Similarly, here is how to output a date 30 days before an existing date Field:

$DateLong($AddDays([[MyDateField]],-30))

Outputs a date 30 days beforeMyDateField’s date in Long Date format (eg. Wednesday June 1, 2009)

By substituting the functions $AddMonths or $AddYears for $AddDays in the above examples would also work in a similar fashion.

Full Function List

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), identifying the required format, plus any notes about the parameters.
• The value that will be returned by the function, and in what format it will be.

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

eg.Comparing date Feb20/2010 and Feb22/2010 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.

eg.Comparing date Feb28/2010 and Mar1/2010 returns 1.

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

eg.Comparing date Dec15/2010 and Jan1/2011 returns 1.

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

eg. 15/6/2011

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

$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, returnsMonday, June 15, 2011

$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, returns15/6/2011

$CustomDateFormat(p0,p1)

Advanced function. Converts a provided date into a custom date format.

P0(date) Date field

P1(text) String containing .NET formatting instructions

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

$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): 1 if current user account has role p0, 0 if it does not.

Note:This function will be changed in the future to return a Boolean type.

$IsNewBusiness(p0)

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

P0(numeric) Transaction ID number of the submission

Returns(numeric): 1 if given transactionID is new business, 0 if it is not.

Note: This function will be changed in the future to return a Boolean type.

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

$Count(p0)

Counts the number of records in a table.

P0(numeric collection) Any column (field) from the table

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

$Sum(p0)

Calculates the sum of a specified column in a table.

P0(numeric collection) The numeric field from the table

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

$Avg(p0)

Calculates the average of all values in a specified column in a table.

P0(numeric collection) The numeric field from the table

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

$Min(p0)

Identifies the lowest value in a specified column in a table.

P0(numeric collection) The numeric field from the table

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

$Max(p0)

Identifies the highest value in a specified column in a table.

P0(numeric collection) The numeric field from the table

Returns(numeric): The maximum value in collection p0

$SetMin(p0,p1,p2)

Identifies the lowest value in a specified column in a table, and compares it to a provided minimum or maximum value.

P0(numeric collection) The numeric field from the table

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 column in a table, and compares it to a provided minimum or maximum value.

P0(numeric collection) The numeric field from the table

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.

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

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

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

$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 will be from 0 to 100, 100 being a perfect match, 0 being no match.

$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(numeric)Precision or nearest value

P2(numeric)Rounding mode:

-0 =Round from midpoint

-1 = Round upward

-2 = Round downward

Returns(numeric): Round off p0 parameter to the nearest value p1 according to theround mode p2

v1.6.0BRIDGE: Calculated Fields Reference Guide