//This script searches all tables for the record identified by a given sys_id.
//It returns the table name where the sys_id was found, and links to the List View and Form View where the record //may be found.
//Specify the sys_id you are searching for.

var sysIdToFind = '10e4603e974381546d2a355e6253afe6';

//Invoke the search function.
FindRecGivenSysId(sysIdToFind);

function FindRecGivenSysId(sys_id) {

                //Exclude any tables which are causing a problem for our search.
                var tablesToExcludeFromSearch = [

                                "itom_licensing_exclusion_metadata",
                                "sn_employee_app_access",
                                "sn_employee_profile",
                                "sn_m2m_note_template_for_table",
                                "sys_ux_sitemap_definition",
                ];

                var baseTablesToSearch = new GlideRecord('sys_db_object');
                var instanceURI = gs.getProperty('glide.servlet.uri');
                var recordFound = false;
                var current;
                var dict;

                //OutputToAll('Searching base tables in sys_db_object for ' + sys_id);
                //Grab base tables, excluding text-indexing, virtual, and sysx_ tables.
                //Important to know: Records from Child tables will still be found in a search of the base table!  
                //  There is no need to search child tables directly.
                //The function getRecordClassName() can then be used to tell you the specific (child) table the 
                //  record was found in.
                baseTablesToSearch.addNullQuery("super_class");
                baseTablesToSearch.addQuery("name", "NOT LIKE", "ts_c_");
                baseTablesToSearch.addQuery("name", "NOT LIKE", "v_");
                baseTablesToSearch.addQuery("name", "NOT IN", tablesToExcludeFromSearch.toString());
                baseTablesToSearch.query();

                while( baseTablesToSearch.next() ) {
                                //OutputToAll('Searching: ' + baseTablesToSearch.name);

                                current = new GlideRecord( baseTablesToSearch.name );

                                //Find out if there is even a "sys_id" column in the table.  If there is not, just 
                                //skip it.
                                dict = new GlideRecord( 'sys_dictionary' );
                                dict.addQuery('name', baseTablesToSearch.name );
                                dict.addQuery('element', 'sys_id');
                                dict.queryNoDomain();
                                if(!dict.next()) continue;

                                //Now search for the actual sys_id in the current table.
                                current.addQuery('sys_id', sys_id);
                                //Prevent Query Rules from running, if allowed, as these may limit our results.
                                current.setWorkflow(false);
                                current.queryNoDomain();

                                if( current._next() ) {
                                                //We found the actual record by its sys_id value!
                                                recordFound = true;

                                                //OutputToAll('Record found in base table: ' + 
                                baseTablesToSearch.name);

                                                //OutputToAll('Record found in child table: ' + current.getClassDisplayValue() + " " + current.getRecordClassName());

                                                var listViewURL = instanceURI + "/nav_to.do?uri=/[theTable]_list.do?sysparm_query=sys_id=[theSysId]";

                                                listViewURL = listViewURL.replace("[theTable]", current.getRecordClassName());

                                                listViewURL = listViewURL.replace("[theSysId]", sys_id);

                                                var listViewHTML = '<a href="' + listViewURL + '"target=_blank>' + "List View" + '</a>';

                                               

                                                var formViewURL = instanceURI + "/nav_to.do?uri=/[theTable].do?sys_id=[theSysId]";

                                                formViewURL = formViewURL.replace("[theTable]", current.getRecordClassName());

                                                formViewURL = formViewURL.replace("[theSysId]", sys_id);

                                                var directLinkHTML = '<a href="' + formViewURL + '"target=_blank>' + "Form View" + '</a>';

                                               

                                                OutputToAll("TABLE: " + current.getClassDisplayValue() + " ( " + current.getRecordClassName() + " )");

                                                OutputToAll("SYS_ID: " + sys_id);
                                                OutputToForm(listViewHTML);
                                                OutputToForm(directLinkHTML);
                                                OutputToLog("List View: " + listViewURL);
                                                OutputToLog("Form View: " + formViewURL);
                                       
                                                //We found what we came for.  No need to keep searching.
                                                break;

                                }

                }
                if (! recordFound){

                                OutputToAll("sys_id " + sys_id + " could not be found in any of the tables searched.");
                }              

                //OutputToAll('FindRecGivenSysId completed successfully');

}

 

