addEncodedQuery is not returning desired list

Kamva · ‎10-04-2023

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&colon;gs.endOfToday()^ORsys_created_onBETWEEN' + today.getDisplayValue() + '@javascript&colon;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 · ‎10-11-2023

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&colon;gs.beginningOfToday()@javascript&colon;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();
})();

View solution in original post

Tai Vu · ‎10-04-2023

Hi @Kamva

Let's give this approach a shot..

userTable.addNotNullQuery('title');
userTable.addEncodedQuery("sys_updated_onONToday@javascript&colon;gs.beginningOfToday()@javascript&colon;gs.endOfToday()^ORsys_created_onONToday@javascript&colon;gs.beginningOfToday()@javascript&colon;gs.endOfToday()");

Make sure to enclose the encoded query related to date and time within double quotation marks. This should help you achieve the desired results.

Let me know if it works for you

Cheers,

Tai Vu

Kamva · ‎10-04-2023

Hi @Tai Vu,

This query returned nothing

Tai Vu · ‎10-04-2023

Hi @Kamva

We should replace this syntax "&colon;" by ":" in the encoded query.

Kamva · ‎10-04-2023

@Tai Vu I did replace those