“Give yourself permission to experiment, to be patient, to take your time and to think about the same work you’ve been doing, but in a new way.”

Sound like music to your overworked ears? That bit of wisdom comes from RHB Slate Integration Consultant John Michael Cuccia. He, along with RHB Senior Integration Consultants Megan Miller, Dom Rozzi and Abraham Noel, delivered a webinar on Feb. 2 showing you just how easily you can start building a foundation using Slate’s Configurable Joins. It’s about working smarter, not harder, right? Check out the video, Megan’s recap of the event and the presentation slides as well.

You may sign up for Part 2 on March 2 and Part 3 on March 30 here. You got this. As always, we are available for expert counsel when you need us.

Transcript:

Megan Miller:

Hi, everyone, and welcome to the first webinar in our series, “Build a Better Instance with RHB: Configurable Joins”. Today, we’ll be “Setting the Foundation”, discussing Configurable Joins concepts and basics, and we’re really glad you’re here with us.

My name is Megan Miller, and I’m joined today by three of my RHB colleagues, Abraham Noel, Domenick Rozzi and John Michael Cuccia. We’re all really excited to share with you today on a topic that we know has been a pretty dominant part of the Slate conversation over the past few years.

As I mentioned, this is the first part of our three-part series, and you’re invited to participate in any or all of them. Today, we’ll be looking at those Configurable Joins basics and concepts, but we’ll be building up after that. On March 2nd, we will jump into those intermediate Configurable Joins skills and use cases, where we’ll unpack things like many-to-one relationships and subquery filters and exports. And then on March 30th, we’ll conclude this series, at least for now, with Configurable Joins subqueries and advanced concepts, talking about topics such as independent subqueries and how to utilize Related and System bases. And if all of those terms mean nothing to you, don’t worry, we’ll talk about all that too.

Looking over our registration list, we were really excited to see a wide variety of Slate users at a range of skill levels, and we’re really glad you all chose to take time to learn with us today. We want to note at the beginning that this webinar will be most useful for you if you already know how to use Local and Slate Template Library queries for the most common bases within Slate; if you understand the different query bases that exist within the Slate environment and what they are used for, and if you are familiar enough with Slate’s underlying structure to understand the fundamental data that exists. If you’re not quite at this level yet, that’s okay, and we’re glad you’re here, but we’d recommend that you make note of any terms or concepts we discuss that are unfamiliar to you, then take some time reading up on those after our session.

And if you’re an advanced user, don’t worry: We have something for you today, as well. While much of what we discuss may be familiar to you, we hope that our presentation today will help you develop a framework for explaining these concepts to those on your team who are less experienced with Configurable Joins.

Also a few housekeeping notes for you today: First, yes, this webinar is being recorded and, yes, we will send it along to you after the event, after the event is over. Second, you’re going to see a question-and-answer section within the webinar platform, so feel free to submit your questions there as we go along, and we’ll talk through some of those at the end of the presentation.

So, having said all of that, let’s talk about what we’re aiming to accomplish today. We have a few goals for today’s webinar. First, we want you to leave today understanding what the fundamental differences are between Local, Slate Template Library and Configurable Joins queries. Second, we will share the Configurable Joins tools and resources that are available within your Slate instance. Third, together we’ll walk through how to build a one-to-one Configurable Joins query step by step. And finally, we’ll do all of this as we work to alleviate your existential angst—about Configurable Joins at least. No promises for any of those other deep, looming questions that are in your life.

As we get started, let’s quickly introduce ourselves here. RHB is a higher education consultancy that equips clients to confidently communicate their distinctiveness throughout the student lifetime journey. We’re now in our 30th year, and RHB has guided more than 200 institutions in enhancing relevance, relationships and revenue. Our counsel and processes are founded on the principle of Coherence, which is discovering and telling the truth about the one place you occupy in the higher education universe.

Our firm comprises four industry-leading practices in Technolutions Slate and related technologies, enrollment management, institutional marketing, and executive counsel to presidents and cabinets. RHB is the seminal and foremost leader in Slate technologies, which is the fastest-adopted CRM in higher education. Our practice has a footprint in more than 10% of all Slate instances, offering best-in-class implementations, diagnostics, advanced builds and training.

As I mentioned before, you’ll be hearing from a few of us today. Abraham [Noel] joined RHB as a Senior Integration Consultant in July 2020, after working as the Manager of Enrollment Services at Macalester College. Dom [Rozzi] has been with RHB as a Senior Integration Consultant since May 2019, after serving as Associate Dean of Admissions at Franklin & Marshall College. John Michael is new to the RHB team—we’re glad he’s here—he joined us as an Integration Consultant just a few weeks ago, after a career as Associate Director of Admission Information Systems at Rice University. And I came to RHB as a Senior Integration Consultant in July 2019, having previously served as the Director of Enrollment Communications at Seattle Pacific University.

But enough about us. Let’s talk about why you’re here today, because that’s what really matters. What exactly has compelled you to join us today on what I would argue is truly the weirdest of all American holidays, Groundhog Day? Well, my guess is that for many of you, you’re actually feeling maybe a little bit like Phil Connors, where no matter what you do, you keep ending up with the same disappointing result. But instead of being stuck in a time loop, you’re stuck in a cycle of query frustration. So, let’s take a look at that.

When you registered for this event, we asked you to tell us: What are your burning questions, concerns, use cases, wonderings and even frustrations with Configurable Joins? Yeah, let me tell you, you all had a lot to say about that. Here are a few samples of your responses.

