Filter Output Fields in Single-Row Query

Note: Don't see this yet? No need to worry. Expect its wider release in a future update.

In a single-row query, you can control which related records appear in your results. When you add a related record type to Output, such as Gifts or Relationships, the query adds it as a node. You can then use that node's Options, Filter, and Sort tabs to decide which related records the node returns and in what order.

How Output Filters Differ from Criteria Filters

In a single-row result layout, filters on the Criteria and Output tabs do different jobs:

  • Criteria filters determine which records the query returns. For example, a Constituent query returns only constituents who match your criteria.

  • Output filters determine which related records appear in the columns for each record the query returns. Each node has its own filters, and they're independent of any filters on the Criteria tab.

For example, you add a Gift Amount filter to Criteria for gifts greater than $5.00. The query returns only constituents who have at least one gift greater than $5.00. If you add a Gifts node to Output without a filter, the node can still return gifts of any amount for those constituents. To return only gifts greater than $5.00 in the output, add the same filter to the Gifts node.

Options, Filter, and Sort for Output Nodes

The tabs and settings available for a node depend on its node type:

  • Options — Settings that control how the node returns records, such as the number of records to return. Gift nodes also include gift node options.

  • Filter — Limits which related records the node includes. For example, a Gifts node can include only gifts to a specific fund.

  • Sort — Sets the order of the records the node returns. When a node returns a limited number of records, the sort order determines which records are returned.

Node type What you can control
One-to-many node, such as Gifts or Actions Number of records, filters, and sort order
Summary node, such as Gift Summary Filters for the records included in the total, average, or other aggregate value
First Gift or Last Gift node Filters for the records considered when selecting the first or last gift
One-to-one node, such as Spouse Nothing. The node returns fields from a single related record.

One-to-many and summary nodes support multiple instances. Add the same node more than once with different filters to return separate sets of related records, such as recent gifts and lifetime gifts.

Add a Filter to an Output Node

  1. Open or create a single-row query.

  2. On the Output tab, select a field from a related record type to add it to the Output list. For example, select Gift Amount under Gifts.

  3. Select the node name to open its settings.

  4. Select Filter.

  5. Select a filter field, then select an operator and enter a value. For more information, see Criteria Operators.

  6. Optionally, select Options or Sort to set the number of records to return or the order of the records.

  7. Save the query.

Some filters are designed for output filters as well as criteria. For example, you can use the Active Dates filter to return only the constituent codes, relationships, or appeals that were active during a date range. Learn how to filter a field using the results of another query in Filter by Another Query's Results.