Comparing values in two tables

Prashant Kumar6 · ‎10-08-2023

Hello Experts,
I have two tables :
1: "sys_atf_test" where I have field called "Name" string type.
2: "tm_test_case" where I have field called "ATF Test Name" string type.
I want to check if there is any common values in both table fields through script ,Also I want to print the value which is common in both table fields. Any help ?Thanks.

Sandeep Rajput · ‎10-09-2023

@Prashant Kumar6 Try with following.

var glideATFTest = new GlideRecord('sys_atf_test');
glideATFTest.query();
while(glideATFTest.next){
var glideTestCase = new GlideRecord('tm_test_case');
glideTestCase.addEncodedQuery('u_atf_test_nameLIKE'+glideATFTest.getDisplayValue('name'));
glideTestCase.query();
if(glideTestCase.next()){
gs.info('Found a duplicate name '+glideTestCase.getValue('u_atf_test_name'));
}
}

Also, in order to find the correct query, I recommend you to do a manual search on u_atf_test_name field on list view of sys_atf_test table, copy the encoded query and use the same here in this script.

View solution in original post

Danish Bhairag2 · ‎10-08-2023

Hi @Prashant Kumar6 ,

You can try with below script

(function() {

// Query records from sys_atf_test table

var sysATFTestGr = new GlideRecord('sys_atf_test');

sysATFTestGr.query();

// Query records from tm_test_case table

var tmTestCaseGr = new GlideRecord('tm_test_case');

tmTestCaseGr.query();

// Array to store common values

var commonValues = [];

// Iterate through sys_atf_test records

while (sysATFTestGr.next()) {

var sysATFTestName = sysATFTestGr.name.toString().toLowerCase();

// Iterate through tm_test_case records and compare with sys_atf_test names

while (tmTestCaseGr.next()) {

var tmTestCaseName = tmTestCaseGr.getValue('atf_test_name').toString().toLowerCase();

// Check for common names (case-insensitive)

if (sysATFTestName === tmTestCaseName) {

// Add the common value to the array

commonValues.push(sysATFTestName);

break; // Exit the inner loop after finding a match

}

// Reset tm_test_case GlideRecord for the next iteration

tmTestCaseGr.query();

}

// Print common values

gs.info('Common values in "Name" field of sys_atf_test and "ATF Test Name" field of tm_test_case:');

commonValues.forEach(function(value) {

gs.info(value);

});

})();

Mark my answer helpful & accepted if it helps you resolve your query.

Thanks,

Danish