“They are so confusing! Sometimes, if you don’t do things in the right order, it gives you like 1,000 lines of red, angry text, and won’t let you go back, so you have to delete it and start all over.”

“Anytime I use them, it’s a lot of random clicking until I get the result that I wanted. I started looking into them when they were first introduced but the lack of documentation turned me off to them, so I gave up entirely.”

“Why are some fields in a prospect or application base so hard to find when you start with a Configurable Joins base?”

“I have trouble understanding the logic for which joins enable me to access certain fields.”

“I never know what to join or where or when to use a subquery and then how, or even why. It takes so long with trial and error, trying to figure it out.”

“I don’t even know where to begin with Configurable Joins. I just don’t get how they work.”

I think that one sums it up pretty succinctly. And those are some really big feelings, guys. That probably resonates for a lot of you. I’ll say that a lot of it lines up with the questions I found running through my head, when I first was introduced to Configurable Joins. I vividly remember Alexander Clark standing on stage at the 2018 Slate Summit and sharing, in the most Alexander Clark way possible, all of the ways that Configurable Joins were going to transform how we use Slate. Technolutions tweeted about it, and I tweeted about it but, really, I had no idea what all this meant.

But, if nothing else, at least the event photographer was on hand, right in my face, to capture me with what I like to call my “Resting Conference Face” as Alexander made that announcement, which was then subsequently posted to Technolutions’ Facebook page so that everyone could go see exactly how I felt about this whole new world of Configurable Joins. I was not embarrassed at all. I was a little bit embarrassed, but that’s okay, I digress.

As with anything in Slate, we won’t be able to fully grasp Configurable Joins until we grasp that conceptual framework that they sit in. And so that means we need to understand how the data in Slate works. Some of you are probably already familiar with the term “relational database”, which is exactly the kind of system that you’ve been working in within Slate. And understanding this is essential because it turns out that people are pretty complicated, both in terms of who they are and what data is attached to them.

Let’s think about the complexity of any person. We all have our basic data. For instance, I’m 37, I’m a US citizen, my personal pronouns are she and her, but there’s a lot more to me as well. There’s my academic history, my family relationships, the jobs I’ve had, the places I’ve lived, the tests I’ve taken and so much more. And all of those data points, those can connect me to other data points or to other people in their data points. Like I said, people are complicated and recording all of that complicated information requires a database that is structured to handle it.

So how does a relational database work? Well for that, I’m going to turn things over to John Michael, and he will build on all of that and what that means for our work in Configurable Joins.

John Michael Cuccia: 

Thanks so much, Megan, for your introduction. We’re going to lay out that blueprint very nicely for you today with our first conversation.

So, a couple of things that Megan said that we’ll revisit quickly before we dive in. For today’s presentation, as well as all of the sessions in our series, as Megan said, we’ll take as a given that you have experience with Slate’s query builder. That is, you’re familiar with how to start and build Local base or Slate Template Library base queries. But don’t worry, you don’t need knowledge of every possible filter and export that’s out there, but we’ll be connecting what you already know and have experienced with the query builder to the new concept of Configurable Joins and the upcoming discussion of a relational database.

A few permissions for you as we begin our conversation: Give yourself permission to experiment, to be patient, to take your time and to think about the same work you’ve been doing, but in a new way. Megan shared with you some of the top themes that we got in our registrations, and one of those was that you all expressed curiosity. So give yourself that permission to be curious about the topic. Maybe you’re also anxious or excited or confused or frustrated or hesitant, those are all fine and valid. One of our intentions today is to inspire you that the CJ world is accessible. 

Before we can begin to set the foundation of construction of CJ queries, we need to spend some time discussing the blueprints and what thought processes go into designing CJ queries. This concept of design is important. Before we can jump in and build with the tool, we have to understand how to use the tool. And before we describe how to use the tool, we have to visit the make-up of the tool itself. The time you invest in thinking about some of these fundamental philosophies of CJ query architecture will, over time, reward you with greater success as you build.

For those of you who said that you were a novice or apprentice with query building, today’s first session emphasizes setting this foundation: things like Slate structure, essential Configurable Join lingo and how to build a basic CJ query. If you’re at a master query building level already, we hope what we discuss today will bolster your confidence and give you some words or styles or techniques you might use when talking to others at your institution about Configurable Joins. Feeling affirmation on something you suspected to be true may be one of your takeaways today.

In the second session, we’ll move more heavily into the conversation about building up, as they say. The top question that we received on registrations was very specific: “When do I join at the bottom or the base level of my query? And when do I join in a subquery export or filter?” Just as a preview, we will set the stage today to dig into that in the second session.

Our third, and potentially beyond, sessions will move into expansion, using CJ queries throughout the modalities in your instance, building CJ queries in the Related and System categories and other advanced concepts. We think this type of progression will afford you the opportunity to hear something new, take it back, review it, practice it, chew on it and return to the next conversation ready to build on your foundation.

Lastly, before we dive in, many of the comments that we received on registrations expressed a really strong desire for use cases. “Why do I need this? How is it going to change my life? What are examples of ways to use this?” We’ll make several points on this throughout these sessions with today’s aim really addressing the “why” aspect. Use cases do provide a great real-world example and inspire some creative thinking, but don’t let this be your focus. It’s important to get comfortable with the fact that this is the future of query building, so try not to think of it as extra or a fringe benefit to the software. Capitalize on the time that you have now to learn and use this technology. By knowing how to see and use Slate through the lens of Configurable Joins, more use cases will present themselves.

