GlideDBFunctionBuilder - Scoped, Global

  • Release version: Yokohama
  • Updated January 30, 2025
  • 13 minutes to read

    • The GlideDBFunctionBuilder API provides methods to build functions to perform SQL operations in the database.

    These methods provide a way to build Relational Database Management System (RDBMS) functions to perform SQL operations on record data. You can use these methods in both scoped and global server scripts.

    To use platform functions:
    • Construct a function using the GlideDBFunctionBuilder constructor and associated methods.
    • After building a function, you apply the function to the current record using the addFunction() method of the GlideRecord class.
    • Add the function to a query using the addQuery() method of the GlideRecord class.
    • Retrieve the results of the function using the existing GlideRecord API methods such as getValue() and getElement().

    For example:

    var functionBuilder = new GlideDBFunctionBuilder();
var dbFunction = functionBuilder.position();
dbFunction = functionBuilder.constant('my'); // search_term: Text to search for in the specified table column.
dbFunction = functionBuilder.field('short_description');  // column: Name of the table column to search.
dbFunction = functionBuilder.build();

gs.log(dbFunction);

var gr_incident = new GlideRecord('incident'); // Table containing the column to search
gr_incident.addFunction(dbFunction);
gr_incident.addQuery("short_description", "CONTAINS", "my");
gr_incident.setLimit(20);
gr_incident.query();
while(gr_incident.next()) {
  gs.info(gr_incident.short_description + "\n position('my', short_description): " + gr_incident.getValue(dbFunction));
}

    For additional information on Function fields, see Function field.

    Scoped GlideDBFunctionBuilder - GlideDBFunctionBuilder()

    Instantiates a GlideDBFunctionBuilder object.

    Table 1. Parameters
    Name Type Description
    None 
    var builder = new GlideDBFunctionBuilder();

    Scoped GlideDBFunctionBuilder - andFunc(expr1, expr2, …)

    Begins a new AND expression, which returns true only if all expressions provided to it as parameters are true. At least one boolean-valued expression must be provided as a parameter.

    Table 2. Parameters
    Name Type Description
    expression Boolean-valued GlideFunction Expression At present, glide functions that meet this criteria include and(), or(), and compare().
    Table 3. Returns
    Type Description
    String The resultant GlideFunction expression.

    The following example shows how to define and build a simple case statement.

    var expr = new GlideDBFunctionBuilder() 
    .andFunc() 
        .compare().field('first_name').constant('=').constant('John').endfunc() 
        .compare().field('active').constant('=').constant(1).endfunc() 
    .endfunc() 
    .build(); 
gs.info("Expression: " + expr);

    Output:

    Expression: glidefunction:and(compare(first_name,'=','John'),compare(active,'=','1'))

    Scoped GlideDBFunctionBuilder - add()

    Adds the values of two or more integer fields.

    Use the field(String field) method to define fields on which the operation is performed.

    Table 4. Parameters
    Name Type Description
    None
    Table 5. Returns
    Type Description
    void 
    var functionBuilder = new GlideDBFunctionBuilder();
var myAddingFunction = functionBuilder.add();
myAddingFunction = functionBuilder.field('order');
myAddingFunction = functionBuilder.field('priority');
myAddingFunction = functionBuilder.build();

    Scoped GlideDBFunctionBuilder - build()

    Builds the database function defined by the GlideDBFunctionBuilder object.

    Table 6. Parameters
    Name Type Description
    None
    Table 7. Returns
    Type Description
    void 
    var functionBuilder = new GlideDBFunctionBuilder();
var myAddingFunction = functionBuilder.add();
myAddingFunction = functionBuilder.field('order');
myAddingFunction = functionBuilder.field('priority');
myAddingFunction = functionBuilder.build();
gs.info(myAddingFunction);
    Output: 
    *** Script: glidefunction:add(order,priority)

    Scoped GlideDBFunctionBuilder - coalesce()

    Takes any number of comma-separated fields as input and returns the first non-empty value.

    Table 8. Parameters
    Name Type Description
    argument String A list containing a constant value (strings, objects, booleans) or a field. Accepted format: glidefunction:coalesce(<string, string, string, etc.>). An argument can have any number of parameters but must have at least one parameter defined.
    Table 9. Returns
    Type Description
    String The first value in the argument list that is non-null. If no values are non-null, null is returned. If no arguments are provided in the creation of the function object, "invalid function" is returned as a string.

    The following example request demonstrates how to form the coalesce guide function to return null values according to closed_at, resolved_at, and sys_updated_on fields.

    var gr = new GlideRecord('incident');
