How to find existing duplicate records on 'Name' but with different ID's


A brief history is that over the years we moved systems a couple of times.  
Now we are in Sugar CRM Ent Cloud v10.2 and have automated our serial number module import.  So going forwards this should not be an issue...

But, duplicate serial numbers have been created in the past ( Serial Number = Custom Module).

I have struggled to find a way to find duplicate records that already exist?  I hoped reports would be the answer but I had no luck there, maybe an API endpoint could get me and dupes in an array?

Has anybody had to do similar that may be a solution for this case?

I have began exporting via reports and to then try in Excel, but there is over 283,000 records in there!!  I really need them all in one sheet to check for dupes too...   I think however it is done will not be much fun :-0

Thank you in advance,

  • Hello, Luke.
    You could try to use Advanced Reports to select only necessary data or another option: create a regular Summation report with "count" by "Name" and sorted descending to select names with qty > 1 quickly. After that, use the regular filter by this module in the separate browser's window to filter data name-by-name and use merge functionality.

    I hope this helps you.

    We make work in Sugar
    more convenient and efficient

  • This is a great way to start. More importantly, what do you plan to do once you have identified the duplicates?

  • Hi ,

    Thanks you for your reply - I have implemented this and now I can find all my duplicate serial numbers - THANK YOU for the point in the right direction ;-)
    I did need some help from my colleague on the SQL syntax though.

    If it helps anybody else here is what we did in Advanced Reports to find duplicate records based off 'Name':

    I had previously, a long time ago when new to Sugar, been a bit confused by advanced reports so I hope this helps.. once you know the sql to include they do make sense to folk like me who are not programmers or developers:

    To be honest for what i needed just creating the SQL Query was enough, but I did go ahead and do the rest of the advanced report more out of curiosity.

    In your Normal Report

    In your normal report (Report Module) to get the SQL build a report close to what you need, tick the Show Query tick box and then click PREVIEW...  I don't know if this is correct but in ours clicking save and run with show sql ticked does not show the sql.  Only preview shows the sql.

    Then after it has ran scroll right down the bottom and the sql is displayed - query 1 and query 2.   Copy Query 1.

    Create the Custom Query

       > > > 

    Then add your SQL...  may need to tweak it a bit to get exactly what you need - I can't help you there!

    This was mine:


    The main addition is:  HAVING COUNT( > 1

    Create an Advanced Report - (which when you select it is titled 'Enterprise' reports?)
    This bit just needs a data format to be created - not as daunting as it initially sounds and easy when you see it.
    Then add the query you just made to the advanced report.

    Hope that helps :-)

    Hi , great question!  Once identified - here in lies the dull part: I need to merge each one!  maybe a job for the new year.  Each record could be related to other tables like contact, case, etc...  So I am unable to just delete.  1474 dupes found! Unamused

    Thanks  for the link!  DeDupit looks great...  but not at that price. Especially in the current climate when so much is uncertain - and I bet I am cheaper to do it manually ;-)  hehe....   Mind you it for one year it would work out at about 66 cents a dupe..  so its not bad.  Plus I bet we have dupes elsewhere too.  Thank you for the info though... always the Sugar Club is a great resource!  Thanks everyone.

  • Thanks for the write up and sharing it. All things considered, 1474 dupes are not too bad Slight smile Good luck.