So, let’s start at the very beginning. After all, it’s a very good place to start. Slate is a relational database made of tables, which you can imagine like spreadsheets. These tables have columns and rows. The columns represent different types of data in the table. Each row is a record in the table, and each cell, the intersection of a row and a column stores the data associated with that column and record row.

A very basic example, the Person table and Slate contains columns like a globally unique ID number, first name, last name, birthdate and many others. Each row in this table represents one person record. The data across that row all relate to that one single person. Now by the way, that ID number, when we say globally unique, which you will often see abbreviated in Slate as GUID or even pronounced as “goo-id” or “gwid”, “globally unique” means that that number is unique across not only your instance, but all instances of Slate. It’s like a fingerprint. It only ever identifies one record.

Another example is the Application table, with columns like a unique GUID number, the GUID number of the related person record, the application round, the application submit date and many more columns. These tables become related to each other by having pieces of information in common. We know that one person can have one or more applications. For each record in the Application table, Slate also stores the Person GUID of the person to whom those applications belong. This creates a relationship between the Person table and the Application table.

Slate has more than 200 different tables. Can you imagine if we tried to store all of that data on one massive table? It would be enormous, inefficient, unwieldy. Your house is not one big room—you have several rooms, differing in sizes, features and purposes. In the same fashion, we have different tables in Slate that store different kinds of data. And in fact, many of the relationships throughout Slate’s 200-plus tables are done in this fashion with cross-storage of these globally unique ID numbers.

Some relationships are made by other data that’s in common between two tables, like keys. For example, schools, which exist on person records, relate to organizations, which are dataset records, and they relate by key. In most instances, we know this as CEEB. The point here is not to know exactly which columns are cross stored between different tables, only just to point out that the collection of all Slate tables is interrelated.

Going back to the blueprint that we’re building today, this concept of a database of related tables is the scroll of paper that your blueprint goes on. You can get to know the family of tables that are in your house through a few different tools available to you in the database modality. And that we’ll show you in greater detail later on. One of them is called the Slate Data Dictionary, which is available under the Standard Query Library option. This Data Dictionary provides a basic readout of the tables in Slate and the columns in each of those tables.

The Configurable Joins Base Explorer is an incredibly useful tool to visually see how these tables relate to each other and which join pathways to follow to get to certain data—more on these later.

You may be thinking, “I get this already. How does this get me any further with Configurable Joins?” Well, we’ve set the stage this way because the way we go about architecting our blueprint is a shift from what you have previously experienced with Local and Slate Template Library queries. Configurable Joins will require you to take your usual questions but ask them in a different way. Where before you could simply start a query, grab a few things from, grab a few template filters and export based on their label and be on your way, your question no longer initially revolves around, “What filter and exports do I need?” but instead becomes about determining an appropriate starting point for your query and how to establish a relationship to the data that you need. CJs require you to think about how the data is organized, where it lives and how it relates to other data.

Up to this point, here’s the gist for how I bet most of us learned how to build queries. You’d pick either a Local or a Slate Template Library (STL) base, and then you’d simply go shopping. You’d shop through all the offerings of filters and exports by typing in keywords, things like “name” or “ref” or “max SAT-R” or “has application by round”. In a certain sense, you rummage through the toolbox, looking for your hammer, your nails and all the objects that you needed for construction. If you didn’t find the right one, well, if you understood enough about what you were doing, you could magically copy a Slate Template Library export or filter to your instance’s Local version of that base, and then customize it accordingly. Maybe you also had use cases where the STL didn’t even serve up what you wanted, and you had to write something yourself if you knew how. 

In the world of Configurable Joins, we return to some of the foundational pieces of query architecture. And before we go reaching for tools to build, we map out our blueprint for what we plan to build. We start with thinking, “What type of record do I want returned as my result rows? Is it an application, a person, a relationship, an organization, an organization contact?” 

Then we need to sketch out what the end looks like. What we want to know about the record. It could be something as simple as knowing the academic program of an application. Something in the middle, like knowing the max ACT composite of each application in the current period. Or something extremely complex, like a list of organization contacts from organizations who have people who applied in the last three cycles and did not receive a decision of Deny. When the start and the end are identified, then your investment is in the middle.

Now notice the emphasis here: it’s not just about how to get the data, but how to get to the data. Herein lies one of the learning curves of Configurable Joins, knowing where certain data lives and identifying the relationship pathways between those data.

A quick note about the future because I can feel it coming through my screens. Maybe you’re sitting there saying, “You will pry my Local and STL queries from my cold dead hands.” Well, a new feature to Slate called Query Libraries allows you to build Configurable Joins exports and filters and save them to a central library, effectively replicating what we’re currently able to do with what Technolutions built in the Slate Template Library, and what you might have also built in your Local bases. There is a light at the end of this tunnel, folks. Remember our permissions, give yourself the space and the grace to be curious about this new approach to query construction. We’ll talk more about libraries in a future session.

One intimidation for you might be around the whole join concept, and the fact that the name of this query type is “Configurable Joins”. But even before this query type existed, we and you and everyone here has been joining all along. Maybe you just didn’t know it but surprise, you’ve been joining when you picked a base. What you saw was choosing the Prospects and Applicants base. What Slate saw was a join from the Person table to the Application table.

Surprise, you’ve been joining all along when you picked an export. In the query builder, maybe you picked the tags export under Record Details, but what Slate saw to get there was a join over to the Lookup Tag table.

