Glidequery Part 5 - Dotwalking and Flags

#glidequery , #scripting , #dotwalking

Peter Bell

4 minute read

Dotwalking and Flags

Similar to GlideRecord, GlideQuery supports dotwalking, both when using select and where. It dramatically simplifies filtering and reading in fields referenced by the current table instead of executing another GlideQuery. Dotwalking is presumed in the Now platform and should be reasonably familiar to most GlideRecord developers.

Dotwalking

Just like GlideRecord, GlideQuery supports filtering using dotwalking:

var tokyoEmployee = new GlideQuery('sys_user')
    .where('company.city', 'Tokyo')
    .selectOne('name')
    .get();

When dotwalking, GlideQuery performs the same set of validation checks as regular field names. Even though the city field is part of core_company, and we’re querying sys_user, it would verify that the city field exists in core_company and has the right type for this query.

This is great when you want to fetch fields associated with the table via a reference field. For example:

new GlideQuery('sys_user')
    .whereNotNull('company')
    .select('name', 'company.city')
    .forEach(function (user) {
        gs.info(user.name + ' works in ' + user.company.city)
    });

// Output:
// Lucius Bagnoli works in Tokyo
// Melinda Carleton works in London

// Each user record in forEach has the shape of:
// {
//     "name": "Lucius Bagnoli",
//     "company": {
//         "city": "Tokyo"
//     },
//     "sys_id": "02826bf03710200044e0bfc8bcbe5d3f"
// }

GlideQuery constructs a nested object within the user object when dotwalking. This allows you to fetch the inner value in a more native JavaScript style. Here we added a whereNotNull call on company, so we can assume company will never be null; however, if you fail to do this, you’ll need to ensure you check that company isn’t null before printing the company’s city.

Field Flags

Some fields support special metadata values for each field. The most common example might be display values. For example in GlideRecord, many reference fields have a display value. A simple scenario is:

var userGr = new GlideRecord('sys_user');
userGr.query();
while (userGr.next()) {
    gs.info(userGr.company.getDisplayValue())
}

// ACME North America
// ServiceNow
// ...

Without the call to getDisplayValue, we would print the 32 character key of each company instead of its display value (which is the company’s name). Display values are a handy tool, and we want to ensure we can fetch them using GlideQuery.

GlideQuery has the notion of flags when querying fields. By appending a " $" to a field name, you can specify what type of metadata you want to read in, instead of the raw value (the default behavior). So, in the case of display values, we could use the DISPLAY flag:

new GlideQuery('sys_user')
    .select('company$DISPLAY')
    .forEach(function (user) {
        gs.info(user.company$DISPLAY);
    });

// ACME North America
// ServiceNow
// ...

Today there are four supported field flags:

  1. DISPLAY: Gets the display value of a field. Equivalent to calling getDisplayValue.
  2. CURENCY_CODE: Gets the currency code of a currency field (e.g. “USD”). Equivalent to calling getCurrencyCode.
  3. CURRENCY_DISPLAY: Gets the currency display value. Equivalent to calling getCurrencyDisplayValue.
  4. CURRENCY_STRING: Gets the currency string of a currency field (e.g. “JPY;123.45”). Equivalent to calling getCurrencyString.

Using the last three currency flags on a non-currency field will cause GlideQuery to throw an Error.

Why append a "$DISPLAY" to a field instead of having, say, a separate selectDisplayValue method? The reason is that you can select both the raw value and the display value in the same select call:

new GlideQuery('core_company')
    .where('market_cap', '>', 0)
    .select('market_cap', 'market_cap$CURRENCY_CODE')
    .forEach(doSomething);

// Each record passed to doSomething looks something like:
// {
//     "market_cap": 48930000000,
//     "market_cap$CURRENCY_CODE": "USD",
//     "sys_id": "0c43af40c6112275011a4bd4c0143fbf"
// }

We use the " $" symbol, as it’s one of the only non-alphanumeric characters allowed in JavaScript identifiers (the underscore being the other one). This allows the field (with or without the flag) being requested to match the field returned by the query.

Conclusion

GlideQuery supports dotwalking, allowing you to filter by fields in referenced tables adjacent to the main table you’re querying. You can also select adjacent fields using select, and GlideQuery will return the values in a native JavaScript object format. Finally, you can use field flags in GlideQuery to read some metadata fields, such as display values.

Comments