Constituent Code Sequence Utility
Comments
-
I didn't even know this existed. I would jump into chat support to ask this sort of question.1
-
I want to take this project on (Const Code re-order), but does not seem possible without a lot of manual entry. If you find any information about the Utility, that would be so helpful!
I'm wondering if anyone has had success changing the order of Const Codes through the Import process? Is the first row that exports in an Import file = the first Const Code you see on Bio2 and would changing the order of the rows in the spreadsheet change how they appear in the record?
Thanks, all.
Jane0 -
I did what Jane is asking about...used Import to clean up (or at least start the clean up) Constituent Codes, in particular the Primary Constituent Code. But that was at least 4 years ago, so I don't remember exactly what/how I did this. Here's the Knowledgebase Article for this, which probably explains it better than I could.
If you've never done anything like this before, I highly recommend testing with just a handful of records first (I have a set of 4 dummy records that I keep in my database for just this sort of thing...they never have to be "put back" and I can set up whatever scenario I need to test whatever I'm working on).1 -
I just completed this cleanup process a little less than a month ago. I did not use Import, I used Global Add and Global Change. It's a multi-step process, and still requires backing up the database first in case of errors.
1) First, re-order your Constituent Codes in Config to the hierarchy you desire. I put the most important Codes top on the list.
2) Starting from the bottom of your hierarchy, pull a Query of all donors who match the criteria for your least significant Code (except "Friends"). Using that Query, Global Change to delete the Code from all the records in that query (to prevent duplicate codes on one record), then Global Add the Constituent Code, marking the button that says "Primary" under Constituent Code Information. This button will make that code the top (Primary) Constituent Code in the donor record.
3) Proceed with the same process for each Constituent Code, going from least important to most important in your hierarchy.
4) Pull a Query of all Constituents who still lack one of your above Constituent codes, and Global Add the Friends code as their Primary code.
I think if you had numerous Constituent codes, an Import might be more efficient, but I found Global Add to be easier for the 12 constituent codes we employ in our new (cleaned up - yay!) system. Once I had my hierarchy figured out, it took me about one afternoon to actually do the Global change process and clean up the records.
4 -
Faith Murray:
I just completed this cleanup process a little less than a month ago. I did not use Import, I used Global Add and Global Change. It's a multi-step process, and still requires backing up the database first in case of errors.
1) First, re-order your Constituent Codes in Config to the hierarchy you desire. I put the most important Codes top on the list.
2) Starting from the bottom of your hierarchy, pull a Query of all donors who match the criteria for your least significant Code (except "Friends"). Using that Query, Global Change to delete the Code from all the records in that query (to prevent duplicate codes on one record), then Global Add the Constituent Code, marking the button that says "Primary" under Constituent Code Information. This button will make that code the top (Primary) Constituent Code in the donor record.
3) Proceed with the same process for each Constituent Code, going from least important to most important in your hierarchy.
4) Pull a Query of all Constituents who still lack one of your above Constituent codes, and Global Add the Friends code as their Primary code.
I think if you had numerous Constituent codes, an Import might be more efficient, but I found Global Add to be easier for the 12 constituent codes we employ in our new (cleaned up - yay!) system. Once I had my hierarchy figured out, it took me about one afternoon to actually do the Global change process and clean up the records.
What you describe is a very elegant way of getting the job done. I hope to remember when it's time for me to work on constituent codes...
0 -
Jill Freidmutter:
What you describe is a very elegant way of getting the job done. I hope to remember when it's time for me to work on constituent codes...
Aw, thanks. I've gotten so many great ideas by reading these forums, it's nice to be able to share something back.
0 -
Jen Claudy:
I did what Jane is asking about...used Import to clean up (or at least start the clean up) Constituent Codes, in particular the Primary Constituent Code. But that was at least 4 years ago, so I don't remember exactly what/how I did this. Here's the Knowledgebase Article for this, which probably explains it better than I could.
If you've never done anything like this before, I highly recommend testing with just a handful of records first (I have a set of 4 dummy records that I keep in my database for just this sort of thing...they never have to be "put back" and I can set up whatever scenario I need to test whatever I'm working on).I read through the article you suggested, and it looks like it's just correcting what the primary code is on the record. For the particular project I'm asking the question for, it's due to the dates. So if I have a staff member who terminates their employment, I'm marking the end date (hopefully through an import, still working on those specifics), and then I'm hoping somehow to move that staff code off of the primary line, and make their primary code become "Member" or "Individual" without having to go into each record and do it manually for 2,000+ staff each month. Suggestions?
0 -
Faith Murray:
I just completed this cleanup process a little less than a month ago. I did not use Import, I used Global Add and Global Change. It's a multi-step process, and still requires backing up the database first in case of errors.
1) First, re-order your Constituent Codes in Config to the hierarchy you desire. I put the most important Codes top on the list.
2) Starting from the bottom of your hierarchy, pull a Query of all donors who match the criteria for your least significant Code (except "Friends"). Using that Query, Global Change to delete the Code from all the records in that query (to prevent duplicate codes on one record), then Global Add the Constituent Code, marking the button that says "Primary" under Constituent Code Information. This button will make that code the top (Primary) Constituent Code in the donor record.
3) Proceed with the same process for each Constituent Code, going from least important to most important in your hierarchy.
4) Pull a Query of all Constituents who still lack one of your above Constituent codes, and Global Add the Friends code as their Primary code.
I think if you had numerous Constituent codes, an Import might be more efficient, but I found Global Add to be easier for the 12 constituent codes we employ in our new (cleaned up - yay!) system. Once I had my hierarchy figured out, it took me about one afternoon to actually do the Global change process and clean up the records.
I like it, it seems like a good system to get these reordered and starting clean (once we have our constituent codes cleaned up!) However, I'm going to need an easy way to keep these cleaned up that doesn't take an entire afternoon to do. We use the dates on the constituency codes for staff, and when they terminate, I want those to somehow be removed from the top line and be replaced with "Member" or "Individual" and not wanting to delete the staff information, so we have some historical data. Suggestions?
0 -
Ryan Hyde:
I didn't even know this existed. I would jump into chat support to ask this sort of question.I may have to, there is really just no information on it anywhere, and nobody seems to have tried it. Thanks!
0 -
Bailey Adams:
I like it, it seems like a good system to get these reordered and starting clean (once we have our constituent codes cleaned up!) However, I'm going to need an easy way to keep these cleaned up that doesn't take an entire afternoon to do. We use the dates on the constituency codes for staff, and when they terminate, I want those to somehow be removed from the top line and be replaced with "Member" or "Individual" and not wanting to delete the staff information, so we have some historical data. Suggestions?
For maintaining an already cleaned-up system, there is, unfortunately, no way to add a Constituent Code end date en masse via Global Add or Global Change, so that would need to be done manually or through Import. (Hopefully you don't have 2000 staff leaving all at once.) Then for checking data integrity, you could periodically pull a query of all staff members who have left, but don't have the Member Constituent code for some reason, and then Global Add the Member code as their new primary, without deleting the old code.
0 -
Bailey Adams:
So if I have a staff member who terminates their employment, I'm marking the end date (hopefully through an import, still working on those specifics), and then I'm hoping somehow to move that staff code off of the primary line, and make their primary code become "Member" or "Individual" without having to go into each record and do it manually for 2,000+ staff each month. Suggestions?If you add an end date to a constituent code then RE no longer considers it to be their primary code once that date has passed. If someone has a "Staff" code with an end date and their second code is "Member" with no end date, when exporting their primary constituent code you will get "Member" rather than "Staff" so I don't know whether there is much to be gained by moving the staff code lower down?
2 -
Another way, and a very quick one, is to run a SQL query in the back end if you have an access to it. The below shows a screen of CONSTITUENT_CODES table. You can see that one of the records has value 2 instead of 1.
The below code would change it for all codes of 8267 (in our case it's a Deactivated code) which have sequence other than 1
UPDATE CONSTITUENT_CODES
SET SEQUENCE = 1
WHERE CODE = 8267 AND SEQUENCE <> 1
This way you can change thousands of records in a matter of seconds. I suspect this is exactly what this utility does.
Hope that helps
Tom2 -
Tomasz Malkiewicz:
Another way, and a very quick one, is to run a SQL query in the back end if you have an access to it. The below shows a screen of CONSTITUENT_CODES table. You can see that one of the records has value 2 instead of 1.
The below code would change it for all codes of 8267 (in our case it's a Deactivated code) which have sequence other than 1
UPDATE CONSTITUENT_CODES
SET SEQUENCE = 1
WHERE CODE = 8267 AND SEQUENCE <> 1
This way you can change thousands of records in a matter of seconds. I suspect this is exactly what this utility does.
Hope that helps
TomThanks for the window into uisng SQL in RE. I need to take some SQL courses...
0 -
Tomasz Malkiewicz:
Another way, and a very quick one, is to run a SQL query in the back end if you have an access to it.Just to highlight the warning in Blackbaud's database administration guide regarding using SQL Server in this way: "If you write to the database, you will [...] invalidate your support and maintenance agreement."
0 -
Alan French:
Tomasz Malkiewicz:
Another way, and a very quick one, is to run a SQL query in the back end if you have an access to it.Just to highlight the warning in Blackbaud's database administration guide regarding using SQL Server in this way: "If you write to the database, you will [...] invalidate your support and maintenance agreement."
Thanks for the info!
0 -
Alan French:
Bailey Adams:
So if I have a staff member who terminates their employment, I'm marking the end date (hopefully through an import, still working on those specifics), and then I'm hoping somehow to move that staff code off of the primary line, and make their primary code become "Member" or "Individual" without having to go into each record and do it manually for 2,000+ staff each month. Suggestions?If you add an end date to a constituent code then RE no longer considers it to be their primary code once that date has passed. If someone has a "Staff" code with an end date and their second code is "Member" with no end date, when exporting their primary constituent code you will get "Member" rather than "Staff" so I don't know whether there is much to be gained by moving the staff code lower down?Right, in export, sure. But we also have our letters customized based on constituent code (well, letter type, and then we have "If/then/else" statements written in, and it only pulls the top line of constituency codes. And I believe it also pulls the top code automatically in gifts, so when we use those for reporting, it's not being totally accurate if it does that.
0 -
Faith Murray:
Bailey Adams:
I like it, it seems like a good system to get these reordered and starting clean (once we have our constituent codes cleaned up!) However, I'm going to need an easy way to keep these cleaned up that doesn't take an entire afternoon to do. We use the dates on the constituency codes for staff, and when they terminate, I want those to somehow be removed from the top line and be replaced with "Member" or "Individual" and not wanting to delete the staff information, so we have some historical data. Suggestions?
For maintaining an already cleaned-up system, there is, unfortunately, no way to add a Constituent Code end date en masse via Global Add or Global Change, so that would need to be done manually or through Import. (Hopefully you don't have 2000 staff leaving all at once.) Then for checking data integrity, you could periodically pull a query of all staff members who have left, but don't have the Member Constituent code for some reason, and then Global Add the Member code as their new primary, without deleting the old code.True, but as for the original orderng of all of our staff that have termed at this point, I was hoping that the Constituent Code Sequencing Utility would be useful in this way so we could prioritize our codes and have it clean up the ordering for us. Seems like nobody on earth has used it before! I may have to be the first
0 -
Haven't tested this with a letter but I've just had a play about with my own record by putting an end date of yesterday for my first code; both query and a new gift correctly pulled through the second code on my record as the new primary. The gift constituency is static and doesn't change when the constituent's primary code changes, so if someone donates while their primary code is Current Staff and later donates again when their primary code is Former Staff then the two gifts will have different constituent codes on them - that may be why you're seeing end-dated codes in your reporting?Bailey Adams:
Right, in export, sure. But we also have our letters customized based on constituent code (well, letter type, and then we have "If/then/else" statements written in, and it only pulls the top line of constituency codes. And I believe it also pulls the top code automatically in gifts, so when we use those for reporting, it's not being totally accurate if it does that.1 -
Bailey Adams:
I've looked just about everywhere for information on how this utility is run, how to set it up, what exactly the process is in the background for how it moves codes around, and I am finding absolutely nothing in the KB or any of the forums. I'm hoping someone on here has used this utility and has some knowledge and experience with some do's or don'ts? Any information is helpful!Bailey,
I wanted to share with you all some information about the Constituent Code Sequence Utility. The purpose of this plug-in is to change the sequence order of the constituent code table on the back-end of RE to resolbe code-table issues. This plug-in will not change the order of constituent codes on the front-end of RE.
Like you, I could not find any resources directly on this plug-in, but it will work similarly to the Membership-Sequence plug-in. For step-by-step instructions, check out this Knowledgebase article: https://kb.blackbaud.com/articles/Article/39348. This too only resolves the back-end issues.
I hope this helps to clarify!
0 -
Bailey Adams:
I've looked just about everywhere for information on how this utility is run, how to set it up, what exactly the process is in the background for how it moves codes around, and I am finding absolutely nothing in the KB or any of the forums. I'm hoping someone on here has used this utility and has some knowledge and experience with some do's or don'ts? Any information is helpful!I go through this process each month as students become alumni. I upload the end date to the student and manually move the alumni record above the student or employee record. It would be SOOOOOOOO nice if Raisers Edge developers would just allow a primary indicator on the constituent code. Similar to addresses and phone numbers.
3
Categories
- All Categories
- Shannon parent
- shannon 2
- shannon 1
- 21 Advocacy DC Users Group
- 14 BBCRM PAG Discussions
- 89 High Education Program Advisory Group (HE PAG)
- 28 Luminate CRM DC Users Group
- 8 DC Luminate CRM Users Group
- Luminate PAG
- 5.9K Blackbaud Altru®
- 58 Blackbaud Award Management™ and Blackbaud Stewardship Management™
- 409 bbcon®
- 2.1K Blackbaud CRM™ and Blackbaud Internet Solutions™
- donorCentrics®
- 1.1K Blackbaud eTapestry®
- 2.8K Blackbaud Financial Edge NXT®
- 1.1K Blackbaud Grantmaking™
- 527 Education Management Solutions for Higher Education
- 1 JustGiving® from Blackbaud®
- 4.6K Education Management Solutions for K-12 Schools
- Blackbaud Luminate Online & Blackbaud TeamRaiser
- 16.4K Blackbaud Raiser's Edge NXT®
- 4.1K SKY Developer
- 547 ResearchPoint™
- 151 Blackbaud Tuition Management™
- 1 YourCause® from Blackbaud®
- 61 everydayhero
- 3 Campaign Ideas
- 58 General Discussion
- 115 Blackbaud ID
- 87 K-12 Blackbaud ID
- 6 Admin Console
- 949 Organizational Best Practices
- 353 The Tap (Just for Fun)
- 235 Blackbaud Community Feedback Forum
- 55 Admissions Event Management EAP
- 18 MobilePay Terminal + BBID Canada EAP
- 36 EAP for New Email Campaigns Experience in Blackbaud Luminate Online®
- 109 EAP for 360 Student Profile in Blackbaud Student Information System
- 41 EAP for Assessment Builder in Blackbaud Learning Management System™
- 9 Technical Preview for SKY API for Blackbaud CRM™ and Blackbaud Altru®
- 55 Community Advisory Group
- 46 Blackbaud Community Ideas
- 26 Blackbaud Community Challenges
- 7 Security Testing Forum
- 1.1K ARCHIVED FORUMS | Inactive and/or Completed EAPs
- 3 Blackbaud Staff Discussions
- 7.7K ARCHIVED FORUM CATEGORY [ID 304]
- 1 Blackbaud Partners Discussions
- 1 Blackbaud Giving Search™
- 35 EAP Student Assignment Details and Assignment Center
- 39 EAP Core - Roles and Tasks
- 59 Blackbaud Community All-Stars Discussions
- 20 Blackbaud Raiser's Edge NXT® Online Giving EAP
- Diocesan Blackbaud Raiser’s Edge NXT® User’s Group
- 2 Blackbaud Consultant’s Community
- 43 End of Term Grade Entry EAP
- 92 EAP for Query in Blackbaud Raiser's Edge NXT®
- 38 Standard Reports for Blackbaud Raiser's Edge NXT® EAP
- 12 Payments Assistant for Blackbaud Financial Edge NXT® EAP
- 6 Ask an All Star (Austen Brown)
- 8 Ask an All-Star Alex Wong (Blackbaud Raiser's Edge NXT®)
- 1 Ask an All-Star Alex Wong (Blackbaud Financial Edge NXT®)
- 6 Ask an All-Star (Christine Robertson)
- 21 Ask an Expert (Anthony Gallo)
- Blackbaud Francophone Group
- 22 Ask an Expert (David Springer)
- 4 Raiser's Edge NXT PowerUp Challenge #1 (Query)
- 6 Ask an All-Star Sunshine Reinken Watson and Carlene Johnson
- 4 Raiser's Edge NXT PowerUp Challenge: Events
- 14 Ask an All-Star (Elizabeth Johnson)
- 7 Ask an Expert (Stephen Churchill)
- 2025 ARCHIVED FORUM POSTS
- 322 ARCHIVED | Financial Edge® Tips and Tricks
- 164 ARCHIVED | Raiser's Edge® Blog
- 300 ARCHIVED | Raiser's Edge® Blog
- 441 ARCHIVED | Blackbaud Altru® Tips and Tricks
- 66 ARCHIVED | Blackbaud NetCommunity™ Blog
- 211 ARCHIVED | Blackbaud Target Analytics® Tips and Tricks
- 47 Blackbaud CRM Higher Ed Product Advisory Group (HE PAG)
- Luminate CRM DC Users Group
- 225 ARCHIVED | Blackbaud eTapestry® Tips and Tricks
- 1 Blackbaud eTapestry® Know How Blog
- 19 Blackbaud CRM Product Advisory Group (BBCRM PAG)
- 1 Blackbaud K-12 Education Solutions™ Blog
- 280 ARCHIVED | Mixed Community Announcements
- 3 ARCHIVED | Blackbaud Corporations™ & Blackbaud Foundations™ Hosting Status
- 1 npEngage
- 24 ARCHIVED | K-12 Announcements
- 15 ARCHIVED | FIMS Host*Net Hosting Status
- 23 ARCHIVED | Blackbaud Outcomes & Online Applications (IGAM) Hosting Status
- 22 ARCHIVED | Blackbaud DonorCentral Hosting Status
- 14 ARCHIVED | Blackbaud Grantmaking™ UK Hosting Status
- 117 ARCHIVED | Blackbaud CRM™ and Blackbaud Internet Solutions™ Announcements
- 50 Blackbaud NetCommunity™ Blog
- 169 ARCHIVED | Blackbaud Grantmaking™ Tips and Tricks
- Advocacy DC Users Group
- 718 Community News
- Blackbaud Altru® Hosting Status
- 104 ARCHIVED | Member Spotlight
- 145 ARCHIVED | Hosting Blog
- 149 JustGiving® from Blackbaud® Blog
- 97 ARCHIVED | bbcon® Blogs
- 19 ARCHIVED | Blackbaud Luminate CRM™ Announcements
- 161 Luminate Advocacy News
- 187 Organizational Best Practices Blog
- 67 everydayhero Blog
- 52 Blackbaud SKY® Reporting Announcements
- 17 ARCHIVED | Blackbaud SKY® Reporting for K-12 Announcements
- 3 Luminate Online Product Advisory Group (LO PAG)
- 81 ARCHIVED | JustGiving® from Blackbaud® Tips and Tricks
- 1 ARCHIVED | K-12 Conference Blog
- Blackbaud Church Management™ Announcements
- ARCHIVED | Blackbaud Award Management™ and Blackbaud Stewardship Management™ Announcements
- 1 Blackbaud Peer-to-Peer Fundraising™, Powered by JustGiving® Blogs
- 39 Tips, Tricks, and Timesavers!
- 56 Blackbaud Church Management™ Resources
- 154 Blackbaud Church Management™ Announcements
- 1 ARCHIVED | Blackbaud Church Management™ Tips and Tricks
- 11 ARCHIVED | Blackbaud Higher Education Solutions™ Announcements
- 7 ARCHIVED | Blackbaud Guided Fundraising™ Blog
- 2 Blackbaud Fundraiser Performance Management™ Blog
- 9 Foundations Events and Content
- 14 ARCHIVED | Blog Posts
- 2 ARCHIVED | Blackbaud FIMS™ Announcement and Tips
- 59 Blackbaud Partner Announcements
- 10 ARCHIVED | Blackbaud Impact Edge™ EAP Blogs
- 1 Community Help Blogs
- Diocesan Blackbaud Raiser’s Edge NXT® Users' Group
- Blackbaud Consultant’s Community
- Blackbaud Francophone Group
- 1 BLOG ARCHIVE CATEGORY
- Blackbaud Community™ Discussions
- 8.3K Blackbaud Luminate Online® & Blackbaud TeamRaiser® Discussions
- 5.7K Jobs Board