How to compare two different tables and get a single value to display on a form

[Anshika4]

Hi All,

I am a newbie to servicenow development and need some expert advice on my below problem!

I want to update the department head field on a form by comparing the department field value from 2 tables. I am not sure how can use glide record in business rule or script to compare table1.employee.department == table2.department. If yes, update the department head field on a form with table2.departmentHead
Table A - Extends table task and have Employee Name, Title, Department field all referenced to sys_user table

Table B - It is a custom table with fields Department Head and Department 

Can someone please help how can I use two tables to update the department head from table 2 to the form?

Thanks in advance.

[Weird]

The basic query for incident table for example is:

var gr = new GlideRecord('incident');
gr.addQuery('active', true);
gr.query();
if(gr.next()){
//code here
}

GlideRecord(''); specifies the table you're querying.
addQuery() allows you to specify conditions. You can select a filter from a list for this as well.
above example could be gr.addQuery('active=true'); as well.

query() runs the query and searches for results.

if(gr.next()) is used to check for a next record and access that if it exists.
You could also use while(gr.next()) but if there are multiple matches for your query then the script inside the while will repeat on all of the matches.

So let's assume that you know the conditions for your query and you'd first query table one and then do another query for table two:

var gr = new GlideRecord('<table_1>');
gr.addQuery('<condition>');
gr.query();
if(gr.next()){
//code here
}

var anotherGr = new GlideRecord('<table_2>');
anotherGr.addQuery('<condition>');
anotherGr.query();
if(anotherGr.next()){
//code here
}

You can then compare values from both of these queries like

if(gr.employee.department == anotherGr.department){
current.<field_on_form> = anotherGr.departmentHead;
}

Note that as I don't really know how your tables have been setup this might require some additional work on your side.
You should still be able to get the right solution if you play around with this.

Also do note that I'm expecting you to run this all in server side as a business rule. If you have a form that you want to update on client side when user makes selections then you need to use client scripts and have an additional glideajax call included in it.
If you're running this as a business rule and are updating the current record you're on after saving, then use a before update (or insert as well) business rule.

...
It seems to be past midnight for me, so hopefully the previous suggestions are understandable. Feel free to comment if anything is weird as I can try to rephrase them tomorrow.

[Robert Campbell]

Not a direct answer to your question but if you use the User table which relates to the Department table which has the Department Head you could easily add it to your form with no scripting.

You would dotwalk and just add the field to the form in Form Layout.  Department and Department Head can be accessed by dotwalking from the 'caller' of the record.