Problem with Find Duplicates

[curalice]

I am using the Find Duplicates function, however when I put the ! in the address field, it finds duplicates of the street numbers only.

120 Main

120 Main

120 First

120 Circle

What I want is all duplicate street number+name.

120 Main

120 Main

120 Elm

120 Elm

How can I do this?

Thanks

[Søren Dyhr]

Eventhoug the streetnumber is of type NUMBER isn't the streetname dito, so if you let the entire field be number will only the number part be considered as match. You should turn the field TEXT instead, and the search will respect the chars as a whole. At least to me doesn't it make much sense to store text in a number field ...where opposed to that, is all chars members of posible in textfields.

--sd

[curalice]

Thanks.

I think my field is a text field "120 Main St". But how would I change the field type from number to text. I couldn't find the way to find out which it is and change it.

:P

[Søren Dyhr]

You need to poke down in the fields def. the lower part of the dialogbox where it look similar to this ...I know from your profile that you have windows, but it looks almost similar in your version.

Notice that I have changed the popup to "text" in the second of my snaps - making it ready to hit change!

--sd

[curalice]

Thank you! Thank you!

Now I have the duplicates for addresses listed nicely.

Do you know the best way to consolidate those?

EG- John Smith and Jane Smith at same address = mailing label The Smith Family

OR

John Jones and Jane Johnson and Bill Barnes at the same address = Household (or maybe all names/one label)

[Søren Dyhr]

Well I have made a template that deal with it:

http://www.filemakerpros.com/Splitting.zip

...it would need some tweaking though, I have in the template made the groupings with company name, where it's in your case should be the address.

Say If you need more help? - since the principles used are different!

--sd

[curalice]

Thank you, Soren.

I will look at your template when I get back this afternoon.

I really appreciate everyone's help!

[curalice]

Yes, that is a cool template.

It is so far beyond my novice abilities in Filemaker that I have no idea how to use it to get from my list to mail labels. I haven't learned anything about scripts yet and, looking at the ones in your template reminds me I need to learn!

I think I am going to have to exporet to Excel and clean up the duplicats one by one.

Cheers to you

[Søren Dyhr]

I think I am going to have to exporet to Excel and clean up the duplicats one by one.

Wait- wait, other options exists! if you have established your found set by entering ! in a find on adresses, could you by adding a summaryfield to your fields def. and stuff a Count on the breaker value address, make a script that after a sort changes the name from the first in each set to the "family" and the last name in the name field.

We can let the script know how many to delete, by omitting the changed and via GetSummary( command get to know the remaining records in the address.

Let me make you a template to show how to do it, the only thing is that's way beyond bedtime in europe!!

--sd

[Søren Dyhr]

Well I've changed my mind, why litter the solution with yet another field?? Let's script it!

Set Variable [ $currentAddress; Value:Case(0;0) ] 

Enter Find Mode [  ] 

Set Field [ Addresses::StreetNumberName; "!" ] 

Perform Find [  ] 

If [ Get ( FoundCount ) ] 

     Sort Records [ Specified Sort Order: Addresses::StreetNumberName; ascending ] [ Restore; No dialog ] 

     Go to Record/Request/Page [ Last ] 

End If 

Loop 

     Exit Loop If [  not Get ( FoundCount ) ] 

     If [ IsEmpty ( $currentAddress ) or Addresses::StreetNumberName ≠ $currentAddress ] 

          Set Field [ Addresses::SubscripersName; RightWords ( Addresses::SubscripersName ; 1 )  & " Family" ] 

          Set Variable [ $currentAddress; Value:Addresses::StreetNumberName ] 

          Omit Record 

     Else 

          Delete Record/Request [ No dialog ] 

     End If 

End Loop 

Show All Records 

However is there a problem with the Family'ing of the surname, because where is the surname actually starting ... let's take an example the name O'connor, the way Rightwords( works is that it only plucks sans the "O", the same thing could be said about chinese names where the family-name comes first...

--sd

[curalice]

Golly!

Ok, I am going to read up on scripts and try what you sent.

I think the surnmae part is easier because I have two different fields: first name, last name.

Thank you, I will work on this tonight.

[curalice]

Ok, I am making some progress on scripting, thanks.

Here is a funny problem with FIND, however-

If i put ! in the MailAdress field, I get a list of duplicates.

If I first do a find of 06=v and 04=v and then constain that set with ! in the MailAdress field, I get duplicate AND unique addresses.

So whay won't ! work to constrain a found set?

[Søren Dyhr]

Perhaps this is even better:

http://www.filemakerpros.com/DUPNTH.zip

--sd