var func = "glidefunction:coalesce(closed_at, resolved_at,sys_updated_on)";
gr.addFunction(func);
gr.query(); 
while(gr.next())
gs.info(gr.getValue(func));

    The script returns the first non-null value from a field out of this list: [closed_at, resolved_at, sys_updated_on] for each incident record in the incident table and outputs them with gs.info.

    2016-12-14 02:46:44
2018-01-09 22:55:16
2018-01-07 22:54:55
2018-01-13 23:02:54
2018-01-09 23:12:02

    Scoped GlideDBFunctionBuilder - compare(exp1, op, exp2)

    Begins a new COMPARE expression which performs a comparison between two expressions.

    Table 10. Parameters
    Name Type Description
    exp1 String Left comparison expression. Can be a constant, reference to a field, or another Glide function.
    op String A comparison operator, surrounded with quotes. Accepted values:
    • “’=’”
    • “’<’”
    • “’>’”
    • “’<=’”
    • “’>=’”
    exp2 String Right comparison expression. Can be a constant, reference to a field, or another Glide function.
    Table 11. Returns
    Type Description
    String The resultant GlideFunction expression.

    The following example shows how to build a glidefunction expression using the compare() operation. After calling compare(), three additional calls must be made to specify each of the three required parameters, which is then completed by a single call to endfun(). Below, we create two separate compare() expressions and provide them as parameters to the or() expression.

    var expr = new GlideDBFunctionBuilder() 
    .orFunc() 
        .compare().field('first_name').constant('=').constant('John').endfunc() 
        .compare().field('active').constant('=').constant(1).endfunc() 
    .endfunc() 
    .build();

    Output:

    Expression: glidefunction:or(compare(first_name,'=','John'),compare(active,'=','1'))

    Scoped GlideDBFunctionBuilder - concat()

    Concatenates the values of two or more fields.

    Use the field(String field) method to define fields on which the operation is performed.

    Table 12. Parameters
    Name Type Description
    None
    Table 13. Returns
    Type Description
    void 
    var functionBuilder = new GlideDBFunctionBuilder();
var myConcatFunction = functionBuilder.concat();
myConcatFunction = functionBuilder.field('short_description');
myConcatFunction = functionBuilder.field('caller_id.name');
myConcatFunction = functionBuilder.build();

    Scoped GlideDBFunctionBuilder - constant(String constant)

    Defines a constant value to use in the function. If used with the dayofweek() method, the string defines whether to use Sunday or Monday as the first day of the week.

    Table 14. Parameters
    Name Type Description
    constant String A constant value used in a function.

    When used with the dayofweek() method, the value defines whether the week starts on a Sunday or Monday.

    • 1: Week begins on Sunday.
    • 2: Week begins on Monday.

    This definition enables the dayofweek() method to return the correct day of the week from a given date. If a value other than 1 or 2 is provided, the dayofweek() method uses Sunday as the first day of the week.
    Table 15. Returns
    Type Description
    void

    The following code example shows how to call this method.

    var functionBuilder = new GlideDBFunctionBuilder();
var dbFunction = functionBuilder.position();
dbFunction = functionBuilder.constant('my');
dbFunction = functionBuilder.field('short_description');
dbFunction = functionBuilder.build();

gs.log(dbFunction);

var g = new GlideRecord('incident');
g.addFunction(dbFunction);
g.addQuery("short_description", "CONTAINS", "my");
g.setLimit(20);
g.query();
while(g.next()) {
  gs.info(g.short_description + "\n position('my', short_description): " + g.getValue(dbFunction));
}

    Scoped GlideDBFunctionBuilder - datediff()

    Determines the duration using a given start date/time and end date/time.

    Use the field(String field) method to define start and end date/time fields.

    Table 16. Parameters
    Name Type Description
    None
    Table 17. Returns
    Type Description
    void 
    var functionBuilder = new GlideDBFunctionBuilder();