And hopefully it comes as no surprise, you’ve been joining all along when you pick a filter. Have you ever used “Has Application by Round”? Well guess what? Inside, baked into that filter, was a join over to the Lookup Round table.

But these were all unconfigurable, shall we say. Previously, you relied on an STL export or a filter or a Local one that you copied from the STL and adapted, or even that you built from scratch yourself to construct your query. Joins have been part of those filters and exports all along. They’ve been baked in, hard-coded into the export or filter itself. 

However, unless you copy those STL filters to your Local base, or you wrote one from scratch yourself, they have been unconfigurable joins. The relationship that joined one table to another was hard-coded in the export or the filter. And even though you might have always been simply reaching for STL exports and filters off of the shelf, one of our friends at Technolutions had to write and maintain all of those as Slate evolved. It also meant unless you sat and wrote your own exports, filters and custom SQL yourself, you were limited to interacting with your data in ways that were prescribed by the Slate Template Library elements.

Consider now how the world of Slate has evolved over time. Not only in the functionality that we have in the product, but in the end use that we all have as clients. Rhetorically asking, how many of you out there switched to a test-optional or test-flexible policy this year? That seemingly simple decision to make by your institutional leadership has a profound impact on how many of your processes in Slate run, including all the functionality that ties back to the query builder itself.

This STL, Slate Template Library, mentality of building queries just simply couldn’t last. Replicate blocks, entities, workflows, unmapped form data, custom list fields, merge fields on forms, the list goes on … no person could ever keep up with developing all the hard-coded exports and filters for the Slate Template Library, nor could Technolutions prognosticate every iteration or combination of business need among all clients and client types. Those client types span from admission to student success and advancement, and not only higher ed institutions, but foundations, high schools, and yes, even K-to-12 Slate instances.

This becomes the fundamental blueprint construction philosophy that CJs completely unlock. You no longer have to depend on an export or a filter being pre-constructed and delivered to you. Not only are nearly every column of every table available as an export, but you also get to configure and customize the ways that tables join together. Our adding the join unlocks relating tables together without any other restriction. 

For the start of this series, let’s set aside the part about knowing when and how you might want to customize the ways that tables join together. Instead, let’s just focus on the first part, that we don’t have to rely any longer on an STL export or filter to decide what data we can have. We can, in fact, have it all the way that we want it just like we like it.

An important call-out at this point, the Slate Template Library is officially deprecated. This is terminology that means the functionality is actively being replaced, and in the case of the Slate Template Library, it is no longer being actively developed. As newer functionality is introduced into Slate, you will not see new STL exports and filters related to that functionality. In fact, your Slate Template Library no longer automatically refreshes itself overnight. If you build a new field and you need it in your STL, you need to use the manual refresh option in the Database modality.

Now my personal guess is that Technolutions will slowly sunset the use of Local and STL query types by discontinuing provisioning those features for newly purchased instances. That’s just my personal guess. As an example, the precursor to the Rules Editor, a feature called Triggers, is viewable in most databases. Triggers were fully replaced by the Rules Editor in 2014, but the feature still runs in very limited capacity for instances who have settings in that area. Brand-new instances, while they can see this, they can’t use it.

A second important call-out, this series is not intended to send you on a crusade to sunset all of your Local- and STL-base queries. While it certainly will be a useful exercise for you to explore upgrading your current ones as freshly built CJ queries as of today’s presentation, there are some modalities that do not yet support Configurable Joins. Reader is one of those. Especially for existing instances, you’re likely facing a hybrid period of time using all three query types while you convert or rebuild throughout a cycle or over the course of a year or even a little bit longer. We encourage you to move Configurable Joins off of your parking lot and make them a priority before they become an urgency or an emergency.

So, let’s summarize what we’ve got to this point. The paper on which we set our blueprint is our database. The rooms we draw on this blueprint will be constructed by the tables in the database. These tables have built-in relationships to each other. So, our rooms can utilize multiple bases. We’ll fill these rooms with data, the intersection of a column in a row within a table or a set of join tables. And we won’t have to rely on any prefab materials for our construction. We get to decide which tables we use, what data we bring in and how we arrange the data.

Let’s go back to some basics with a quick peek under the hood on what’s happening when we use Slate’s query builder. This will tee us up for a more in-depth tour of the query tool, highlighting what’s new, and what’s evolved with the introduction of Configurable Join query types.

Here’s what we see when we build a query in a Local or an STL base. We start with the selection of the base itself. This represents each row in our result set. And then we go shopping. We drop in some available exports and filters and perhaps we’d also sort our data. Here, we’ve quickly constructed an Applications base query, so that’s one result per application. We filter to show only results that have our 2021 Undergrad round, we’ve exported the Slate person ref ID, first, preferred and last names, and finally, just for good measure, we’ve sorted the results in ascending order by the name field.

Now, you might not have fancied yourself an SQL writer, but that’s in fact what you’re doing with Slate’s visual drag-and-drop query builder. Here’s what Slate sees when we build queries. Select our exports from the table base that we chose where these filters are true and order the results by our sort criteria. That SQL statement that Slate sees is what you’re constructing with the visual builder. The colors indicate where each part of the statement comes from.

Now, have you ever tried to run a query without any exports added? And you probably know you can’t quite do that, which is why Slate feeds you back an error message. The first two parts, selecting exports from a table base, are the baseline requirements. Filters and sort parameters are always optional. 

