Zoho Creator: Find Duplicates by Customer Name for Large Datasets

: Category: Zoho Creator; 12 July 2023; 12 July 2023; Hits: 23152; Rating:

( 716 Ratings )

What?
There are other sources on the web which show you how to deduplicate a contact but usually involve entering a customer name or other key to check if there are already records in the system with that key. I can't sit there entering one customer at a time. Even a loop would hit a statement execution limit.

Why?
My use-case is that I need this done over a dataset of 20k+ contact records and that's only half of the production system's contacts. I needed a quick bit of code to find any duplicates, all within the Zoho Creator app using Zoho Deluge and avoiding the use of any third-party APIs.

How?
My team of developers actually helped me solve this one as I was slowly dispairing...

The Plan

Get a list of all unique/distinct Customers
Get a list of all Customers
Remove the list of unique ones from the "all" list to be left with just the duplicates.

The Code

copy raw

v_CountDiscrepancies = 0;
//
l_Customers1 = Customer[ID != 0].distinct(Customer_Name);
l_Customers2 = Customer[ID != 0].Customer_Name.getAll();
//
if(l_Customers1.size() != l_Customers2.size())
{
	v_CountDuplicates = l_Customers2.size() - l_Customers1.size();
	v_Grammar = if(v_CountDuplicates == 1,"","s");
	info v_CountDuplicates + " record" + v_Grammar + " are duplicates!";
	//
	// return a distinct list
	l_Customers2.removeAll(l_Customers1);
	//
	// loop through and output to user
	for each v_DistinctCustomer in l_Customers2
	{
		v_CountDiscrepancies = v_CountDiscrepancies + 1;
		c_Customer = Customer[Customer_Name.equalsIgnoreCase(v_DistinctCustomer)];
		info v_CountDiscrepancies + "#: " + c_Customer.Customer_Name;
	} 
}

v_CountDiscrepancies = 0;
//
l_Customers1 = Customer[ID != 0].distinct(Customer_Name);
l_Customers2 = Customer[ID != 0].Customer_Name.getAll();
//
if(l_Customers1.size() != l_Customers2.size())
{
v_CountDuplicates = l_Customers2.size() - l_Customers1.size();
v_Grammar = if(v_CountDuplicates == 1,"","s");
info v_CountDuplicates + " record" + v_Grammar + " are duplicates!";
//
// return a distinct list
l_Customers2.removeAll(l_Customers1);
//
// loop through and output to user
for each v_DistinctCustomer in l_Customers2
{
v_CountDiscrepancies = v_CountDiscrepancies + 1;
c_Customer = Customer[Customer_Name.equalsIgnoreCase(v_DistinctCustomer)];
info v_CountDiscrepancies + "#: " + c_Customer.Customer_Name;
}
}

Caveat(s):

There was a risk that the removeAll() command would remove every customer from the "all" list. That didn't happen so the above code works at time of print. Things can change though and if the function is changed to remove every instance of the "distinct" list, then I'll have to come up with a different solution. Maybe Zoho could introduce a removeFirst() if they change removeAll()..
The number of duplicates may easily differ to the loop of discrepancies simply because some records may be triplicates or more rather than just duplicates.

Category: Zoho Creator :: Article: 383