Using function fields to perform database operations

Create function fields and scripts in the Now Platform to perform common database transformations and calculations.

Regular fields store a value in the database. A function field does not store data, but rather displays the results of a database query. Function fields do not have a database column associated. Instead, function fields generate a value based on simple computations of other fields and constants. Function fields can be used like any other regular fields in the system: in forms, lists, query conditions, reports, and so on.

Use case

You want to identify all incidents with a probably useless short description of fewer than 10 characters.

Rather than query all incident records and then individually determine whether each record meets the criteria, you can instead create a function field or function script that retrieves only the records that meet your criteria. This is a much more efficient query.

For a simple computation, it is better to use a function field or function script rather than store a computed value. You do not use up space, and the value is always up to date. An additional benefit of using function fields or function scripts is that the transformation is performed by the database server rather than the application node.

Function fields versus calculated fields

The values of calculated fields are stored in the database. The values of function fields or function scripts are not stored in the database, but are computed in real time. Function field values are always up to date.

Function fields versus business rules

When business logic is based on a simple transformation of one or more existing fields, function fields or function scripts let you implement business logic without storing and maintaining the result of the transformation using existing platform means such as business rules or calculated fields.

Function fields versus filters, query strings, and Rhino

Rather than calling Rhino to perform string transformations, you can use a function field or function script to perform the transformations for you. Function fields and function scripts are more efficient and result in up to date values that do not have to be stored or maintained.

Function field limitations

Because function field calculations are not actually stored in the database, some operations are not relevant.
  • Function fields cannot be directly audited or indexed.
    Note: To index a function field as if it were a regular field, make sure the individual fields used by the function are indexed. Or for best performance, make sure there is a composite index including all fields that are used in the function.
  • Function fields cannot be encrypted, since a function field value is never stored in the database.
  • Function fields cannot be converted to regular fields or vice versa.

Defining platform functions

Platform functions can be defined in two ways:
  1. Use the following application programming interfaces (APIs) to build and use functions in a script.
    Table 1. Function APIs
    APIs Description
    Scoped GlideDBFunctionBuilder Construct the function to perform a SQL operation.
    GlideRecord - addFunction(Object function) Apply the function to a GlideRecord.
  2. Create a field that holds the function definition, as shown in the following example.

    Function field definition

    The Dictionary Entry [sys_dictionary] form has a Function field check box which specifies that the field runs a function rather than stores a value.

Operations performed by function fields

Function fields can perform the following operations.
Table 2. Function field operations
Operation Description
add() Takes 2 number fields as input, performs the addition, and returns the results as a field value.
concat() Takes any number of fields and constants as input, concatenates the input, and returns a single string as a field value.
datediff() Takes 2 date/time fields as input, calculates the difference between the dates in days, minutes, and seconds, and returns the results as a duration field value.
dayofweek() Takes two arguments: a date field and a constant of either 1 (week starts on Sunday) or 2 (week starts on Monday). Returns the results as an integer value that represents the day of the week.
divide() Takes 2 number fields as input, performs the division, and returns the results as a field value.
length() Takes a string field such as a text field as input, calculates the field length in characters, and returns the results as a field value.
multiply() Takes 2 number fields as input, performs the multiplication, and returns the results as a field value.
subtract() Takes 2 number fields as input, performs the subtraction, and returns the results as a field value.