Deleting a duplicate state in the state table

Melanie Freimuth · November 2015

We had several instances of state entered twice (probably from imports) in Config | Tables. I managed to find all of the records, change to the correct state and delete the incorrect state for all but our state. Any ideas on how to find this? I have run queries and can select the iteration I am looking for but the queries returns no results.

Thank you!

Ellen Turner · November 2015

It's possible that the wrong/incorrect version of the state still exists in records that are inactive or excluded in your queries if the queries are excluding records that are inactive, deceased, or have no valid address.

If it were me, try the queries again making sure all of those options are not being excluded from the query. If you still get no results, maybe try a global change of all records where the State = incorrect version of your state to correction version of your State? That way you can delete the wrong version in your table.

Sometimes you can combine/merge two options in a table field into one, and select the correct version of the State to be the version that is applied throughout the database.

Alternatively, you can change the incorrect version of the State to something else at the table level, and it will change all of the affected records globablly if that will make it easier to find the affected records via query.

Good luck!

Jen Claudy · November 2015

Unfortunately, there is no Table Cleanup option for the Cities table. I went thru this when I started at my current org...17 different entries for Cincinnati spelled differently (by people who live here! although some were abbreviations). You said you've covered all Constituent Records, so I would start looking at Relationship Records...that is probably where they're hiding.

Rachel Slager 2 · December 2015

Address records exist for constituents and relationships, so you need to query and global change each one before you can delete the state. Be sure to include deceased, inactive, and invalid address in your queries. Start: Constituents >global change> all addresses (not just preferred). Then you will need to globally change for Organization/Business, then Individual/Employee/Contact relationships.

If you still can't delete the state...

Check in Configuartion >General> your institutional state is correct

and Configuartion >Fincancial Institution>these states are all correct

If you still can't delete the state...

Then you might have an attribute problem. Attributes can be created and linked to a table; the States table can be used for this purpose. Go to Config > Attributes and search each type of attibute to see if there is one linking to States. In order to just correct the state you will need to export the record import id, the attribute import id, and the description field, correct the description to be the right state, import it all back in.

If you still can't delete the state, martini lunch.

Melanie Freimuth · December 2015

Blackbaud has a plug in that I had to ask for. They tested it on my data first and then sent me the plug in so I could do it myself. I didn't think of the martini lunch. That might have helped my state of mind.

Rachel Slager 2 · December 2015

That'd be a very useful plugin for future cleanup, and state of mind. Just curious, did they provide it free of charge?

Melanie Freimuth · March 2016

Yes, they did provide the polug in Free of charge.

Deleting a duplicate state in the state table

Comments

Categories