Query on Gift Increase

Chandrabarna Saha
Chandrabarna Saha
in Blackbaud CRM Developer Discussions

Hello! I want to generate a query that will help me identify a group on donors who have made a gift in a specific period (I have Batch numbers) and have increased their gift amount from their last gift.

What would be the best way to get this result?
TIA!

Tagged:

Answers

  • Aaron Thompson
    Aaron Thompson
    Third Anniversary Name Dropper Facilitator 1

    Hi -

    You can probably do this using 2 smart fields, and building a query comparing them (return records where A > B)

    First build a smart field using the 'Constituent Revenue Application Amount' type. For this one, you can use Largest Gift Amount. Use the 'Application selection' option at the bottom to build a query of the people who gave to those batch numbers. This will give you the largest amount (if they had more than one) given that you want to compare.

    Smart field 2 is similar, but opposite. Build it on the same smart field type. But in this case, you will use Latest Gift Amount to get their last gift. Again use 'Application selection' to build a query of revenue that is NOT part of those batches (effectively, everything else). You may also need to include only revenue prior to a certain date range, or revenue type, designations, etc. How you define the revenue to consider will determine exactly how that query is structured.

    Final step is simply to build a query outputting both of these smart field values. In your criteria, select records where Smart Field A > Smart Field B. That should do the trick, I think!

    Hope that helps!

    Thanks,
    Aaron

Categories