Constituent Queries To-Go, Part 1: How to Query Constituent records based on Summary Giving and Including Soft Credits
Published
This will be a multi-part blog with 4 separate entries all about Constituent Queries! Part 1 in this series will cover how to query on Constituent records based on summary giving and including soft credits.
Your boss wants a list of donors who have given at least $1000 total of cash in the past fiscal year and they want soft credit included as part of that total. Where do you start?
Your first decision is: what type of query do you need? One way to think about it is that Query is like a Party and the type of query is who (what type of record) is being invited to the party. In this example, your boss wants to find a group of Constituents, so it’s a party for Constituents and we’ll create a Constituent query.
The second step is: set up the criteria. We need to find Constituents who have given at least $1000. This means they could have given $500, $200, $300 separately and we would want them in the results, so we need to use the section in query for Summary Information.
Summary fields in a Constituent query are different from other types of criteria. When you select Total Amount of Gifts, a new window appears to edit the field criteria, but this window has 2 tabs and we need BOTH of them to get the results we want.
Set the operator on the Criteria tab to greater than or equal to and enter the value of 1000. Then switch over to the Filter tab of the same window. We need to add some additional criteria INSIDE this Summary Field. We’ll add filters for Gift Date between 7/1/2014 and 6/30/2015 (the dates of the last fiscal year) AND Gift Type one of Cash, Pay-Cash, MG Pay-Cash and Recurring Gift Pay-Cash. Your filter tab will look like this:
You may wonder why we had to specify these gift types. Not all gift types are included in a Summary field within a Constituent query. Pay-Cash and MG-Pay Cash are not included by default, so we have to tell the query to consider those gift types in its calculations. There is a nice link at the top of the Summary Field that will help you remember which gift types are included.
Once you click OK on the Summary field criteria window, it will be on the filter side of your criteria with a small plus sign next to it. If you click the plus sign, you can see the filters that are INSIDE the Summary field.
OK, so we have our criteria now, but we still have another step: include soft credits. Your boss wants Soft Credit included in this calculation. To do that, we need to click Tools > Query Options > Gift Processing tab and make the selections there.
We want to select Soft Credit to Both and Use amount in grid. So if I have given a $500 cash gift, have a $200 Soft Credit for another gift and a third gift of $300 towards my ongoing Recurring Gift, my combined total is $1000 and I will be in the results of this query.
These Knowledgebase articles can help you with other queries that are similar to this one.
This covers how to query on Constituent records based on Summary Giving (and including Soft Credits). In the next post of this 4-part blog series (coming next week), I’ll discuss how to query on Constituent records that have a specific zip code or a range of zip codes. You won’t want to miss it!
To learn the basics of query, take the Raiser’s Edge: Fundamentals-Query 1 class. To take your query knowledge to the next level, take the Raiser’s Edge: Query 2 class.
Your boss wants a list of donors who have given at least $1000 total of cash in the past fiscal year and they want soft credit included as part of that total. Where do you start?
Your first decision is: what type of query do you need? One way to think about it is that Query is like a Party and the type of query is who (what type of record) is being invited to the party. In this example, your boss wants to find a group of Constituents, so it’s a party for Constituents and we’ll create a Constituent query.
The second step is: set up the criteria. We need to find Constituents who have given at least $1000. This means they could have given $500, $200, $300 separately and we would want them in the results, so we need to use the section in query for Summary Information.
Summary fields in a Constituent query are different from other types of criteria. When you select Total Amount of Gifts, a new window appears to edit the field criteria, but this window has 2 tabs and we need BOTH of them to get the results we want.
Set the operator on the Criteria tab to greater than or equal to and enter the value of 1000. Then switch over to the Filter tab of the same window. We need to add some additional criteria INSIDE this Summary Field. We’ll add filters for Gift Date between 7/1/2014 and 6/30/2015 (the dates of the last fiscal year) AND Gift Type one of Cash, Pay-Cash, MG Pay-Cash and Recurring Gift Pay-Cash. Your filter tab will look like this:
You may wonder why we had to specify these gift types. Not all gift types are included in a Summary field within a Constituent query. Pay-Cash and MG-Pay Cash are not included by default, so we have to tell the query to consider those gift types in its calculations. There is a nice link at the top of the Summary Field that will help you remember which gift types are included.
Once you click OK on the Summary field criteria window, it will be on the filter side of your criteria with a small plus sign next to it. If you click the plus sign, you can see the filters that are INSIDE the Summary field.
OK, so we have our criteria now, but we still have another step: include soft credits. Your boss wants Soft Credit included in this calculation. To do that, we need to click Tools > Query Options > Gift Processing tab and make the selections there.
We want to select Soft Credit to Both and Use amount in grid. So if I have given a $500 cash gift, have a $200 Soft Credit for another gift and a third gift of $300 towards my ongoing Recurring Gift, my combined total is $1000 and I will be in the results of this query.
These Knowledgebase articles can help you with other queries that are similar to this one.
- How to query on soft credits
- How to query on constituents based on their total giving within a date range
- How to use Summary Information query filters
This covers how to query on Constituent records based on Summary Giving (and including Soft Credits). In the next post of this 4-part blog series (coming next week), I’ll discuss how to query on Constituent records that have a specific zip code or a range of zip codes. You won’t want to miss it!
To learn the basics of query, take the Raiser’s Edge: Fundamentals-Query 1 class. To take your query knowledge to the next level, take the Raiser’s Edge: Query 2 class.
News
Raiser's Edge® Blog
10/23/2015 12:12pm EDT
Leave a Comment
Diane is the best!