comparing data in two files?

[ncasares]

Here's what I would like to do:

I have 2 files called Customers and Prospects.

I would like to somehow compare the two files. If a Customer record appears in the Prospects file (based on first and last name) I want to remove the record from the prospects file.

I can envision a script that would do this, but it seems unecessarily complex to accomplish this task.

Does anyone know of a shortcut, or is there something I'm forgetting about?

Thanks in advance. As always you guys are great!

[BobWeaver]

Essentially, you want to find all the records in the Prospects file that are in Customers file. You need to find this set and then delete it.

1. Back up both your files.

2. I'll assume you have a single field in both files that uniquely identifies the customer/prospect. Since you are using first name and last name, you need to set up a calculated field in both files called FullName with the formula:

FirstName & " " & LastName

3. Create a relationship from the Prospects file to the Customers file using FullName as the key field on both sides of the relationship. Assume the relationship is called 'Customers'.

4. Create a script in Prospects with these steps:

Show all records

Go to Record/request [last]

Loop

If [isValid(Customers::FullName)]

Omit Record

End If

Go to Record/Request [previous][exit after first]

End Loop

Show Omitted

---

5. Run the script. It will bring up all records that exist in the customers file.

6. Check it to make sure it looks right.

7. Delete the found set.

You can, of course, incorporate steps 6 and 7 into the script, once you know for sure that it is bringing up the correct records.

Don't forget step 1.