When you create a query and select Edit Query → Edit Properties, you’ll find the following settings:
Limit Rows
Restrict the number of returned results by configuring a row limit. This setting is typically used when configuring a query for:
Call list
If an external system can only handle a certain number of incoming records.
Execution Options
Retrieve all records each time query is run
All records that meet the query criteria will be included in the query results each time the query runs.
The execution history is not saved for queries that are set to Retrieve all records each time query is run. This includes queries that use the Update queue settings (as they require that particular execution option).
Retrieve all records and save recent result history
All records that meet the query criteria will be included in the query results each time the query runs. Additionally, the history of the query results are saved for one year, so records that met the query criteria for previous query executions are visible.
Set Interaction will display when this option is selected.
Retrieve only the new records since the query was last run
When the query runs, only records that were not included in the previous query results will appear. The history of the query results are saved.
Set Interaction will display when this option is selected.
Important Considerations For Retrieve and Save History Execution Modes
Use of the “Retrieve all records and save recent result history” and “Retrieve only the new records since the query was last run” execution modes should be carefully considered and implemented only when your process requires a list of which records ran on a given date. It is important to note that these execution modes do not store query exports, only the ids of records that met the query criteria. As such, this tool is not appropriate as a point in time auditing tool for exports and should not be used for that purpose. Excessive use of these execution modes can lead to database bloat and slowness.
Switching between “Retrieve all records and save recent result history” and “Retrieve only the new records since the query was last run” will change the behavior of “saved” query runs. After switching the execution mode, you will be able to view the existing row count of query runs, but will not be able to open them to view results.
📝 Note
Set Interaction does not work with Form Response Bases. When aiming to utilize this feature, you will want to use the base related to the interaction (Person) or activity (Application). When using Configurable Joins, you can filter for the existence of a specific form response if necessary.
Fetch Behavior
Preserve where clause on fetch if required by one or more filters
If any of the filters in the query have the 'Preserve Where Clause' setting set to "Yes," the WHERE clause generated in the query is preserved each time the query results are retrieved. Most commonly used.
Always preserve where clause on fetch
Tells the query to always preserve the filters when viewing the query results, including viewing an individual run history. Records that may have initially met the criteria and were exported may no longer meet the criteria due to an update to the record made after the first run and therefore no longer show up in the run history.
Queue
The Queue setting is used primarily in data exports to an external system. The following options are available:
None
The default option.
Application
Return application records that have been updated since the last time the query ran. This pulls in any application in an active period after an update to the application or to the person record associated with the application.
Person
Return person records that have been updated since the last time the query ran.
Decision
Return decision records that have been updated since the last time the query ran.
Primary and Secondary Key
The record GUIDs (globally unique identifier) are used in tracking queries to track which records are included in a query on a particular date and time.
Inserts, updates and deletes to decision, school and address records are considered as updates to both person and application records. Person updates also trigger an application update.
📖 More about query queues
Updated records do not appear in query results immediately when using the Queue options.
When a record is updated, it is flagged and added to the Deferred Update Queue (as is done with rules). When rules run, all records in this queue are added to any Update Queue queries.
As a result, you might have to wait up to 15 minutes for the updated records to display in query results. Please keep this in mind for testing.
💡 Tip
When using this type of export for the SIS, consider changing the Disable Update Queue setting on SIS import source formats to "Prevent records from entering update queue upon import." This helps prevent records from being constantly put back in the queue.