var myDateDiffFunction = functionBuilder.datediff();
myDateDiffFunction = functionBuilder.field('sys_updated_on');
myDateDiffFunction = functionBuilder.field('opened_at');
myDateDiffFunction = functionBuilder.build();

    Scoped GlideDBFunctionBuilder - dayofweek()

    Returns an integer representing the day of the week for a given date.

    Use the field(String field) method to define the given date/time. Use the constant(String constant) method to define whether the week starts on a Sunday or Monday.

    This method can be used with MySQL, Oracle, and Microsoft SQL Server databases only. If using an Oracle database, the NLS_TERRITORY setting must be set to a territory with Sunday as the first day of the week.

    Table 18. Parameters
    Name Type Description
    None
    Table 19. Returns
    Type Description
    Integer

    If the first day of the week is set to Sunday in the constant(String constant) method, return values are associated with the following days of the week:

    • 1: Sunday
    • 2: Monday
    • 3: Tuesday
    • 4: Wednesday
    • 5: Thursday
    • 6: Friday
    • 7: Saturday

    If the first day of the week is set to Monday:

    • 1: Monday
    • 2: Tuesday
    • 3: Wednesday
    • 4: Thursday
    • 5: Friday
    • 6: Saturday
    • 7: Sunday

    If a value other than 1 or 2 is provided in the constant(String constant) method, the dayofweek() method uses Sunday as the first day of the week.

    		 
    var functionBuilder = new GlideDBFunctionBuilder();
var dayOfWeekFunction = functionBuilder.dayofweek();
dayOfWeekFunction = functionBuilder.field('opened_at');
dayOfWeekFunction = functionBuilder.constant('2');
dayOfWeekFunction = functionBuilder.build();

var now_GR = new GlideRecord('incident');
now_GR.addFunction(dayOfWeekFunction);
now_GR.query();
while(now_GR.next())
gs.log(now_GR.getValue(dayOfWeekFunction));

    Scoped GlideDBFunctionBuilder - divide()

    Divides the value of one integer field by another.

    Use the field(String field) method to define fields on which the operation is performed.

    Table 20. Parameters
    Name Type Description
    None
    Table 21. Returns
    Type Description
    void 
    var functionBuilder = new GlideDBFunctionBuilder();
var myDivideFunction = functionBuilder.divide();
myDivideFunction = functionBuilder.field('order');
myDivideFunction = functionBuilder.field('priority');
myDivideFunction = functionBuilder.build();

    Scoped GlideDBFunctionBuilder - field(String field)

    Defines a field on which a SQL operation is performed.

    Table 22. Parameters
    Name Type Description
    field String The field on which you are performing the SQL operation.
    Table 23. Returns
    Type Description
    void 
    var functionBuilder = new GlideDBFunctionBuilder();
var myAddingFunction = functionBuilder.add();
myAddingFunction = functionBuilder.field('order');
myAddingFunction = functionBuilder.field('priority');
myAddingFunction = functionBuilder.build();

    Scoped GlideDBFunctionBuilder - length()

    Determines the number of code units in a field.

    Use the field(String field) method to define fields on which the operation is performed.

    Table 24. Parameters
    Name Type Description
    None
    Table 25. Returns
    Type Description
    void 
    var functionBuilder = new GlideDBFunctionBuilder();
var myLengthFunction = functionBuilder.length();
myLengthFunction = functionBuilder.field('short_description');
myLengthFunction = functionBuilder.build();

    Scoped GlideDBFunctionBuilder - multiply()

    Multiplies the values of two integer fields.

    Use the field(String field) method to define fields on which the operation is performed.

    Table 26. Parameters
    Name Type Description
    None
    Table 27. Returns
    Type Description
    void 
    var functionBuilder = new GlideDBFunctionBuilder();
