query - not constituency or constituency and over

@Ryan Moore This is where I would use Query List. Start with two queries. The first should be anyone who does not have the Constituent Code of Monthly Donor. The second would be anyone who has the constituent code of Monthly Donor with an end date. Then, go to Query List. Add the first query. Then add the second. If you need to remove anyone (such as people with certain solicit codes, etc.), you can also remove from the list. When you're finished, you will have a static query that you can use with Export, Reports, etc.

@Ryan Moore
i do think your single query should work, so have you tried it and hasn't work for you?

@Ryan Moore
i understand you have other conditions, but like any complex criteria-ing, you should start with your questionable criteria first and see if it is giving you want you want first. then add on the rest.

might be helpful if you provide the rest of your criteria needs

@Joe Moretti:

@Ryan Moore My question to you is why use Monthly donor or donor as a constituent code, since that could be figured out by donor giving and also why go thru the hassle of constantly changing that constituent code. My opinion is using those as constituent codes is not the best practice and could cause issues down the road.

If there was an emoji to stand up and clap and shout, I would use that. I have never once seen a database where a “monthly donor” constituent code was accurate. Same goes for codes like “Major Donor”, “Donor", or “Prospect”.

I'm sure there are some people who monitor and maintain this carefully, but I have not yet met them. Thanks for elevating this topic!

@Joe Moretti it is exactly the people who do not understand db maintenance who request this type of const code. grrrrr

@Faith Murray I completely understand. I know it works for some organizations and is a recognized part of the update process. I personally have just never seen it work well, and whenever I encounter this kind of model, I discourage it.

@Brian Soucie I think we both agree that ALL records should have a consituent code, there should never be a case where a record does not (we have it set where a constituent code is required). BUT a constituent code should not be something based on money, since that is something that is then hard to manage, when giving history can be used for segmentation. I have been working with Raiser's Edge for 28 years in various non-profts and the first thing I always fix up if need be are the constituency code. As far a segementation, gift history can be used as well as various attributes. Why use a code of “Donor” when if a person gives money, it is already know they are a donor. Constituency does should be generalized. Codes such as Board, Staff, Former Board, Goverment, Foundation, Corporation, Friend of the Non-Profit (a general term for those who do not fit into the other ones). Then if you need to go deeper, create attributes. But using codes that are about money is not the way to go, since giving history already tells that story. And like I said before, who is going to manage a code like that. If you use a constituency code of “prospect”, are you going to remember to change that once someone give money. (more than likely not, since I have cleaned up many of those in the various organizations I worked for.

While I mostly agree with what Joe and others in his position said, I also advise against using “tag” (constituent code or attribute) for information that already exists in gift details. However, I would provide the pro/con and let executive decide.

For those that are new to being db manager/admin for RE, the consideration is going to be:

1. No tag used (no constituent code, no constituent attribute/custom fields)
   1. We can create a query/export that has the $10K+ but less than $25K, $25K+ but less than $100K, and $100K+.
   2. Pro: nothing extranous anyone needs to do, report is ran, look for your donor. If fundraiser wants to know from constituent record/webview page, they will have to look at the giving history, and do some quick math (if only a few gift) or export the gift and sum up (if there are dozens of gift)
   3. Con: well pretty obvious, not easy to see quickly what level the donor is at
2. Use a tag (constituent custom field)
   1. Requires running of the previous steps' said query/export and do a global add/change or import to tag the constituent as SILVER/GOLD/PLATINUM, this will need to be ran daily/weekly/monthly etc to keep things up to date
   2. Pro: fundraiser can easily tell someone is at a particular level (with the lag time of the refresh) without having to run query/export and lookup the constituent or do math
   3. Con: resource intensive depending on interval of refresh, someone from CRM/DB team will have to do this work manually

These 2 options are what I used to present to my exec when they ask. Since migrating to RE NXT, and proficient myself with SKY API and SKY Add-in. I now present them with a 3rd options, which in my opinion is best of both world BUT has its own CON (time/money).

If you interested, you can read more about it here: https://community.blackbaud.com/forums/viewtopic/147/66741#p262518

@Brian Soucie:

@Karen Diener @Joe Moretti

Would I be right to think that both of you disagree with the notion that all records must have a constituent code? If you don't believe in use of ‘Donor’ to describe why the constituent is in the DB, do you use something else or rely only on the gift data?

I tend to agree with @Faith Murray that a ConsCode can be an effective way in summarising constituent engagement for use in segmentation and reporting. But I'm definitely curious about other perspectives since I'm about to clean-up a ConsCode mess from years of inconsistent use based on no formal guidelines or definitions.

I generally think that all records should have a constituency code, but I would never use Donor. A lot of people say that constituencies can be useful for segmentation, which is absolutely true, but segmenting “donor” seems odd to me in practice. That information is constantly changing, so changing codes to match donor levels seems like busywork that I would not want my staff to undertake. And if someone donated 10 years ago, is “donor” still a useful code?

I'm not saying I have all of the answers for every organization. I was just trying to illuminate where I tend to see consistent problems across organizations I have worked with. Constituent codes are easy and obvious for some nonprofits, but not for all of them.

Just fyi that I am unlikely to continue responding in this thread. I think these are good topics for future reference but am not interested in reading or writing long responses nor do I want to continue this hijack of the original post. It starts to feel argumentative and sometimes defensive, and as I've said, I completely understand that constituent codes are useful for segmentation. I never said they weren't. I think the challenge for each organization is to figure out what is useful for them, which is not going to be the same for everyone. I always look for efficiency since most organizations are overwhelmed with data management, and setting up codes that need constant monitoring and changes to other data does not strike me as efficient. That's all.

@Ryan Moore - Adding to Alex's post to say that “option 2” (tagging with a const code or attribute) can be managed automatically through Power Automate and updated on any frequency you want. Our jobs as DBAs are changing, so that the mindless-busy-body tasks can be outsourced to automation where a ‘robot’ can do that work for you.

@Faith Murray I have been working with RE queries for a decade and never knew I could nest parentheses! Thank you for giving me a new very valuable tool ?

@Jill Freidmutter
I think I may have seen something simliar on Community before, but you will have to try searching for it, not that easy to find stuff on Community through search. Otherwise, try checking the template showcase. (I don't remember where the template showcase is, maybe @Heather McLean or @Erik Leaver can help you with a link.

There is also going to be a Power Automate User Group tomorrow at 12-1:30 EDT: https://community.blackbaud.com/events/item/66/4148 which I will be going over this “3rd option”