script include with GlideAggregate to find duplicate records based on multiple values

[karolisjon]

Hello

I'm trying to build a report which would filter out records, which have identical values in two different fields. I'm trying to achieve that by a script include, which is called in the report. 

Script include: 

function getDuplicates() {  
  var dealsGA = new GlideAggregate('x_deals'); 
  dealsGA.addAggregate('COUNT', 'company_name'); 
  dealsGA.addHaving('COUNT', 'company_name', '>', '1'); 
  dealsGA.query();  

  var listOfDuplicates = new Array(); 
	
  while (dealsGA.next()) {  
    listOfDuplicates.push(dealsGA.getValue('company_name')); 
  } 
  return listOfDuplicates; 
}

When running the report, it filters out an groups records by company_name field, where there are multiple records having the same company_name. 

I want to do the same, but where company_name and code_name are checked. I tried adding addAggregate and addHaving for another field in the same way as with the current one, but that didnt work. Has anyone deal with something similar before? 

Thanks.

[karolisjon]

Hi @Mahendra RC , I'm trying to create a report, which would filter out records having identical values in two fields: company_name and code_name. As I've read, this has to be done through a script include called from the report. 

I created a script include with GlideAggregate which as of now filters out and groups records where company_name values are identical: 

function getDuplicates() {  
  var dealsGA = new GlideAggregate('table_name'); 
  dealsGA.addAggregate('COUNT', 'company_name'); 
  dealsGA.addHaving('COUNT', 'company_name', '>', '1'); 
  dealsGA.query();  

  var listOfDuplicates = new Array(); 
	
  while (dealsGA.next()) {  
    listOfDuplicates.push(dealsGA.getValue('company_name')); 
  } 
  return listOfDuplicates; 
}

That part work fine, it groups records having an identical company_name:

What I want to do now, is to adjust the script include/report, so that it would only group records where company_name AND code_name fields have identical values. In the screenshot below you can see that there are 4 records having identical company_name values, but only two code_name values are the same. I would only want to see such results. 

[Abhijit4]

Try below script :

function getDuplicates() {  
  var dealsGA = new GlideAggregate('x_deals'); 
  dealsGA.addAggregate('COUNT', 'company_name'); 
  dealsGA.addAggregate('COUNT', 'code_name'); 
 dealsGA.addHaving('COUNT', 'company_name', '>', '1'); 
 dealsGA.addHaving('COUNT', 'code_name', '>', '1'); 
  dealsGA.query();  

  var listOfDuplicates = new Array(); 
	
  while (dealsGA.next()) {  
    listOfDuplicates.push(dealsGA.getValue('company_name')); 
  } 
  return listOfDuplicates; 
}

Please mark answer as Correct or Helpful based on impact.

[karolisjon]

Tried that @Abhijit4 , but that didnt change anything. It groups by matching company_name values, but dont filter out those where code_name values are different: 

F.e. in this case it should only show records where company_name = TestName and code_name = test2, but it still shows other two where code_name values are different. 

[Abhijit4]

Ok, got it. The filter you are using is Company Name is one of the (companies returned by script). In your above case it will definitely returns TestName Company which is correct.

Now, you would need to add one more filter like Code Name is one of the (code name returned by below script)

function getDuplicates() {  
  var dealsGA = new GlideAggregate('x_deals'); 
  dealsGA.addAggregate('COUNT', 'company_name'); 
  dealsGA.addAggregate('COUNT', 'code_name'); 
 dealsGA.addHaving('COUNT', 'company_name', '>', '1'); 
 dealsGA.addHaving('COUNT', 'code_name', '>', '1'); 
  dealsGA.query();  

  var listOfDuplicates = new Array(); 
	
  while (dealsGA.next()) {  
    listOfDuplicates.push(dealsGA.getValue('code_name')); //returning code name instead of company name
  } 
  return listOfDuplicates; 
}

OR you can keep only Code Name filter, I don't think company name filter is needed anymore (as more than one company filter is already validated in new Code Name script itself).

I haven't tested the script, you might need to do few modification if required. let me know if you still need further help.

Please mark answer as Correct or Helpful based on impact.