Find Duplicates & merge

[Madapple]

I am working on cleaning up the mailing list for the company I work for.

We produce 4 catalogs for different brands. People request the catalogs and now we have 25000 addresses, but some are the same people asking for each catalog one at a time - instead of asking for all 4 catalogs. or some ask for 3 catalogs, then come back and ask for the 4th, or 2 and 2...

how can i merge the duplicate records and include all of the catalogs they are requesting into one record?

example

Record 1 John Doe

cat2

Record 2 John Doe

cat 4

record 3 John Doe

cat 1

and I need

record 1 John Doe

cat 1 cat 2 cat 4

Edited August 6, 2008 by Guest

[mr_vodka]

Well your addresses should have been in its own table / file in your case to begin with, but I would start there. Each unique address could be assigned a uniqe ID. Then in a second table / file, you could have your actions.

If you want to keep it in one table, you can still clean up into unique addresses. Create a self join keyed on the unique identifier and then create a value list based on the related self join category field. Now you can set the field using ValueListItems and then remove all duplicate records. (See past posts on how to do this in FM6)

lue list based on

[Madapple]

I gotta be honest...

I dont have a clue as to what you just said!

I get the each address should be unique... after that you lost me

[mr_vodka]

I would start by search for old posts on finding duplicates for FM6 and the "self join" concept.

Next the function ValueListItems () will give you the value of a particular value list that you have setup. So if you create a new value list based upon the related category field and then refer to it using the ValueListItems () you can use it to set the field with the data for all the different records that are for the same address. But again, I would start with reading up on older posts. It should help you clear some things up. :

[Madapple]

before I go looking for FM6 - will it behave differently in FM9?

I didnt realize i had not updated my profile in a while...

[comment]

I am not sure you need to do anything: your records are NOT truly duplicates, and they are organized correctly. True, it would be better to have an additional table with unique addresses, but that's hard to maintain with requests coming in individually, and not essential.

The missing piece here is how do you produce your mailing list. For example, you can very easily produce mailing labels with only one label per address with a list of catalogs requested from this address. All you need is to summarize the records in a report.

[mr_vodka]

No the basic concept is the same. With FM9 there are just more techniques you can use to remove the duplicates.