Search code examples
servicenow

How to compare 2 columns in GlideRecord.addQuery() method?

Assuming I have a table data as:

id    col_1    col_2
----------------------  
11    10000    20000
12    13000    10000

Is it possible to retrieve ids for which col_1 < col_2?. So the expected output here is id:11.

Solution

  • You an compare fields with special field comparison operators:

    var gr = new GlideRecord("u_table_name");
gr.addQuery("col_1", "SAMEAS", "col_2"); // i.e. WHERE col_1 = col_2
gr.query();

    The operators that allow comparing two different fields on the same table:

    • SAMEAS: field = other field
    • NSAMEAS: field != other field
    • GT_FIELD: field > other field
    • LT_FIELD: field < other field
    • GT_OR_EQUALS_FIELD: field >= other field
    • LT_OR_EQUALS_FIELD: field <= other field

    When in doubt, you can always write a GlideRecord query for anything that's supported by the filter UI. Just build the filter you want in the UI, run it, then right-click the breadcrumbs (just above the filter builder UI), and select "Copy URL". This will give you the Encoded query URL which you can either just paste directly into the GlideRecord api gr.addEncodedQuery(...) or deconstruct using the 3-argument gr.addQuery(columnName, operatorName, value), where in the case of the dynamic field comparison operators, the value arg is the other column name.