In this image here, you can see the counter next to the filter section says I’ve got three matching result Application rows. Let’s see what these rows look like. Pretty straightforward, the traditional spreadsheet-looking table output of our query results. Our exports are in columns, our resulting records are in rows, the set is filtered down and ordered based on how we specified.

Let’s take a look at what Slate sees. We see the basic structure of a SQL statement, select our exports from our table base where our filters are true and order the results. Hmm, now wait: what is this in the middle of the statement? How is it possible that there is a join in here when this is a Local base query? We built a query that’s based on the Application table. And we added exports that are returning data from the Person table. The join is hard-coded in the grouping of exports that we selected establishing that link from an application over to its related person. All of this happened quietly like ships in the night, but the relationship was only possible because of some pre-programmed information on those exports themselves.

Let’s look now at what happens when we CJ query. We have the same basic structure—we select the base that corresponds to the table that we want to start in, which will represent the result rows over our query. This time, instead of letting Slate do it by virtue of adding exports and filters, we’re going to join to the Person table at the base of the query. We’ll talk more about why the join is down there later on. We’ll draw in the exact same exports as before—Person ref ID, first name, preferred name, last name—we’ll filter just for the applications in the 2021 Undergrad round, we’ll order the results by ascending Person name. That’s what we see in the query builder.

Let’s take a look at what Slate sees. It’s largely the same structure as before. Select our exports from the original table base, join to the other tables that we’ve specified, filter the results and order them. Our exports can come from either the original table base or from any of the tables to which we’ve joined. All of the exports still get listed together in the first part of the statement, regardless of which table they come from.

Hopefully, it comes as no surprise, we see the same spreadsheet-style output of our data. In fact, returning the exact same data as before. And what Slate sees is largely the same with some under-the-hood improvements to the construction of certain parts of the query statement. Simply notice that it appears in the same order as it did before. Select the specified exports from our base table and our join table where our filters are true and sort them as we’ve specified. The join statement looks a little bit different, but it’s in the same as it was before. And more importantly, this is a join that we specified. While this example replicates existing functionality, the ultimate power of this query methodology is that we get to decide the pathways for these joins, allowing you to get to your data in whatever way you want rather than a prescribed way or dictated way from existing exports or filters that may also not have been built by you.

All of Slate’s tables have been divided up into three categories, and you’ll see these options when you start building a new Configurable Join query. In general, here’s how you can approach these categories. Records are your highest-level dataset records. These are applications, persons, organizations, organization contacts, and records for any other datasets that you might have. If you’re an advancement instance, you will see more datasets here that come with your instance, like companies and foundations and funds. The Related category houses everything that relates to parent records, things like schools, tests, addresses, devices, jobs, form responses and more. Lastly, you can also build queries for System objects themselves. A query in this category could give you one result per bin or per period or per user or decision or checklist item. 

We told you there are a lot of tables. Here’s where they are all sorted out amongst each category. The idea here is simply to allow you to see, from a high level, this relationship aspect that Configurable Joins really brings to the surface. 

While we’re here, and before we move into a tour of the tool with Dom, one final note, particularly about permissions in Slate. Utilizing these Configurable Joins in your instance will require some advanced review and consideration of your current permissions architecture. Permissions will impact your ability to roll out Configurable Join queries. For example, if your Slate captains begin writing Configurable Join queries and want to share them with regular admission officer users, the end users must have the correct permissions to at least run those queries. Maybe they don’t care about their architecture and maybe they don’t need to know, but you have to roll out certain permissioning in advance. If you have a highly permissioned Local or STL query environment, you may find some limitations or obstacles. In Configurable Joins, you restrict access to data, generally speaking, by applying permissions to bases and join pathways, discreetly and independently. In other words, one person can be given access to the Person and Application bases, but not allowed to one to the other.

That’s our theory, that’s our blueprint, that’s our foundation. Dom is going to take us into a tour of the tool and start taking some of these concepts and connecting them to the actual modalities in the query builder. What we click on, how it’s organized, what each component means. Dom?

Domenick Rozzi:

Thanks, John Michael. Appreciate that in-depth, thorough exploration of the Slate database, how things set up and how tables relate to each other.

Before I get into the tour, I do want to make sure that we reiterate that we do have the Q&A section, and please feel free to ask any questions that you might have as we go through, going through all these different pieces and parts.

As mentioned before, we are drawing a blueprint by which we will use to pour the foundation to build the walls and to put a roof on our house. We hope that this is a different way of thinking of building queries and that this will begin to actually sort of make more sense as we continue to go along in this exploration.

So to start actually constructing a house, it’s important to understand the tools that we’re going to be using for that build. So we’re going to go through a short tour here to highlight some of the visual cues that Technolutions has put together for us to help us put our queries together.

John Michael mentioned the first and foremost important starting point is our query base. This determines what will come out of our query results. This can be people, applications, or frankly, any other table in the database. We’ll dive into these other tables at a later time.

So right now, we’re just going to focus on the Person table. Once we go into our builder, we see things that are very similar to what we’ve experienced in our Local and Slate Template Library query types. We have a section for exports, we have a section for filters, and we have a section for sorts. We also see now a section for joins. And as a general practice, I like to start from the ground up when I’m building my house, and for those who know me, I’ve got pretty good amount of experience in that space. So why don’t we just start there with our queries, as well?

