Calculated Fields Reference Guide
Version 1.7.7
Introduction
Guide
Expressions
Operators
Arithmetic Expressions
Expressions with Placeholders
Defining String Data
Concatenation (combining data)
Policy References
Premiums
Term Premiums
Current Term
Previous Term
Limits
Deductibles
Taxes
Retrieving Premium IDs
Functions
Calculate the Time Between Two Date Fields
Discovering User Specific Roles
Defining Multiple Fields in a Function
Specify a Time Before or After a Date Field
Compare Data Against Sanction or Watch Lists
Conditional Results
Full Function List
Appendix A
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.7.7BRIDGE: Calculated Fields Reference Guide
+Plus
-Minus
*Multiply
/Divide
(Open bracket
)Close bracket
$Denotes a Function name
v1.7.7BRIDGE: 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.
Some placeholders use single quotes ' to identify a specific version of the variable. These must be straight quotes ' or the formula will not function. Some word processing programs will replace straight quotes with angled quotes or "Smart Quotes". These quotes will need to be replaced with straight quotes, either in the word processor, or within Bridge.
Defining String Data
In some situations, the formatting of data could be mistaken for calculations.
For example, the date format 5/15/2012 could be interpreted as 5 divided by 15 divided by 2012, or a policy number with dashes, such as 2012-12345 could be interpreted as 2012 minus 12345.
The String: formatting notation is used at the beginning of a calculated field formula, and will cause the system to treat all values as text. No calculations will be performed, and any operators or arithmetic expressions will be treated as text. Functions and placeholders will still function normally, but the resulting data will be treated as text.
For Example:
String:[[field1]]+[[field2]]/[[field3]]
If the values of the three placeholders are 2, 4, and 6 respectively, the value of this calculated field would be 2+4/6, and would be displayed as such in the field and any documents or e-mails that pull in this calculated field.
Concatenation (combining data)
If no Operators or Arithmetic Expressions are used between placeholders and/or data entered directly in the formula, the system will simply pull in the placeholder data without any modifications.
This is useful for merging data that is collected separately, but needs to be used together. For example, if a user's first name and last name are collected in separate fields, the following formula could be used:
String:[[FirstName]] [[LastName]]
Note: The String: notation must be used to display text in fields, emails, and generated documents.
The value of this field would be the first name, then a space, then the last name. Note that the space must be entered between the placeholders, or the two fields will be combined without a space.
Another example would be to assemble an address:
String:[[StreetAddress]], [[City]], [[Country]].
The commas, spaces, and period will be included in the calculated value.
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:
[[PremiumInfo[PremiumType='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.
It is also possible to return the commission amounts associated to the various premium types:
[[PremiumCommissionList/PremiumCommissionInfo[PremiumTypeId='#']/CommissionAmount]]
Where # is the Idnumber 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='#']/NetPremium]]
[[PremiumCommissionList/PremiumCommissionInfo[PremiumTypeId='#']/GrossPremium]]
In all cases, changing the premium type name or numerical Idwithin the function will retrieve the associated premium information as in the examples above.
Term Premiums
Term premium details are available and can be called for the current term and the previous term.
Current Term
The Current Term references will access the total values for all bound transactions in the current term. The references can access premium values, commissions, and taxes, for individual premium types or a total for all premium types.
[[CurrentTerm/Premium/PremiumInfo[PremiumType='Name']/Amount]]Returns the total premium for the current term, for the Name premium type.
[[CurrentTerm/Premium/PremiumInfo[PremiumType='Name']/CommissionAmount]]
Returns the total commission for the current term, for the Name premium type.
[[CurrentTerm/Premium/PremiumInfo[PremiumType='Name']/TotalTaxAmount]]
Returns the total taxes for the current term, for the Name premium type.
Note: For the above references, the premium type is identified by name using the [PremiumType='Name'] identifier. For these references, the premium type can also be identified by code using the [PremiumTypeId='#'] identifier instead. See the Retrieving Premium IDs section below for instructions on obtaining the ID numbers.
[[CurrentTerm/Premium/TotalPremiumAmount]]
Returns the total premium for the current term, for all premium types.
[[CurrentTerm/Premium/TotalCommissionAmount]]
Returns the total commission for the current term, for all premium types.
[[CurrentTerm/Premium/TotalTaxAmount]]
Returns the total taxes for the current term, for all premium types.
Notes: Only values from Bound transactions within the current term will be returned. Using these references in a submission will have one value before binding (when the transaction being viewedis not included) and a different value after binding (when the transaction being viewedis included).
Once a transaction in a particular term is bound, the calculated fields are locked for that transaction. They will not be updated if other transactions are subsequently bound within the same term.
Previous Term
The Previous Term references will access the total values for all bound transactions in the previous term, at the time the current term was created. The references can access premium values, commissions, and taxes, for individual premium types or a total for all premium types.
[[PreviousTerm/Premium/PremiumInfo[PremiumType='Name']/Amount]]Returns the total premium for the previous term, for the Name premium type.
[[PreviousTerm/Premium/PremiumInfo[PremiumType='Name']/CommissionAmount]]
Returns the total commission for the previous term, for the Name premium type.
[[PreviousTerm/Premium/PremiumInfo[PremiumType='Name']/TotalTaxAmount]]
Returns the total taxes for the previous term, for the Name premium type.
Note: For the above references, the premium type is identified by name using the [PremiumType='Name'] identifier. For these references, the premium type can also be identified by code using the [PremiumTypeId='#'] identifier instead. See the Retrieving Premium IDs section below for instructions on obtaining the ID numbers.
[[PreviousTerm/Premium/TotalPremiumAmount]]
Returns the total premium for the previous term, for all premium types.
[[PreviousTerm/Premium/TotalCommissionAmount]]
Returns the total commission for the previous term, for all premium types.
[[PreviousTerm/Premium/TotalTaxAmount]]
Returns the total taxes for the previous term, for all premium types.
Note: When a renewal is created, the values for the previous term are copied into the renewal transaction for use with these placeholders. If the previous term is changed in any way (such as by endorsement), the details saved in the renewal transaction may no longer be valid. If the renewal has not yet been bound, it may be possible to delete it and create a new renewal transaction with the updated values.
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]]will return the 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/Deductible[n]/Description]]will return the description of deductible for nth premium type.
[[DeductibleList/Deductible[n]/Amount]]will return the deductible amount for nth premium type.
[[DeductibleList/Deductible[n]/Currency]]will return the deductiblecurrency 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.
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.
Defining Multiple Fields in a Function
Some functions evaluate multiple fields to provide a result. These functions may calculate the average of all values in a single column of a grid, the sum of a series of fields throughout the submission form, or a mix of fields and columns.
For functions that support multiple fields, the Pipe character ( | ) is used to separate the fields in the list. For columns in a grid, only the field names must be provided. The system will automatically retrieve all records in the grid.
Examples:
To determine the average value of three fields:
$Avg([[field1]]|[[field2]]|[[field3]])
To determine the sum of all records in a column, plus one field outside the grid:
$Sum([[column]]|[[field]])
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.
Compare Data Against Sanction or Watch Lists
One or more fields can be compared to one or more sanction or watch lists from around the world.
Examples:
Comparing a field against a single list:
$Compliance([[ScanField]],List)
Where ScanField is the field to be scanned and List is the code of the list.
Outputs a numerical value from 0 (no match) to 100 (perfect match).
Comparing a field against multiple lists:
$Compliance([[ScanField]],List1,List2,List3)
Where ScanField is the field to be scanned and each List# is the code of a list. Any number of lists can be included.
Comparing multiple fields against multiple lists:
$Compliance([[ScanField1]][[ScanField2]][[ScanField3]],List1,List2,List3)
Where ScanField# are the fields to be scanned and each List# is the code of a list. The scan fields are combined and scanned as one large block of text.
This function outputs a numerical value from 0 (no match) to 100 (perfect match). If a different score is achieved on different lists, the highest score will be returned.
See Appendix A for a table of all lists and their associated codes.
Conditional Results
The $If function is used to define the value of a calculated field based on the content of other fields in the workflow. A trigger is defined as the condition, and the function will return one value if the trigger evaluates as true, or a different value if the trigger evaluates as false.
The condition line is constructed as follows:
$If(TriggerReference, TrueValue, FalseValue)
If TriggerReference is true, the TrueValue will be returned, otherwise the FalseValue will be returned.
Note: The System Reference code for the trigger must be used. The code is based on the name of the trigger, but may not match exactly.
The True/False values can include placeholders, formulas, or even other functions (including other $If functions).
Examples:
The function could return a monthly value as an annual value, or leave it as one month, depending on the trigger.
$If(Trigger, [[FieldA]]*12, [[FieldA]])
The function could return the user's name (concatenated from two fields) or their spouse's name (also concatenated).
String:$If(Trigger, [[UserFirstName]] [[UserLastName]], [[SpouseFirstName]] [[SpouseLastName]])
Using functions in the True/False values allows this $If function to return the sum of a table column, or the average of the values in that column.
Tip: Be sure to close the parentheses for both functions at the end. The total number of closing parentheses in the entire formula must match the number of opening parentheses.