New / Edit Query Settings
|Name||Give the query a name. This name will only be visible administratively. The name may be changed at any time if necessary.|
|User||This will default to the user's name but may be changed to another user.|
|Realm||Control access (who can build, run, and edit) to the query using a realm, if necessary.|
|Sharing||Check this setting if the query should be shared with other users who have the query and query base permissions.|
|Folder||Select an existing folder for the query or select 'other' to create a new one.|
|Type||Specify whether the query should use - we strictly encourage the usage of Configurable Joins.|
Configurable joins query bases are organized by category.
|Base||Base determines what each row of the query should represent (e.g. one row per person record, one row per application, one row per form response, etc.)|
Edit Properties Settings
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.
|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.
|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.
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/Prospect) or Activity (Application/Applications). When using Configurable Joins, you can filter for the existence of a specific form response if necessary.
|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 is available when the Retrieve all records each time the query is run execution option is selected. The Queue setting is used primarily in data exports to an external system. The following options are available:
|Application Update||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 Update||Return person 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.|
Primary & 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.
Schedule Export Settings
|Status||Determine whether or not the export should automatically be based on the schedule established.|
|Destination||Select the appropriate endpoint:
|View||Provide the view to be associated with the Materialized View.|
|Endpoint||Provide a secure endpoint for the corresponding Web Service (Remote) server.|
|Headers||Define custom headers for the web service.|
|Connection||Provide a secure connection for the corresponding Custom Server.|
|Path||Specify the path of the file. A forward slash will create a directory (e.g., "sis_exports/export_file_%F.txt" will generate a text file in the sis_exports directory within the outgoing directory.) Use the link at the right of the text box to see the possible date and time variables for the file name. Files with identical names will be overwritten; adding the %FT%T variable (full date and time stamp) makes it highly unlikely that files will be overwritten.|
|Encryption||Select Secure Transfer or PGP Encryption
|Public Key||Available when PGP Encryption is selected as the Encryption type. This would only be necessary if the file is transferred to an external site in an insecure manner (i.e., not SFTP.)|
|Notification||Set the conditions under which a notification email should be sent regarding this query.
|Notify Email||Selecting a Notification will display this setting which accepts comma-delimited email addresses.|
|Format||Format options include:
|Doctype||Specify the doctype for XML files.|
|Null Handling||Specify the inclusion of null elements in XML files.
|Root Element||Provide the Root Element for XML files.|
|Row Element||Provide the Row Element for XML files.|
|Archive||When using the Document Export v2 Format, select the appropriate archive container.
|Document Type||Specify the document type for Document Export v2 format.
|DPI||Select the DPI for TIFF document exports.
|Pixel Depth||Select the color options for TIFF document exports.
|Index Format||Specify the file format for Index files for document exports.
|Index Filename||Provide the filename for the document export index file, if applicable.|
|Delimiter||Determine the delimiter in the data file.
|Headers||Include header row or Exclude header row; specifies the inclusion of a header row.|
|Text Qualifiers||Enable double quote text qualifiers or none.
|Line Endings||Determine the character(s) for line endings.
|Encoding||Specify the character encoding for the file.
|Suppress Empty||Allow or suppress empty files; stops creation of empty files if a query returns no records. Otherwise, an empty file or one with only a header row is created.|
|Requested Delivery Window (Eastern Time)||Determine the delivery window for the automated query execution.|
|Requested Weekdays||Determine the days of the week to run the export.|
This optional configuration prioritizes query exports within a specific Requested Delivery Window.
For example, if you have five queries scheduled to run during the morning window, and one is set to "high priority," three are "normal priority," and one is "low priority," the high priority query will run first, then the three normal priority queries, then the low priority query.