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

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

Warning: A function field that references another function field in the function will result in an error. Function fields don't hold data or evaluate in run-time.

Example: Use case

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

Instead of querying all records to determine which record meets a given criteria, create a function field or function script that retrieves only the records that meet the criteria.

For a simple computation, it is better to use a function field or function script rather than store a computed value. The value is always calculated on retrieval. Another benefit of using function fields is that the database server performs the transformation rather than the application node.

Function fields versus calculated fields

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

Function fields versus business rules

Use function fields or scripts when business logic is based on a simple transformation of one or more existing fields. Function fields and scripts let you implement business logic without storing and maintaining the result of a transformation.

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 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 that 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.
  • Function fields that you create in the Reporting UI do not support dot-walking. For more information, see Configure function fields in Reporting.
  • Security is evaluated on the components of the function and on the calculated value of the field. When used in visualizations, sections that contain information that the user is not permitted to see are hidden from the user.
  • Field function names must be unique.

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.
    GlideDBFunctionCaseBuilder - Global Build case statements.
  2. Create a field that holds the function definition, as shown in the following example.

    Function field definition

    Select the Function field check box on the Dictionary Entry [sys_dictionary] form. This action specifies that the field runs a function rather than stores a value.

glidefunction operations

Function fields can perform the following operations using the glidefunction:<operation> syntax. When providing a field as an argument, you can dot-walk to related fields. For example, cmdb_ci.name.
Note: The function field type must be compatible with the return type of the glide function. For example, integer, longint, string, and so on. If the returned type cannot be converted to the dictionary type, you get exceptions.
Table 2. Function field operations
OperationDescriptionExample
add() Takes two number fields as input, adds them, and returns the results as a field value.

This function also takes numerical values for either input. Place numerical values in single or double quotation marks.

 glidefunction:add(child_incidents, parent_incident)

Returns 6 if the incident has five child incidents and one parent incident.

Possible return types: Decimal, Floating Point Number, Large Whole Number, Whole Number
concat() Takes any number of comma-separated fields and constants as input, concatenates the input, and returns a single string as a field value. glidefunction:concat(incident_number, '/', short_description)

Returns "INC0001 / My client needs a new laptop." if the value of the number field is 'INC0001' and the short_description is 'My client needs a new laptop'.

Return type: Text
coalesce() Takes any number of comma-separated fields as input and returns the first non-empty value. glidefunction:coalesce(closed_at, resolved_at, sys_updated_on)

If the value of closed_at is empty, the function returns the value of resolved_at. If the value of resolved_at is also empty, the function returns the value of sys_updated_on.

Return type: Text
divide() Takes two number fields as input, divides the first by the second, and returns the result as a field value.

This function also takes numerical values for either input. Place numerical values in single or double quotation marks.

 glidefunction:divide(u_num2,u_num1)

Returns 5 if num2 = 10 and num1 = 2.

Possible return types: Decimal, Floating Point Number, Large Whole Number, Whole Number
datediff() Takes two 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. glidefunction:datediff(closed_at, sys_created_on)

Returns the duration of an incident from the creation date to the close date. Example result: 10 days, 8 hours 23 minutes 11 seconds

Return type: Duration
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.

The dayofweek() function uses UTC dates, but adjusts comparison values based on the instance's time zone.

 glidefunction:dayofweek(resolved_at, '1').

If resolved_at occurs on a Wednesday, returns 4 if the integer is 1 and returns 3 if the integer is 2.

Return type: Whole number
distance_sphere() Takes two locations and returns the distance between them in meters.
  • glidefunction:distance_sphere('-0.189937', '51.473584', '80.206458', '25.767242')

    Returns the distance between the two sets of longitude and latitude pairs.

  • glidefunction:distance_sphere(u_location, '80.206458', '25.767242')

    Returns the distance between the u_location geo point field and the longitude and latitude coordinates.

  • glidefunction:distance_sphere(location1, location2)

    Returns the distance between the location1 and location2 geo point fields.

  • glidefunction:divide(distance_sphere(location1, '-0.189937', '51.473584'), '1000')

    Returns the distance between the location1 geo point field and London in kilometers.

