addEncodedQuery is not returning desired list

[Kamva]

Hi All,

I am trying to write a JS script that will query a job title, value, sequence from the job titles dictionary in the sys_user table and insert them to a u_job_titles user-defined table in ServiceNow. The script should pull only job titles updated or created today where the job title is not empty. However; my script returns everything even though I have included the addEncodedQuery filter on it.

Please help me debug.

(function() {
  // Define a function to fetch and insert job titles
  function fetchAndInsertJobTitles() {
    var userTable = new GlideRecord('sys_user');
    var today = new GlideDateTime(); // Get the current date and time

    // Calculate the start and end of today
    var todayStart = new GlideDateTime();
    todayStart.setStartOfDay();
    var todayEnd = new GlideDateTime();
    todayEnd.setEndOfDay();

    // Set the query to find records updated or created today with non-empty job titles
    userTable.addEncodedQuery('sys_updated_onBETWEEN' + today.getDisplayValue() + '@javascript:gs.endOfToday()^ORsys_created_onBETWEEN' + today.getDisplayValue() + '@javascript:gs.endOfToday()^titleISNOTEMPTY');
    userTable.query();

    userTable.query();

    while (userTable.next()) {
      var jobTitle = userTable.title.getDisplayValue();
      var jobValue = userTable.getValue('job_value');
      var jobSequence = userTable.getValue('job_sequence');

      gs.info('--------------------------\n\n' + jobTitle + '\n');
      //insertJobTitle(jobTitle, jobValue, jobSequence);
    }
  }

  // Define a function to insert job titles into the u_job_titles table
  function insertJobTitle(title, value, sequence) {
    var jobTitleTable = new GlideRecord('u_job_titles');
    jobTitleTable.initialize(); // Initialize a new record

    jobTitleTable.u_title = title;
    jobTitleTable.u_value = value;
    jobTitleTable.u_sequence = sequence;
    
    var recordID = jobTitleTable.insert();

    gs.info('Inserted job title with ID: ' + recordID);
  }

  // Call the function to fetch and insert job titles
  fetchAndInsertJobTitles();
})();

[Kamva]

Hi @Vishal Birajdar,

This script returns an empty set...

[Vishal Birajdar]

Hi @Kamva 

Can you update/create some user record & then check..?? also as per filter update /create record which have title.

[Kamva · Accepted solution]

Hi Community,

I have finally found the solution. The problem is that I have been querying the wrong table, to query a Dictionary we should be using the sys_choice table

/**
 * This job queries the job titles created in that day and insert the in the u_job_titles table
 * The u_job_titles table is required to ensure the knowledge articles are searchable by a job title tag
 * This job is required to ensure that the sys_user.title dictionary and the u_job_titles table has the same information
 */

(function() {
  // Define a function to fetch and insert job titles
  function fetchAndInsertJobTitles() {
    var userTable = new GlideRecord('sys_choice');
    
    // Calculate the start and end of today
    var todayStart = new GlideDateTime();
    todayStart.setStartOfDay();
    var todayEnd = new GlideDateTime();
    todayEnd.setEndOfDay();

    // Set the query to find records updated or created today with non-empty job titles
    var encodedQuery = "name=sys_user^element=title^sys_created_onONToday@javascript:gs.beginningOfToday()@javascript:gs.endOfToday()";
    
    if (userTable.isValidEncodedQuery(encodedQuery)) {
      userTable.addEncodedQuery(encodedQuery);
      userTable.query();

      while (userTable.next()) {
        var jobTitle = userTable.label.getDisplayValue();
        var jobValue = userTable.value('job_value');
        var jobSequence = userTable.getValue('job_sequence');

        //gs.info('--------------------------\n\n' + jobTitle + '\n');
       insertJobTitle(jobTitle, jobValue, jobSequence);
      }
    } else {
      gs.error('Invalid encoded query: ' + encodedQuery);
    }
  }

  // Define a function to insert job titles into the u_job_titles table
  function insertJobTitle(title, value, sequence) {
    var jobTitleTable = new GlideRecord('u_job_titles');
    jobTitleTable.initialize(); // Initialize a new record

    jobTitleTable.u_title = title;
    jobTitleTable.u_value = value;
    jobTitleTable.u_sequence = sequence;
    
    var recordID = jobTitleTable.insert();

    gs.info('Inserted job title with ID: ' + recordID);
  }

  // Call the function to fetch and insert job titles
  fetchAndInsertJobTitles();
})();