Jump to content

How to Find duplicates in Name field?...Part 2

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

Recommended Posts

  • Newbies

My original May'06 Post#204069 states:

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?

John Mark Osborne's response was: 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:

Case(Right(Name, 1) = ".", Left(Name, Length(Name) - 3), Name)

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.

It worked, but only on names with a period after the middle initial. I later discovered that others have a middle initial without a period, and some names with the middle name spelled in full. My need is to have only a Last Name (including two, or hypenated last names), a comma, and one First Name. I referenced FMPro 6 Help and read the available info on Case Function. I've tried several possible modifications to John coding, but am not successful thus far.

John, or anyone willing to help...Any suggestions? I would be most grateful for any help, or suggestions.


Link to comment
Share on other sites

This would tell you if the ending word was an initial:

Length( RightWords( Name ; 1 ) ) = 1

But that wouldn't solve the case where the middle name was spelled out.

What you want is the last name (the text up to the comma) and then the first word after the comma:

Let( [

comma = Position ( Name ; "," ; 1 ; 1 ) ;

nolast = Right ( Name ; Length ( Name ) - comma - 1 ) ];

Left ( Name ; comma ) & " " & LeftWords ( nolast ; 1 ) )

Link to comment
Share on other sites

This topic is 6467 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.