Database cleanup

Lisa Blumenthal

I am very new to RE and was hired to help clean up the database, among other things. I have discovered that over 2000 constituents have duplicate constituent codes (i.e., some have alumni listed 2-3 times). I need to clean that up, but I'm just too new, and Chat is spinning me in circles. I have already created my CSV with constituent code and ID. Now I need to know how to safely remove those extra constituent codes. Can anyone help? (Hopefully this makes sense)

JoAnn Strommen

Am thinking about options. Are the duplicate constituent codes all the same - do they have date from/date to data? Or is it just the code?

If all instances of the code on those records were deleted, I'm assuming you would have a way to find records needing 'alumni' constituent code based on education relationship? Sometimes I don't like to rely on a static query staying static. 🤔

Joe Moretti

You will need to create a constituent Code called something like DELETE ME. Create the query of those with the same duplicates like Alumni. You will then export that query that contains the Constituent ID, the constituent Code and the Constituent Code Import ID. You can only export 2 of these codes at one time. Open the CVS file once exported, remove one of the constituent code you will keep and change the other code to DELETE ME. Import that back into the system using the constituent code Import and use Update Existing Records, validate only (to make sure all is correct and choose use the constituent ID. Match up the fields to import and choose Create output query and control report. Click Validate Now. If that goes right, uncheck the Validate data only and then click Import Now. The one constituent Code should now be DELETE ME and the other will stay the same of Alumni. Now go to Globally Change Records and choose Constituent. Include the query you saved from the import. Go all the way down till you see Constituent Codes and click on Constituent Code, delete for the operation and delete that Delete Me Code. The delete me code should be removed from those records. BUT if you have more than one duplicate, you will have to do this over again until they are all removed. Once all of those codes have been changed to DELETE ME and that code deleted in the global change, you can then go to your tables and delete that DELETE ME CODE.

Hope this makes sense, because it has several steps and I have done this in the past to clean up what you are going through.

Faith Murray

I was thinking of a similar process to @Joe Moretti except that you can do a simpler process through a couple rounds of Global Change, if you do not need or have not been using your start and end dates (as Global Change alone will not retain these).

1. As Joe said, start with your static query of alumni who should have that constituent code. This will protect your group.
2. Then, go to Global Change, Constituent, and simply Delete the Alumni code from all records in that query. You may need to run it a few times.
3. Finally, in Global Change once more, "Add" the "Alumni" code back in - once - to all records in that query.

If start and end dates are important, you'll want to use Joe's more retentive method. You can also accomplish the same data retention by:

1. Make your query, containing ALL alumni.
2. Export all the Alumni constituent codes with start and end dates.
3. Create your DeleteMe code and use webview Code Tables cleanup function to change your Alumni code to the new DeleteMe code.
4. Under Tables, make a NEW Alumni constituent code.
5. Create a new Import file with a single instance of Alumni, combining your start and end dates from the Export in step 2.
6. Import the new file, adding your new Alumni code to all alumni records.
7. Then, using Global Change, delete all instances of DeleteMe from the records.

This last method is similar to Joe's, except you can export more than 2 code columns at a time using Export rather than Import to generate the starting file, and you don't need to output constituent code import IDs, because you are instead importing the data fresh. Which method is easier will depend on how large your Alumni file is (ie, how long a full Alumnus import would take), and how many duplicates constituent codes you have on each record (dealing with a 2-count output limitation).