GlideQuery Cheat Sheet

Alberto Colombo

Tech Enthusiast.

GlideQuery Cheat Sheet

Apr 20, 2021

ServiceNow
GlideQuery

GlideQuery from Quebec is now installed by default on every ServiceNow instance.
This is a short cheat sheet to make the best use of it. All examples use the tables of the ITBM module.

These are the main sources of information used:

Jace’s post

Overview of GlideQuery by Samuel Meylan and his cheat sheet

Official ServiceNow Docs

ServiceNow Developer blog post made by Peter Bell, creator of GlideQuery

Read records

var gqProjects = new GlideQuery('pm_project')
.where('project_manager.user_name', 'paul.martin')
.select('short_description', 'number', 'goals$DISPLAY','cost$CURRENCY_DISPLAY')
.forEach(function (project) {
    gs.info('Project ' + project.short_description + '; number ' + project.number + '; Planned cost ' + project.cost$CURRENCY_DISPLAY + '; goals: ' + project.goals$DISPLAY);
});

Read only one record

var gqSingleProject = new GlideQuery('pm_project')
.where('number', 'PRJ0010383')
.selectOne('short_description', 'number', 'goals$DISPLAY', 'cost$CURRENCY_DISPLAY')
.ifPresent(function (project) {
    gs.info('Project ' + project.short_description + '; number ' + project.number + '; Planned cost ' + project.cost$CURRENCY_DISPLAY + '; goals: ' + project.goals$DISPLAY);
});

Read records with complex where and or conditions

var gqActualsByPortfolios = new GlideQuery('pm_project')
.where('active', true)
.where('phase', '!=', 'closing')
.whereNotNull('primary_portfolio')
.where(new GlideQuery()
    .where('duration', '>', '100 00:00:00')
    .orWhere('department.name', 'Engineering')
)
.groupBy('primary_portfolio.name')
.aggregate('sum', 'work_cost')
.having('sum', 'work_cost', '>', 100)
.select()
.forEach(function (actualsByPortfolio) {
    gs.info(JSON.stringify(actualsByPortfolio));
});

Check if record exist

var gqProjectExists = new GlideQuery('pm_project')
.where('number', 'PRJ0010383')
.selectOne()
.isPresent();
gs.info('Project exist? ' + gqProjectExists);

Create record and return sys_id

var projectObject = {
    short_description: 'New Project',
    description: 'Please help!',
};

var gqCreateProject = new GlideQuery('pm_project')
.insert(projectObject)
.get();
gs.info(JSON.stringify(gqCreateProject));

Update multiple

var gqUpdateProjects = new GlideQuery('pm_project')
.where('department.name', 'Facilities')
.updateMultiple({
    priority: 2
});
gs.info(JSON.stringify(gqUpdateProjects));

Delete

var gqDeleteProjects = new GlideQuery('pm_project')
.where('department.name', 'Facilities')
.deleteMultiple();

Example of filter and result manipulation

var gqProjects = new GlideQuery('pm_project')
    .whereNotNull('goals')
    .select('short_description', 'number', 'goals', 'goals$DISPLAY', 'percent_complete')
    .filter(function (project) {
        return project.goals.indexOf(",") != -1;
    })
    .map(function (project) {
        var roundedPercent = Math.round(project.percent_complete)
        var nameUpperCase = project.short_description.toUpperCase();
        return nameUpperCase + '. Percent complete ' + roundedPercent + '%.' + project.goals$DISPLAY;
    })
    .forEach(function (project) {
        gs.info(project);
    });

Aggregation

var projectsEconomics = new GlideQuery('pm_project')
    .aggregate('count')
    .aggregate('max', 'cost')
    .aggregate('sum', 'cost')
    .aggregate('max', 'budget_cost')
    .aggregate('sum', 'budget_cost')
    .groupBy('primary_portfolio.name')
    .groupBy('primary_program.short_description')
    .select()
    .forEach(function (projectEconomics) {
        gs.debug(JSON.stringify(projectEconomics));
    });