Problem with more complex query.

Ben Rubinstein

Hi all. First time poster. I have some experience doing simple queries, but I'm trying one a bit more complex and am stumped. I tried using the resources found on this site to build the query, but I'm not getting the results I want. I'd appreciate any help. This is what I'm trying to find.


Criteria: 

Total Amount of Gifts_1 equals greater than $100 (what follows is a filter on that criteria)

Gifts made after 7/1/16. 

AND Gift subtype equals Board Gifts

AND Gift Type does not equal pledge (because otherwise pledges are added to gifts)

AND Fund ID one of (insert several funds)


Output:

I largely mimicked the criteria, but included address & saltuation information. 


Results? I'm getting gifts from all dates. I'm getting Gift TYpe = pledge. 


Can anyone please help? Thank you!

Ben

Steven Cinquegrana 2

Hi Ben,


I can't emulate your problem. (I'm not using all of the criteria you mention, for example, we don't have a "Board Gifts" gift type and I left out the Funds.) I output the gift total and Constituent ID.


Spot checks show the gift data I would expect regarding date range and type. Nothing seems amiss


Can you perhaps post a screen grab of your query criteria and output panes?


Cheers, Steve

Steve Cinquegrana | CEO and Principal Developer | Protégé Solutions

 

Aaron Rothberg

Hi Ben-


I think the problem is caused by your mixing summary information in a Constituent Query and then asking to see data around individual gifts. To complete this task you are going to need to use a Constituent Query to find your constituents and then a Gift Query to find the specific gifts you're interested in.


Here's an example. I just built something similar to your query and my Constituent Query Criteria includes:


Total Amount of Gifts is:

    Gift Date greater than or equal to 7/1/2016

    AND Gift Type is not one of Pledge, Write Off, MG Writeoff, MG Pledge, Recurring Gift

    AND Gift Subtype equals In-Kind


For my output tab I list only one field, Name, and I see the names of 5 constituents that meet that criteria. If I now ask Output to include Gift Type and Gift Subtype, I have a problem. I'm asking query to show me gift information about those 5 constituents and to do that Query is going to list each and every gift each constituent has ever made because Constituent Queries give you lists of Constituents, not lists of Gifts. As a result, I'm seeing Gift Subtypes of Credit Card and Check when I specified in my Summary Criteria to only grab gifts with the Subtype of In-Kind.


Unfortunately, Gift Queries don't offer summary fields so you can't do all of this work in a Gift Query either.


To solve this problem, I would create a Constituent Query using your Summary information. I would only output the Constituent Ids of folks that meet that criteria. I would then use those Constituent Ids in a Gift Query and in the Gift Query Criteria I would include:


Constituent Id is one of <ConsIds you got from the Cons Query>

AND Gift Date is greater than or equal to 7/1/16

AND Gift Subtype equals Board Gifts

AND Fund Id is one of <list Fund Ids>


That will give you a list of gifts from only Constituents that qualified in your Constituent Query Gift Summary and it will list only those gifts that meet your Gift Date, Gift Subtype, and Fund Id criteria.

Joanne Felci 2

Ben,

I would also suggest instead of Gift Type is not one of, I'd use Gift Type is one of and choose all the options besides the ones you do not want


I had a query once that I kept getting wrong info and it had to do with mixing 'one of' and 'not one of' within the same query


Not sure why but it fixed the output


And for output I'd would not do that within the query, I'd do a gift export and within that you can specifiy the gift types you want to export


Good luck!

Joanne

Ryan Hyde

Aaron has a good solution here. There's one other solution though, and it sort of involves thinking differently about the tools you have available to you in RE.


First off, RE purists will tell you that queries are for grouping and exports are for exporting. You should not rely on the query output tab to export data because you can get duplicates if you're not super careful, and unless you know exactly what your'e doing, it may be difficult to realize you've got duplicates. The export tool, however, won't allow duplicates, so it's typicall a cleaner way to get data out. Of course, I'm sure we all break this rule routinely, but when your'e facing a complicated task like the one you describe, you may want to start thinking about using these tools the way they were built. 


