To prevent duplicated name & best practice

Posted on 2014-08-18
Last Modified: 2014-08-25

1- Let say we don't want user to add duplicated company name in the system.
Using a search form, what will be the best practice to use for searching, How far should we go.
(the admin can set to make name and-or phone number verified before to add a new company)

What it does for now, if the system find an "Exact Match", it's prevent to add the company.

Right now if we search : "MyCompanyName"
this will not find "MyCompanyName Inc" exist
and will allow the user to add the company

When searching should the system ignore:
accent (in french we have éàî )
Special caracters like - space & _ . ,
Inc Lte

2- Another case when doing a "Regular Search" the system ask a minimum of 4 characters and "space" are considered as 1 characters to my opinion the space should not be counted
The system  allow to search "My  C"
What will be the best way to manage it?
 My Co = 4 not 5

I know we cannot control everything but I'm looking for a moderate way to prevent duplicate company name
Question by:lenamtl
    LVL 10

    Assisted Solution

    Rather than restricting the search you should allow the users to search using wildcards '%userValue%' so that it will pull up a list of any portion of a company name and allow them to select it, and also provide them with an option of "other" if they cannot find it.  If other is selected then provide them with a text input which they can type the new company name into.

    Before doing the insert of the new company name, do a select to check and make sure it does not exist or bring back any similar options and ask the user "are you sure you did not mean to select this instead?"

    - If new company names do not come up frequently after you have an established list then you might consider having an administrative process where they request a new entry created that they can then select...

    LVL 52

    Assisted Solution

    by:Scott Fell, EE MVE
    On my forms for this type of situation, I don't have an "add" button until you do a search.  I encourage users to start with just the first few letters and my results include a wild card without having a user to add a wild card.

    A search for "comp" is sent to sql server as "comp%".  Depending on how many contacts they have, I will limit the results to something manageable.  20 to 30 results with a note to refine the search.

    It does force the user to work a little and that comes with some consequences of them asking for a faster way to search or add people.  I'm working with contact db's with under 100,000 contacts and many have 10K to 20K.

    There is no perfect solution, you do have to work it out with the project owner and go through the options from the easiest to add contacts to the most reliable way to ensure there no duplicates.  I also include a report of possible duplicates.  How the report is put together depends on the situation.    Typically, I may make a key by concatenating  the first few characters of multiple fields like company, address, city as example.  This would make "My Company, 1234 Maple Rd, Someplace" have a key, "My 123som".  Then find other keys that match.  You could also do this very thing when  you are about to insert new data.  Let them fill in the full info, then make up a key like this and give an alert with a listing of other possible duplicates and question if they want to proceed.

    Because no one way is perfect, I also build in a merge routine that allows them to merge one company info to another and then archive the duplicate.

    Probably the best way though is to use a 3rd party service to look up contact/company and perform some address verification and standardization before inserting to the db. Using address standardization will really help in finding duplicates.
    LVL 10

    Expert Comment

    NOTE: by using wildcards i was referring to your query... The user doesn't need to know that you are using them... ( '%userValue%' ) where userValue would be substituted with what they actually typed...
    LVL 24

    Author Comment

    I don't think this is possible to use wildcards in this case or maybe I missed something

    Here more info about the way it's working right now

    User enter a company name
    Transport ABC
    click search button, the word Transport ABC is frozen in the field and cannot be edited at this point

    If the exact match exist it's display the information
    If the exact match does not exist the Add button appear

    Using this logic we cannot use wildcards for the search because the name that is entered before the search will be the one use to create the contact (frozen) and will the one that will make appear or not the Add button.

    Note: We usually use wildcards for other search let say for report or to filter list, but for that case it seems not possible.
    LVL 52

    Accepted Solution

    Trying to search on exact match by a name is going to be tough.  Things can be spelled wrong or somebody may have added two spaces between word, Transport  ABC or maybe the company goes by multiple names UPS/United Parcel Service, FedEx/Federal Express, Or creatively spelled Bellwether vs Bellweather perhaps Transport ABC vs Trans port ABC.  

    Using just the name, you are going to have to make changes to what you are doing.  If you want to keep the full name frozen on a form, then do an exact match search first and if you don't find anything, perform another search but add the wild cards programatically.  Personally, I find the first few letters are a good start and only place the wild card to the right.

    Your results could show, "We did not find an exact match, but found similar company names..."
    LVL 10

    Expert Comment

    @Scott  - Agreed.

    If you don't allow for some flexibility in the search/match then you will likely end up with a bunch of duplicates.  By making it easier for a user to select the correct one by presenting possible options prior to creation will decrease the likeliness of duplicates.  Most users will take the simplest path to achieve their objective and just create the new one under your present scenario if that means they can continue with what they need to do...

    The only way to almost eliminate duplicates completely is to make creation an admin function where they are trained to not create dups... of course admins can make mistakes too... and then have the user select from a pre-defined list.
    LVL 24

    Author Comment

    We will probably add a dropdown for company name extension (INC, LTE, Corp etc) in the first time and will take in consideration using wildcards and progressive search.

    Adding a merge tool is also considered but not for now because of the complexity of the actual form.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: HTML5 Specialist

    HTML5 development skills are critical to all developers. HTML5 is the foundation to almost any development process. That's why business, design studios, development shops and other organizations need HTML5 developers. Get your foot in the door as a HTML5 specialist.

    This article covers the basics of the Sass, which is a CSS extension language. You will learn about variables, mixins, and nesting.
    Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
    The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…
    The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now