Fix Script Needed

[Saib1]

Hi All,

We will get the excel sheet forom customer with condition employee_number=AG50600

Need to write a fix script and send this excel sheet and pass only the conditions column to the encodedquery on the script and need to execute it. 

Script 

var a = new GlideRecord('sn_shn_notes');
a.addEncodedQuery('conditionsINemployee_number=AG53345');
a.query();
gs.print(a.getRowCount());
while(a.next()){
a.setValue('display_type','true');
a.setValue('status','1');
a.setValue('table_name','sys_user');
a.setValue('type','2');
a.setValue('conditions','employee_number=AG53345');
a.setWorkflow(false);// Do not run business rules
a.autoSysFields(false); // Do not update system fields
a.update();
}

How to rewrite on this script. Please help me

[Bhavani Shankar]

Hi @Saib1,

Check if this code works for you.

var parser = new sn_impex.GlideExcelParser();        //Excel parser API

var attGR = new GlideRecord('sys_attachment');
attGR.addQuery('table_sys_id', '7a8c708183c7821032fba530ceaad323'); //sys_id of the current fix script record 
attGR.query();

if (attGR.next()) {

    var attachment = new GlideSysAttachment();
    var attachmentStream = attachment.getContentStream(attGR.sys_id);
    parser.setSheetNumber(0);
    parser.setNullToEmpty(true);
    parser.parse(attachmentStream);
    while (parser.next()) {
        var row = parser.getRow();
        // row["Conditions"] - gets the conditions value starting from first row

		// Your script follows
        var a = new GlideRecord('sn_shn_notes');
        a.addEncodedQuery(row["Conditions"]);
        a.query();
        gs.print(a.getRowCount());
        while (a.next()) {
            a.setValue('display_type', 'true');
            a.setValue('status', '1');
            a.setValue('table_name', 'sys_user');
            a.setValue('type', '2');
            a.setValue('conditions', row["Conditions"]);
            a.setWorkflow(false); // Do not run business rules
            a.autoSysFields(false); // Do not update system fields
            a.update();
        }
    }
}

Would appreciate a thumbs up if this helps.

[Saib1]

Hi @Bhavani Shankar  - The above script id doing the update on all the records.

we have the 8000+ records in sn_shn_notes all the records got updated in the table . 

[Bhavani Shankar]

If you want to update sn_shn_notes only for certain condition values, add an If clause before the GlideRecord operation like this

while (parser.next()) {
        var row = parser.getRow();
        // row["Conditions"] - gets the conditions value starting from first row

        // Adding If condition here

       if(row["Conditions"] == "opened_for.employee_number=600700522" ) {
		// Your script follows
        var a = new GlideRecord('sn_shn_notes');
        a.addEncodedQuery(row["Conditions"]);
        a.query();
        while (a.next()) {
            a.setValue('display_type', 'true');
            a.setValue('status', '1');
            a.setValue('table_name', 'sys_user');
            a.setValue('type', '2');
            a.setValue('conditions', row["Conditions"]);
            a.setWorkflow(false); // Do not run business rules
            a.autoSysFields(false); // Do not update system fields
            a.update();
        }
      }
    }

This way it only updates the records which match your condition.

[Bhavani Shankar]

Hello @saib,

Let me know if this worked.

If it worked for you, I'd appreciate a thumbs up and mark as helpful.

Thanks,