function OutputToAll(outputString){

                OutputToForm(outputString);
                OutputToLog(outputString);
}

Function OutputToForm(outputString){
                gs.addInfoMessage(outputString);
}

function OutputToLog(outputString){
                //Log Prefix makes it much easier to find the statements we care about in the system log (table syslog).

                var logPrefix = "FindRecGivenSysId: ";
                gs.print(logPrefix + outputString);
                gs.log(logPrefix + outputString);
}

// Grab data from a column in a table and apply it to a new column in another table
//  jwj0215 05/05/23
//
var gr=new GlideRecord('Live feed message table name');

gr.query();

//gs.print(gr.getRowCount());

while(gr.next())
{
var cs=new GlideRecord('Target table name');
cs.initialize();
//as like bellow you can take values from gr and initialize them 
//make sure that field type should be compatible
   // cs.u_item=gr.sys_id;
   // cs.u_stockroom='ffec3762dba71f004b6cf3561d9619c6';
   // cs.u_stock_number=100;
   // cs.u_stock_limit=10;
cs.insert();
}

// Defect Label Removal   PROD

(function executeRule(current, previous /*null when async*/) {

	// sys_id of the global label     987038f3470aa110bbfe699e536d43d3
	var labelSysId = '987038f3470aa110bbfe699e536d43d3';
	// Color of the global label
	var colorStr = '#f95050';

	// Insert the Visual Task Board Label
	var grBoardLabel = new GlideRecord('vtb_board_label');
	grBoardLabel.initialize();
	grBoardLabel.setValue('active', false);
	grBoardLabel.setValue('board', current.getUniqueValue());
	grBoardLabel.setValue('color', colorStr);
	grBoardLabel.setValue('label', labelSysId);
	grBoardLabel.setValue('order', 1000);
	grBoardLabel.insert();

})(current, previous);

//  jwj0215 4.18.23  update set 041823 JWJ-Taskboard Labels
// Feature Label Removal

(function executeRule(current, previous /*null when async*/) {

	// sys_id of the global label
	var labelSysId = '8d93fe95975d5194b6a7fe021153af3a';
	// Color of the global label
	var colorStr = '#71e279';

	// Insert the Visual Task Board Label
	var grBoardLabel = new GlideRecord('vtb_board_label');
	grBoardLabel.initialize();
	grBoardLabel.setValue('active', false);
	grBoardLabel.setValue('board', current.getUniqueValue());
	grBoardLabel.setValue('color', colorStr);
	grBoardLabel.setValue('label', labelSysId);
	grBoardLabel.setValue('order', 1100);
	grBoardLabel.insert();

})(current, previous);

https://servicenowguru.com/scripting/gliderecord-query-cheat-sheet/


//Change state from New to Closed
var gr = new GlideRecord('incident');
gr.addQuery('state',1);
gr.query();
while(gr.next()) {
inc.comments='Incident closed manually by jwj0215';
gr.state = 7;
gr.update();
}



var gr = new GlideRecord('incident');
gr.query();
while (gr.next())


//  JWJ0215 Find all incidents with a priority of 1 or 2
var gr = new GlideRecord('incident');
gr.addQuery('priority', 1).addOrCondition('priority', 2);
gr.query();

// JWJ0215 Find all active incident records and make them inactive
var gr = new GlideRecord('incident');
gr.addQuery('active',true);
gr.query();
while (gr.next()) {
gr.active = false;
gr.update();
}


//  JWJ0215 Find all inactive incidents and delete them all at once
var gr = new GlideRecord('incident');
gr.addQuery('active', false);
gr.deleteMultiple(); //Deletes all records in the record set


//  JWJ0215 Find all inactive incident records and delete them one-by-one
var gr = new GlideRecord('incident');
gr.addQuery('active',false);
gr.query();
while (gr.next()) {
//Delete each record in the query result set
gr.deleteRecord();
}

// JWJ0215 Find all active incidents and log a count of records to the system log
var gr = new GlideAggregate('incident');
gr.addQuery('active', true);
gr.addAggregate('COUNT');
gr.query();
var incidents = 0;
if (gr.next()){
incidents = gr.getAggregate('COUNT');
gs.log('Active incident count: ' + incidents);
}

