Query Settings

New / Edit Query

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 strongly encourage Configurable Joins.

Category

Configurable joins query bases are organized by category.

• Records: returns one row per record of the specified type.

• Related: returns one row per instance of the specified object.

• System: returns one row per system object (lookup/metadata).

• Views: returns one row per custom query base

Base

Base determines what each row of the query should represent. For example, one row per person record, one row per application, or one row per form response.

Edit Properties

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.

Schedule Export

See Scheduling Data Exports for more details.

Status

Determine whether or not the export should automatically be based on the schedule established.

Destination

Select the appropriate endpoint:

• Technolutions SFTP: Sends the file to the Slate SFTP site.

• Custom File Transfer: Sends the file to the Slate SFTP or an external SFTP site.

• View (Dynamic): Configures the query as a dynamic view.

• View (Materialized): Configures the query as a materialized view.

• Web Service (Remote): Configures an endpoint that can be called via web services.

• Retargeting: Facebook Audience: Sends a targeted audience to Facebook Custom Audiences.

View

Provide the view to be associated with either the Dynamic or 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. For example, sis_exports/export_file_%F.txt, generates 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

• SecureTransfer: The default

• PGPEncryption: Enter the PGP key to encrypt the data file. This is only necessary if the file is transferred to an external site in an insecure manner (i.e., not SFTP).

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.

• No Notifications

• Failures only

• Failures and late deliveries

• Successes, failures, and late deliveries

Notify Email

Selecting a Notification will display this setting which accepts comma-delimited email addresses.

Format

Format options include:

• Delimited (tab-delimited, CSV, etc.)

• Excel Spreadsheet

• Fixed Width

• JSON

• XML

• Document Export v2

• Document Export v1 (deprecated)

• Document Export Combined PDF

Doctype

Specify the doctype for XML files.

Null Handling

Specify the inclusion of null elements in XML files.

• Omit null elements

• Send xsi:nil="true" for null elements

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.

• ZIP

• ZIP64

Document Type

Specify the document type for Document Export v2 format.

• PDF

• TIFF (multi-page)

• JPEG (first page)

DPI

Select the DPI for TIFF document exports.

• 72

• 96

• 150

• 200

• 300

• 600

Pixel Depth

Select the color options for TIFF document exports.

• 24 (full color)

• 8 (grayscale)

• 1 (black and white)

Index Format

Specify the file format for Index files for document exports.

• No Index File

• Delimited (tab-delimited, CSV, etc.)

• Excel Spreadsheet

• Fixed Width

Index Filename

Provide the filename for the document export index file, if applicable.

Delimiter

Determine the delimiter in the data file.

• Tab  

• Comma ,

• Semicolon ;

• Pipe |

• Caret ^

• Bang !

Headers

Include header row or Exclude header row; specifies the inclusion of a header row.

Text Qualifiers

Enable double quote text qualifiers or none.

• Double quotes "

• No text qualifiers

Line Endings

Determine the character or characters for line endings.

• <Cr><Lf>

• <Lf>

Encoding

Specify the character encoding for the file.

• Unicode (utf-8)

• Western European (iso-8859-1)

• ASCII (us-ascii)

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.

Requested Priority

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 runs first, then the three normal priority queries, then the low priority query.