For the purposes of this exercise here that we’re going to do today, let’s work on joining to our addresses, our mailing address. So a few points that I want to make sure that are noted here is when we come in and look at joins, and a few different visual indicators to us: One is that we’ve got joins that are just the label. This represents a join to a table that establishes a one-to-one relationship. We also have icons that represent a one-to-many join to that table. In other words, there can be multiple values of courses that are related to a single person. Oh, and there’s this one special little icon here, the magic hat. Forms actually carry another secondary piece which we’ll get into in another time—there’s configuration in order to configure the join to our form responses. Important to note that these indicators are helpful when deciding where we’re going to be doing some joins.

Okay, for right now we’re going to do mailing address. Number 1: So we come in, specify our mailing type and we’ll go ahead and join to the Rank 1 table. The next thing that we’ll do after we’ve made our join is we’ll move into the filters, or in other words, we’re going to put dimension to our house. Going to measure where the foundation walls need to be built, and we’re going to go from there.

We have a lot going on when we look at filters, namely that when we open the tool, we first see this section that is called “Direct Filters”. This represents all of the traditional quote-unquote filters that we would have had in the Local or Slate Template Library that are affiliated to any tables that we have previously joined. Our base table in Person and our join table Address. These operate in the exact same way that our Slate Template Library and Local queries operate. But we have more, and if we scroll down just a little bit further, we see something called “Extended Filters”. Extended filters give us the chance to further expand the available filtering to our query. And Slate has afforded us a very convenient way to do this on the fly.

So selecting any of the particular exports or filters, from down here in the indirect area or extended area is going to give us the ability to specify how we want to join to that table—and we even have an indication of which tables and types of joins we would have access to in putting that filter in place. We’re going to go into that more in depth later, so I’m just going to leave that right there for right now.

We also have the ability to create subquery filters, and this little icon that sits next to the filter and next to the export is just that, the subquery versions of filters and exports. When we come into a subquery export, a subquery filter, we’re going to see things that are very similar. If we were to just go to “Filter”, we’re going to have our direct filters. We’re going to have our indirect filters, and we’re going to be able to pull those into the idea of a filter, but we can also filter on concepts. These are things that exist in the Local and Slate Template Library currently, things like the “exists” filters, the “has something” filters. Those are all subqueries, and you’ve been using them all along. Important thing is now when you go to use it, you’re actually specifying each part of that build. 

So in the example here that we would want to look at the existence of the country in an address, we would pull in “country exists”, and we’d be able to filter that. Now Slate doesn’t presume that it knows what the filter is that you’re building. So you, of course, need to name it. Naming conventions would be very beneficial here to keep them as specific as possible. And as you get further and further into the inception of subqueries, you want to make sure you label things in an according way so as not to confuse yourself as you start to roll up into the larger contexts.

Now that we have our foundation in place, we’re ready to build our walls, right? This is the query output. These are the things that people see, right? We put our query together, you’re able to see the columns and the rows. When you put a house together, you’re able to see the walls and the roof. In a very similar way to what we have in our former STL and Local query building days, we’ve got exports. Some of them are just named a little bit differently. So perhaps we have GUID instead of Person ID or Prospect ID. It’s the same thing, it’s just named a little bit different.

But I know that we’re all looking for some of the native features that were available in our Local and Slate Template Library queries. The existence export, the formula export—I love these things, I use them all the time. But they’re not overtly apparent that they exist in the Configurable Join world. Oh, fortunately, they are here, and they operate exactly in the same way. But they are now in a subquery. And this just showed up, that’s super fun, we’ve got some examples of separator options. The life of a Slate person, right? Always changing.

Here, we have not only the existence and the formula options for outputting our exports, we’ve got a whole host of other things. We’re not going to belabor each of these today, we will get into these in a future webinar. But I just wanted to note that they are here and they operate exactly in the same way that you would set up your existence export in the old query types, right? Set your filter, define it, sort of the binary output for it, and you’re off and running.

I want to just pause there for a quick second, because that, in a nutshell, gives you everything that you need for building Configurable Join queries. There’s a lot in there, and I know that we have a question that sort of was raised in numbers, in the registrations. And that’s, “When and where do I create a join?” Well, we’ve constructed a little bit of an output that should hopefully help you chart to help you give a little bit of a sense for what these things are.

When we’re joining at the base query level, we’re establishing a one-to-one relationship between the base table that we’ve established as our query base and the table that we’re joining to. The net result of something like that, in terms of our query output, is that we’re going to have a single output in each row for each of our exports. In some cases, we actually have to create that one-to-one relationship. And that’s where we see things like most recent, or Rank 1, or primary. You have to establish those at the base level in order to have the one-to-one output in our query.

But what happens if we want more than just one in our output, or if we need to aggregate information so that our single column of information in our query is giving us more than just that one piece of information? This is a subquery join. When we put together the join inside our subquery, it’s not only looking at the table that we’re joining to in a one-to-one existence to the row that’s coming out of our query, but it’s looking for all the permutations of that data point against that row. And so we come up with something like our most recent applications with released decisions. We have that round, that we have in our one-to-one relationship, but now we’re able to actually do some concatenation and pull in the actual decision and the date and whatever else you want.

Or perhaps we’re looking at Miss Doe’s record here. She actually had two high schools on her record which was not visible in that one-to-one relationship that we had initially looked at. And so that loses some context, perhaps when we want to build something that’s a little bit more comprehensive in this kind of a way.

Or maybe we actually want to see counts of numbers of values, as opposed to actually seeing the raw values. We can do that with subquery exports as well, which really gets us into a hybrid space of what the reporting tool does in Slate and what the query tool does in Slate. This is a really happy space.

