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 the gifts that have been made in fulfillment of a pledge to be grouped together and queried upon.
Utilizing the one-to-many join of Pledge to Received as part of a subquery export, the details of gifts made in fulfillment of a pledge can be returned and associated directly alongside the details of the original pledge.
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 Pledges to their Gifts"
- Type - Select "Configurable Joins"
- Category - Select "Related"
- Base - Select "Gift." This will return one row per gift in the query.
- Add a main join from 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, pledged amount, and status category of each gift. Add format masks, as necessary.
- Add a Filter to return only pledges:
- To return all of the gifts associated with each pledge, a subquery export must be added. By adding the subquery join of Pledge to Received, all of the potential gifts associated with each pledge can be displayed.
- Configure the subquery export as follows:
- Add gift exports from this join, such as the date and amount. Format the exports as desired, and add any literals to assist with the display of the information.
- Add any filters as desired, such as displaying only the hard credits associated with the pledge.
- Click Save.
The final output with all of the gifts applied towards each pledge contained within a single subquery export should resemble this: