Embedded Dataset Row Queries
  • 19 Apr 2024
  • 6 minute read
  • Dark
    Light
  • PDF

Embedded Dataset Row Queries

  • Dark
    Light
  • PDF

Article summary

An embedded dataset row query is a special type of query embedded on a dataset dashboard that displays rows of queried data.

Some examples of common embedded dataset row queries are:

  • Applicants from an organization

  • Occupants of a residence hall room

  • Community spaces in a building and their maximum occupancy

An example embedded dataset row query showing residence hall rooms’ current occupants.

Overview

There are two steps to embedding a dataset row query on a dataset record:

  1. Creating the dataset row query, including some configurations that let Slate pull the query data to the dataset record

  2. Associating the query with the dataset record

☑️ Prerequisites

This article presumes you have existing familiarity with the following Slate tools:

Step 1: Create the Dataset Row Query

Design the related data snapshot to be displayed on the record

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

  2. Click New Query. Give the query a Name.

  3. Click New Query. A pop-up appears.

  4. Configure the following settings:

    • Name: Give the query a descriptive internal name

    • Sharing: Select Share query with other users with the query and query base permissions

    • Folder: Select Other and enter "System" in the field that appears. Then, select Other and enter "Tab" in the subfolder field. You must follow this step as written, even though a System folder already exists: selecting the existing System folder will not prompt the subfolder menu to display, as the System folder does not normally allow subfolders.
      Type: Configurable Joins

    • Base: Select a base from the lists that will display the information you want to see. In many cases this will be the Person or Application base, but it could also be Organization Contacts, CBO Contacts, Residence Hall Rooms, Community Spaces, or another base.

  5. Click Save. You will be redirected to the top-level page of the query where you can make additional changes.

Tip

Remember the difference between the Person and Application query bases. Person queries return one row per person, while Application queries return one row per application, which means there could be multiple rows for one person if they have more than one application that meets the query criteria.

After saving the new query, you may see the following warning message when viewing the Edit Query popup:

The selected folder does not support subfolders. The subfolder currently set will be removed upon saving and may result in the unintended activation of this resource, so you may want to change the folder if desired.

You may safely disregard this message for this task.

Add Parameters

A parameter is used to pass the ID of the record currently being viewed to the embedded query. This limits the query results to only those related to the selected record.

To create the parameter:

  1. Click Edit Query.

  2. Click Edit Web Service on the right side. The ‘Edit Web Service’ pop-up will open.

  3. Enter the following in the Parameters field:

    <param id="record" type="uniqueidentifier" />
  4. Confirm the Edit Web Service pop-up appears as below, then click Save.

Add Joins and Exports

Exports added to the query will determine the columns displayed when viewing the query on the record’s Dashboard.

For some exports, it may be necessary to add joins to the query. For example, a query with an Application base requires a join to Person to export the First and Last name of the Person associated with the Application. Joins make the connection between the base of the query and the record on which the data will be displayed. There are two types of joins: one-to-one and one-to-many.

If the joins are one-to-one, like Person to Dorm Room or Organization Contact to Organization, add the appropriate joins from the query’s base to the type of record where the query will be displayed. For example, to create a query displaying a list of prospects who have the associated Organization listed as their Rank 1 School, create a base join from Person to School by Rank Overall (setting Rank = 1), and from School by Rank Overall to Organization.

If the joins are one-to-many, or if you desire more granular control over when a record appears in a query, use a subquery filter. For example, if you want to display a Person record on all companies and foundations at which they have worked, create a subquery filter with a join from Person to all Jobs, and from Jobs to Companies and Foundations.

Once you have added any desired or needed joins, add your desired exports. While any number of exports can be added as needed, there is a limit to the space that a dataset record can occupy on a computer screen, and thus a limit to the space that a query can occupy on the record as well.

Tip

As with regular queries, column widths can also be adjusted for embedded dataset row queries. This is done by specifying a specific pixel width or width as a percentage on the corresponding export parts within the Query Builder. To define the width, double-click the export and enter the desired value in the Display Width setting:

Filters

Slate relies on the parameters from Step 1b and a specific filter to determine what information is relevant to the currently viewed record.

To add the needed filter:

  1. Select the GUID filter for the record on which the query will be displayed, then click Continue. The ‘Edit Part’ pop-up window will open.

    • For example, if the query will be shown on Organization records’ Dashboards, locate the filter for Organizations > GUID. If the query will be shown on Employer records’ Dashboards, locate the filter for Employers > GUID.

    • If you employed a subquery filter while building your query, it may be necessary to add this filter within your existing subquery filter.

  2. In the ‘Edit Part’ pop-up window, add the parameter @record where prompted for ‘GUID’. This filters down to only records related to the record on which we are viewing the query. Click Save.

    Edit_Part.png

  3. If desired, add additional filters to your query to further limit your results.

Step 2: Associate the Query with the Dataset Record

After creating your query, create a new custom field that allows the query to be associated with the desired dataset.

To create a custom field:

  1. From the main navigation, select Database.

  2. Under Records and Datasets, select Fields.

  3. Click New Field. A pop-up appears.

  4. Configure the following settings:

    • Status: Active

    • Scope Category: Records

    • Scope: Select the dataset you will display the query on. For example, Dorm Rooms.

    • ID: Enter a unique, computer-friendly field ID. For example, “res_hall_room_dataset_query”.

    • Name: Enter a descriptive name for the rows to be displayed, such as “Room Occupants.” The name appears as the title of the table.

    • Folder (optional): Keep fields organized by selecting a folder. You can create a new folder by selecting Other and entering a name.

    • Category (optional): The category will be displayed internally when adding the field to Forms.

    • Field Type: Dataset Row Query

    • Query: Select the query you created earlier.

  5. Click Save. This is necessary to enable settings used in subsequent steps.

  6. Locate your newly-created field in the Fields tool, then click on it to re-open the Edit Fields pop-up window.

  7. Select the Display tab, then use the following settings:

    • Tab: Dashboard

    • Order (optional): If multiple embedded dataset row queries are added to a dataset, use the Order setting in each of the fields to control what order the queries display on the dataset page.

  8. Click Update.

  9. Refresh the Fields cache using the link in the bottom right corner of the screen.

Don’t see the Tab setting?

The Tab setting will only appear after saving your field for the first time. Save your field, then locate it within the Fields tool and click on it to edit it. The Tab setting should appear under ‘Display’ when editing the field.

Review

Once you’ve completed the steps above, confirm your query is working as expected by navigating to a record’s Dashboard tab. The query will display as a table, similar to a query results page, as shown below:

A search box is available within the table for performing a text search on the list. The maximum number of rows that can be displayed at a time is 100; active links for "Prev" and "Next" will display on the table to allow browsing on lists exceeding 100 rows. A maximum of 500 rows (5 pages) are loaded on embedded dataset row queries; to view additional relevant records, it’s necessary to create and execute a query within the Query Builder.

Table.png


Was this article helpful?