Update a field value for a reference table through business rule/workflow script 4m a catalog item

[Nitin Baijal]

Script to autopopulate the owner name for mailbox in the catalog item - 

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

var caller = g_form.getReference('mb', doAlert);
}
function doAlert(caller) {
g_form.setValue('owner', caller.u_owner);

}

Task at hand - 

once the user submits the ticket with all this information through a catalog item, replace owner1 with owner2 and close the ticket. so basically, find Mailbox1 in reference table and replace the existing owner of it to the variable value of new owner field from the catalog item.

I am fairly new to this , so some of it makes sense to make me but not all of it.

Issues faced - I have tried both business rule and script by executing below script but i am unable to find any clear error messages(maybe I am looking at the wrong places)

question - In glide record should i be giving the reference table name because thats where i want the update to happen or sys_user because the owner name resides there?

--------------------------------

var user_ref = current.variable.owner.getValue(); //get the sys_id of the Owner
var userManValue = current.variables.newowner.getValue(); //get the sys_id of the new Owner
var userManDisplayValue = current.variables.newowner.getDisplayValue(); //get the displayValue of the new owner

var userU = new GlideRecord('reference table 1');
if (userU.get(user_ref)){ //get user record using the owner sys_id
userU.setValue("owner",userManValue,userManDisplayValue); //set the owner's value and displayValue (prevents roundtrip to server)
userU.update(); //update record

}

Please help me with this, it is getting pretty urgent that i resolve this

[SanjivMeher · Accepted solution]

Ok..Got it...Please try below code

var user_ref = current.variables.owner.getValue(); //get the sys_id of the Owner
var userManValue = current.variables.newowner.getValue(); //get the sys_id of the new Owner

gs.info('------------user_ref -'+user_ref);
gs.info('-------------userManValue -'+userManValue);

var userU = new GlideRecord('u_shared_mailbox');
userU.addQuery('u_owner',user_ref);
userU.query();
if (userU.next())
{ //get user record using the owner sys_id
userU.setValue("u_owner",userManValue); 
userU.update(); //update record
}

[Nitin Baijal · Accepted solution]

You sir are a champion 🙂

thank you for hand holding me for this one.

it worked and the value is changed.

Few additional requirements that i have are -

- once the RITM is raised, it goes for approval

- once approved, a task is created

- once the task is created, the owner is updated and task is closed

I am going to work on these on my own and only if i am stuck somewhere, i will bother you again 🙂

you are a rockstar!!!

[Nitin Baijal]

do you think the code is not able to fetch the value ? can it be because it is looking for sys id and not display value.?

I am not sure

[Nitin Baijal]

Hi Sanjiv,
when i run the business rule on requested item -

i am getting the values in the logs but those are sys ids

so maybe we are one step closer but the values still dont update in reference table.

[SanjivMeher · Accepted solution]

Ok..Got it...Please try below code

var user_ref = current.variables.owner.getValue(); //get the sys_id of the Owner
var userManValue = current.variables.newowner.getValue(); //get the sys_id of the new Owner

gs.info('------------user_ref -'+user_ref);
gs.info('-------------userManValue -'+userManValue);

var userU = new GlideRecord('u_shared_mailbox');
userU.addQuery('u_owner',user_ref);
userU.query();
if (userU.next())
{ //get user record using the owner sys_id
userU.setValue("u_owner",userManValue); 
userU.update(); //update record
}

[Nitin Baijal · Accepted solution]

You sir are a champion 🙂

thank you for hand holding me for this one.

it worked and the value is changed.

Few additional requirements that i have are -

- once the RITM is raised, it goes for approval

- once approved, a task is created

- once the task is created, the owner is updated and task is closed

I am going to work on these on my own and only if i am stuck somewhere, i will bother you again 🙂

you are a rockstar!!!

[Nitin Baijal]

Hi @SanjivMeher ,

I am back with more complexity 🙂

so with the above code, I am able to change the owners but i need to refine it to change it for the chosen mailbox.

for e.g.

I need to change the owner where the mailbox name is 1206.

so the query should support 2 conditions - mailbox and owner.

I have tried to rattle the cage and tweak the code but i think i am missing something.

var user_ref = current.variables.owner.getValue(); //get the sys_id of the Owner
var userManValue = current.variables.newowner.getValue(); //get the sys_id of the new Owner
var sharedmailbox = current.variables.shared_mailbox.getValue();//get the sys_id of mailbox selected

gs.info('------------user_ref -'+user_ref);
gs.info('-------------userManValue -'+userManValue);
gs.info('-------------sharedmailbox -'+sharedmailbox);

if (current.variables.mailbox == 'Shared Mailbox')
{
var userU = new GlideRecord('u_shared_mailbox');
//userU.addQuery("u_owner",user_ref);
userU.addEncodedQuery("u_display_name="+sharedmailbox+"^u_owner="+user_ref);
//userU.addQuery("u_display_name",sharedmailbox);
userU.query();
if (userU.next())
{ //get user record using the owner sys_id
userU.setValue("u_owner",userManValue);
userU.update(); //update record
}
}

I have tried multiple ways like adding 2 addquery statements to chose 2 conditions(i have commented them out above). secondly then i tried encodedquery but still no joy.

I think the problem lies in the portion with addquery().

any thoughts??

P.S. i also need to fit in a snippet to handle the null values as well, because right now if the owner value is blank , it doesnt work either 😞