How many records to delete are we talking about? 10? 100? 10,000?
Edit: And with a Mr. and Mrs. if you delete one of the addresses, have you considered how you would access the address information? If the address is only on one of two records, and you were looking at the address-free one, how would you know to look elsewhere to find the address...and where to look?
This is a list of 1,864 duplicates in the form of Mr. & Mrs.. I just need the addresses of either one of the parties since they reside at the same address and will receive the same information mailer. Thus, I want to cut that list to 932 where the result would be that I could find the names and addresses by means of the street names.
I know there must be a formula to remove either one of the parties from the duplicate street name and address but I can't find it or either am looking at the formula and don't know it. I am working with the records in the "View as Table" record format. Hope this helps with clearing up any questions you asked.
You should create a second table for the addresses. Rename your current table as 'names'. You can create a looping script that will create a new addresses record. After all the records have new related addresses, you can delete out the address specific fields in your orig 'names' table.
The easiest way would be to create a calculation which would identify the unique address such as ( lets call is cUnique ):
Address & "_" & City & "_" & State & "_" & Unit
Also create a new field in 'names' called: fkAddressID ( number field )
Now in your new table "Addresses" create the following fields ( etc if you need others ):
pkAddressID (turn on serial option under auto-enter field options)
Create a relationship from cUnique to TempKey and turn on allow creation of related record on the Address side.
Now go to your layout based on names and run a script such as:
Go to layout [ "names" ]
Show All Records
Go to Record [ First ]
Set Field [ Address::Address; names::Address ]
Set Field [ Address::Unit ; names::unit ]
Set Field [ Address::City; names::City ]
Set Field [ Address::State; names::State ]
// Sets new foreign key
Set Field [ names::TempKey; Address::fkAddressID ]
Go to Record [ Next; Exit After Last ]
Once this script has run it should have created one record for each of the unique addresses.
Now you can change your relationship to be keyed on
pkAddressID = fkAddressID and you should be able to have your one to many.
Last step. Double check everything and remove unnecessary fields.
Thanks Mr. Vodka. I'll work with what you set out and get back to you tomorrow with the results achieved. Thanks to all ............ I have to sign off for now.
I've been working on this problem using your suggestions on and off for the past several days now to no avail. The specific fields that I have that are duplicates of information which I want to eliminate one and have the other remain are:
I believe that by using the "Street Number" as the target to eliminate one of the duplicates the end result will be that I have the remaining single "Street Address" with all the other related information. As I said, I'm not the sharpest tack in the box but believe there should be a relative easy way to do this without going insane. The last time I used FileMaker before the present time was in 2004 and have forgotten almost everything except how to open the program.
My version of FileMaker Pro 7.0.2 and I'm working on a Mack Pro with OS 10.11 if that helps. Any help directing me to the solution would be appreciated ...... more than greatly.
Mr.Vodka's method is elegant...here's a brute force one that I've used sparingly (sometimes a hammer CAN drive a screw...if you hit it hard enough). I would exhaust the elegant solutions first, and use the hammer only if you've got a couple of hours free and a big cuppa coffee.
List the records in list view with enough info to recognize duplicates.
Add a button on the right set to "Delete Record/Request"
Sort the list by street name, and street number and zipcode.
Scroll through and manually kill the duplicates.
Long, tedious, inelegant, but totally functional. Again, I would exhaust the elegant solutions first...perhaps you didn't implement Mr. Vodka's suggestion the way he intended???
Hope you don't need this approach, but know that it exists.
I tried working with Mr. Vodka's method/procedure for hours to no avail. It's possible I wasn't applying it correctly since I'm wasn't to sharp at plugging in his formula as I read it.
What I did to solve my problem was similar to yours except I manually went through them after acquiring the "duplicates" and omitted one of the two duplicates. Long process but it worked.
Thanks for your input.