Compare list of records to another list to find matches

[Dazler]

Hi,

I have created a custom table for our Role Base Access.  This table contains what access to grant a user based on their job roles.  The current project that I am working on involves job changes, where a user joins another position within the company.  

The goal is to use the custom table to retrieve what access they were granted based on their previous job role.  Then retrieve the access they should be granted for their new role.  Now that we have both list, I need do the following:

1. compare both list and find a match based on 2 fields on the table.

2. A list of the items in the previous job role, that didn't match.

3. A list for the items in the new job role, that didn't match.

Does anyone know how I can accomplish this?

[Kashyap Gundepu]

Hi @Dazler 

Can you please elaborate on the use case, what are the common fields in both of these tables?

Do you want to print the list of fields on each table that didn't match or the values of the fields?

An example of the scenario would help us to help you with the resolution.

[Dazler]

@Kashyap Gundepu 

I have 1 custom table called Role Base Access and this is what I need to do. 

1. I query the Role Base Access table to return the records associated with the users previous job role. 
2. Next, I query the Role Base Access table again to return the records associated with the users new job role.
3. Now that I have both list of records, on the Role Base Access table there are two fields:
   • (1) A reference field to the CMDB
   • (2) A multi-line field
4. First, I need to compare both the query records for the previous job role and new job role to find the matches based on the 2 fields from above.
5. Next, I need to determine the records from the previous job role that were NOT a match.
6. Finally, I need to determine the records from the new job role that were NOT a match.

I would prefer to print out the records sys_id into an array for each of these options.

[Dazler]

@Kashyap Gundepu 

Hi, I wanted to follow up to see if you had a chance to look at my need in the reply.

[jaheerhattiwale · Accepted solution]

@Dazler Use below script:

var firstTable = new GlideRecord("FIRST TABLE");

firstTable.query();

while(firstTable.next()){

    var scondTable = new GlideRecord("SECOND TABLE");

    scondTable.addQuery("SECOND TABLE FIELD", "VALUE FROM FIRST TABLE FIELD");

    //Add the queries here if needed

    scondTable.query();

    if(scondTable.hasNext()){

        //Match found

    }else{

        //Match not found

    }

}

Please mark as correct answer if this solves your issue.