var myMultiplyFunction = functionBuilder.multiply();
myMultiplyFunction = functionBuilder.field('order');
myMultiplyFunction = functionBuilder.field('priority');
myMultiplyFunction = functionBuilder.build();

    Scoped GlideDBFunctionBuilder - orFunc(expression)

    Begins a new OR expression which returns true if at least one of the expressions provided to it as a parameter is true.

    At least one boolean-valued expression must be provided as a parameter.

    Table 28. Parameters
    Name Type Description
    expression Boolean-valued GlideFunction Expression At Present, glide functions that meet this criteria include and(), or(), and compare().
    Table 29. Returns
    Type Description
    String The resultant GlideFunction expression.

    The following example shows how to build a glidefunction expression using the or() operation. The expression returns true if either the first_name field is 'John" or the active field is true. The resulting expression string may then be used wherever glidefunction expressions are accepted.

    var expr = new GlideDBFunctionBuilder() 
    .orFunc() 
        .compare().field('first_name').constant('=').constant('John').endfunc() 
        .compare().field('active').constant('=').constant(1).endfunc() 
    .endfunc() 
    .build(); 
gs.info("Expression: " + expr);

    Output:

    Expression: glidefunction:or(compare(first_name,'=','John'),compare(active,'=','1'))

    Scoped GlideDBFunctionBuilder - position()

    Returns the first occurrence of a specified string within a column of a table.

    Optionally you can specify a location within the string to start the search. This method equates to the LOCATE(substring, str, position) in MySQL.

    You can use this method to find interesting data points or custom reports by data analysts. Typically you will use this method for data warehousing or to extract, transform and load (ETL) data into less flexible systems.

    To use this method in Dictionary records, use glidefunction:position(<serarch_term>,<column>).

    Note:
    The parameters specified in the following table are not passed with the method call, but rather are set in the GlideDBFunctionBuilder() object.
    Table 30. Parameters
    Name Type Description
    search_term String Text to search for in the specified table column.
    column String Name of the table column to search. The table is specified in the associated GlideRecord object.
    start_position Number Optional. Location in the column text to start the search.

    Default: 1
    Table 31. Returns
    Type Description
    None Position of the first occurrence of the specified search term.

    Returns 0 if the search term is not found in the associated text. Returns NULL if any required argument is NULL.

    The following code example shows how to call this method.

    var functionBuilder = new GlideDBFunctionBuilder();
var dbFunction = functionBuilder.position();
dbFunction = functionBuilder.constant('my'); // search_term: Text to search for in the specified table column.
dbFunction = functionBuilder.field('short_description');  // column: Name of the table column to search.
dbFunction = functionBuilder.build();

gs.log(dbFunction);

var gr_incident = new GlideRecord('incident'); // Table containing the column to search
gr_incident.addFunction(dbFunction);
gr_incident.addQuery("short_description", "CONTAINS", "my");
gr_incident.setLimit(20);
gr_incident.query();
while(gr_incident.next()) {
  gs.info(gr_incident.short_description + "\n position('my', short_description): " + gr_incident.getValue(dbFunction));
}

    Output:

    *** Script: glidefunction:position('my',short_description)
*** Script: Wireless access is down in my area
 position('my', short_description): 28
*** Script: Printer in my office is out of toner
 position('my', short_description): 12
*** Script: Reset my password
 position('my', short_description): 7
*** Script: I can't launch my VPN client since the last software update
 position('my', short_description): 16
*** Script: Missing my home directory
 position('my', short_description): 9
*** Script: Seem to have an issue with my hard drive...
 position('my', short_description): 28
*** Script: Please remove the latest hotfix from my PC
 position('my', short_description): 38
*** Script: I can't get my weather report
 position('my', short_description): 13
*** Script: Reset my password
 position('my', short_description): 7
*** Script: my PDF docs are all locked from editing
 position('my', short_description): 1
*** Script: My desk phone does not work
 position('my', short_description): 1
*** Script: Can't log into SAP from my laptop today
 position('my', short_description): 25
*** Script: My computer is not detecting the headphone device
 position('my', short_description): 1
*** Script: My disk is still having issues. Can't delete a file
 position('my', short_description): 1
