FileMaker newbie - finding dupes

[J. Weber]

hello - i'm new to FileMaker and am trying to perform what i think is a very basic function, but i just can't get it to work. (many thanks to those patient enough to help me!) we're using FM 5.5, a flat-file database, which doesn't have very many bells & whistles at all, no complicated relationships or calculations or anything like that. I'm trying to find dupes of people, and i'd like FM to search by more than one field, i.e. match in both last name AND first name field. no matter how i enter the symbol to search for dupes (the !) it still only shows matches of one field or the other. i created a Full Name field, creating a calculation field that combines Frist Name and Last Name, but that doesn't see to work either - still only shows dupes of first names.

am i crazy?! or is this a bug in 5.5? any help would be very appreciated, thanks very much!

[Damocles]

Here is a sample that may do what you want. It still requires you to go through and delete the duplicate records by hand (if that's what you want...)

There are a couple things in this sample that you did not mention in your question:

First, I created a field called LastFirst that is just the lastname and firstname stuck together. I added the "Substitute" feature to the field definition in order to remove any spaces that may be in the fields. The result is just a string of letters with no spaces.

Next, I created a relationship (see attached picture) that relates "Dupes" (the database example) back to itself by using LastFirst::LastFirst as the relationship key. This way, any records that have the same lastname and firstname are "related".

Finally, I created RelatedCount = Count(Dupes::LastFirst) as a field that will tell how many other records in your database are related (i.e., have the same last name and first name).

If you do a search for any records where RelatedCount is greater than one, this should return a list of any records that have duplicate last and first names. I added an address field and placed it in a portal, just so you can review the search results more easily. Alternately, check Layout #2 for a list of study results (instead of using a portal).

Hope this helps.

Paul

Duplicates.zip

[-Queue-]

It would be better to use a space or underscore to separate the first and last names rather than cramming them together. If the total length of both names combined is greater than 20 characters, your relationship won't work correctly if there are similar names that much up to their first 20 letters and diverge afterward.

[Damocles]

Thanks! Just to make sure I'm not going off half-baked,

Filemaker will only evlauate the first 20 characters of a word when comparing words, but that can be overcome by breaking the text field by using spaces or underscores (?)...

So it would be better to define the relationship key as:

ConcLastFirst = Substitute(Lastname," ","")&"_"&Substitute(FirstName," ","")

OR

ConcLastFirst = Substitute(Lastname&"_"&FirstName," ","")

I'm trying to avoid the ambiguity of having "VanDonald" and "Van Donald" as last names that may be entered either way.

Thanks for the "critical length" reminder.

Paul

[-Queue-]

Yes, using spaces or underscores allows it to index up to the first 60 characters (max of 20 characters in a row) in v6 and below.

I'd say it might be safest to use Substitute(Lastname & "_" & Firstname, " ", "_" ) as your definition.

[Damocles]

-Queue-

Doesn't adding the underscore interfere with the relationship? I just tried Substitute(Lastname & "_" & Firstname, " ", "_" ) as the field definition. When I created new records, Lastnames Van Buren and VanBuren were not related, since one was converted to "Van_Buren" and one stayed as "VanBuren".

I was originally trying to make them appear as related records, since the inserted space (or lack thereof) is frequently a mis-typed user entry. Of course, the original question didn't ask for allowance for inconsistent data entry. (I know, answer the QUESTION! )

Paul

[-Queue-]

You're correct. That does pose a problem. It would probably be best to check first for any last names that might exceed 20 characters and manually compare them. Then it would be safe to use Substitute(Lastname & "_" & FirstName, " ", "") as your definition.

[Damocles]

Is it redundant to say Substitute(Left(Lastname,20)& "_" & Left(FirstName,20), " ", "") since FM only evaluates the first 20 letters anyway?

If you refine the relationship key to be Right("0"&Length(Substitute(Lastname, " ", "")),2)& Substitute(Lastname & "_" & FirstName, " ", "") it is possible to differentiate between fairly long names based on length, further eliminating the need for manual comparisons.

Of course, this solution doesn't acknowledge hyphenated names, but that's for tomorrow.

paul

[-Queue-]

No, I don't think that would be redundant because that would make the field fully indexable instead of merely 'falling off' on the 21st character of the Lastname and ignoring the rest of the calculation. Nice idea, Paul!

I think Right( "00" & Length(Lastname), 2 ) & "_" & Substitute( Left( Lastname, 20 ) & "_" & Left( FirstName, 20 ), " ", "" ) could be a viable key. I haven't tested it though.

[Damocles]

Right( "00" & Length(Lastname), 2 ) .....

This didn't work as the first part of the field definition (tried, erred, edited previous post! ) since it counted the spaces in the field. It listed "Van Buren" as 09 and "VanBuren" as 08. That defeated the rest of the Substitute functions because the records were no longer related.

Paul

[-Queue-]

I thought about that. You'd have to use Right( "00" & Length( Substitute( Lastname, " ", "" ) ), 2 ) instead.