// JWJ0215 Find all active incidents and order the results ascending by category then descending by created date
var gr = new GlideRecord('incident');
gr.addQuery('active', true);
gr.orderBy('category');
gr.orderByDesc('sys_created_on');
gr.query();

// JWJ0215 Find all incidents where the Short Description is empty
var gr = new GlideRecord('incident');
gr.addNullQuery('short_description');
gr.query();

// JWJ0215 Log the number of records returned by the query
var gr = new GlideRecord('incident');
gr.addQuery('category', 'software');
gr.query();
gs.log('Incident count: ' + gr.getRowCount());

// JWJ0215 Show all incidents that have the STATE of NEW. with incident number
var inc = new GlideRecord('incident');
inc.addQuery('state',getState('incident','New'));
inc.query();
while (inc.next()) {
    gs.print('-----------'+inc.number);
}
function getState(tablename,label)
{
var ch = new GlideRecord('sys_choice');
ch.addQuery('name',tablename);
ch.addQuery('label',label);
ch.query();
if (ch.next())
{
return ch.value;
}
}

// JWJ0215 Show the total number of closed incidents
var count=0;
var er = new GlideRecord('incident');
er.addEncodedQuery("state=7");
er.query();
while(er.next())
{
count++
gs.print('Incident number is ' + er.number);
}
gs.print('Total number of incident is '+ count);

// Idea disable 4993fe95975d5194b6a7fe021153af3b


(function executeRule(current, previous /*null when async*/) {

	// sys_id of the global label
	var labelSysId = '4993fe95975d5194b6a7fe021153af3b';
	// Color of the global label
	var colorStr = '#6d79e8';

	// Insert the Visual Task Board Label
	var grBoardLabel = new GlideRecord('vtb_board_label');
	grBoardLabel.initialize();
	grBoardLabel.setValue('active', false);
	grBoardLabel.setValue('board', current.getUniqueValue());
	grBoardLabel.setValue('color', colorStr);
	grBoardLabel.setValue('label', labelSysId);
	grBoardLabel.setValue('order', 1200);
	grBoardLabel.insert();

})(current, previous);

JavaScript for ServiceNOW

javascript: gs.getUser().getRecord().getValue('email');

javascript: var userPhone; var user = new GlideRecord('sys_user'); if (user.get(gs.getUserID(''))) {userPhone = user.phone}; userPhone;

javascript: var userLoc; var user = new GlideRecord('sys_user'); if (user.get(gs.getUserID(''))) {userLoc = user.location}; userLoc;

javascript: var userfirst_name; var user = new GlideRecord('sys_user'); if (user.get(gs.getUserID(''))) {userfirst_name = user.first_name}; userfirst_name;

javascript: var userlast_name; var user = new GlideRecord('sys_user'); if (user.get(gs.getUserID(''))) {userlast_name = user.first_name}; userlast_name;

javascript: gs.getUser().getRecord().getValue('email');

javascript: gs.getUser().getRecord().getValue('UserID');

javascript: gs.getUser().getRecord().getValue('last_name');

javascript: gs.getUser().getRecord().getValue('first_name');

javascript: gs.getUser().getRecord().getValue('location');

javascript: gs.getUser().getRecord().getValue('email');

cmn_location 

function resolveIncident() {
    // Ensure that "Assigned to" is populated
    var assignedTo = g_form.getValue('assigned_to');
    if (!assignedTo) {
        g_form.setMandatory('assigned_to', true);
        g_form.addErrorMessage("You cannot resolve an incident without populating 'Assigned to'");
    } else {
        //Set the 'Incident state' and 'State' values to 'Resolved', and display mandatory fields
        g_form.setValue('incident_state', 6);
        g_form.setValue('state', 6);
        g_form.setValue('resolved_by', g_user.userID);

        gsftSubmit(null, g_form.getFormElement(), 'resolve_incident'); //MUST call the 'Action name' set in this UI Action
    }
}
//Code that runs without 'onclick'
//Ensure call to server-side function with no browser errors
if (typeof window == 'undefined')
    serverResolve();

