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
copyraw
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 :: Article: 854
