The With Function

UNDER REVIEW

The "With" function allows you to build a query using the results of another query or several queries. You can also create several "base" queries that can be used with new queries. If your queries often include a group of constituents, such as everyone who responded to an appeal, you can create an Activity query that includes these names for use in other queries.

Consider the following example:

You need to create a query that includes people who responded to a specific set of appeals, with a gift of $50 or more and excludes any donor who received a specific solicitation letters.

Create an Activity query that includes everyone that gave $50 or more to any of the appeals whose ID begins with OC3CG12. (See an example of this query in the demo database, named "With Query Activity Demo"). This query results in 942 records.
Create a Letter query that includes every who was mailed any of the solicitation appeals whose ID begins with OC3HC0. (See an example of this query in the demo database, named "With Query Letter Demo"). This query results in 66,601 records.
Create the main query. Start by creating a Contacts query. In the Criteria pane, expand the Activity group, and scroll down to the With Activities field. Drag this field to the right pane. When you release the mouse button, the Criteria dialog box will appear. Leave the operator as "Contained In", then click on the variable field box. You should see the following:
Choose the Activity query we create earlier, "With Query Activity Demo", and save the criteria.
Next, expand the Letters group, and select the "With Letters" field and drag it to the right pane.
Since these are the people we do not want included in our final results, choose the "Not Contained In" operator, and select the "With Query Letter Demo" in the variable field box.
Your criteria pane should now look like this:
Now, switch to the output tab, and under the Contacts group, drag the Partner ID field to the right pane. When you click to view Sample Results, your query will result in about 72 records.
You can view a sample of this query in the demo database, named "Linked Query Demo".

The With function is available in Activities, Contacts, Gifts, Invoice Gifts, Invoice Items, Invoices, Letters, Outbound Activity, and Pledges type queries. These fields are identified with a "Linked Query" data type in the Field Definitions tables.

Contact Functions

Within the Contact query type, there is a folder named "Functions". These are specially created by Aegis CRM's design team to help facilitate contact criteria.

ActivityValue2010 - This function will filter records based on the total value of the partner's contributions for the year 2010.
With Donation to Appeal Group - This function will filter partner records based on an Appeal Grouping. Adding it to the Criteria pane will result in the following dialog:

In the Demo Database, there is a Grouping Type named "Program", and the Options are "2007, 2008, 2009, 2010, 2011"
With Letter - This function will filter records based on partners who received an Appeal ID.
With Letter by Package - This function will filter records based on a specific Package within a specific Appeal.
With Letter by Segment - This function will filter records based on a specific Segment within a specific Appeal.
With Letter by Segment and Package - This function will filter records based on a Package and Segment within a specific Appeal.
With Response To Appeal - This function will filter records based on whether a partner responds to a specific Appeal.
With Response to Appeal Group - Similar to the "With Donation to Appeal Group" function, except non-monetary responses will be included in the results.
With Response to Appeal Segment - This function will filter records based on an Appeal Segment, and will include non-monetary responses.

For Each

Queries of the Appeal and Contacts type offer a special "For Each" filter. This feature limits the returned records in your query. The following is an example of how to use this function:

Create an Appeal query. (Check the Sample-For Each Appeal query in the sample database)
In the criteria pane, expand the Activity group, and choose the "For Each Activity Date" field
Drag the field to the right pane, and use the following criteria; "For Each Activity Date is Greater than or Equal to 01/01/2008"
Drag the same field again to the right pane (Make sure you use the "And" operator) and use the following criteria; "For Each Activity Date is Less than 07/01/2008"
Your criteria pane should look like this:
Switch to the Output Tab, expand the Activity folder, and output the Appeal ID field, and the Activity Count field.
Click on the Sample Results icon. The following window will be displayed:
The "For Each" function has limited the Appeals to only those that had activity within the date range you specified. In addition, the Activity Count column only includes Activities which occurred within your date range.

This function is also available in:

Query Type	Query Group	Field Name
Appeals	Gifts	For Each Gift Date
Appeals	Gifts	For Each Gift Value
Contacts	Gifts	For Each Gift In Month/Year
Contacts	Gifts	For Each Gift In Year

Miles From Point

Within the Contacts type query, in the Contacts folder there is a field named "Miles From Point". This feature allows you to query for all partners within a given area, based on miles from an address.