MENU
    Query Elements
    • 25 Apr 2024
    • 3 minute read
    • Dark
    • PDF

    Query Elements

    • Dark
    • PDF

    Article summary

    The Query Builder is used to isolate records that meet specific criteria. Some of the queries created in Slate will be used continually as part of business processes, while others can be used just once to accomplish a non-recurring task. Choose New Query when the query should be saved for future use, or choose Quick Query for ad hoc queries.

    Query Type 

    Quick Query

    1. Click Queries / Reports in the top navigation bar.

    2. Click Quick Query

    3. Pick a Type and a Base for the query. Configurable Joins bases are grouped by Category (Records, Related, and System) so if you do not see the specific base you are looking for, you may need to switch to a different Category.

    The query base determines what each row of the query results will represent. For example, a query on the Application base will return one row per application, while a query on the Person base will return one row per person record.

    While the base selected will begin to define the query results, you must apply filters to limit the records appearing in the query results more precisely.

    Save Query

    Saved queries can be used to find the same records regularly to accomplish a recurring business task. Additionally, saved queries can easily be shared with other Slate users.

    1. Click Queries / Reports in the top navigation bar.

    2. Click New Query

    3. Enter the following configurations in the popup window:

      • Name the Query.

      • User - Select a User. This setting will default to the current user.

      • Sharing - Check the Sharing check box to share the query with other Slate users who have the appropriate permissions.

      • Folder - Save the query in a folder and, optionally, a subfolder.

      • Population - Choose a population for the query.

    📝 Note

    You cannot change the query base once the query build is started.

    Quick Query Options

    Quick Query Options 

    Run Query

    Once exports and filters are added, click Run Query to display query results.

    Preview Results

    See a sample of query results before running the query.

    Display SQL

    View the SQL generated from adding exports and filters.

    Copy

    Save a quick query for future use.

    NOTE: When saving a Quick Query, update the folder destination. 

    Exports

    Add Exports 

    Export

    Click Export to begin defining the data (i.e., the columns) that appear when the query runs.

    Literal

    Click Literal to add a static value as an export column. Every row in the query results will have the same static value in this column.

    Formula

    Click Formula to produce calculated values based on a defined formula.

    Custom SQL

    Click Custom SQL to add a custom export column. This option is used for more complex data exports. In many cases, Configurable Joins removes the need for custom SQL exports.

    Existence

    Select Existence to return a value based on whether or not the record meets certain criteria. On Configurable Joins query bases, Existence exports are created using subqueries.

    Subquery 

    Configurable Joins query bases include the option to add subquery exports.

    Filters

    Add Filters 

    Filter

    Click Filter to restrict the population of records to be exported. Add as many filters as needed.

    Custom SQL

    Click the Custom SQL button to add a custom SQL filter. This option is used for more complex data exports. In many cases, Configurable Joins removes the need for custom SQL filters.

    OR, NOT, ), (

    When necessary, add OR and NOT logic operators. You must use parentheses with logic operators.

    Subquery

    Configurable Joins query bases include the option to add subquery filters.

    Joins

    Joins enable you to join or combine data from multiple tables or sources in a flexible and customizable manner by creating relationships between data sets, consolidating information from multiple sources into a single view, and manipulating the joined data to meet your specific needs and provide options for you to select which columns to include, and specify join conditions.

    There is seldom a reason to make a one-to-many join at the main level of a query. For instance, if you wanted to exclude records by making a main join from "Person" to "Tags", Slate would randomly retrieve one tag set for that record - that tag may or may not be the "Test Record" tag. In that case, we would use a subquery filter that joins from "Person" to "Tags" so we can look at all tags associated with a person record:

    Sorts

    Add Sorts 

    Sort

    Sort query results based on one or more data points.


    Was this article helpful?