Constituents Who Have Only Given 1 Gift

Spencer Smith

What is the best way to query constituents who have only given 1 gift and to a specific fund? I don't want to include constituents who have given to other funds just 1 gift and to this fund? I am thinking this is will need to be a series of queries. Any thoughts are appreciated.

Tara Dustrude

Hey Spencer!


I think this will probably take a total of 4 queries but each one should be fairly simple. The logic here is similar to like a LYBUNT, you're looking for a group of people but then you want to exclude people that meet an additional set of criteria. To do this, we need to identify both groups and subtract one from the other. You may need to play with it a bit when you see your results but here's what I'm thinking...


Query 1

Starting Query: Base/All Constituents

Data Return Type: Journal Entries

Criteria Matching: Match all criteria

Criteria: Funds - mark the Fund you are looking for


Query 2

Starting Query: Query 1

Data Return Type: Journal Entries or Accounts (depending on what you want in the end)

Criteria Matching: Match all criteria

Criteria: Cumulative Number Transactions - Equal to 1


Query 3

Starting Query: Base/All Constituents

Data Return Type: Accounts

Criteria Matching: Match at least one criteria

Criteria: Funds - mark all the Funds except the one you picked in query 1


Query 4 - This one should be a Compound query!

Data Return Type: Journal Entries or Accounts (if this one is Journal Entries, query 2 should also be JE)

Interaction: Subtract

Query 1: Query 2

Query 2: Query 3


It's very important we keep that Cumulative criteria in a separate query than the Fund for it to work correctly, so definitely don't combine queries 1 and 2 into one. And, when we subtract we want to subtract the whole Account and not just the Journal Entries, so the data return type of query 3 definitely needs to be Accounts. But I think what we're doing here is


Query 1 - Finding all the gifts to that Fund

Query 2 - Finding all the gifts or donors with ONE gift to that fund

Query 3 - Finding all the people who donated to other Funds

Query 4 - Subtracting the people who donated to other Funds from the people who donated one gift to the Fund you want to report on.


I hope this helps!


Tara

Karen Tuecke

This is fantastic Tara Dustrude‍. Thank you!!

Spencer Smith

Tara,


Thank you for all of your thought into this reply. I am wondering if my post is in the wrong section. I was thinking Raiser's Edge related but I am wondering if your reply is more on the finance end of things since you reference Journal Entries and Accounts. It may not be. I am hung up on what to do in Query 4. You say not to merge queries 1 and 2 into 1. But, I am wondering if I should. That becomes a query and then a second query is created of 2 & 3. This is then subtracted from Queries 1 and 2.


Thanks for clarifying.

Tara Dustrude

Spencer,


I'm sorry! I thought you were talking about eTapestry rather than Raiser's Edge. A similar idea may work for RE, but queries in RE are different than eTapestry. I would suggest heading over to the RE page and asking over there to get someone more familiar with Raiser's Edge queries. This link should get you to the right place: https://community.blackbaud.com/products/raisersedge7.


Good luck!