Next, any time you want to get data out of RE, you have to identify what the data type is. Here, you're looking for gift information, so you're going to need to use either a gift query or a gift export to get the data out. Trying to get gift info out of a constituent query is pretty much always going to result in you getting data you didn't think you were asking for. What you've done with the query your wrote is you've asked RE to do the following: "give me ONLY constituents who meet these paramenters, and then show me every one of their gifts." Constituent queries are not built to output a limited set of gifts. 


In this particular circumstance, because of the complexity of what you're asking, Aaron's solution is the only one that will really work without having to clean up the resulting spreadsheet. But you still need the intermediary step of exporting constituent IDs, then copying those into a gift query. And since I don't know how your data is set up exactly, there's an off chance (not a high chance, but still) of getting duplicate data out of your query.


So alternately, you could use a gift export (which again will ensure you don't get duplicates, but I don't think your query in this case is likely to output duplicates, unless you split gifts) and limit the gifts by gift date, fund, and gift type. However, you can't limit your output by gift subtype in a gift export (which seems like an oversight on Blackbaud's part). So you'll need to include subtype as one of your output fiends in the export, and then you can just sort by subtype in your output and delete anything that's not what you're looking for. 


Sort of six of one, half a dozen the other, but personally I think it's best to get in the habit of using export to export data, not a query output. 

Christine Cooke 2

Ben Rubinstein:

Hi all. First time poster. I have some experience doing simple queries, but I'm trying one a bit more complex and am stumped. I tried using the resources found on this site to build the query, but I'm not getting the results I want. I'd appreciate any help. This is what I'm trying to find.


Criteria: 

Total Amount of Gifts_1 equals greater than $100 (what follows is a filter on that criteria)

Gifts made after 7/1/16. 

AND Gift subtype equals Board Gifts

AND Gift Type does not equal pledge (because otherwise pledges are added to gifts)

AND Fund ID one of (insert several funds)


Output:

I largely mimicked the criteria, but included address & saltuation information. 


Results? I'm getting gifts from all dates. I'm getting Gift TYpe = pledge. 


Can anyone please help? Thank you!

Ben

I cannot replicate your query because we do not have a Gift SubType like that.  

1.  But in reading what you have -- Not One of... never works like you would think it does -- change it to One Of... and pult all the Gift Types you DO want. 

2.  Query is a grouping tool not a reporting tool, so if a record fits any and/or all criteria more than once, the record will show up multliple times, which is what you are referring to.  In order to get one line per donor, you will need to Export your Query through Export.

 

Ben Rubinstein

Christine Cooke:

Ben Rubinstein:

Hi all. First time poster. I have some experience doing simple queries, but I'm trying one a bit more complex and am stumped. I tried using the resources found on this site to build the query, but I'm not getting the results I want. I'd appreciate any help. This is what I'm trying to find.


Criteria: 

Total Amount of Gifts_1 equals greater than $100 (what follows is a filter on that criteria)

Gifts made after 7/1/16. 

AND Gift subtype equals Board Gifts

AND Gift Type does not equal pledge (because otherwise pledges are added to gifts)

AND Fund ID one of (insert several funds)


Output:

I largely mimicked the criteria, but included address & saltuation information. 


Results? I'm getting gifts from all dates. I'm getting Gift TYpe = pledge. 


Can anyone please help? Thank you!

Ben

I cannot replicate your query because we do not have a Gift SubType like that.  

1.  But in reading what you have -- Not One of... never works like you would think it does -- change it to One Of... and pult all the Gift Types you DO want. 

2.  Query is a grouping tool not a reporting tool, so if a record fits any and/or all criteria more than once, the record will show up multliple times, which is what you are referring to.  In order to get one line per donor, you will need to Export your Query through Export.

 

 

Thank you very much for all of this great info! I learned RE from someone who was also self taught, so obviously knowledge is very lacking. I had never even heard of a Gift Export. I'll look into that now!