Jump to content

How to find duplicates in name field?


This topic is 5690 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

My data base has the entire name in one field, the "Name" field (data is imported from a data source), and there are multiple record for individuals. Sometimes the individuals name will include their Middile Initial, therefore FM does not see them a duplicates when executing a standard search for duplicates. For example:

Smith, John and Smith, John W. are not considered duplicates though they are the same person.

How can I get FM to see them as duplicates?

Link to comment
Share on other sites

I would parse out the first and last names into their own fields, and then do my duplicate flag based on a Concatenation of those two fields.

You could probably get by with

[color:blue]LastName (calculation, Text Result) =

LeftWords([color:blue]YourField, 1)

[color:blue]FirstName (calculation, Text Result) =

MiddleWords([color:blue]YourField, 2, 1)

and then do your duplicate check on a Concatenate the results.

[color:blue]CombinedNames (calculation, Text Result) =

[color:blue]LastName [color:green]& [color:blue]FirstName



Link to comment
Share on other sites

The word functions won't handle two word last names or first names so I think the Position function might work better in this scenario:

LastName = Left(Name, Position(Name, ",", 1, 1) - 1)

FirstName = Right(Name, Length(Name) - Position(Name, ",", 1, 1))

This still doesn't solve your problem with two word last or first names. So, forget the formulas above since all you really want to do is remove the middle name if it exists. Or, you could transfer everything but the middle name to another field. Start by creating a new calculation field called Name2 with the following formula:


Right(Name, 1) = ".",

Left(Name, Length(Name) - 3),


Now you will have a new field (Name2) with just the first and last name even if you have a last name like "Del Rio". You can search this new field for duplicates as long as the data entry is consistent.

Link to comment
Share on other sites

When you finally get them split, Rocket, leave them split. It's VERY easy to put fields together but quite difficult to split them apart (for the very reasons Lee and John mentioned); particularly with names or addresses. There simply are too many variables. Only a PERSON can make proper distinctions and those distinctions should be made at the moment of data entry because only THAT person knows to ask the Customer if it's unclear. Getting a name right is very important! :wink2:

My pet peeve is a computer determining how my NAME should be capp'd and lowercasing the R. :crazy2:


Link to comment
Share on other sites

  • Newbies

Thank you, to all who responded :laugh2:

For my immediate need, Lee's suggestion did the trick. After I complete my immediate deliverable, I definitely will try the other suggestions, as well.

Thanks, again!! - Rocket

Link to comment
Share on other sites

This topic is 5690 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.