Constituency Code Cleanup

Joanie Rogers

Hi all,

After years of bad data, I am embarking on a mission to clean up the constituency codes.  From the beginning, anyone who made a gift to our foundation was coded as "Donor".  We have made it work all these years, but it's time to get the codes cleaned up and more meaningful.

I am trying to figure out how to generate a report so I can see each constituent code and all the constituents who have that code.  I'm having difficulties finding an easy way to generate a report that will look something like below.  Any suggestions?  Thank you!

DonorProspectAlumniFacultyJohn Doe  John DoeJane Doe Jane Doe  AB Corp   Jill Mill Jill MillJoe Dot Joe DotJoe Dot

JoAnn Strommen

Joanie Rogers:

Hi all,

After years of bad data, I am embarking on a mission to clean up the constituency codes.  From the beginning, anyone who made a gift to our foundation was coded as "Donor".  We have made it work all these years, but it's time to get the codes cleaned up and more meaningful.

I am trying to figure out how to generate a report so I can see each constituent code and all the constituents who have that code.  I'm having difficulties finding an easy way to generate a report that will look something like below.  Any suggestions?  Thank you!

DonorProspectAlumniFacultyJohn Doe  John DoeJane Doe Jane Doe  AB Corp   Jill Mill Jill MillJoe Dot Joe DotJoe Dot

Data is in RE, but don't know that it's that easy to pull in that format. 

Export would be the most logical place but while you get one constituent per row, you get first constituent code/second constituent code in the columns.  Would get you your data but would require some manual manipulation to move codes into their own columns. 

Basically doing an export is what's described in BB186680 - How to report on the constituent name and constituent code.  In the knowledgebase solution it says: To have each constituency code on a separate line:  Create a constituent code import file   Guess I'm not thinking clearly because I don't see how an import file's going to do this - maybe with a csv file of all your records - just not thinking clear enough right now. 

Maybe possible through a Crystal report - don't know.  Hope someone has a better idea for you.

 

 

 

Joanie Rogers

JoAnn Strommen:

Data is in RE, but don't know that it's that easy to pull in that format. 

Export would be the most logical place but while you get one constituent per row, you get first constituent code/second constituent code in the columns.  Would get you your data but would require some manual manipulation to move codes into their own columns. 

Basically doing an export is what's described in BB186680 - How to report on the constituent name and constituent code.  In the knowledgebase solution it says: To have each constituency code on a separate line:  Create a constituent code import file   Guess I'm not thinking clearly because I don't see how an import file's going to do this - maybe with a csv file of all your records - just not thinking clear enough right now. 

Maybe possible through a Crystal report - don't know.  Hope someone has a better idea for you.

 

 

 

I will give that a try!  Thank you.

Michael Sherman

Joanie Rogers:
I will give that a try!  Thank you.

You may want to take a look at doing a query with the entire database using "Name" and "Constituency Code" for output. Try sorting by name or Constituency Code. You can export and manipulate the results in Excel. If a donor has 5 constituency codes they will be listed 5 times instead of once with all codes on the same line. I know the mantra: "Query is not a reporting tool" but sometimes you feel like a nut and sometimes you don't.

Helen Wieger

Joanie Rogers:

Hi all,

After years of bad data, I am embarking on a mission to clean up the constituency codes.  From the beginning, anyone who made a gift to our foundation was coded as "Donor".  We have made it work all these years, but it's time to get the codes cleaned up and more meaningful.

I am trying to figure out how to generate a report so I can see each constituent code and all the constituents who have that code.  I'm having difficulties finding an easy way to generate a report that will look something like below.  Any suggestions?  Thank you!

DonorProspectAlumniFacultyJohn Doe  John DoeJane Doe Jane Doe  AB Corp   Jill Mill Jill MillJoe Dot Joe DotJoe Dot

 I'm about to start a similar clean up project. The easiest way I found to get a list of constituent codes like the one above is to create a pivot report. (For more thorough instructions on pivot reports see Knowledge Base solution BB88412.)

To do make a pivot report, create a query with the output of Name and Constituent Code. Save the query. Then in Reports create a Pivot Report based off the query just saved. Put Constituent Code for the columns and Name for the rows. Then drop the "Constituent Count" into the middle. That should give you a chart like above, except that rather than repeating the name across the rows, it will just have a 1.

 Hope that helps!