Building Formulas

In This Topic...

Formulas are assembled using common logic and notation, and should be familiar for users experienced with formulas in spreadsheet and database applications.

Operators

The system uses the following symbols to represent common arithmetic operators.

+

Add

-

Subtract

*

Multiply

/

Divide

(

Open bracket

)

Close bracket

Simple Arithmetic Formulas

The above operators are used to build the basic structure of the formula.

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

Note: Spaces are optional, but can be included for clarity.  They will be ignored by the system.

Placeholders Within Formulas

The real power of calculated fields in the Bridge Specialty Suite is the ability to pull in information from the system and from the submission or claims form itself.  This is achieved using placeholders.  When the system encounters a placeholder while resolving a formula, it replaces the placeholder with the current value from the system or workflow.

The placeholders are predefined, and are detailed in the Placeholders section.  Placeholders for fields within the submission or claims form are made up of the Code for the field (defined in Field Management), enclosed in double square brackets [[ ]].

Example:  

100 x The Number of Trucks - 50

Would be input into a calculated field as:

100*[[NumberOfTrucks]]-50

Where NumberOfTrucks is the Code for the field “The Number of Trucks”.

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

Defining String Data

Calculated fields are not restricted to using numerical values.  Dates and text can also be manipulated using a selection of functions and features.

In some situations, however, 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.

There are two methods for defining string data.

  • The String: formatting notation can be used at the beginning of a calculated field formula, and causes the system to treat all values as text.  No calculations are performed, and any operators, arithmetic expressions, and spaces are treated as text.  Functions and placeholders still function normally, but the resulting data is treated as text.

    • Example:  

    String:[[field1]]/[[field2]]+[[field3]]

    If the values of the three placeholders are 8, 4, and 2 respectively, the value of this calculated field would be 8/4+2, and would be displayed as such in the field and any documents or e-mails that pull in this calculated field.

  • Any text, numbers, or symbols enclosed in quotes " will be treated as simple text.  No calculations will be performed on content within quotes, spaces in quotes will not be removed, and even functions and placeholders will be treated as text instead of being replaced with system information.

    • Example:  

    "The value of " [[field1]] " / " [[field2]] " + " [[field3]] " equals " [[field1]] / [[field2]] + [[field3]]

    If the values of the three placeholders are 8, 4, and 2 respectively, the result of this calculated field would be:

    The value of 8/ 4 +2 equals 4

    The first set of operators are treated as text and displayed in the output, while the second set are calculated and the result is displayed in the output.  Note that the spaces within the quotes are kept, while the spaces outside of the quotes are ignored.

    Note: All quotes must be straight quotes " or they will not be recognized.  Some word processing programs replace straight quotes with angled quotes or "Smart Quotes".  These quotes must be replaced with straight quotes, either in the word processor, or within the system.

  • If a field is used within an arithmetic expression that evaluates to null, the result is '0' (zero).

Concatenation (Combining Data)

If no Operators are used between placeholders and/or data entered directly in the formula, the system simply pulls in the placeholder data without any modifications.

This is useful for merging fields that are collected separately, but need to be used together.

Example: If a user's first name and last name are collected in separate fields, the following formulas could be used:

String:[[FirstName]] [[LastName]]  -or-  [[FirstName]] " " [[LastName]]

The value of this field would be FirstName LastName.  Note that a space must be included between the placeholders, or the two fields are combined without a space, such as FirstNameLastName.

Another example would be to assemble an address:

String:[[StreetAddress]], [[City]], [[Country]].  -or-  [[StreetAddress]] ", " [[City]] ", " [[Country]] "."

The commas, spaces, and period are included in the calculated value.  For the example using quotes, note that the spaces within the quotes will be kept, while the spaces outside the quotes will be ignored.

Adding Comments to Formulas

Comments can be added at the end of a formula and are commonly used to explain of the intent of the formula or describe the return value.

Comments are specified by the expression "//" and continue until the end of the line in the Calculated Field Formula field on the Field Management page. If comments should continue on the next line, the text must be preceded by another "//" expression. Any text following a double slash and up to the end of the line will be ignored when the formula is calculated.

Example:  

CurrentDateTime()//This formula returns the current Date and Time in the full system format.