A dataset row query enables related data to appear on a dataset record. For example, an Organizations dataset record could also show a list of applicants currently attending that institution. The dataset row query allows users to see a snapshot of relevant data or statistics regarding each record right on the record itself, without needing to create a separate query and run it every time from Query Builder. The dataset row query is first created with Query Builder. The query is saved in a subfolder under the main System folder, which lets Slate associate or “embed” the query in dataset records. Dataset row queries can be used with custom datasets as well.
- Click Queries / Reports in the top navigation bar.
- Click New Query. Give the query a Name.
- Make sure to check "Share query with other users with the query and query base permissions".
- You must save the query in a System folder and Tab subfolder. To assign the query to a folder:
- Select Other from the main folder list.
- Enter “System” into the text field.
- Select Other from the subfolder list.
- Enter “Tab” into the text field.
Important!
Follow the above step even if the System folder already exists. Selecting the existing System folder from the dropdown will not prompt the Subfolder dropdown to display, as the System folder does not normally allow subfolders.
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.
- Select Configurable Joins from the Type list.
- Choose a Category and 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, Church Contacts, or another base.
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.
- Click Save.
Add the parameter into the query settings. 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:
- Click Edit Query.
- Click Edit Web Service on the right side.
|
|
- Click Save.
Add exports to the query to denote the columns to be displayed on the embedded query.
This step may require adding 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.
Adding Joins
Make the connection between the base of the query and the record on which the data will be displayed. This can be done in one of two ways:
-
If the joins are one-to-one, like Organization Contact to Organization, add the appropriate joins from the query’s base to the type of record where the query will be displayed at the bottom of the query.
For example, if this is a query displaying a list of prospects who have the associated organization listed as their rank 1 school, create a join from Person to School by Rank Overall, 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.
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 pixel widths on the corresponding export parts. To define the pixel widths, edit the export part, and append the pixel width to the name, for example, Entry Term:100px.
-
Add a filter to the query (if joins are on the overall query), or to the subquery (if joins are in a subquery), for the GUID of the record on which the query will be displayed. This will most likely be a filter for the Organization, CBO, Church, or Company/Foundation record GUID.
-
Add the parameter @record to the filter. This filters down to only records related to the record on which we are viewing the query.
The final step is to create a new custom field that allows the query to be associated with the desired dataset.
To create a custom field:
- Click Database in the top navigation bar and select Field.
- Insert a new field with the following settings:
- Dataset: Select the dataset with which to associate the query, such as Organizations.
- ID: Provide a unique field ID.
- Name: We suggest providing a descriptive name of the rows being displayed, such as “Applications.” The name will also appear as the title of the table.
- Navigate down the list of settings to Data Type. Set the Data Type to Dataset Row Query. Then, return to the Tab setting. Select Dashboard.
- Value: Set to Store Value.
- Query: Select the desired query saved in the System / Tab folder.
- Click Save.
Tip
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.
The query will display as a table, similar to a query results page, on the dashboard tab of a dataset record:
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.