[Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

To prevent duplicated name & best practice


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
  • 3
  • 2
  • 2
3 Solutions
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...

Scott Fell, EE MVEDeveloperCommented:
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.
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...
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

lenamtlAuthor Commented:
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.
Scott Fell, EE MVEDeveloperCommented:
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..."
@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.
lenamtlAuthor Commented:
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.


Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now