When a gift is applied towards a particular pledge, the gift inherits the unique ID (called the GUID) of the pledge. Slate stores this unique ID in a field on the gift table called Parent GUID. This association allows two primary conclusions to be drawn:
- Gifts with a value in the Parent GUID field are gifts that have been made in fulfillment of a pledge
- Slate knows which gifts are specifically associated with any given pledge
Utilizing this knowledge and the one-to-one join of Received to Pledged, a query can be created to return one row per gift that is associated with a pledge and the details of the original pledge will be listed as exports.
Querying on Gifts with Pledges
While this type of querying can occur from the Person base, it is better demonstrated by starting with the Gift configurable joins base. To start:
- Click Queries / Reports in the top navigation bar. Click New Query and enter the following in the popup:
- Name - Provide a name for the query, such as "Associating Gifts to Pledges"
- Type - Select "Configurable Joins"
- Category - Select "Related"
- Base - Select "Gift." This will return one row per gift in the query.
- Add a main join to the Person base. Once added, add a few person exports to identify to whom each gift is associated. Add any other person-scoped filters as desired. In this example, the results have been limited to the gifts of Alexander Hamilton.
- Add several Gift Exports, such as the date, amount, and status category of each gift. Add format masks, as necessary.
- Add the following Filters to return only those gifts that have been received and are associated with a pledge:
- Status Category - IN Received
- Subquery Filter
- To return details on the original pledge, a new join must be added. Because each received gift can only ever be associated with one pledge, a one-to-one join can be added that connects each received gift to the correct pledge. Add the Received to Pledged join off of the gift base.
- Once the join has been added, click Export to return the details of the originating pledge, such as the pledge date and amount pledged. Format the exports, as desired.
The final output should resemble the following, with the details of the received gift alongside the original details of the pledge: