Former Constituent Code still being included in queries

Beth Rudich · September 5

I imagine that best practice would be to use start and end dates for a constituent code, and be able to track people's connection to our organization over time. The problem I run in to is that my queries are still including people who no longer actively have that constituent code. For example: if Robert Hernandez was a volunteer from 1/1/25-9/1/25, then became a member of the board on 9/2/25, if I pull a query today (9/5) of my volunteers, I would expect him to be left off. That is not the case. Am I doing something wrong?

Until now, I have been sure that each constituent has only one constituent code, and any thing prior gets moved to the notes. But my concern is that this is not best practice. I appreciate any advice.

Carlene Johnson · September 5

In query you need to use end date to get only folks who are currently active with that Cons Code. So in this instance you'd ask for Cons Code = Volunteer AND Date To is Blank in order to exclude anyone who is no longer an active volunteer.

In webview when using Lists, it will prompt you to indicate a date that you wish for the cons code to be active which is really helpful!

I'm a big fan of Cons Code Start & End dates and find they work really well as long as you are consistent with them.

Dariel Dixon · September 5

https://community.blackbaud.com/discussion/comment/306526#Comment_306526

@Carlene Johnson, I think the issue with start and end dates in constituent codes is that there is a learning process of how to query and report on them. This does take time and you have to rewire your thinking to get it to work. @Beth Rudich's thinking is how people think and it makes sense, but remembering to think differently for the query is hard. And remembering to do so consistently is hard as well, but it does get easier over time.

Carlene Johnson · September 5

https://community.blackbaud.com/discussion/comment/306531#Comment_306531

@Dariel Dixon, you are correct! When I first started my career I was at organizations that didn't use dates. When I switched to an organization that had a need for dates I found setting up the system and switching my thinking around queries was challenging. Once I grasped the principals behind it and started using dates consistently it eventually became second nature.

I also can't be in a conversation about Constituent Codes without including two of the best resources out there. These two resources are from @Bill Connors and should be mandatory reading for anyone thinking about Constituent Codes:

Best Practices in the Use of Constituent Codes (Blackbaud K12 Conference 2019 presentation slides)

“Former” Constituent Codes? (post to fundsvcs)

Joe Moretti · September 8

A better solution instead of dealing with dates, is to have a constituent code of FORMER VOLUNTEER, that way, he would not have pulled and you would not have to worry about dates. We do this for Staff and Board Members. Makes like much more easier.

Angel Lawrence · September 8

Completely agree with Carlene and Joe. Either include "End date is blank" in your query criteria or create a separate constituent code for formers (the later will require some updating/cleanup/maintenance).

Beth Rudich · September 8

Thank you all for the feedback. It sounds like we would need to update every query then to include "end date is blank" or risk inaccurate pulls. Joe, we do have a code for former volunteers, as well as former board, but there are other relationship definitions that might apply which has me rethinking this approach. I appreciate the help!

Carlene Johnson · September 9

https://community.blackbaud.com/discussion/comment/306618#Comment_306618

I recommend the use of end dates AND "former" Cons Codes as people can move in & out of constituency codes. Example:

Code

Date From

Date To

Trustee

7/1/2025

Former Trustee

7/1/2014

6/30/2025

Trustee

7/1/2012

6/30/2014

Carlene Johnson · September 9

https://community.blackbaud.com/discussion/comment/306631#Comment_306631

@Beth Rudich

Not sure why I can't edit my previous comment, but here is a better image of how the Cons Code table should look:

ConsCode	DateFrom	DateTo
Trustee	7/1/25
Former Trustee	7/1/2014	6/30/25
Trustee	7/1/2012	6/30/2014

This also works really well for tracking any codes which folks move in and out of: Current Parent/Past Parent, Employee/Former Employee, Volunteer/Former Volunteer, Current Student/Former Student (withdrawn), etc.

Hope this helps!

Former Constituent Code still being included in queries

Answers

Categories