• All Files

Advanced Query Sample

UNDER REVIEW

As an example, we will create a query based on the following question:

"We need to know how many of our partners that received our March Announcement Letter
have given at least three gifts in the past 6 months that totaled more than $1000,
or were added to the system in the last three months and have given at least $100."

Note:  Since the sample database is static, we have to make a few adjustments in regards to dates.  To illustrate the following sample we will assume that today’s date is 1/1/2010.  Generally, queries that are created for continual use will often have the "Adjust over time" check box checked to maintain proper date ranges.

None of the samples in the help section will have this box checked, but in normal use you will often utilize this feature.

Since this request is asking for donors based on a previous mailing, the March Announcement Letter, we will need to create a separate query to retrieve the proper records, and then refer to that query in our new query.

Note:  For more information on this, as well as other Special Query Functions, Click Here.

  1. First we will create a query that finds all the people that received the letter.  
  2. Create a new query, name it XX-March Announcement Recipients (XX represents your initials)
  3. Choose "Letters" as the Query Type
  4. Insert "Constituents who received March Announcement Letter" in the description field
  5. Save the query
  6. Switch to the Criteria tab, and choose "Appeal ID" under the "Appeals" folder, and drag it to the criteria pane.
  7. Use the "Is equal to" operator, insert the appeal id (DemoLetter) in the value field
  8. Switch to the Criteria tab, and choose "Constituent ID" under the "Contacts" folder, and drag it to the output pane
  9. Click the "Sample Results" icon.  The query should return just over 34,000 records.
  10. Save the query.

Now, we will create the main query.

  1. Create a new query, and name it "March Letter Analysis"
  2. Choose "Contacts" as the Query Type
  3. Insert "March Letter Analysis" in the description field
  4. Save the Query
  5. Switch to the Criteria tab, click on the Plus(+) sign next to the "Letters" folder, and drag "With Letters" to the right pane.
  6. Aegis CRM will present you with the following dialog box, containing all of the letter queries in the database.
  7. Choose the March Announcement Recipients query you created earlier and click "Save".
  8. Next, expand the "Gifts" folder, and drag "For each gift date" to the right pane, releasing the mouse over the existing criteria.  Choose the "Greater than or equal to" operator, and enter 7/1/2009 in the date value field.  This action will limit the gifts included to the specific date range.
  9. Next, under the "Gifts" folder, drag the "Gift Count" field to the right pane, releasing the mouse on the red ampersand.  Choose the "Greater than" operator, and enter 2 in the value field
  10. Next, under the "Gifts" folder, drag the "Sum of Gift Value" field to the right pane, releasing it on the red ampersand.  Choose the "Greater than or equal to" operator, and enter a value of $1000.  
  11. Next, under the "Gifts" folder, drag the "Sum of Gift Value" field to the right pane, releasing it on the red ampersand.  Choose the "Greater than or equal to" operator, and enter a value of $100.  
  12. Next, under the "Contacts" folder, drag the "Date Joined" field to the right pane, releasing it on the red ampersand.  Choose the "Greater than or equal to" operator, and enter 7/1/2009 in the value field.  
  13. Click Save.
  14. Your criteria pane should now look like this:

  15. Aegis CRM has certain characteristics which allow the user to manipulate complex criteria.  For example, a line item that is dragged to the red ampersand will automatically be moved to the last entry in the group.  Right-click and hold on "Sum of Gift Value is Greater than or Equal to 100", and drag and release it on the red ampersand.  Notice that this line has now moved to the last entry, as below:

  16. Obviously, at this point for a record to be included in your output, it would have to meet all of the criteria listed. Now is the time to use the "Or" operator.
  17. 14. Drag the fourth line ("Sum of Gift Value Is Greater than or Equal to 1000") onto the line immediately below ("Date Joined Is Greater than or Equal to 7/1/2009") and release the mouse.  Notice that now a new group has been created with these two items, identified by a blue "OR".

  18. Next, drag the line "Sum of Gift Value Is Greater than or Equal to 100" onto the first line of the new group ("Date Joined Is Greater than or Equal to 7/1/2009") and release the mouse.  The criteria pane should now look like this:

  19. Note that the Date Joined criteria, and the Sum of Gift Value criteria has been grouped together under a red ampersand, and the Gift Count line is alone under the blue "OR" symbol.  Hover your mouse over the first ampersand to view the complete criteria, which should look like this:

  20. Save the query, move to the output tab, and add the following fields:
    1. Contacts - Partner ID
  21. Click the Result Count icon.  You should receive approximately 220 records.

 
 

Copyright ©2017 Aegis Premier Solutions