How to get values of related records through a script?

[Santiago Tombio]

I have an HR Case Record. The table itself is called sn_hr_er_case. This record has a related record under the related links section called "Involved Parties". These belong to another table called sn_hr_er_involved_party. As you will see on the screenshot, this is the name of a person (Display Name) and a type of Involved Party (Type). 

Now, what I actually need to do is to fetch through a script (which I'll put in a business rule) the email of the Accussed  Involved Party, which at the same time is stored in the sys_user table. 

Here's what I tried but it is not working nor logging anything... I'm also quite sure I shouldn't be using GlideRecord, but what's the alternative? 

var hrCase = new GlideRecord('sn_hr_er_case');
hrCase.get(current.sys_id);

var involvedParties = hrCase.sn_hr_er_involved_party;

for (var i = 0; i < involvedParties.getRowCount(); i++) {
var involvedParty = involvedParties.getRow(i);
var user = involvedParty.user;
var type = involvedParty.type;

if (user && user.sys_class_name == 'sys_user' && type == 'Accused') {

var userEmail = user.email.getDisplayValue();
gs.log('Accused email: ' + userEmail);

}
}

Thank you in advance!!!

[Prince Arora · Accepted solution]

@Santiago Tombio ,

I am assuming your business rule is on "sn_hr_er_case"

var gr = new GlideRecord("sn_hr_er_involved_party");

gr.addQuery("hr_case" , current.sys_id); // check backend field name of HR case

gr.addQuery("type" , "accused"); // please check what is the backend value of type

gr.query();

while(gr.next()){

var userEmail = gr.user.email;
gs.log('Accused email: ' + userEmail);

}

I have not tested this code, because I don't have HRSD application installed, but you need to get email by above mentioned way!

If my answer solved your issue, please mark my answer as ✅Correct & 👍Helpful based on the Impact.

[AakashG2003]

Hi @Santiago Tombio 

Try this, You will find this helpful.

var hrCase = new GlideRecord('sn_hr_er_involved_party');          // Creating an object of related link table
hrCase.addQuery('hr_case', current.sys_id);                   // hr_case is the backend label name of the HR Case field
hrCase.query();
while (hrCase.next()){
    if (hrCase.user && hrCase.user.sys_class_name == 'sys_user' && hrCase.type == 'Accused') {
        var userEmail = hrCase.user.email.getDisplayValue();
        gs.log('Accused email: ' + userEmail);
}
}

[Santiago Tombio]

Thank you @Prince Arora ! It works like a charm! However, everytime the script is executed, the Read operation of the Involved Party table becomes invalidated in Restricted Caller Access Privileges and then it stops working 😞 

I go to the RCA record, mark it as allowed, run the script, and same thing happens: runs well once, makes the RCA Invalidated afterwards. 

Is there a way to prevent this from happening?

Thank you again! 

[Santiago Tombio]

Ah, I figured it out: it was because of me changing the update sets, so nevermind! 

Thank you so much for your help once more!