Use of Constituent Codes

Hi Matthew-


As you know there are a number of ways to accomplish this and each one will have limitations. If I were going to track this data and I planned to use Export for most of my outputs, I think I would create two organizations; Current Lottery Players and Former Lottery Players. I would track current players with relationships to one org and track historical data with relationships to the other Org. You could opt to create only one Org and use Start and End dates to show who is active and who is not. If you went with one Org you could further segment the data by creating specific relationship types such as Current Player and Former Player. I think, however, this might get confusing in that during Exports you'll need to focus on which relationship type you want for each export (Current versus Former) and you'll also have to constantly update the start date of your org in the Export field with today's start date each time to run it so as not to pick up former members that have an end date before today's date.


So, instead, going back to my two Org examples, I would create two organizations; Current Lottery Players and Former Lottery Players. I would add a relationship between my Current Players and the Current Players Org. Once their play-period is over, I would delete that relationship entirely, but copy the start and end dates into a relationship with the Former Lottery Players Org. By doing this, when you export data you'll never have to worry about the relationship type and you'll never have to worry about updating start dates in your Export field. All you'll have to do is focus on the Org in question to get former or current players. You'll also be able to export multiple relationships to the Former Org if you want to review someone's play history.

Hi Matthew!


Our organization had a similar problem with employee giving levels. Sometimes they would go back and forth between two giving clubs, and we weren't sure how to track it. Our current process is to use start and end dates AND add new lines to Bio 2 with the constituent code. Someone's profile may look like this:


John Smith

Acorn Club Member 01/01/2003-12/31/2003

Anniversary Club Member 01/01/2004-12/31/2010

Acorn Club Member 01/01/2011- blank (current)


We love this method. We can do a quick rundown of the history this way. We used to have a code that was "Former ---", but we found it became confusing if they bounced back and forth. We found this prevents the constituent code table from getting cluttered, because we just utilize the standard list instead of creating new ones. However, we query a LOT, and that's why we use this method. It organizes it in a sort of timeline in query for us.


I hope that helps! I'm interested to hear advice on this as well.

Membership has an Attributes Tab, so you could add multiple Lottery IDs there.  If you don't already have the module, you would probably want to start by getting the pricing for it.  Not sure the module would be worth the cost unless you're using it for something else as well.

 

Happy to help! Let me see if I can cover all of your questions here. So, you CAN query on specific constituent codes and blank end dates. If we keep using the John Smith example, I could query on "Constituent Code = Acorn Club Member" AND "Constituent End Date = blank". That way, it would only pull up the ACTIVE Acorn Club member constituent code. You can do it the opposite way to search for the inactive constituent codes as well.


We often use merged queries to exclude past memberships from mailing lists. For example, we are sending a mailing to INACTIVE Acorn Club Members to see if they will renew their membership. If we query on inactivity, you are right - John Smith would appear and seem to be inactive. However, because we use the multiple const. code method, we have two dynamic queries for the active & inactive memberships (constituent end date = blank & not blank). THEN we do merge queries (SUB) operator. We use the inactive query as primary, and the active as secondary. John Smith would then be removed from the final mailing list!


I'm sorry if this seems complicated, but we find it helpful since we don't have the membership or event modules. If anyone has an easier way to do this, we'd LOVE to hear it. Great question overall, Matt. Best of luck!

Matthew Page:

 

KaLeigh Hurley:

Hi Matthew!


Our organization had a similar problem with employee giving levels. Sometimes they would go back and forth between two giving clubs, and we weren't sure how to track it. Our current process is to use start and end dates AND add new lines to Bio 2 with the constituent code. Someone's profile may look like this:


John Smith

Acorn Club Member 01/01/2003-12/31/2003

Anniversary Club Member 01/01/2004-12/31/2010

Acorn Club Member 01/01/2011- blank (current)


We love this method. We can do a quick rundown of the history this way. We used to have a code that was "Former ---", but we found it became confusing if they bounced back and forth. We found this prevents the constituent code table from getting cluttered, because we just utilize the standard list instead of creating new ones. However, we query a LOT, and that's why we use this method. It organizes it in a sort of timeline in query for us.

Thanks KaLeigh and sorry for the slow reply - I was away on holiday last week, so everything's been a bit out of hand between when you replied and now.


So how do you query on this then? In the above example how do I find all the people who are "inactive" currently? (If I query on "constituent code" and "Date to is not blank" then John Smith would flag up, wouldn't he? Do you do something else?


Also I'm never sure how query works if I'm trying to find someone who has various constituent codes with a blank end date. If John Smith was still inactive, but he had another constituent code without an end date can you make query only find those who have a blank entry for one specific Cons Code, rather than just any?


Thanks again,


Matt

 

Testing is always great! I highly recommend taking the query courses through Blackbaud University. They have changed our work experience tenfold. You can play around in the test database with no consequences.


We test in our live database all of the time too. Just make a few test constituents (We have Mr. & Mrs. Sally Hamburger, for example). I highly recommend marking them with a TEST solicit code or const. code. It will be easier to find, edit, and even delete. Good luck!

Matthew Page:

Thanks for taking the time to help with this KaLeigh.


On the first thing, I guess I'm just never really confident that it works OK. I should probably just test and test and test till I'm confident, but at the moment as much as what you've said seems right I'm also wonder "so what would I do if I wanted to find everyone who has had that constituent code at some point and has any open constituent code?". 

- I just need to get oer it don't I?


On the second, I guess thath's how I'll have to do it, but I'm always a bit loathe to use static queries (which is what the merge does, ultimately). I now you can always refresh the static ones, but...


Anyway, thank you. I think your reply certainly nudges me in a certain direction which is really helpful,


Matt