Calculation Builder

Various controls in the Script Designer, Data Entry Designer, and Grid Designer give you access to the Calculation Builder.

Screenshot of the Calculation Builder

The Calculation Builder lets you write expressions in SQL and JavaScript. You can freely enter any JavaScript in your expression. You can also build up expressions by using the functions provided in the dropdown.

Inserting a Function

To add a function to your expression, select the function that you want to add and then click Insert. The function is added to your expression in the format functionName(parameter1, parameter2).

To use the function, you must specify the parameter values (arguments). Each function has a different number of parameters.

For each parameter, you can pass:

  • A value that you enter manually.
  • A database field, entered either manually or by using the table and field dropdowns.
  • Another function (in other words, you can nest functions).

A nested function looks like this: functionA(functionB(argumentB1, argumentB2), argumentA2). In the example below, functionB is passed as the first parameter of functionA.

EXAMPLE 

Jack at Inisoft Travel wants to display the customer’s age to the agent. Inisoft Travel store the customer’s date of birth in field DOB in the CONTACT table.

Jack drags a Calculation control onto a data entry element. He then edits its Expression property to open the Calculation Builder. Here he does the following:

  1. In the Insert function dropdown, Jack selects calculateAge. He clicks Insert.

    The Expression box now looks as so: calculateAge( birthDate , otherDate )

  2. In the Expression box, Jack double-clicks birthDate to select it. In the Table name dropdown, he selects CONTACT. In the Field name dropdown, he selects DOB. He clicks INSERT.

    The Expression box now looks as so: calculateAge( [CONTACT.DOB] , otherDate )

  3. In the Expression box, Jack double-clicks otherDate to select it. In the Insert function dropdown, he selects now. He clicks INSERT.

    The Expression box now looks as so: calculateAge( [CONTACT.DOB] , now() )

That’s it! At runtime, the calculation will show the customer’s current age.

Inserting a Database Field

To insert a database field into your expression, first select whether to:

  • Show only fields used in element: Shows only tables and fields that are used by controls in the data entry element.
  • Show all fields in universe: Shows all tables and their fields.

To add a database field, select the database table in the Table name dropdown and then the database field in the Field name dropdown. Last, click Insert.

Descriptions of Functions

The following table describes the functions that you can use.

Function Description
Date Functions  
addHours(startDate, hours) Given a date and time and a number of hours, adds the hours to the date and time and returns a new date and time.
addMinutes(startDate, minutes) Given a date and time and a number of minutes, adds the minutes to the date and time and returns a new date and time.
calculateAge(birthDate, otherDate) Returns the age of a person on a particular date given their birth date.
now() Returns the current date and time.
Number Functions  
random(minValue, maxValue) Returns a random integer between a given minimum value and maximum value.
String Functions  
lower(value) Converts the string to lowercase.
stringLength(value) Returns the number of characters in the string.
stringReplace(stringValue, toReplace, replaceWith, replaceAll, ignoreCase)

Replaces characters (toReplace) in a string (stringValue) with other characters (replaceWith).

Set replaceAll to true to replace all occurrences in the string or to false to replace only the first occurrence.

Set ignoreCase to true to ignore differences in case or to false to perform case-sensitive replacements.

stringsEqual(value, value2, ignoreCase)

Compares two strings and returns true if they are the same or false if they are not.

Set ignoreCase to true to ignore differences in case or to false to perform case-sensitive replacements.

upper(string) Converts the string to uppercase.