Return type: Decimal
greatest() Takes two or more values as input and returns the greatest value of the list of arguments. glidefunction:greatest(10,100,1000)

Returns 1000

Possible numeric return types: Decimal, Floating Point Number, Large Whole Number, Whole Number

This function also works with date and string fields.
least() Takes two or more values as input and returns the lowest value of the list of arguments. glidefunction:least(10,100,1000)

Returns 10

Possible numeric return types: Decimal, Floating Point Number, Large Whole Number, Whole Number

This function also works with date and string fields.
length() Takes a string field as input, calculates the field length in characters, and returns the results as a field value. glidefunction:length(short_description)

Returns 37 if short_description = "This application is performing a test".

Return type: Whole number
multiply() Takes two number fields as input, performs the multiplication, and returns the results as a field value.

This function also takes numerical values for either input. Place numerical values in single or double quotation marks.

 glidefunction:multiply(u_num1, u_num2)

Returns 48 if num1 = 8 and num_2 = 6.

Possible return types: Decimal, Floating Point Number, Large Whole Number, Whole Number
position() Takes two text fields or two text fields and a whole number as input. One or both of the text fields can also be strings.
  1. Returns the position of the first occurrence of the string of the first text field in the second.
  2. If a whole number is specified, this function returns the position of the second text field after the position of the whole number.

Returns 0 if the first text field is not present in the second (after the position of the whole number if specified).
  • glidefunction:position('e',short_description)

    If the value of the short_description field is "This computer is performing an upgrade", returns 12.

  • glidefunction:position('e',short_description,'14')

    If the value of the short_description field is "This computer is performing an upgrade", returns 19.

Return type: Whole number
substring() Takes a text field and two whole numbers as input. Returns the first instance of a string that starts at the position of the first whole number and is the length of the second. glidefunction:substring(short_description, '7', '2')

If the value of the short_description field is 'We're going to the store', returns 'go'.

Return type: Text
subtract() Takes two number fields as input, subtracts the second from the first, and returns the result as a field value.

This function also takes numerical values for either input. Place numerical values in single or double quotation marks.

 glidefunction:subtract(u_num1, u_num2)

Returns 2 if num1 = 8 and num_2 = 6.

Possible return types: Decimal, Floating Point Number, Large Whole Number, Whole Number

Create a function field to perform database functions

Create a field that displays the results of a database function, such as a mathematical operation, field length computation, or day of the week calculation. Test in a subproduction instance before deploying to a production instance.

Before you begin

Role required: personalize_dictionary or admin.

Procedure

  1. Navigate to All > System Definition > Dictionary.
  2. Click New.
  3. Select Function field.
  4. Fill in these fields on the form.
    Table 3. Fields for function fields
    FieldDescription
    Table Table to which the function field is added.
    Type Species the type of field: string, number, date, and so on.
    Column label Defines the label for the column.
    Column name Defines the name of the column. Created automatically based on Column label.
    Max length Defines the maximum length of the return value.
    Function field If selected, creates a field that displays the results of a database function, such as a mathematical operation, field length computation, or day of the week calculation.

    Once the new function record is saved, you cannot clear the check box to make the field a regular field.
    Function definition Defines the function that the field performs.

    Starts with glidefunction:, followed by the operation to be performed (for example, concat), followed by function parameters. Constants must be enclosed in single quotes.

    For example, the following function definition creates a field that shows the short description, followed by a space, followed by the caller name:

    glidefunction:concat(short_description, ' ', caller_id.name)

    Note: Function parameters support dot-walking. See Dot-walking to data in related tables.
  5. Click Submit.
    You return to the Dictionary Entry [sys_dictionary] table.
  6. Navigate to the table to which you added the function field.
  7. Add the function field to the list.
    See Configure items on forms or in lists using a list collector.

Result

Use the function field like any other field. For example, add it to a form, use it in a filter, or use it in the condition builder.

If the function definition is invalid, instead of the expected value you receive the message Invalid function if the function field is a string type field, or an empty value for other field types.