function serverResolve() {
    // JWJ0215 Prevent Incident resolve if tasks are open
    var gr = new GlideRecord('incident_task');
    gr.addQuery('active', 'true');
    gr.addQuery('incident', current.sys_id);
    gr.query();
    if (gr.next()) {
        gs.addInfoMessage(('Please Close All Incident Tasks Before Resolving the Incident'));
		action.setRedirectURL(current);
    } else {
        current.incident_state = IncidentState.RESOLVED;
        current.state = IncidentState.RESOLVED;
        current.resolved_by = gs.getUserID();
        current.update();
    }
}


Workspace check workspace form button
function onClick() {
	//Set the 'Incident state' and 'State' values to 'Resolved', and display mandatory fields
	g_form.setValue('incident_state', 6);
	g_form.setValue('state', 6);
	g_form.setValue('resolved_by', g_user.userID, g_user.getFullName());

	// Call the UI Action and skip the 'onclick' function
	g_form.submit(g_form.getActionName());
}

email
javascript: gs.getUser().getRecord().getValue('email');

phone
javascript: var userPhone; var user = new GlideRecord('sys_user'); if (user.get(gs.getUserID(''))) {userPhone = user.phone}; userPhone;

location
javascript: var userLoc; var user = new GlideRecord('sys_user'); if (user.get(gs.getUserID(''))) {userLoc = user.location}; userLoc;

setup_same_as

user_id

var myVar = g_form.getValue('setup_same_as');

g_form.setValue('user_id',myVar );

