What is a query? I consider a query as a real-time, on-demand list.
Let’s suppose that there is a list of accepted students. It may be an accurate list when the list is created. As there are more newly admitted students, the list becomes outdated and it needs to be updated.
With queries, such repetitive jobs don’t have to be performed as queries only show an updated list of filtered students. To put it simply, the query will run through the filters every time you run the query– therefore, you don’t have to worry about its accuracy.
Another advantage of utilizing Slate Query is that you can change how the data appears in the query without making changes to the underlying data source.
For example, the Slate Form has “I paid the deposit” as one of the drop-down menus for students. This same data can be shown as “Paid” in the query for administrators.
Similarly, the date information can be customized to MM/dd/YYYY or YYYY-dd-MM depending on your preference without making changes to the source data.
When you know how Slate Queries can change the way you work and, what is more, you cannot go back to the spreadsheet-only business process.
Let’s dive into how you can be freed from repetitive work with Slate Queries.
Real case scenario- Benefits of using Slate Queries:
*Before the CRM (BC) and after the CRM adoption (AD)
- BC: When international students are issued visas, students’ information such as education level, gender, date of birth, agent, etc were entered manually
- AD: Export a Slate Query that has all the information automatically pulled from the student’s record. What staff needs to do is copy and paste the columns to the internal spreadsheet. By doing so, it can increase the data accuracy and efficiency.
- BC: To see if the student meets 4 pre-registration steps, 4 columns were created and practitioners had to go through each row to see if the student completed all pre-registration steps.
- AD: By utilizing filters or subquery, an export (column) can be created to automatically show if the student completed all of the steps or not. This makes it very easy and intuitive to follow up with students.
- BC: To explain how the internship is related to the major, the office had a template and customized it for each student with internship information.
- AD: An export that shows in the query as a column can be a concatenated with multiple fields. For example, if you want to say “The student will be working for {{employer}} for {{term}},” this full sentence can be automatically updated for each student/internship by having 4 fields:
1. Literal field “The student will be working for ”
2. Export {{employer}}
3. Literal field “for “
4. Export {{term}}
Scroll down to the “How to create Slate Queries” section to find a step-by-step guide on how to build Slate Queries.
- BC: The commission information was entered manually every semester.
- AD: An export (column) can be created to show “Commission required” if the agent information is not blank (= the student comes through the agent).
- AD: Quick Query is useful for getting simple lists which is a great to cross-check the number that you see in your working query.
- AD: Historical data query can be pulled by adding a date field which is critical to see the trend, compare the numbers, etc. For example, by having the “application created date <1/1/2025”, all of the filtered applications that are created before 1/1/2025 will be retrieved.
- BC: The list of scholars who completed the program was manually moved to a different tab
- AD: The list of inactive scholars can be managed easily by using the sort button, or by creating two queries- one with active and the other with inactive scholars.
- AD: The status of any field within the query can be updated to active/inactive easily with a toggle button- this is useful when the field is not applicable for this term but it may be in future terms. Instead of removing the field, by making it inactive, the user can be reminded to switch it on in the future.
- BC: Based on the query, use the mail merge to mail out if needed
- AD: The same query can be exported in virtually all formats including an Excel spreadsheet, CSV file, PDF report, or HTML report. It can be used to send out mailing as well.
How to create Slate Queries
Having a good understanding of how a query works and knowing how to use it can transform your work forever.
Building Slate Query may not seem easy at first but it’s not! As I explained at the beginning of this post, remember two things: with Slate Query, 1. you can pull the most up-to-date information and 2. how data appears can be customized without making changes to the source data.
Let’s learn how to create a basic query by adding fields.
1. To create a Slate Query, click the Queries icon that looks like a graph, then click the “New Query” button. To create a quick query, click the thunder icon next to the “New Query” button.
2. Type the name of the query, designate the folder, and choose the query base. If you are building off of the applications, the base will be the “Application.”
Savvy tip: If you have the authority, go with the Configurable Joins which allow you to join form submissions, applications, systems, and more.
3. You can start building queries by adding exports or filters. I would recommend adding exports first so you can see how Slate is filtering out records based on the filters that you add.
4. Most queries would need students’ first names, student IDs, and email addresses as identifiers so I will add them here. You can do so by clicking the “Export” button.
5. To add a first name, type “First” in the search box. As we chose “Application” as the base, “Direct Exports” shows application record fields. As we chose “Configurable Join” as a query type, “Extended Exports” will show all fields that contain “First” in the field name that can be pulled related to the application records.
Savvy tip: If you are not sure which field to use, check with the relevant office or Technology team.
6. To change the export name which will be the column title when you export the query, hover over to the field and click the “Edit” icon.
7. Now that we have basic fields, let’s add filters to narrow down the group of students we actually need to look at. Without any filters, there are more than 100K rows. To add filters, click the “Filter” button on the right.
8. One of the most used fields in higher ed will be term and year. If you are not sure which term or year field, reach out to the contact person or Technology team as wrong fields can pull wrong queries.
Savvy tip: When choosing fields to build filters, it’s critical to think about which field requires low- maintenance. In other words, create a query that has a minimum number of fields that need to be updated every semester or year.
If the query needs to be updated regularly, consider utilizing the “Edit Notes” button to write down the list of fields that need to be updated as well as when they need to be updated.
9. Once you are done adding filters, go back to the query by clicking the query name on the top. You can see that the query has 73 matching rows.
10. Select your desired output that serves your needs and click Export.
Savvy tip: Query can work as a search box. By typing the student’s identifier in the search box, you can search for the student in the query output preview to see if the student meets the logic that you built.
Similarly, you can click the “Check Logic” button and search the student’s record to see if the student meets the logic, and thus appears in the query.
Intermediate users are the one who knows how to take one step further by manipulating the fields to serve their needs.
Let’s look at different output types that are frequently used in real life- hope you can be inspired to take advantage of them!
1. Concatenate
If you have used the “Concatenate” Excel Function, you would know what this is. It basically combines two texts (strings). At work, I use this function to combine the on-campus building name and the room number.
You can do the same job in Slate Query. To do so, click the “Export” button and select the “Concatenate” output. Then, click the “Export” and select the field.
To add the space between two fields, click the “Literal” field and enter the space “ “ for the Name and Literal fields.
When you are done, hit the save button – now the field will show the building name followed by the room number with the space in between like “Savvy Hall 111.”
2. Existence
The “Existence” output looks at the filed value and sees if the value exists or not. For example, the same field that we used above “Building Name” can be used to see if the student is assigned to the dorm or not.
To do so, click the output field and select “Existence” then type what you would like the field to say when the value exists next to the “Value If Exists.” Similarly, type what you would like to see when the values do not exist next to the “Value If Not Exists.”
The field will return “Assigned” when the “Building Name” value exists. If not, it will return “Not assigned.”
As you can imagine, utilizing various output types requires an understanding of the business process at your office. This is why I believe it is important for practitioners to know how to use Slate as they are the ones who know the business process better than the technology team!
3. Format Data Type
In addition to the various output types, you can also change how the data appears in your query. The most commonly used format data types are date, string, and bit. Let’s take a look at them closely below:
3-1. Date
For example, if the field returns a date data type, you can use various date combinations to show the date value to your liking or to follow the office policy.
To do so, choose any date field and double-click the field, or by clicking the edit icon to edit the field. Slate may automatically detect the date data type or you can select the “Date” from the Format Type drop-down menu.
Next to the Format Mask field, combine MM/MMMM, dd, yyyy to your liking or according to the office policy to customize how the date data shows in your query:
- MM/dd/yyyy: 12/31/2024
- MMMM dd, yyyy: December 31, 2024
- yyyy MM dd: 2024 12 31
- yyyy MMMM dd: 2024 December 31
3-2. String
Additionally, if the field returns texts (strings) and you would like it to be all capitalized, you can edit the field and select “String (Upper case).”
Similarly, “String (Proper Case)” can be used to format all the values to have the following format: 1 capitalized letter + lowercase letters. By doing so, “sevis savvy” will be automatically formatted to “Sevis Savvy.” *Note the source data will not be affected.
3-3. Bit
Imagine that you have a “checkbox” field in your form and you are creating a query base off of the form. You can use the “Bit” format type to customize how the query shows if the form responder checked the box or not.
For instance, if you would like to create a query that shows the list of students who signed up for the event, select the “Bit” format type.
Then, type “Y” next to the “Value True” and “N” next to the “Value False.” This export will return Y for students who signed up for the event and N for students who did not sign up for the event.
One of the output types- formula is fun to use but requires an understanding of how conditional logic works. Go to the “For Advanced Users” tab to learn more about frequently used formulas.
1. Formula (SQL statements)
When you need to create an export that shows different values based on the logic, formula output is the way to go. If you are reading this post, it’s highly likely that you are an educator/administrator who does not have a computer science background.
Therefore, I will share my 2 go-to formulas which are easy to use.
To use any formula, the first step is to click the connection dot icon on the right and select the “Formula” in the Output drop-down menu.
Then, click the “Export” button to export the field that is going to be the base field. For example, if you would like to have a field that shows “UG” or “GR” based on the “Education Level” field, export the Education Level field.
Savvy tip: In most cases, I rename the export field to have one word- by doing so, it’s easier to write a formula.
From here, the first formula we are going to use is “CASE WHEN.” This statement can be used when the field value does not change. The statement would look like the image:
This “CASE WHEN” statement will look at the field “Education Level” and if the value is “Undergraduate” then it will return “UG.” If not, it will return “GR.”
The other formula that I use very often is the “CASE WHEN LIKE” statement. This statement is helpful when the export values are not exactly the same but have commonalities.
For instance, if the application values have “term + year + Undergraduate Application” format in your case (e.g. Fall 2024 Undergraduate Application or Spring 2025 Undergraduate Application) in your campus, you can write a statement like the following:
This statement will look at the application field and if the field has the word “Undergraduate,” it will return “UG.” If not, it will see if the same application field is equal to “Exchange,” and if so, it will return “EX.” If not, it will return “GR” to indicate they are graduate students.
Note that the “CASE WHEN” statement and the “CASE WHEN LIKE” statement can be nested together with ().
By utilizing “CASE” statements, Slate users can create a field that only exists within the query without making changes to any of the source data or records.
In other words, you do not need every single field to create queries you need. You can use Formulas based on the existing fields which is incredible! This is why I said you cannot go back to the pre-slate query work process!
Savvy tip: When you are building a query that needs to be updated every term, it is recommended to use the term “code” which is more general rather than the term “name.”
Using a general field instead of a specific field is especially useful when you use simple SQL to create an export. Otherwise, the export that uses SQL needs to be updated every term as well as the term name.
For example, if you write a SQL—case when @term = “2025 Undergraduate Application” then “Undergraduate” else “Graduate” end— the highlighted part also has to be updated every year. Instead, try —case when @term = “ug_app” then “Undergraduate” else “Graduate” end—. By doing so, this Export does not have to be updated every year.
2. Filters within the export
Let’s suppose that we need to tackle the real case scenario number 2, where the office would like to see the column that shows the student’s pre-registration step completion status. If you are an EXCEL expert, you will be able to achieve this with multiple IF functions. That being said, adding more than 5 IF functions could be confusing and challenging.
In Slate, it’s easier to use multiple ifs by using filters within the export. By utilizing parenthesis and fields, you can create conditional logic within the export. Because it is intuitive, it’s easy to build the logic.
As you can see in the image, by utilizing filters within the export, I added 20 filters very easily to see if the student completed 5 different pre-registration steps.
It would have taken a lot of time if I wanted to achieve the same result with IF functions in EXCEL or Google Sheets.
3. Join
In the previous post, I covered how to create and use Slate Forms. Queries can be created based on the form submissions. To do so, click the “Join” button next to the Filters. Then, click the “Form Responses.”
As you can see, it is important to know if the form you are trying to join is a Person-based form or an Application-base form. To learn more about how the two forms are different, check out the previous post.
Once the form is selected, you can export the “Form submission date” and set the “Existence” as an output type to show “Y” if the form has been submitted or “N” if the student has not submitted the form.
Savvy tip: To join various applications and form responses, the query base must be “Configurable joins.”
As you can see, there is a lot you can do with Slate Queries and it will certainly free you from performing repetitive tasks.
Although this article covers technical features, the most important thing when it comes to building Slate Queries is the understanding of the business process on your campus and what fields you need to use.
If this post convinced you to start building your own query, one of the easiest and fastest ways to learn which fields you need to use is by having the technology team create the first query for your office.
From there, you can click the “Edit Query” button to see which exports and filters are used. By referring to this query, you will be able to build multiple queries— because the truth is that the same fields will be used again and again in the future. That’s the beauty of building queries.
One downside of Slate Query is that users need to manually export the query. What should you do if you would like to have the most updated list in your inbox automatically?
You can utilize Slate Reports to schedule which I am going to cover in my next post.
Did you find it helpful?
Click the button below to check out all the Professional series!
© copyright SEVIS SAVVY 2024