How To Pull Certain Constituencies through Query or Export

Options

Hello.

Suppose I have two constituents, John Doe and Michael Fox, who are members of the Board of Directors (and BOD is their primary constituent code). They are both current parents (so their second constituent code is CPAR with a Date From of 05/01/19). Now John Doe's child just graduated, so I add a new constituent code, PPAR for Past Parent, with a Date From of 05/31/23 and a blank Date To. I kept the CPAR code but added a Date To of 05/31/23. Michael Fox remains a true current parent.

If I want to pull all Current Parents from RE, I can pull a list based on the Primary Constituency. But what if there are other current parents whose primary constituency is BOD (just like Michael Fox), how can I pull Michael Fox without pulling John Doe?

Can I pull this only if I depend on the Date From/To information? I only ask because we have not necessarily made good use of the Date From/To fields in our database (some are left blank).

Thank you.

Comments

  • @Bernard Nemis
    I personally won't have a constituency code of Current Parent. I would have PAR and the constituents who are current would not have a to date. The past parents would have a to date filled in. The date from/to use helps you to keep track of and properly report proper status as far as constituent codes go. If we have multiple constituent codes on a record, the primary is on the first line of the constituent codes and the rest follow according to the hierarchy in the constituent code of the record. You can set up your constituent code table using your hierarchy with the most important (for us Alumnus/ae) down to your least important. That way when constituent codes are assigned to new records or added to existing records the sort order of the table helps with the code assignment. We do a lot of campaign reporting based on constituent codes so we do constituent code cleanup regularly to ensure the accuracy of our records.

  • JoAnn Strommen
    JoAnn Strommen ✭✭✭✭✭
    Ancient Membership Facilitator 4 Name Dropper Photogenic

    @Bernard Nemis Alex's post should get you what you need.

    Additional comment about end dates. You will need to figure out what works for you and your org. Do you want every record to have a valid constituent code for today's date? What Annette described regarding primary/hierarchy is correct.

    If you use end dates for codes like ‘current parent’/'faculty' and that is the only code on the record they will not have a valid code when you pull some reports. For that reason, we do use codes such as ‘former faculty’ with the date from that is the date to of their ‘faculty’ code.
    So a record might look like this:
    Former faculty 6/ 1 / 2023 (open date to)
    Faculty 8/ 1 /2018 6/ 1 /2023

    The primary constituency is also the default gift constituency. If there is no open constituency when gift is entered the gift constituency is blank and that also can affect your reporting.

    Just some things to consider.

  • @JoAnn Strommen

    Thank you, everyone. So based on what I gathered from all of you is, to continue with my plan of doing a historical list of all constituencies and continue to leave the Date To field blank if that is still a valid code. If not, then I need to add a Date To value. Since those non valid codes will still get pulled by a query, I have to make good use of the Date From/Date To values. Which leaves me to the conclusion that I have to go back to my old records and make sure I globally add dates on them.

    Thank you, all!

  • Alex Wong
    Alex Wong ✭✭✭✭✭
    Ninth Anniversary Facilitator 4 Name Dropper Photogenic

    @Bernard Nemis
    A lot of folks already mentioned, but when I replied, didn't exactly go through what we do as an org.

    For example, we currently have a National Board Member constituent code, and a Past National Board Member constituent code, and we do not use Date from/to. So as long as we properly keep these 2 code used on the right constituent, we can simply query one or both of these code to get the list of constituent we need. We also have error checking to ensure no record has both.

    This is not neccessary the “better” way to go, as we lose the information on when the donor became national board member or stop being one. This current way of recording is “lazy” and efficient if you don't care for the “when”. However, if you do need to know in a query-able manner, then use 1 code, and utilize Date from/to will be better.

  • JoAnn Strommen
    JoAnn Strommen ✭✭✭✭✭
    Ancient Membership Facilitator 4 Name Dropper Photogenic

    The whole date thing is interesting. We have many codes on records with no dates whatsoever. As long as we use the hierarchy correctly, IMO, no dates is easier to deal with than dates.

    I inherited a db of alumni that does not even have the date from on their primary code of alumnus/a. Was quite surprised. A project to go back and put their graduation date is near the bottom of my list of clean up projects.

    What you may want to do @Bernard Nemis is take a half dozen records (real or test) and put in codes with and with out dates. Run a couple queries/reports/exports. See what you get. It may help you decide what will work best for your org.

    Best wishes on your project.

  • @JoAnn Strommen
    Thank you. Quick question, if you are not using the date fields, I go back to my original post… how do you pull Michael Fox w/o pulling John Doe? I thought the dates will prevent you from pulling constituents with an invalid constituent code from the query, if the date to is expired. Currently we do have a hierarchy of codes, but only on paper. I am planning to implement that in how we enter them in RE. I do want to ask you how placing the codes in hierarchy in BIO2 can be useful in pulling out only “true" current parents.

  • JoAnn Strommen
    JoAnn Strommen ✭✭✭✭✭
    Ancient Membership Facilitator 4 Name Dropper Photogenic

    @Bernard Nemis If I'm thinking clearly (long week already LOL) you should be able to pull using a query criteria of constituency equals CPAR and date to is blank.

    I'm trying to think of something I could test in my db. Try that, if it doesn't work. Post and we'll figure out another option.


  • @Alex Wong
    Hello, Alex. Thank you. That is currently what we do now. I am being advised that the more traditional way is to keep history of the codes in their records.

  • @JoAnn Strommen
    You are correct! I tested it. Adding the Date To = blank in the criteria gives me a true list of CPAR! That means I will really have to rely on the Date From/To. I just want to create a plan on how to update existing data. Thank you. Hope the week gets better.

Categories