Finding duplicated constituent codes and cleaning up

Katharine George 2

I'd like to know the best way to clean up duplicated constituent codes. I suspect these are being generated through our RELO app but I can't pinpoint. What I'm seeing are individual records with 3-5 constituent codes that are identical. How do I query on multiple of the same code to see impact across the system? Then eliminate?

Gaby Palmadessa

Hi there, I found this article, I'm not entirely sure if it's a perfect fit for your situation but I hope this helps! 

https://kb.blackbaud.com/articles/Article/37719

If it doesn't help I suggested just chatting with Analytics they usually can walk you through it!

Alan French

I'm not sure there's a way to query for this directly, but I would start by looking at everyone who's got more than one constituent code (there's a "total constituencies" criteria in query) and then export those records to Excel. Depending on whether you've exported the data from a query or an export, you'll have either one line per code or one line per constituent, and you can then use a countifs or countif formula to count how many times each code appears for each constituent.

Melissa S Graves

I never thought of deleting them all and just globally adding them all back in just one time but this would NOT work if you have to and from dates on your constituency codes. You could delete them all but not until you export a file of them all. Then you could clean up the export to have everyone only have one per person, and then import them back in.

Ryan Hyde

Or you could create a static query that looks for everyone who has an instance of that particular code. Use the query to do a global change to remove that particular code from all of those records, and then use the same query to re-add the constituent code. Since it's a static query, deleting the codes won't impact the contents of the query itself. And the nice thing about this method is you don't have to dink around with an import.


But as Melissa said, if you're using date from/to, this will not work as you won't be able to retain that information. So hopefully you're like me and never use date from/to :P


Also, talk to a Luminate support rep. It shouldn't be doing this. I'm not sure if this is an option you can flag in the RELO connector or if it's something in Luminate itself, but I have RELO and I do not experience this problem.

Katharine George 2

Wow - thanks everyone. This is super helpful!

Katie Cagney 2

Hi, I'm just replying in case anyone's found a way in the intervening three years!!

We use dates on constituent codes, so couldn't just globally delete and then re-add.


I have been able to find some dupes by narrowing my query to has certain codes and not others, with total constituencies being 2, then 3, etc, which might help with the clean up for anyone else, but it just makes it more manageable rather than solving the issue.


Thanks,

Katie