I leave these two right now. We’re going to continue to expand on this, but I just wanted to give you these two little charts as a way to represent when you should perhaps think about using the query base join, or when you’re looking at subquery joins. As we go on today, I do want to pass it over to my good friend and colleague Abraham to actually go through a full query demo.

Abraham Noel:

Thanks, Dom, and thanks everyone else for really setting the stage. This is really the shortest part of our presentation today, but hopefully what you’ll see here now is a synthesis of everything we just talked through. 

So kind of talking conceptually about building a query, I’m going to actually share my screen, and what I’m going to do today is actually synthesize everything you’ve just heard. So, our goal is to build a query that will display, by application, a person’s name, school, some information about their phone number and email, and then perhaps a decision if we have the time. I’m going to take you through the actual process of doing that is part of building out a query. 

This kind of goes back to the concept that John Michael was talking about earlier: What type of record do I want? Well, you have to know that before you actually build a query. That’s real important that you decide that.

One of the things about Configurable Joins that is the limitation is that when you set the initial base, once you build it and start building on top of it, you cannot change that. If you need to change it, you need to actually rebuild the query. That is a common thing that sometimes happens, or sometimes you’ll get into a Configurable Join and you will realize later need to change it. So in that situation you would rebuild it. It behooves you to kind of know that ahead of time before you actually start putting anything into the query.

And then this, you know, kind of building on that, what do you want to know about the record? So, when you ask that question, you kind of need to know what are the potential joins to that record. And we’ve shown you a couple of techniques to do that.

One thing I want to point out to you which is really helpful is this Configurable Joins Base Explorer. So this is a tool that’s under the Database area. And what it allows you to do is actually pull up records in Configurable Joins and look by record type. In this case, I’m going to look at the Application and see all the possible joins there. What’s powerful about this is that you are able to see all the different combinations of things that you can do based upon a particular base that you start with.

The other thing that I find really helpful as I tend to think visually when I build queries, and in this tool, there’s actually a diagram that shows you all the joins. So in this case, you can actually see we have the Application records. And from that, we’re able to join to a variety of things. The biggest one and maybe the most-simple one is the Person one. I’m actually going to demonstrate how we do that in a query today.

So going back to our query, what I’ll do is I’ll set up the query, we’ll call this one “My Very First CJ Query”. You have to select Type “Configurable Joins”. If you don’t see this in your list, that means that you have a permission set up for your user account, so ask your Slate administrator to grant this to you in the appropriate instance. 

Then you want to pick your Category. So today we’re going to be sticking to Records. Records in this case include things like the Application, the Person records, a variety of different things, we’re going to today, because we’re going to be answering the question—we’re building a query that pulls back individual application records, we’re going to select “Application” as our Base. So I’ll go ahead and do that. And then you’ll see the sort of basic layout that Dom and others talked about here earlier. 

Now I tend to be somebody that likes—I like to build from the bottom up. There are different ways of doing this, you could start from the top down or build from the bottom up. The reason I do the bottom up is because for me, I think then about this sort of diagram in our Base Explorer.

So what we’re going to be doing here first is we want in this query to get the round that an individual is-—actually, let’s not do the round, we want to actually get to the person’s name first. If you look at the export, and we’ll see it pull it up here, you’ll see that there is no “name” in this right here. To do that, we have to do our join to the Person. Going back to our Explorer here, we see Application to Person, so I’m going to build that join first. I’ll click on “Join”, I’ll type in “Person” and that should bring up the option. We click “Continue” and we save this, then we’ll create that join.

And now if we go to our export—and what I’m doing here, by the way, as I’m turning off extended exports, I just want to show you for sake of demonstration the direct exports, you’ll see all the Person records. Now, all the person exports. So what I’m going to do is I’m going to add my “name” field. If I simply run this query in Preview mode, I’m just going to see a list of all the applicants in the system with their names.

Next, what I want to do is I want to add a school to this. I want to know, what school did this person actually attend? And where this gets into is the concept of rank. We’ll talk about that in more detail in our next sessions but in this case, we want to only pull the Rank 1 school. So this is where you’re allowing Slate to really write a little bit of SQL for you behind the scenes, but the concept really is the same.

If we go back to this and we go back to our Person, so I’m actually going to click through this and you’ll see now on the diagram for Person, we have all the possible joins here. So we went from Application to Person and what we’re going to be doing then is we’re going to be joining to the School record. You’re going to notice that on the Explorer here, this is a thick line. What this means is this is a one-to-many relationship. So we’d potentially have many schools that are joined to one person. 

For sake of sort of the demonstration today, we’re not going to talk about one-to-many but what we are going to do is we’re going to make sure that we pulled the Rank 1 school. So we’re going to pull the school that in the list of schools is listed at the top. What I’m going to do is, I’m going to look for “school” and I’m going to select, in this case, “School by Rank Overall”. You’ll notice there’s a couple of different options here. In our next session, we’ll talk a little bit more about how this would work if you needed to pull back multiple school records. But for today we’re going to stick to “School by Rank Overall”.

So we’re going to select “School by Rank Overall”, and we’re going to put in our Rank 1 school. By doing that now, if I go back to export and I look under the School by Rank Overall, we’ll now see information about that school. So what I’m going to do is add the name of the school and I’ll add the city, the region and country. All three of those, all those things are now accessible by virtue of the fact that we added that join. And if I run my Preview Results, you’re going to see we now have a couple with some of our applicants. 

