I am preparing for a massive overhaul of our database's Constituent Codes and for a large import of new records as well. If there's anyone out there who has taken on such a project and might have some tips for where to begin with reviewing contituency codes, etc. I'd love to hear about your process.

Christine, I am going to be doing the same thing in the near future, any responses you get directly and would be willing to share, I would appreciate it. [Email Removed] Cindy American Civil War Center

We are contemplating the exact same thing. In order to reduce the "me toos" maybe some kind soul will answer this question and also post their procedures on the BB server for all to view. We have approx 50,000 records but they are not all current. We also wish to bring in several other lists and flag the constituencies and solicit codes for use by all. It will be a huge undertaking but one that will benefit our entire org. I know I would appreciate the insight of someone who has done this and knows where to find shortcuts (if any exist). Thanks, Hillary Murphy Phoebe Foundation PO Box 1828 Albany, Georgia 31702-1828 229-312-1492 229-889-7439 fax [Email Removed] www.phoebeputney.com

I have been struggling for an answer for you guys but honestly there is no common set of procedures for clean up. You simply clean up the data you have. You query on codes i.e. attributes, solicit codes - you expect people to have and don't. You query on addresses with incorrect formatting i.e. Street instead of St. or vice versa. You query on blank fields people should have. You query on common mistakes. Your clean up depends on your internal policies and procedures. Because we are all using RE in so many vastly different ways the common mistakes in my system are dependent on the way we use RE. If you have a more specific question I will try to answer but ths one is too general for me to be of help. Melissa S. Graves Manager, Development Database & Direct Mail Planned Parenthood of Connecticut [Email Removed] 203-752-2804 P.S. I will be attending the conference in October. A group of Forum users will be meeting on Sunday. Contact me directly if meeting interests you.

Thanks Melissa...this is the needle in the haystack. I have done much of the formatting part you mention (Street to St, etc....) We have greater problems than that.....With such a large donor base on no one in the office will use it. There are no real solicit codes, etc.... except the ones I have attached. Constituency codes are basically non-existent as well but I am working on a plan to update those and I guess I'm back to good old fashioned way of looking at each record one at a time. That's the only way we'll ever complete this cleanup. If I ever come up with the magic bullet I'll pass it on. Hillary Murphy Financial Administrator Phoebe Foundation

Thanks to all. This gives me the starting point i needed - and it's just as i suspected; no identifiable process, just greuling lists upon lists upon lists to clean. looking forward to it!

I would start with the "most active" records first, and prioritize clean up from there. We've been on RE since 1999 and are still cleaning up things from the old db..... (but the ICEBERG is melting....... melting....)LOL. Good Luck, Elaine Elaine Tucker [Email Removed] Texas Lutheran University

I recommend utilizing the Global Change function once you've identified groups of records that have common changes (ie. Street needs to be St) I can't tell you how many months I spent going line by line through queries to correct data because I was too nervous to try Global change. Practice using Global Change & Delete in the sample database until you get a feel for how it works. Once you've got that down, it'll save you a tremendous amount of time! Tracie Cassidy
Database Coordinator
Seattle Children's Hospital Foundation
(206) 987-6833
[Email Removed]

A suggestion to add on to Tracie's comments is to have a test server. I use my local desktop machine, and it works alright as long as your database is not over 2 gigs yet (MSDE max is 2 gigs). If this is not possible, try to convince the PTB's that a test database is essential to your processes. This way you can test and retest and destroy data as many times as you want without affecting what your production database. Doug Creek University of Alaska Database Administrator [Email Removed]

When I am testing out global changes, after I've gotten my larger group together I choose up to 10 records from that group and do it on them. And before I do the change, I write down what was on the record before I wanted it changed so I can manually fix it if the change doesn't do what I want it to do. To get my smaller group I usually pick on the people whose last names begin with Q or Z. We don't have too many of those overall. I've been doing this for 6 years and there are still times when hitting that Change Now button is a little nerve racking! Good luck with your clean up! It feels good once it's done. laura Laura A. Caswell Information Technology Worcester State College Worcester, MA 01602 [Email Removed] (508) 929-8867

What Doug recommends is similar to how our department handles imports and global changes. We have a stand-alone version of our data on a laptop that is updated periodically and which we use to test a large import or global change before doing it in our live data. Catherine Catherine Seebald Director of Advancement Services Houghton College One Willard Ave. Houghton, NY 14744 585-567-9395 [Email Removed]

Very timely topic as I am in the process of cleaning up constituent codes based on a seminar I attended last week and subsequent brainstorming with our local user group. The rule of thumb is CC categories need to be broad, therefore I am going from 52 to 15, and the only reason I have as many as 15 is we want begin and end dates for board and committee members. So, I have things like individual, business, church, organization, foundation; ask yourself WHAT is this constituent. For example, I'm moving 10 volunteer CCs to one attribute = volunteer, using the description field to identify the type of volunteer. Darcie Esch Development Asst. Metropolitan Family Service [Email Removed]