TeamDesk Knowledge Base & Support

   Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
  
Duplicate checking
Most clients need a way to prevent duplicate entries. Like for name. Setting the column to be unique is not a good solution because sometimes there may be two people with the exact name.

What we do is make a many-to-many relation on the same table, set matching on names (first, middle, last), then concatenate the results. We call that column Possible Duplicate Records. This is a visual clue to help avoid entering duplicates.

But now we run into a bigger issue.. A client wants the same sort of checking for a Company name, but that's only one column. The problem is this ... They get a call from a company named "Western Van" and enter that in their database. Then they get a call again, only this time they say the company is "Western Van Lines". These won't match, so they won't even see the possible duplicate.

Using Contains doesn't work. Using the function "Begins With" would likely work but that is not an option in the many-many matching.

I am hoping someone can think of a way to do this.
ID
1486
Category
TeamDesk
Author

cooper collier
Date Created
11/29/2021 1:50:29 PM
Date Updated
12/9/2021 11:26:07 AM
Comments
basenine 11/29/2021 2:07:53 PM
Perhaps try matching by phone or email or website.
cooper collier  11/29/2021 3:04:15 PM
That is an excellent idea and may help some.
But the companies they have tend to be large and spread out with multiple offices.
You can see how that would muddy this.


What is needed is a nice fuzzy search function. That works as they type.
basenine 11/29/2021 3:52:38 PM
What...so one company has multiple offices but your client only wants to create one record for all?

Maybe create a Company Locations table for all the outlets....relate that to the parent company. When adding in a Company Location record, use the Search field reference and when you start typing the main Company, it should show...
Pierre 12/7/2021 8:54:30 AM
Hello Basenine
I was curious to see if any answers would come to your question / I have struggled on the same issue.

Here's what I do - it is heavy, inelegant but partly adresses your question. I mainly write it down to 'participate'.

Basically, (I did not know about many-to-many relations), I recreate my own many-to-may relation and there I can have all the selection formulas I want.

So I have a COMPANY table. I want a unique [Name] identifier.
- I create a [CleanedName]= Upper Trim / Replace ( Space / Hyphens .)
- I create then pointers with 5 / 6 / 7 letters: [@CN5] = Left([CleanedName],5), [@CN6], ...
I create a COMPANYCHECK table with a [@CHECK] field
I create several relations from COMPANY to COMPANYCHECK using the [@CN5], [@CN6], fields as pointers
In COMPANYCHECK I count the number of children with 5/6/7 letters. I also concatenate the names of the children into a summary field.
In COMPANY I retrieve from COMPANYCHECK the counts and the concatenation
In each COMPANY I then have the information about potential duplicates and the name of the duplicates.

Kind regards,

Pierre


Pierre 12/7/2021 2:00:39 PM
Also, :
- each time there is a change in a pointer field of COMPANY ([@CN5]), I create a record in COMPANY CHECK with this value
- the index of COMPANYCHECK is the text field
cooper collier  12/8/2021 11:00:29 AM
I would love to see this in action sometime.
It looks really complicated
Pierre 12/9/2021 1:26:36 AM
Hello Cooper

Firstly I am sorry - I addressed my message to Basenine instead of you

Secondly I created a trial database for you to see this work - my explanations were complicated, the database is not :-)
https://me.youtom.io/secure/db/82927/default.aspx

This is the first time I make a trial
I think I need your email address to activate it for you - do you have a way to share it with me ?

Pierre


cooper collier  12/9/2021 7:56:23 AM
email: cooper@frobbit.com

thanks
Pierre 12/9/2021 11:01:13 AM
Sent
I created a trial with another url than what I mentioned above
I have also a (simple) process to validate duplicates, which I did not include in the database
Kind regards,
Pierre
cooper collier  12/9/2021 11:07:40 AM
I am in, and I see the results which are nice!
let me have setup rights so I can look at the code.
Pierre 12/9/2021 11:19:42 AM
Oooops
Done
cooper collier  12/9/2021 11:26:07 AM
If you have time, email me direct so I am not flooding this post with back and forth questions.
We can then post a final comment after thrashing it out
cooper@frobbit.com
Feedback
Back to Search Results