Person-based Gift Receipting

Gift receipts and acknowledgements can be created and tracked on a per person basis. Leveraging query execution modes, as well as subquery comparison operators, a record can be both in the history of the receipt run, and also be returned in new runs when a new gift (or gifts) is created. 

This method of execution on a person-based versus a gift-based acknowledgement query provides several benefits. Namely, records who have split gifts among multiple funds will receive a single aggregated receipt in lieu of a receipt per individual gift. 

Step 1: Create the Receipting Query

To create the receipting query, follow the steps outlined below:

  1. Click Queries / Reports in the top navigation bar
  2. Click New Query
  3. Enter the following configuration in the popup window:
    • Name - Provide an intuitive name for the query
    • Type - Configurable Joins
    • Category - Records
    • Base - Person
  4. Click Save

Once established, the query execution options should be modified to retrieve all records each time the query is run and save the results history. To modify these settings:

  1. Within the query, select Edit Properties 

  2. Enter the following configurations in the popup window:

    • Limit Rows - Leave blank

    • Execution Option - Select "Retrieve all records and save result history"

    • Fetch Behavior - Ensure "Preserve where clause on fetch if required by one or more filters" is selected

    • Queue - Leave set to "None (default)"

    • Secondary Key - Leave blank

  3. Click Save

Query Execution Mode

Step 2: Add Filter to Find New Gifts

Since the execution mode of the query is looking to return all records each time the query is run, a filter must be added to control for only returning records for which a new gift (one that hasn't been included in a previous query run) exists. Because a person may have have multiple gifts that meet this criteria, a subquery filter must be added:

  1. Add a new Subquery Filter 
  2. Add an intuitive name to the filter (e.g. "Has New Gift Since Last Query Run")
  3. Add a Join to the Gifts table
  4. Add the following Exports:
    • Gifts Created Date
    • System Current Query Run Timestamp Start 
    • System Current Query Run Timestamp Stop
  5. Add any necessary exports to further refine the types of gifts for which need receipting (i.e: hard credits, received, amounts above a certain amount, etc.)
  6. Configure the remaining elements as follows:
    • Type - Dependent subquery
    • Aggregate - Comparison
    • Field 1 - Gifts Created Date
    • Operator - between
    • Field 2 - System Current Query Run Timestamp Start
    • Field 2 End Range - System Current Query Run Timestamp Stop
  7. Click Save

Filter Settings

 

 What does this filter do?

This filter allows Slate to find only those records where a gift was created between the time the query is executed and when it completes its execution. This allows for the tracking of subquery criteria - enabling the same record to be returned in the query as long as the subquery criteria (like a gift) is new and hasn't been included in a previous query execution.

Step 3: Add Exports for Use in a Merge

With the filter added, Slate is now returning only those records where a new gift has been created since the last time the query was run. Exports must now be added to both properly identify both biographical information on the donor as well as return detailed information only on those gifts that were created since the last query run.

To return detailed information on new gifts, a subquery export must be added. This export will need to be filtered on the exact same criteria as the filter on the overall query in order to ensure only the appropriate gifts are returned. 

  1. Add a new Subquery Export
  2. Add an easy to understand name to the export, such as "Gift Details"
  3. Add a Join to the Gifts table
  4. Add a second Join from Gifts to Funds
  5. Add any exports that will be referenced within the receipt. For example:
    • Gift Date
    • Literal (:)
    • Gifts Amount
    • Literal (--)
    • Funds Name
  6. Configure the remaining elements of the subquery export:
    • Type - Dependent subquery
    • Output - Concatenate
    • Row Separator - \n (this will produce a line separated list of each gift that meets the filter criteria)
    • Row Offset - 1
  7. With the basics of the exports added, a subquery filter must be added to ensure only the newly created gifts are displayed as exports. Add a Subquery Filter

  8. Add the following exports:

    • Gifts Created Date

    • System Current Query Run Timestamp Start

    • System Current Query Run Timestamp Stop

  9. Configure the elements of the subquery filter as follows:
    • Name - Provide an intuitive name, such as "Gift Created"
    • Type - Dependent subquery
    • Aggregate - Comparison
    • Field 1 - Gifts Created Date
    • Operator - between
    • Field 2 - System Current Query Run Timestamp Start
    • Field 2 End Range - System Current Query Run Timestamp Stop
  10. If desired, add any additional filter criteria for the gifts (such as credit type, amount, etc.)
  11. Click Save in the pop-up window.
  12. Click Save

Subquery Export

Subquery Filter within Export

 

 Why do I see all the gifts when previewing?

The filters that were added reference the query run. Since the query hasn't actually been run (a preview has been generated), the results won't appear as configured. When the query is executed, only the appropriate gifts will be returned.

Step 4: Execute the Query and Merge to Letter

With the query properly configured, the query can be run and the results will be saved for future reference. After each query run, the number of matching rows should return to '0' and increase as new gifts are created. The query execution is an audit event and will be displayed on a person's audit log, ensuring they received a receipt for a particular gift.

Subquery Filter within Export

The results of the query may be merged into a Word document in order to assist with the sending of the receipt. To export the records to Word, follow the steps below:

  1. Select Run Query

     

  2. Select the new row that was added to the results history

  3. In the Output dropdown, select Mail Merge Word Document

  4. Select Export

  5. Upload a Word document to serve as the receipt template.

  6. Click Export.

Export to Word

Within Word, leverage the query exports as merge fields within the document.

Example Output - Setup Example Output - Merged

Briefcase an example of this query into your Slate database:

51fe7fbf-5ce8-65b0-8cef-46ae43628ac0@slate-advancement-showcase
Was this article helpful?
2 out of 2 found this helpful

Comments

0 comments

Please sign in to leave a comment.