function onChange(control, oldValue, newValue, isLoading) {
if (isLoading || newValue == '') {
return;
}

var request = g_form.getReference('requesters', callback); 

function callback(request) { //reference is passed into callback as first arguments

g_form.setValue('setup_same_as',request.sys_user);

}


javascript: var myVar = g_form.getValue('setup_same_as');
g_form.setValue('sys_id',myVar );

// Notification email to include 'location' and 'location other' 


(function runMailScript(/* GlideRecord */ current, /* TemplatePrinter */ template,
						/* Optional EmailOutbound */ email, /* Optional GlideRecord */ email_action,
						/* Optional GlideRecord */ event) {

	var sLocationToOutput;
	//If the user selected "Other" as the location...
	if (current.u_sd_location == "4a44e5761b26e11099e265b1604bcb43") //"Other"
	{
		//Use what they typed into the "Location Other" field.
		sLocationToOutput = current.u_sd_location_other;
	}
	//Otherwise, use the NAME of the location field that they selected.
	else
	{
		sLocationToOutput = current.u_sd_location.u_name; //Dot walking to name property.
	}

	template.print(sLocationToOutput);

})(current, template, email, email_action, event);

// JWJ0215 10.30.24 Evening
// Project Insight Update Script
// This script will get needed data from the ServiceNow Project Request (before approved for a project), and 
// push the data to Project Insight.

(function executeRule(current, previous /*null when async*/ ) {
    var payload = {};

    // get user from user table
    var gr = new GlideRecord("sys_user");
    gr.addQuery("sys_id", current.requested_for);
    gr.query();

    if (gr.next()) {
        payload.requested_for = gr.email.toString();
    }
    // ***End get user***

    // Get Project Insight Records from the project Insight table **U_PROJECT_INSIGHT_RECORDS**
    // get the sys id
    var pigr = new GlideRecord("u_project_insight_records");
    pigr.addQuery("u_requested_item", current.sys_id);
    pigr.query();

    if (pigr.next()) {
        payload.u_project_insight_records_sysid = pigr.sys_id.toString();
        payload.u_project_insight_records_number = pigr.number.toString();
        payload.u_prioritization_group = pigr.u_prioritization_group.toString();
        payload.priority = pigr.priority.getDisplayValue();
        payload.u_expense = pigr.u_expense.toString();
        payload.u_project_request_status = pigr.u_project_request_status.toString();
        payload.short_description = pigr.short_description.toString();
		payload.description = pigr.description.toString();
        payload.u_estimated_hours = pigr.u_estimated_hours.toString();
    }

		// Grab the Description from the task table ******************************************
	gr = new GlideRecord("task");
    gr.addQuery('description');
    gr.query();

    if (gr.next()) {
        gr.description.toString();
    }



    gr = new GlideRecord("sys_user");
    gr.addQuery("sys_id", current.variables.sponsor_manager_and_up_only_for_this_field);
    gr.query();

    if (gr.next()) {
        payload.sponsor_manager_and_up_only_for_this_field = gr.email.toString();
    }


    gr = new GlideRecord("sys_user");
    gr.addQuery("sys_id", current.variables.contact_name);
    gr.query();

    if (gr.next()) {
        payload.contact_name = gr.name.toString();
    }

    // which_location_is_the_project_for
    gr = new GlideRecord("cmn_department");
    gr.addQuery("sys_id", current.variables.which_location_is_the_project_for);
    gr.query();

    if (gr.next()) {
        payload.cost_center = gr.name.toString();
    }


    // ***End get Project Insight Records***


    // below information is from the **SC_REQ_ITEM** table.
    payload.sys_id = current.sys_id.toString();
	payload.u_project_approved_date = current.u_project_approved_date.toString();
    payload.number = current.number.toString();
	payload.u_project_type = current.u_project_type.toString();
    payload.request_created_on = current.variables.request_created_on.toString(); //sc_req_item   
    payload.contact_s_phone_number = current.variables.contact_s_phone_number.toString(); //sc_req_item
	payload.what_is_the_problem_you_are_trying_to_solve = current.variables.what_is_the_problem_you_are_trying_to_solve.toString();

    //payload.u_project_insight_records_sysid = current.
    //payload.u_project_insight_records_number = 


    var PI = new ProjectInsightScriptInclude();
    var res = PI.send(payload);

})(current, previous);

// JWJ0215 10.23.24
// Project Insight Update Script
// This script will get needed data from the ServiceNow Project Request (before approved for a project), and 
// push the data to Project Insight.

(function executeRule(current, previous /*null when async*/ ) {
    var payload = {};

    // get user from user table
    var gr = new GlideRecord("sys_user");
    gr.addQuery("sys_id", current.requested_for);
    gr.query();

    if (gr.next()) {
        payload.requested_for = gr.email.toString();
    }
    // ***End get user***

    // Get Project Insight Records from the project Insight table **U_PROJECT_INSIGHT_RECORDS**
    // get the sys id
    var pigr = new GlideRecord("u_project_insight_records");
    pigr.addQuery("u_requested_item", current.sys_id);
    pigr.query();

    if (pigr.next()) {
        payload.u_project_insight_records_sysid = pigr.sys_id.toString();
        payload.u_project_insight_records_number = pigr.number.toString();
        payload.u_prioritization_group = pigr.u_prioritization_group.toString();
        payload.priority = pigr.priority.getDisplayValue();
        payload.u_project_type = pigr.u_project_type.toString();
        payload.u_expense = pigr.u_expense.toString();
        payload.u_status = pigr.u_status.toString();
        payload.short_description = pigr.short_description.toString();
	 	payload.u_estimated_hours = pigr.u_estimated_hours.toString();
    }

    gr = new GlideRecord("sys_user");
    gr.addQuery("sys_id", current.variables.sponsor_manager_and_up_only_for_this_field);
    gr.query();

    if (gr.next()) {
        payload.sponsor_manager_and_up_only_for_this_field = gr.email.toString();
    }


	gr = new GlideRecord("sys_user");
    gr.addQuery("sys_id", current.variables.contact_name);
    gr.query();

    if (gr.next()) {
        payload.contact_name = gr.name.toString();
    }
// which_location_is_the_project_for
	gr = new GlideRecord("cmn_department");
    gr.addQuery("sys_id", current.variables.which_location_is_the_project_for);
    gr.query();

    if (gr.next()) {
        payload.cost_center = gr.name.toString();
    }
    // ***End get Project Insight Records***

    // below information is from the **SC_REQ_ITEM** table.
    payload.sys_id = current.sys_id.toString();
    payload.number = current.number.toString(); 
    payload.request_created_on = current.variables.request_created_on.toString(); //sc_req_item        
    payload.contact_s_phone_number = current.variables.contact_s_phone_number.toString(); //sc_req_item

    var PI = new ProjectInsightScriptInclude();
    PI.send(payload);

})(current, previous);

//  Request Disable  0593fe95975d5194b6a7fe021153af3c

(function executeRule(current, previous /*null when async*/) {

	// sys_id of the global label
	var labelSysId = '0593fe95975d5194b6a7fe021153af3c';
	// Color of the global label
	var colorStr = '#fc8a3d';

	// Insert the Visual Task Board Label
	var grBoardLabel = new GlideRecord('vtb_board_label');
	grBoardLabel.initialize();
	grBoardLabel.setValue('active', false);
	grBoardLabel.setValue('board', current.getUniqueValue());
	grBoardLabel.setValue('color', colorStr);
	grBoardLabel.setValue('label', labelSysId);
	grBoardLabel.setValue('order', 1300);
	grBoardLabel.insert();

})(current, previous);

// search metadata to find a specific sys_id (sys_metadata table)
// jwj0215
// 6.27.23

// Change sys id.....
var searchSys_id = "0129c780db1976802a0a73b9bf9619d6";
  
       var y = new GlideRecord('sys_metadata');

       y.addQuery('sys_id',searchSys_id);

       y.addActiveQuery();

       y.query();

       while(y.next())

       {
               if(y.sys_id == searchSys_id ) // NEED this to double check or else log will spit out alot lot of rubbish 
               {
                       gs.log(" ID: " + y.sys_id + " Name: " + y.name + " Class: " + y.sys_class_name);
                       gs.log(y.getRowCount());
               }
       }




// JWJ0215 Show the total number of closed incidents
var count=0;
var er = new GlideRecord('incident');
er.addEncodedQuery("state=7");
er.query();
while(er.next())
{
count++;
gs.print('Incident number is ' + er.number);
}
gs.print('Total number of incident is '+ count);

/*MDP0722 10/23/2024
Take project insight response and update ServiceNow
*/

try {

    var r = new sn_ws.RESTMessageV2('Project Insight Get Updates', 'Default Get');
    //response.setStringParametersNoEscape('hoursBack', '24');

    var response = r.execute();
    var responseBody = response.getBody();
    var httpStatus = response.getStatusCode();
    gs.info("Status = " + httpStatus);
    //UserLog("Response Body = " + responseBody);

    var responseObj = JSON.parse(responseBody);
    gs.info("Response Body = " + responseBody);
    gs.info("Records = " + responseObj.length);
    for (var i = 0, len = responseObj.length; i < len; ++i) {

        var projectName = responseObj[i].Name;
        var description = responseObj[i].description;
        //var requestedFor = responseObj[i].requested_for;
        var sysId = responseObj[i].sys_id;
        var number = responseObj[i].number;
        var prioritizationGroup = responseObj[i].u_prioritization_group;
        var priority = responseObj[i].priority;
        var expense = responseObj[i].u_expense;
        var status = responseObj[i].u_status;
        var shortDescription = responseObj[i].short_description;
        var estimatedHours = responseObj[i].u_estimated_hours;
        var sponsorMgrUp = responseObj[i].sponsor_manager_and_up_only_for_this_field;
        var contactName = responseObj[i]["variables.contact_name"];
        var costCenter = responseObj[i]["variables.cost_center"];
        var createdOn = responseObj[i].request_created_on;
        var contactPhone = responseObj[i]["variables.contact_s_phone_number"];
        var dueDate = responseObj[i].due_date;
        var piId = responseObj[i].u_project_insight_records_sysid;
        var piNum = responseObj[i].u_project_insight_records_number;
        var projectType = responseObj[i].u_project_type;
        var complete = responseObj[i].Complete;



        /*
                gs.info("**************************************************************************")
                gs.info("Record # = " + i);
                gs.info("Project = " + shortDescription);
                gs.info("Description = " + description);
                // gs.info("Requested For = " + requestedFor);
                gs.info("Sys ID = " + sysId);
                gs.info("Due Date = " + dueDate);
                gs.info("PI Sys ID = " + piId);
                gs.info("Sponsor = " + sponsorMgrUp);
                gs.info("Contact Phone = " + contactPhone);
                gs.info("Cost Center = " + costCenter);
                gs.info("Contact Name = " + contactName);
                gs.info("PI Number = " + piNum);
                gs.info("Estimated Hours = " + estimatedHours);
                gs.info("Prioritization Group = " + prioritizationGroup);
                gs.info("Project Type = " + projectType);
                gs.info("Expense = " + expense);
                gs.info("Status= " + status);
                gs.info("RTIM= " + number);
                gs.info("% Complete= " + complete);
             */

        var name = sponsorMgrUp.split('@')[0];
        var lname = name.split('.')[1];
        gs.info("name = " + name);
        gs.info("lastname = " + lname);

        //Query the u_project_insight table by sys_id and update fields for the matching sys_id
        var gr = new GlideRecord("u_project_insight_records");
        gr.addQuery("sys_id", piId);
        gr.setLimit(100);
        gr.query();

        if (gr.next()) {
            gs.info("Project Type Test Query = " + gr.number);

            if (status.length > 0) {
                gr.u_status = status;
            }
            /*  if (shortDescription.length > 0) {
                  gr.short_description = shortDescription;
              }*/

            gr.u_estimated_hours = estimatedHours;
            gr.u_complete = complete;

            gr.update();
        }
        var gr1 = new GlideRecord("sc_req_item");
        gr1.addQuery("number", number);
        gr1.setLimit(100);
        gr1.query();

        while (gr1.next()) {
            //  gs.info("sc req item table =" + gr1.due_date);

            if (dueDate.length > 0) {
                gr1.due_date = dueDate;
                //   gs.info("SN Date = " + gr1.due_date);
                //  gs.info("PI Date = " + dueDate);
            }
            gr1.u_complete = complete;
            gr1.u_status = status;
            gr1.u_hours = estimatedHours;

            // gr1.update();

            var gr2 = new GlideRecord("sys_user");
            // gr2.addQuery("email", sponsorMgrUp);
            gr2.addEncodedQuery("email=" + sponsorMgrUp + "^last_name=" + lname);
            gs.info("email=" + sponsorMgrUp + "^last_name = " + lname);
            gr2.setLimit(100);
            gr2.query();

            if (gr2.next()) {
                gs.info("Sponsor Query =  " + gr2.last_name + " Sys ID = " + gr2.sys_id)
                var sponsorSysId = gr2.sys_id;
                gr1.variables.sponsor_manager_and_up_only_for_this_field = sponsorSysId;
            }
            gr1.update();
        }
        //    

    }

} catch (ex) {
    var message = ex.message;

}

// JWJ0215 11.01.24 		- 		Last tested on 11.01 @ 12.34
// Project Insight Update Script
// This script will get needed data from the ServiceNow Project Request (before approved for a project), and 
// push the data to Project Insight.

(function executeRule(current, previous /*null when async*/ ) {
    var payload = {};

    // get user from user table
    var gr = new GlideRecord("sys_user");
    gr.addQuery("sys_id", current.requested_for);
    gr.query();

    if (gr.next()) {
        payload.requested_for = gr.email.toString();
    }
    // ***End get user***

    // Get Project Insight Records from the project Insight table **U_PROJECT_INSIGHT_RECORDS**
    // get the sys id
    var pigr = new GlideRecord("u_project_insight_records");
    pigr.addQuery("u_requested_item", current.sys_id);
    pigr.query();

    if (pigr.next()) {
        payload.u_project_insight_records_sysid = pigr.sys_id.toString();
        payload.u_project_insight_records_number = pigr.number.toString();
        payload.u_prioritization_group = pigr.u_prioritization_group.toString();
        payload.priority = pigr.priority.getDisplayValue();
        payload.u_expense = pigr.u_expense.toString();
        payload.u_project_request_status = pigr.u_project_request_status.toString();
        payload.short_description = pigr.short_description.toString();
		payload.description = pigr.description.toString();
		payload.u_status = pigr.u_status.toString(); // trying u_status
       
		
    }

		// Grab the Description from the task table ******************************************
	gr = new GlideRecord("task");
    gr.addQuery('description');
    gr.query();

    if (gr.next()) {
        gr.description.toString();
    }



    gr = new GlideRecord("sys_user");
    gr.addQuery("sys_id", current.variables.sponsor_manager_and_up_only_for_this_field);
    gr.query();

    if (gr.next()) {
        payload.sponsor_manager_and_up_only_for_this_field = gr.email.toString();
    }


    gr = new GlideRecord("sys_user");
    gr.addQuery("sys_id", current.variables.contact_name);
    gr.query();

    if (gr.next()) {
        payload.contact_name = gr.name.toString();
    }

    // which_location_is_the_project_for
    gr = new GlideRecord("cmn_department");
    gr.addQuery("sys_id", current.variables.which_location_is_the_project_for);
    gr.query();

    if (gr.next()) {
        payload.cost_center = gr.name.toString();
    }


    // ***End get Project Insight Records***


    // below information is from the **SC_REQ_ITEM** table.
    payload.sys_id = current.sys_id.toString();
	payload.u_project_approved_date = current.u_project_approved_date.toString();
    payload.number = current.number.toString();
	payload.u_project_type = current.u_project_type.toString();
    payload.request_created_on = current.variables.request_created_on.toString(); //sc_req_item   
    payload.contact_s_phone_number = current.variables.contact_s_phone_number.toString(); //sc_req_item
	payload.what_is_the_problem_you_are_trying_to_solve = current.variables.what_is_the_problem_you_are_trying_to_solve.toString();
	payload.u_estimated_hours = current.u_hours.toString();  // trying u_hours


    var PI = new ProjectInsightScriptInclude();
    var res = PI.send(payload);

})(current, previous);

//  story disable    cd93fe95975d5194b6a7fe021153af3c

(function executeRule(current, previous /*null when async*/) {

	// sys_id of the global label
	var labelSysId = 'cd93fe95975d5194b6a7fe021153af3c';
	// Color of the global label
	var colorStr = '#fcc742';

	// Insert the Visual Task Board Label
	var grBoardLabel = new GlideRecord('vtb_board_label');
	grBoardLabel.initialize();
	grBoardLabel.setValue('active', false);
	grBoardLabel.setValue('board', current.getUniqueValue());
	grBoardLabel.setValue('color', colorStr);
	grBoardLabel.setValue('label', labelSysId);
	grBoardLabel.setValue('order', 1400);
	grBoardLabel.insert();

})(current, previous);

// Variable Finder for Catalog Item variables
//
// JWJ0215 09.28.24
// This code will retrieve the Catalog Item Variables and organize them into containers
//
ClearUserLog();  //remove this line if using gs.print in line 42.
var CATALOG_ITEM_SYS_ID = '2c3cb2a7874b0210fdd876a6cebb3503', //TODO: REPLACE THIS WITH THE SYS_ID OF THE CATALOG ITEM-Current is 'New Project Request'
    i,
    currentVar,
    fieldName = 'sys_id',
    varMap = {},
   currentContainer,
    containerLevel = [],
    item = GlideappCatalogItem.get(CATALOG_ITEM_SYS_ID),
    grVariables = item.getVariables();

//For every variable found...
while (grVariables.next()) {

    var varName = grVariables.getValue('name');
    var varSid = grVariables.getValue('sys_id');
    var varType = grVariables.type.getDisplayValue();
    var varQuestion = grVariables.getValue('question_text');
    var varActive = grVariables.getValue('active');

    if (varType === 'Container Start') {
        containerLevel.push(varSid);
    } else if (varType === 'Container End') {
        containerLevel.pop();
    }
    currentContainer = varMap;
    for (i = 0; i < containerLevel.length; i++) {
        if (!currentContainer.hasOwnProperty(containerLevel[i])) {
            currentContainer[containerLevel[i]] = {};
        }
        currentContainer = currentContainer[containerLevel[i]];
    }

    currentContainer[varSid] = new Variable(varName, varSid, varType, varQuestion, varActive);
}

UserLog(JSON.stringify(varMap)); // you can replace UserLog with gs.print

function Variable(varName, varSid, varType, varQuestion, varActive, varOrder) {
    this.varName = varName;
    this.varSid = varSid;
    this.varType = varType;
    this.varQuestion = varQuestion;
    this.varActive = varActive;
    this.varOrder = varOrder;
}