Important! Configurable Joins Query Bases
All new queries should be built using Configurable Joins. To get started with querying with configurable joins, refer to:
Slate is a relational database, and due to the one-to-many relationship between prospects and the many other types of data collected, it is important to first identify the kind of data you're looking to query on or affect. Query bases, as the name suggests, are the representation of what to base a query on. In the context of the query builder, another way to think of query bases is "what should each row of my query represent?"
Query bases often have filters and exports associated with them. However, it's also possible that a particular query base has a related base from which it inherits additional filters and exports. For example, if you have an applications query base, not only do you get filters and exports for application data, but it also inherits filters and exports from the related Prospects query base.
Person Record Query Bases
The most-used query bases are those associated with person records.
Relation to the Prospects Base and Application Base
| Query Base | Prospects | Applications | Description |
|---|---|---|---|
| Prospects | N/A | Returns one row per person. If a person has multiple applications, they'd only appear once in this query. For convenience, persons who have a tag of "Opt-Out" are excluded. | |
|
Prospects (including opt-outs) |
Yes |
|
Returns one row per person. If a person has multiple applications, they'd only appear once in this query. |
| Applications | Yes |
N/A |
Returns one row per application. If a person has multiple applications, they'd appear multiple times in this query; once per application. |
| Prospects and Applicants | Yes |
Yes (Only for Rank 1 Application) |
Returns one row per person. The Prospects and Applicants query base is useful if you are querying for person records and also want to include data from the most recent application (Rank #1), if that exists. It is important to note that if you do not need to look at application data, then you should use the Prospects query base. If you want to only see data for applicants, you should use the Applications query base. The Prospects query base has filters that allow you to limit results based on records that have applications, and the Applications query base has a filter that allows you to limit results based on the application rank. |
| Addresses | Yes |
|
Returns one row per address |
| Application Activities | Yes | Yes | Returns one row per application activity. |
| Application References | Yes | Yes | Slate Application Only - Returns one row per reference. |
| Decisions | Yes | Yes | Returns one row per decision. |
| Devices | Yes | Returns one row per device. | |
| Document Export (Application) | Yes | Yes | Returns one row per material. Note: Almost all Document Export queries will use the "Materials for Document Export" filter to identify which materials should be included. |
| Interactions | Yes | Returns one row per interaction. | |
| Interests | Yes | Returns one row per interest. | |
| Jobs | Yes | Returns one row per job. | |
| Message Inbox | Yes | Returns one row per message received via Inbox. | |
| Messages | Yes | Returns one row per message. | |
| Payments | Yes | Returns one row per payment. | |
| Relationships | Yes | Returns one row per relationship. | |
| Schools | Yes | Returns one row per school. | |
| School Courses | Yes | Returns one row per course. | |
| Sports | Yes | Returns one row per sport. | |
| Test Scores | Yes | Returns one row per test. |
Form & Event Query Bases
| Query Base | Description |
|---|---|
| Forms/Events | Returns one row per form or event. This is not intended to view specific registrants, but rather details about the forms and events. This query base is primarily used when batch managing events. |
| Form and Event Registrations | Returns one row per registration. If someone registers for an event multiple times, they would appear multiple times in this query; once per registration. |
Dataset Query Bases
| Query Base | Description |
|---|---|
| Organizations | Returns one row per record from the Organization Dataset. |
| Organization Contacts | Returns one row per record from the Organization Contacts Dataset. |
| Custom Datasets | Returns one row per record from each of your custom datasets. |
Special-Use Query Bases
| Query Base | Description |
|---|---|
| Custom SQL | Custom SQL queries are unsupported and rarely should be used. The most reliable and performant queries are built using configurable joins. |
| Geography | Returns one row per zip code. This query base is primarily used in conjunction with Voyager for data visualization by leveraging data from the American Community Survey (ACS) in addition to recording counts of your Slate data. |
| Prompts | Returns one row per prompt. This query base is primarily used for mass updates and additions to the custom prompts table. |
| Users | Returns one row per user with user-scoped filters/exports available. |