Result Layout

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

Result layout controls how records and their related data appear in your query results. Choose a layout based on how you want to view, analyze, or export the data.

You can return results in either a single row or multiple rows.

To save a copy of a query with a different result layout, select Save as in the Query editor.

Single-Row Layout

Single-row layout produces one row per record based on the query type you’ve chosen. Any related values you include in the output appear as additional columns in the same row. When you add related values to a query, each value is automatically named to reflect where it appears in the query structure.

This layout matches the results of database view Export and is useful when you want a flattened, one row per record result set.

For more differences between database view Export and single-row Query, see Compare Export and Single-Row Query.

Note: Unlike Export, Query includes deceased constituent records by default. Review and update your criteria if you need to exclude these records.

Use single-row layout to:

  • Display exactly one row per record

  • Group related values into columns on a single row rather than spread across multiple rows

Example

Name Address 1 Address 2
Robert Hernandez 75 Fourth St 21 Main St
John Smith PO Box 75 19 Elm St

Multi-Row Layout

Multi-row layout produces one row for each combination of selected values. When a record has multiple related values, the system repeats the core record information, one row for each related value, on separate rows.

This layout matches the results of database view Query and is useful when you need to work with individual related records. All queries created in database view are in multi-row layout.

Use multi-row layout to:

  • Display one row per related value.

  • Filter, sort, or analyze related records outside of Query, such as in external applications.

  • Retrieve all related records that meet your criteria.

Notes:

  • Only use this option when multiple rows per core record are expected and acceptable.

  • When Options, Suppress duplicate rows is selected and you only filter or sort on related records, the application combines rows that have identical values.

Name Address
Robert Hernandez 75 Fourth St.
Robert Hernandez 21 Main St.
John Smith PO Box 75
John Smith 19 Elm St.

How Layout Affects Your Results

  • In single-row results, related values are combined into a single row using additional columns.

  • In multiple-row results, the main record is repeated once for each combination of related values included in the output.

The result layout does not change which core records Query returns. It only changes how the records appear. Results are based on the same set of Criteria filters, so Query returns the same base table records for each result layout. The result layout controls how many rows appear in the output and how related records are selected.

Multi-row layout returns related records based on Criteria filters. Single-row layout returns related records based on node counts, filters, and sorts.

Query performs the following order of operations:

  1. Applies Criteria filters to find selected query type records. The filters limit the records that are returned.

  2. Outputs selected fields listed on the Output tab. This is where the difference in Result layout comes into effect.

  3. Sorts the results for the chosen fields based on the Sort values.

Gift node options help customize how gift records appear in results and how gift amounts are calculated. When a gift record includes a soft credit or matching gift, the Soft Credit/Matching Gift flag field identifies what each returned row represents.

Example

In a Constituent query, if you include a filter on gift amounts that are greater than $5.00, Query will only include constituents with at least one gift that is greater $5.00.

When you include related fields on the Output tab, the behavior changes between the different result layout types.

  • Multi-row result layout:

    • If you include Gift Amount as an output field, gifts that match gift-related filters on the Criteria tab appear in the results. In this example, the results include gifts that are greater than $5.00.

    • If the constituent has multiple gifts that match those filters, multiple result rows are returned for that constituent – one for each gift.

  • Single-row layout:

    • If you include Gift Amount as an output field, it involves adding one or more Gifts nodes to the output.

    • Each Gifts node has filters associated with it that determine which gifts are output for that node. The filters are independent of any gift-related filters on the Criteria tab. For more information, see Filter Output Fields in Single-Row Query.

    • Criteria tab filters on related records are only used to determine which query type records are returned. They don’t impact the output of related records.

If a gift also has a soft credit or matching gift, it can return rows beyond those described above, regardless of result layout. The Soft Credit/Matching Gift flag field identifies and controls the type of each returned row.