Check 2 fields from 2 different table

[Kanna12]

Hello Community,

I have a situation in users table. I want to add a groups to user by looking at employee type in users table and check  user Company ID is available in departmnet table. Right now we are using below query.
Taking employee type from users table and listed (hard coded)all company ID's in sys_property. 

"New requirement":

1. Need to take employee type from users table and take company ID ( exit functionality)

2. Check Comapny ID is available in departmnet table. (Right now checking company ID from sys_property)

Below is the query we are using.

var empRec = new GlideRecord('sys_user');

empRec.addEncodedQuery('u_employee_type=EMPLOYEE^company.nameIN' + gs.getProperty('company_names_employee_contractor')); // Store Company Names in Sys Property

empRec.addActiveQuery();

empRec.query();

while (empRec.next()) {

    var empRec1 = new GlideRecord('sys_user_grmember');

    empRec1.addQuery('user', empRec.sys_id);

    empRec1.addQuery('group', 'group_sysID'); // sys_id of the group " Employee"

    empRec1.query();

    if (!empRec1.next()) {

        empRec1.initialize();

        empRec1.user = empRec.sys_id;

        empRec1.group = 'group_sysID'; // sys_id of the group  Employee"

        empRec1.insert();

    }

}

Please let me how can we processd with new erequirement and checking 2 fileds from 2 diffrent table.

Thank you in advance!

[Bhavya11]

Hi @Kanna12 ,

you can try something like below

Business rule type make it has before update/insert

then add the Employee type changes

if (current.u_employee_type == 'employee' && current.department) { // check if the employee type is employee and has department information
        var departmentGr = new GlideRecord("cmn_department"); //query to department table 
        departmentGr.addEncodedQuery('company' + current.company + '^sys_id' + current.department);// pass user department and company value 
        departmentGr.query();
        if (departmentGr.next()) { //if present then check if the user is member of group or not
            var empRec1 = new GlideRecord('sys_user_grmember');
            empRec1.addQuery('user', current.sys_id);
            empRec1.addQuery('group', '4691af77c316d610c7311412b4013164'); // sys_id of the group " Employee"
            empRec1.query();
            if (!empRec1.next()) {
                empRec1.initialize();
                empRec1.user = current.sys_id;
                empRec1.group = '4691af77c316d610c7311412b4013164'; // sys_id of the group  Employee"
                empRec1.insert();
            }
        }

    }

If I could help you with your Query then, please hit the Thumb Icon and mark as Correct !!

Thanks,

BK

[vishwajeet5550]

checking fields from two different tables can be done using GlideRecord queries in a script. To do this, you can query both tables separately and retrieve the values of the fields you're interested in. For example, if you want to check the priority field from the Incident table and the state field from the Change Request table, you can create a script where you first use GlideRecord to query the Incident table, retrieve the priority value, and then query the Change Request table for the state field. You can also query tables with related records. For instance, if the Incident table has a reference field pointing to the Change Request table, you can retrieve the reference field value, and then query the related Change Request table to check its state field. This approach allows you to efficiently check and compare data across different tables using the GlideRecord API

var incidentGR = new GlideRecord('incident');
incidentGR.addQuery('active', true); 
incidentGR.query();

if (incidentGR.next()) {
    var incidentPriority = incidentGR.priority; 
    gs.info('Incident Priority: ' + incidentPriority);
} else {
    gs.info('No active incidents found.');
}

var changeRequestGR = new GlideRecord('change_request');
changeRequestGR.addQuery('active', true); 
changeRequestGR.query();

if (changeRequestGR.next()) {
    var changeState = changeRequestGR.state; 
    gs.info('Change Request State: ' + changeState);
} else {
    gs.info('No active change requests found.');
}