Then what we’re going to do is we’re going to add in a phone number and an email. So this is common to many queries. The thing that you need to know about this is that in Configurable Joins, what you’re actually doing here is you’re joining to something called the Device table. So the Device table is over here. This again is a one-to-many relationship, so you could have many device records for a single person. 

And devices typically include things like email and phone numbers of a variety of types. If you’re also a school that uses custom device codes, like WhatsApp or Twitter or a variety of other things, it would also be in the same area of Slate. So what we have to do here is, because there are many relationships and we want to pull back both a phone and an email, we actually have to connect to the Device table twice. 

So first of all, I’m going to go look for “device”. And in this case, I’m going to use my “Device by Type and Rank”. What I’m going to do for the first one is connect to the email address. And I’m going to pull back the Rank 1 email. So with that, you have to make sure you select any device type you want.

Now, this is where a good strategy can be employed with the name of the join. And you’ll see how that carries over in a moment. So what I’m going to do here is type in “email” and I’m going to just add that to the front of the name. When I click “Save”, that’s going to then be carried down here.

Now, the second time I’m going to join back in is for the phone number. It’s going to be the same join. So I’m going to go ahead and find my Device table again. And I’m going to pick “Device by Type and Rank”. And now I’m going to select, let’s do “mobile phone”. So I pick my mobile phone. I’m going to pick the top mobile phone in my list of mobile phones, which will be the Rank 1. And again, I’m going to put a name into my alias. So by doing that, now I have two joins to the Device table. So in essence, I’m going from person to device twice. And what I’m able to do now is in my exports, I’m able to pull that information into my query exports.

So here it is, mobile and email. And you’ll see that the alias or the name that I put into the join, now carries over into the area. So you know very specifically, which version of that device record you’re pulling. So in this case, what we want is we want the value for both. I’ll go ahead and click “Continue”. And by virtue of doing that renaming now, we can see which is which. So you can see here’s the value for mobile, and here’s the value for email.

You can also further rename that if you’d like, this is a standard thing with any of the queries in Slate. So I’ll go ahead and we’ll type in “mobile” right here. And I’ll go ahead, and in my email, I’ll do the same thing. So now if I go ahead and run my Preview Results, you should see a couple of mobiles and emails, mobile phone numbers and emails.

So finally, we want to pull the decision. So the Rank 1 Decision in Slate would be the most recent decision code. So you could obviously have, or maybe not obviously, but if you’re new to Slate, this is new to you. But in Slate you could have multiple decisions per application record. So, what we need to do in this case is we need to make sure that we’re pulling the most recent one, which again, using that terminology of rank is going to be the Rank 1 decision. 

If I go back to my Configurable Joins Base Explorer, I’m going back to my Application records. So I jumped from Person back to Application. I’ll go ahead and click on this. And now I’m going to look at my diagram and I can see that I have a join from the Application to the Decision. And you’ll see that thick line, that means a one-to-many.

So this is where again, we’re going to use our Rank join, and I’m going to go ahead and add it here. And what I’m going to look for is “Decision”, and I’m going to pick, in this case I want to use the most recent decision that has been released. So that’s a differentiation. You can do it, a variety of statuses, but we’ll stick to “Rank Released”. Go ahead and click this. I’m going to go ahead and put in my Rank Released, which is 1, so this is going to be the most recent decision that was released to the individual, click “Save”. 

And now by virtue of doing that, I should have an export. If I look for “decision”, let’s actually do it this way. I’ll scroll down, and here it is. So now we’ll go ahead and pull in the name of that decision, which is here, and you’ll see that it carries over sort of the join name into that. And now if I run my Preview Results, I see my Rank 1 Decision.

So that is a basic Configurable Joins query. Hopefully that gives you a sense of some of the things that we’ve talked about. And for our next couple of sessions, we’re going to be going into much more detail about, like different combinations of this, dealing with one-to-many relationships and subqueries. But if you can master these basics, you really will be in a good place to understand Configurable Joins and to sort of build on it. 

And I think all of us would encourage you to do that, to simply get into Configurable Joins, experiment, try to use the different things that we showed you today. And in particular, having this Configurable Joins Base Explorer will help you see visually, or if you prefer a list result, sort of how you can join the various things.

So I hope this is helpful to you. And with that said, I’m going to turn it back over to Megan to wrap us up today.

Megan:

All right, thanks so much, Abraham. I appreciate all of that, that’s really helpful. We saw some really great questions coming in while this was all going on, and while we don’t have time to answer those today, we are going to be putting out an article in the next few days where we will respond to those questions. There will also be the recording for this included in that as well. So you will get to hear all of that soon. While I think we could probably talk about this all day long, we are out of time for now, so we need to wrap up.

We’d like to say thank you so much to Dom, Abraham and John Michael, for all of this great information, but also thank you to all of you who chose to spend an hour-—a little more than an hour actually—with us today. We are really looking forward to seeing you at our next session, which is called “Building Up: Intermediate Configurable Joins Skills and Use Cases”. That will be on March 2. If you are not registered yet, or if you know someone else who might find that useful, you can head over to our event page at rhb.com/webinar. 

Once this event is concluded, you’re going to see a brief survey also display in your browser. So if you have feedback for us, we would really love to hear it. That will help us to keep making these presentations more helpful and more relevant for you. 

But until then, we’ve got to say thank you for now and say goodbye. So best of luck to all of you in your Slate work and happy joining! Bye for now.