General practices for coding K-12 constituencies

Alana Bianco Cruz

Hi all!


I recently came into a K-12 database where they use constituency codes to identify the connection one has to the school, i.e. Parent, Student, Alumnus. Then they use attributes to define the connection, i.e. Type of Parent = Current/New/Returning, etc... Because a good population of the database has more than one tie to the school, there are sometimes three or four constituency codes on one file.


The issue I'm finding with this are-


1) There is no historical data.

-If you were a student and graduate, your student tags are replaced with alumnus.The other problem with this is pulling data from past years. Since the tag is lost, the data doesn't pull for historical queries.


2) Having multiple constituencies creates issues during query pulls. 

-Because the constituency codes also designate hierarchy, to pull a parent, who is also a trustee, who is also an alumnus, can get very dicey.


3) When someone's status changes, you have multiple areas to update and change.

-While an understandable practice, it seems a bit redundant when both areas reflect the same information.


I would like to generalize constituency codes to distinguish a person vs a business/foundation/etc.. and leave the defining categories to attributes, but I'm new to the school world (previously working for a non-profit membership database), and I would love to hear how others in this environment represent this data and how it works for you.


Suggestions, ideas, or advice would be welcome. Thank you!

 

Faith Murray

Being a school, your alumni are one of your most valuable demographics. I can't imagine not marking them in the Constituent code. However, as you point out, there are a lot of varieties in the word "Alumnus" that really needs tracked on the side. Our org, also, uses a Constituent code and an Attribute for our Alumni. Constituent codes (ref. Bill Connors) are supposed to demonstrate how the constituent is connected to you - it sounds like your setup is overall correct. But maybe there are some areas for improvement by simplifying or re-configuring the present system?


Have you tried splitting your Parent cons code into Alumnus Parent and Student Parent? Then you could eliminate the Parent Type Attribute.


Do you use start and end dates in your cons codes to retain historical data? (We don't, but many orgs find it very useful.) Then you could keep both Student and Alumnus cons codes on the record, with the appropriate dates.


What if you combined the Alumnus/Student cons code into one and instead used the "Status" drop-down menu in the Education window to delineate "Current Student", "Terminated", and "Graduate"? A new Education record under Relationships for each time a student returns, with the most advanced degree becoming Primary, would track the historical aspect.


Maybe you could create a custom report to accomodate those instances where you need to report on non-primary constituent codes?


Just a few thoughts!


 

Faith Murray

PS. Your idea of tracking things more by Attribute will probably work. We used to do things that way. Just make sure all of your reports work the way you want them to. The Comparison and Summary canned report will compare any and all constiituent code types simultaneously, but will only report on one attribute type at a time: it will not compare a Student attribute with a Parent attribute, or an Alumnu attribute donor with a Foundation constituent code donor. In order to use the report to compare, say, how much Alumni gave vs. Foundations, you would need to either 1) run the report twice, or 2) create a single Attribute with a drop-down table Description for each donor type - at which point, you have basically made a constituent code attribute.


Schools frequently care a great deal about how their alumni perform in donations, event participation, etc., so as long as your new setup has full reporting functionality, then it's basically whatever setup works best for you.

Christine Cooke 2

Alana Bianco Cruz:

Hi all!


I recently came into a K-12 database where they use constituency codes to identify the connection one has to the school, i.e. Parent, Student, Alumnus. Then they use attributes to define the connection, i.e. Type of Parent = Current/New/Returning, etc... Because a good population of the database has more than one tie to the school, there are sometimes three or four constituency codes on one file.


The issue I'm finding with this are-


1) There is no historical data.

-If you were a student and graduate, your student tags are replaced with alumnus.The other problem with this is pulling data from past years. Since the tag is lost, the data doesn't pull for historical queries.


2) Having multiple constituencies creates issues during query pulls. 

-Because the constituency codes also designate hierarchy, to pull a parent, who is also a trustee, who is also an alumnus, can get very dicey.


3) When someone's status changes, you have multiple areas to update and change.

-While an understandable practice, it seems a bit redundant when both areas reflect the same information.


I would like to generalize constituency codes to distinguish a person vs a business/foundation/etc.. and leave the defining categories to attributes, but I'm new to the school world (previously working for a non-profit membership database), and I would love to hear how others in this environment represent this data and how it works for you.


Suggestions, ideas, or advice would be welcome. Thank you!

 

wowie.  Being a school, alums, parents and parents of alums are in the top of the list of those most important relationships.  I have worked in four schools and we have always kept the current and past affilations in Constit Codes so that one can see the through line/history.


Examples:
Parent-Current- the From Date is when the first child started at your school and End Date is never entered because you have a relationship forever moving forward
Parent-of Alum - after graduation of last child, the Constit Code changes from Parent-Current but the Date From stays the same
Student-Current - Date From is the date they started school with you (is linked to their parents and siblings, and any other relations, i.e. grandparents, aunts/uncles/cousins through Relationships)   One of the key relationships is Education and the Primary Education should be Your School and what their Graduation year would be -- in your case I would suggest that there would be three and whichever one they are currently matriculating in, or the one they are in when they leave is the Primary of the three.  I would be utilizing the Date Entered and Date Left Left, the Class of for Lower School, Middle School and High School. 

You are absolutely correct that keeping attribiutes that are changed will not give you the story.  And it will be a huge challenge to pull reports, lists and analysis.
Alumni  - after they graduate the Student-Current becomes the Alumni Constit Code.  The Date From stays the same with no To Date becaue the relationship is forever.  The Education Relationship should have a Date Graduated and a status of Graduated vs. Withdrawn or Expelled. 


In the high school level, we also kept track of clubs, and awards etc. on the Education Attributes, noting what school year they participated in which activities.  This info becomes important in the future when you want to build a new theatre or have a reunion of the basketball or baseball teams, or fund a new gym or field.


Your Consit Codes - you mentioned have a hierarchy assigned, as they should.  And each record should reflect that hiearchy so that their Primary is at the top of the Const list on their Bio2 so that your trail into Gift Constit Codes reflects the same hierarchy and therefore your reporting can.


I have a whole bunch of stuff I have written for myself over the years on how to make this work and not drive yourself insane.  lol.  You you would like to talk or I can share docs PM me. 

 

Amruta Dnyanmote

Hi Christine,


I have a question about arranging the hierarchy of constituency codes. If I want to arrange/rearrange constituency codes for all the records, what will be the optimal way? For the individual record, I find NXT is an easy option. But looking for a bulk change options.


Regards,

Amruta

Christine Cooke 2

So -- you want to rearrange/arrange the hierarchy because it has not been consistent?  You are recoding everyone?  It's a big project with multiple layers in a school setting.


You will need to establish what code was and/or is their primary constituent code during any given period of time.  For parents, they would be a current parent for the time in which they had one or more kids enrolled.  Trustees will be during their time on the board.  Are you going to use to and from dates on all?  or just the codes?

Once you have established the date windows you will have to change Gift Constituent Codes to reflect the primary code at the time the gift was received if you are going to pull any sort of year over year historical reporting or comparisons.


Constituent Codes are not a bulk or global change option without completely knocking out what each person has on their record completely,  And that would be bad.  This will be something you will have to do in groups.  You can decide if you want to utilize imports and exports to achieve this or not.  


I feel like there is a lot more to say, but that it is based on what exactly you are trying to do.  If you would like to take this conversation offline you can email me directly at ccooke@charlesarmstrong.org.  It's not the first time I've had this conversation for sure.


Hope this at least gives you more to consider.