Exporting Gift Amount by Package ID

Carlos Lewis

Team,


I'm having a difficult time finding a way to export gift amount by Package ID. I have the amounts for our Appeal IDs (see below). How do I get Package IDs to show up like this?



 

Thanks!

Aaron Rothberg

Hi Carlos-


In a constituent query, select Summary for Gift and when the Edit Field Criteria dialog pops-up, collapse the GIFT INFORMATION node, expand the PACKAGES node and select your Package Id. You can combine your Package Id with your desired Gift Types under GIFT INFORMATION or your desired Appeal under the APPEALS node.



 

Lisa Slawinski

Carlos Lewis:

Team,


I'm having a difficult time finding a way to export gift amount by Package ID. I have the amounts for our Appeal IDs (see below). How do I get Package IDs to show up like this?



 

Thanks!

Hi Carlos,


Do you mean this?  If not, message me and I'll see if I can walk you through it.  



Lisa

Marie Stark

 

In your export, right click on the Gift Amount, and select Column Heading. Then you can enter whatever you need for the heading, such as CMNCOHSA18.

Ryan Hyde

If you're doing this from a constituent Export rather than a Query, it's a tiny bit trickier. You'll want to create a separate gift query that looks for any gift with the package you want to find. And then in the summary gift amount criteria, go to the Filters tab and attach the query you just created to the Query filter option at the bottom of the list. Then of course name your column so that you know what in the world you're looking at.

Carlos Lewis

Ryan Hyde:

If you're doing this from a constituent Export rather than a Query, it's a tiny bit trickier. You'll want to create a separate gift query that looks for any gift with the package you want to find. And then in the summary gift amount criteria, go to the Filters tab and attach the query you just created to the Query filter option at the bottom of the list. Then of course name your column so that you know what in the world you're looking at.

You all bring up a good point. I have been building my criteria in Query and then creating an Export. Right now I only have one Query setup as follows:


 

Ryan Hyde

You can get all of your per-package totals calculated from either Constituent Query or Export by using the constituent query you've created - that shouldn't be a problem. You'll just need a new "summary gift amount" field for each package, and dial in your settings appropriately. And if you use the Export option, you'll need to create separate gift queries for each package you're looking to get a total for and pipe those into your summary filters. 

Carlos Lewis

Ryan Hyde:

You can get all of your per-package totals calculated from either Constituent Query or Export by using the constituent query you've created - that shouldn't be a problem. You'll just need a new "summary gift amount" field for each package, and dial in your settings appropriately. And if you use the Export option, you'll need to create separate gift queries for each package you're looking to get a total for and pipe those into your summary filters. 

Should I be using the Output tab in my Query versus setting up my output in Export? I was told using Export dedups the constituents. I'm still learning so please excuse my questions.

Carlos Lewis

Ryan Hyde:

If you're doing this from a constituent Export rather than a Query, it's a tiny bit trickier. You'll want to create a separate gift query that looks for any gift with the package you want to find. And then in the summary gift amount criteria, go to the Filters tab and attach the query you just created to the Query filter option at the bottom of the list. Then of course name your column so that you know what in the world you're looking at.

By gift query, you mean Query Type: Gift?


 

Ryan Hyde

The general rule is that you should use Export for this kind of thing. Using output in query can work just fine, though, if you know exactly what you're doing.


Query generates duplicate lines when you're trying to query on or output a one-to-many type of field. For instance, if you're trying to put out Constituent Code, even if it's a specific constituent code, you're going to end up with one output line per constituent per code. 


But if all you're outputting is a series of one-to-one data fields (first name, last name, constituent ID, that kind of thing), then you won't get any duplication. 


Fortunately for you, "summary amount of gifts" is really a one-to-one field. It's essentially a gift query nested inside of a constituent query, and it's only ever going to yield one result per constituent because it's doing just one calculation. So for you, you could output basic constituent info (as noted above) and a series of "summary gift amount" fields and not get duplication.


You can always find out whether or not your query results are showing duplicate rows. The first time you set up your query, don't put anything in output. Go to the results tab. The resulting number is the number of unique constituents. There will never be duplication in that number. Then when you start adding output criteria, you can check your number of rows by looking in the lower right hand corner of your query. If this number ever increases above the initial number of results, you know you have duplicates.


I think it's valuable to understand exactly how this stuff works. Much of the time, it's best just to stick with the rule of thumb that you should only use Export to get informaiton out, but there are actually cases where you can't get particular information out of export but you can get it out of the output tab in a query. So play with this stuff and see what happens when you choose certain criteria and output settings. Doing so will eventually open the door to doing fun stuff like creating dashboards full of the outputs of various constituent and gift queries, which you can use in all sorts of fun ways.

Ryan Hyde

Yep, we typically just refer to that as a 'gift query.' You'll see folks referring to gift query, constituent query, action query, event query, etc., quite often here in the forums. Each of those adjectives relates to a query of one of the specific types availabile when creating a query.

Carlos Lewis

Ryan Hyde:

If you're doing this from a constituent Export rather than a Query, it's a tiny bit trickier. You'll want to create a separate gift query that looks for any gift with the package you want to find. And then in the summary gift amount criteria, go to the Filters tab and attach the query you just created to the Query filter option at the bottom of the list. Then of course name your column so that you know what in the world you're looking at.

^This.I couldn't understand what I was needing to tell RE. With this and help from Lisa, I got the report to our gift officer in time!


Thanks everyone!