*** Script: The USB port on my PC stopped working
 position('my', short_description): 17

    Scoped GlideDBFunctionBuilder - substring()

    Returns a substring from a specified column of a table starting at a specified location.

    Optionally you can also specify a location within the string where to stop the text extraction. This method equates to the LOCATE(substring, str, position) in MySQL.

    This method works similar to that of JavaScript, just at the database level. You can use this method to find interesting data points or custom reports for data analysts. Typically you will use this method for data warehousing or to extract, transform and load (ETL) data into less flexible systems.

    To use this method in Dictionary records, use glidefunction:substring(<field>,<start_position>,<end_position>)

    Note:
    The parameters specified in the following table are not passed with the method call, but rather are set in the GlideDBFunctionBuilder() object.
    Table 32. Parameters
    Name Type Description
    field String Name of the column in the table from which to obtain the text. The table is specified in the associated GlideRecord object.
    start_position Number Location in the column text to start extracting text.
    end_position Number Optional. Location in the column text to stop extracting text.

    Default: End of text in the specified column.
    Table 33. Returns
    Type Description
    String Text extracted from the specified table column.

    The following code example shows how to call this method.

    var functionBuilder = new GlideDBFunctionBuilder();
var dbFunction = functionBuilder.substring();
dbFunction = functionBuilder.field('short_description');  // field: Column within the table to obtain the substring
dbFunction = functionBuilder.constant(0); // start_position: Location in the column text to start extracting text
dbFunction = functionBuilder.constant(20); // end_position: Location in the column text to stop extracting text.
dbFunction = functionBuilder.build();

gs.log(dbFunction);

var gr_incident = new GlideRecord('incident'); // Table containing the column from which to extract the text
gr_incident.addFunction(dbFunction);
gr_incident.addQuery("short_description", "CONTAINS", "my");
gr_incident.setLimit(20);
gr_incident.query();
while(gr_incident.next()) {
  gs.info(gr_incident.short_description + "\n substring(short_description, 0, 20): " + gr_incident.getValue(dbFunction));
}

    Output:

    *** Script: glidefunction:substring(short_description,'0','20')
*** Script: Wireless access is down in my area
 substring(short_description, 0, 20): Wireless access is 
*** Script: Printer in my office is out of toner
 substring(short_description, 0, 20): Printer in my offic
*** Script: Reset my password
 substring(short_description, 0, 20): Reset my password
*** Script: I can't launch my VPN client since the last software update
 substring(short_description, 0, 20): I can't launch my V
*** Script: Missing my home directory
 substring(short_description, 0, 20): Missing my home dir
*** Script: Seem to have an issue with my hard drive...
 substring(short_description, 0, 20): Seem to have an iss
*** Script: Please remove the latest hotfix from my PC
 substring(short_description, 0, 20): Please remove the l
*** Script: I can't get my weather report
 substring(short_description, 0, 20): I can't get my weat
*** Script: Reset my password
 substring(short_description, 0, 20): Reset my password
*** Script: my PDF docs are all locked from editing
 substring(short_description, 0, 20): my PDF docs are all
*** Script: My desk phone does not work
 substring(short_description, 0, 20): My desk phone does 
*** Script: Can't log into SAP from my laptop today
 substring(short_description, 0, 20): Can't log into SAP 
*** Script: My computer is not detecting the headphone device
 substring(short_description, 0, 20): My computer is not 
*** Script: My disk is still having issues. Can't delete a file
 substring(short_description, 0, 20): My disk is still ha
*** Script: The USB port on my PC stopped working
 substring(short_description, 0, 20): The USB port on my

    Scoped GlideDBFunctionBuilder - subtract()

    Subtracts the value of one integer field from another.

    Use the field(String field) method to define fields on which the operation is performed.

    Table 34. Parameters
    Name Type Description
    None
    Table 35. Returns
    Type Description
    void 
    var functionBuilder = new GlideDBFunctionBuilder();
var mySubtractFunction = functionBuilder.subtract();
mySubtractFunction = functionBuilder.field('order');
mySubtractFunction = functionBuilder.field('priority');
mySubtractFunction = functionBuilder.build();