Querying on Pledges and their Gifts

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:

1. 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. 

2. 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. 

3. Add several Gift Exports, such as the date, amount, pledged amount, and status category of each gift. Add format masks, as necessary. 

4. Add a Filter to return only pledges:

   • Status Category - IN Pledged

     

     The total number of rows being returned in the query should now be equal to the number of pledges. In this example, that is the total number of pledges on Alexander Hamilton's record.

5. 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. 

6. Configure the subquery export as follows:

   • Name - Provide an easy to understand name, such as "Gifts associated with pledge"

   • Output - Select 'Concatenate'

   • Row Separator - Add a comma, pipe, or other deliminator to separate the details of each gift that will be associated with the pledge

   • Join - Add the join "Pledge to Received"

     

7. 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. 

   

8. Add any filters as desired, such as displaying only the hard credits associated with the pledge. 

   

9. Click Save. 

The final output with all of the gifts applied towards each pledge contained within a single subquery export should resemble this: