PDA

View Full Version : FileMaker delete duplicates based on two columns




Helmutek
Oct 1, 2010, 07:31 PM
Hi, I've been running around google, filemaker knowledge base, and everything else for that matter and still can't find an answer to this... or at least not as easy as I thought it would be.

Scenario is; I have FM11Pro, and imported a file with 40,000 records. This is my mailing list database, and every bit of data is separated into columns. What I'm trying to accomplish is select and delete duplicates based on two columns (because a lot of the addresses are duplicated simply because there is an entry for every member of the household).

It looks a little something like this;

Mr. | John | Smith | 123 | Main | Street | City | State | Zip
Ms. | Ana | Smith | 123 | Main | Street | City | State | Zip

So ultimately I would like to send one piece of mail to only one recipient per household, and I don't really care which one is selected mr., or ms. as long as get don't spit out duplicates for obvious reasons of being cost effective.

Thanks in advance for responding to this. :)



BertyBoy
Oct 2, 2010, 03:26 PM
Errr, do you have something against calculated fields ??

Doesn't matter whether you store it or not (disk is cheap).

Only you know the columns that would identify a household, the subject line says 2 columns but I suspect that you mean more, perhaps Surname and all the Address line fields.
So a calculated field that is the concatenation of all the fields - perhaps put some character between the fields to make debugging easier - then a script to sort by that new calculated field and step through the records either deleting or ignoring those that have the same calc'd field as the last record. Either use a global or a variable to hold the value of the calc'd field over to the next record.

Join the FileMaker forums http://www.fmforums.com/ to maybe get a better answer, I'm still back on FMP v7.