On March 30, RHB’s Abraham Noel, Dom Rozzi, John Michael Cuccia and Megan Miller hosted part three of our three-part webinar series, Build a Better Instance with RHB: Configurable Joins. In “Expanding into New Spaces: Configurable Joins Advanced Concepts,” our team discussed independent subqueries, Related and System bases and some of the clever ways we can utilize Configurable Joins in areas that you might not have previously considered. Here, you can read a transcript of the full presentation, or read the takeaways and a transcript of the question-and-answer happening in the chat during the event. You can watch the talk here. And, you can revisit all the great information from our March 2 (transcript, event recap with slides, and video) and Feb. 2 (transcript, event recap with slides, and video) events. Any time you want to talk more about this or any of the other ways you can get more out of your Technolutions Slate instance, we are here to share our expertise.

Megan Miller:

All right, let’s go ahead and get started.

Hello everyone, and welcome, to the third and final webinar in our series, “Build a Better Instance with RHB: Configurable Joins.” Today, we get to “Expand into New Spaces,” where we’re going to take a look at some of the more advanced Configurable Joins concepts that allow us to take everything we have been learning to a new level. We’re glad you decided to be with us today. My name is Megan Miller, and I have three of my RHB colleagues with me today. So along with John Michael Cuccia, Domenick (Dom) Rozzi, and Abraham Noel, I’ll say thanks for being here.

We have finally arrived at part three of our three-part series where we have been taking an in-depth look at how Configurable Joins work, and also how we can use them to do more in Slate. We kicked all of this off back on Feb. 2, 2021, when we explored Configurable Joins concepts and basics—those fundamentals of Configurable Joins. During that session, we got an overview of the structure of the Slate database, and we learned how to build one-to-one top-level joins. Then on March 2, we talked about some of those intermediate Configurable Joins concepts. We especially spent some time diving into dependent subquery exports and filters. And now today, we’ll be wrapping up this series for the time being as we explore advanced concepts surrounding independent subqueries, and the Related and System bases. And then we’ll wrap that up with a little bit of conversation about what all of this means for our work across our Slate instances.

I’ll note a couple of things as we get started. This session is being recorded, so we will share all of that, as well as the transcript, our slides and some key takeaways, once this event has concluded. Within the webinar platform, you’re also going to see a question-and-answer section, and we will respond in real time to your questions there. So please don’t be shy if there’s something you want to know. And then after this event is over, we will also post a recap of that Q-and-A as well.

As we get going, here are a few things you can expect to learn today during the webinar. We will start by taking a look at independent subqueries. We’ve seen a lot of questions about those and at long last, we have some answers for you. We’re also going to discuss the Related and System bases that are available in Configurable Joins, examining what they are exactly, and then how do we leverage them? And then finally, we’ll talk through how we can take what we’ve learned over the course of these three webinars and apply it across our instances in some pretty innovative ways.

Now, a lot of you know us pretty well by now but I’m going to offer a really quick introduction. RHB is a higher education consultancy that’s now in our 30th year, and in that time, we have guided more than 300 institutions in enrollment management, institutional marketing, executive counsel to presidents and cabinets, and Technolutions Slate services. Our firm now has a footprint in more than 10% of all Slate instances, where we offer best-in-class implementations, diagnostics, advanced builds and training, as well as providing ongoing support and consulting to our clients.

A few of us are here today. We’ve got John Michael; he is one of our Integration Consultants, and he came to RHB from Rice University back in January. Dom, Abraham and I are all Senior Integration Consultants here. Abraham joined RHB last year after a career at Macalester College, while Dom and I have been here since 2019. Dom previously served at Franklin and Marshall College, and I was previously at Seattle Pacific University.

I’m going to hand this off to John Michael in just a second, but we do want to quickly reiterate the main concepts from our last session before we jump into that. You can read about all of this in more depth over on the RHB Insights blog, but here is a really high-level summary.

First, dependent subqueries: they are the tool to use when we’re working within one-to-many relationships. They allow us to look at a record and its related data in greater depth than those Local and Slate Template Library queries can, so that we can see the whole story of what’s happening in our database in ways that are customized to what we care about.

Second, we dug into subquery exports. Those give us the ability to summarize, aggregate and format our data in the ways that are important to us. While we can certainly do things like concatenate data points from the tables that we’ve joined to, we can also go beyond just grabbing those values, and we can employ outputs like aggregates, formulas, existence exports, and some more advanced formats such as XML and JSON.

And finally, we talked about subquery filters and how they allow us to precisely define our search criteria. We can define that criteria in many ways, and sometimes we may want to know whether a value exists or does not exist. But at other times we might want to utilize some of those more sophisticated comparative operators to really narrow things down. And of course, we can also create what we sometimes call “subquery inception”: using subquery filters within the subquery exports or within other subquery filters, to dig really, really deeply into the information that’s available in our database.

So like I said, if you would like to review or revisit any of this, all of it’s available on rhb.com. You’ll find articles, transcripts, recordings—all of those things that’ll help you out. You can check all of that out at any time. But for now, let’s go a step further and explore how we can move from being dependent to independent. John Michael, go ahead and get us started on that.

John Michael Cuccia:

Thanks so much, Megan, for getting us started today. Hi, everyone. Good to have you back with us for the third part of our series on Configurable Joins. Let’s jump right in.

To start, let’s quickly revisit dependent subqueries. Remember that most subquery exports and filters you build in a Configurable Joins query are likely to be dependent. That is, the subquery will always relate in some way back to the base of the main query. We start a query in the Application base, which means each result row in the query will represent one application. We can join to Person and build subquery exports and filters based upon that Person data, which all depends on that application result row. We can join to Form Response from either Application or from Person, depending upon the use case, and build subquery exports and filters based upon a particular form response. Again, all fully depending on that specific application row, because that’s where the query started.

An independent subquery allows us to break out of the main query, and run a fully independent query that has no dependency on the base of the main query. Again, starting a query in the Application base, adding Application-based filters and exports. We can join to Person, build dependent subquery filters and exports. And then, for example, we could add an independent subquery export to some other base entirely, like Form. By making the subquery independent, it breaks out of the original Application base of the main query and runs a fully independent subquery. This subquery has no dependency at all on what’s happening in the main query.

Visually represented here, is an independent subquery export that queries Forms and returns the form name and date concatenated together. It has absolutely no relationship to the application or the person information that’s being returned in the row. So of course, this naturally begs how and why would we use this? Let’s take a look at a couple of examples before we move forward into building.

In this first example, we’ll use an independent subquery export to compare an applicant’s score to a cohort’s average score. So in this snippet, I’ve started in an Application base query, and I’ve pulled in the academic performance rating for that application. That’s the top export: ACA performance rating. My next export is actually a subquery, and in this particular case, it’s an independent subquery. Starting over again in the Application base that is one row per application, I’ve joined over to my Reader review form for my undergraduate counselor review, and I’ve pulled in the academic performance rating.

But I’ve told this independent subquery to be an aggregate output of an average, so what my result rows are going to look like is what you see on the right side of the slide. One row per application, based on my main query. And in the left column, I’ve got my individual academic performance ratings five, three, four, three, and five. And then my second column is actually the same all the way down. It’s this independent subquery average of everyone’s academic performance rating in the cohort.

Another example: let’s say I’d like to search for Person records who do not have a volunteer interviewer in their region. I start my main query in the Person base, and then I make an independent subquery filter that breaks out of that main query, and start instead in the Alumni Volunteers base. Now this particular subquery filter goes from being independent to actually being dependent again—that’s why I have an asterisk at the top. The reason that it becomes dependent again is because I’m taking a piece of information from my main query—that export that’s labeled “Person Active Address One Region”—and I’m using it inside of this independent subquery in order to compare to my volunteer active addresses and find where the person does not have a region of an active volunteer.

Here’s an example of counting call response forms by user and name of the originating call list query. This is in the Report Builder. I’ve started in a base of Form Response. I’ve had the responses initially grouped by the user who submitted that call form response. And then I’ve used an independent subquery export to connect that call response form back to the call list query from which it originated. So again, there’s an asterisk here because inside of this independent subquery export, I’ve actually made it dependent again by injecting a piece of information from the outer query itself. In this particular example, then, I can have my groupings: here are the number of response forms submitted by each user. And here’s how many of those call response forms actually related back to each of these individual call list queries that were used to put in these call response forms.

The last example is the one we’re going to demonstrate building today. This is a Deliver message where we’re reaching out to students and letting them know about the next three campus visit events that we have. Now, this is an ongoing Deliver message, and of course, as time goes on the available events are going to change. And we don’t want to have to stop and start this Deliver message every time one of our events completes or rolls off of the schedule and the next ones start to roll in.

So here we have “Preferred, we’re excited you want to join us on campus! Here are our next three upcoming campus visit events.” And then we merge in information about those events like the name and location, date and time, and a registration link behind the scenes on the green button that you see. This utilizes an independent subquery. And Dom is going to take us through building this together.

Domenick (Dom) Rozzi:

Thank you so much, John Michael, for that wonderful setup on independent subqueries. As John Michael mentioned, utilizing independent subqueries can be very useful in constructing data outputs that are not necessarily dependent on the rows of the query, but can still be iterated through just like the rows of the query. This is especially helpful in Deliver mailings, but will likely find its way into reporting and probably in portals too.

The example that we’re going to go through today is setting up an ongoing Deliver message that iterates through three campus visits that we have on our calendar. We’re using campus visits as our example today, but this could be the next three open house events, or the next two times a counselor is going to be in the vicinity of a student’s school. As you can tell, I’m pretty hopeful that we’re going to be getting back to travel-based and face-to-face recruitment very soon. So in that hopeful nature, let’s stick with campus visits.

So we want to set up our Deliver mailing. I am not going to go through all the components of Deliver mailings proper, but it’s safe to say that there’s going to be some configuration on this end, and we’re using a template for our message. Today, we’re going to focus specifically on our recipient list. And to not belabor too many of the other inner working components— because we do want to focus on independent subquery here—I’ve started to build out what we’re going to be looking at here, specifically related to how this query starts to get constructed.

It’s important to note that we’re looking at Persons, and we don’t have any specific table joins that we’re going to need to create that are in a one-to-one environment. So we don’t have any joins here at the query base level. You will see I have subquery filters, and this is because we are joining to other tables as it pertains specifically to getting at certain data points—namely, whether a student has attended a campus visit in the past, and whether they’re in the population timestamp that we are focused on. For our identification of students who have visited campus in the past, what we need to do is focus on form registrations.

And it’s important to note when we’re using the Form Response join that we know the form that we’re trying to get to and what the scope of that form is. In this particular case, I know that our event is Person-based, and so I can use the Form Response that’s in the Person table as a means for getting to those form responses. If you were using an Application-based form or an Application-scoped event, then you would need to make the join to the Application and then do Form Response from the Application table. In this particular case, we’re looking at a very specific event, and that is our campus visit for which we use a template.

So when we come in and do a specific Form join, we can come into our templates, our event templates most specifically, and pull out our campus visits. This join is now tied to that template and will span all of our events that are tied to that template. What we’re looking for here are those students who have attended. This may seem contrary to what we’re looking for; in fact, we’re looking for all the students who have not attended. But in finding all those who have attended, we’re able to do the Not Exists version of the Aggregate output, which will give us, in fact, everybody who has not visited. You can see now our numbers changed. We had two students who had visited, right? So our number went from 167 to 165.

And the other component of our mailing here is that we’re trying to do this as an ongoing message. And so we want it to cycle through students in our population at a certain sequencing of time, and that is defined as 20 days. So again, it’s a dependent subquery and we’re going to join the Populations. We can define that Population by Name, and we can define that Population by Timestamp Days. In turning on our filter here, I did happen to notice that we have zero records. And that’s because, of our records in our instance, we don’t have anybody that is on the current 20-day timestamp. We do have students on our 29-day timestamps. So I’m going to go and edit our record to be 29, and we can see we have eight students who would be scheduled today to get this message. And this is all we need for filters.

So now we’re going to jump up to our exports, and we would pull in our normal things like preferred name and email address…I’m going to grab our email address. And then we’re going to pull in our independent subquery, which is to say, we want to then display the next three events from our campus visit folder that we want to display in the communication. So we’re going to do that as a subquery. And instead of using a dependent subquery, which is going to be reliant on this specific person that is coming out as the row in our query, we’re going to do an independent subquery. That independent subquery is no longer a record—we’re not looking for a record. In this particular case, we’re looking at our related tables.

And so, here, we want to go to Form. With our new query that will be coming off of Form, we no longer have the constraint of which person we’re actually considering here, and instead, we’re looking at all forms. Now we have to create the specificity to show which forms we actually want. More specifically in this case: which campus events are we trying to display in our mailing. So when we open up our filters in this case, we actually are presented with the new section, and it’s identified as an independent subquery. This is our query inside of our query. I want to look at things like our event title. I can find Title. You can also pull in Start Date, and I’m going to pull in our Status as well.

We’re going to start with our folder. So we want to look specifically in our Campus Visit folder. We’re going to look at events that are happening in the future, and that’s to say, that are beyond today. If we wanted to, we could also show ones for today but I think there’s probably going to be some sort of timing delay that we want to establish for the purposes of registering in advance of the event. So let’s just go with Greater Than Today. Maybe this becomes Today + 1. And then we want to look for our events that are actually set to be confirmed, right?

Or perhaps we use Public as a mechanism for not displaying certain events that are actually not intended to be displayed. And so let’s focus on our public events only. Now that we’ve identified what we’re showing, what within that are we looking for? So for our exports in this particular case, we’re looking at things like Title and Start Date. We’re going to also look at Location, and we want some students to be able to register on the fly so we need to pull in our URL.

These are going to ultimately be best served if we rename everything. So I’m going to come in and rename these for the purposes of a merge. And so this is going to be our Start Date. We also want to make sure that we format these. In this particular case, we’re going to do a full date, and so we’ll look at something like a format that is the full month written out with the day and a year. I’m also going to want another date which we can copy, and we’re going to call this one “s-date2”. In this particular case, all I want to show is my month and day. And I’ll explain why we’re doing this when we go to our actual mailing side of things.

I’m going to iterate another version of this. It’s strictly just looking at our time. For all of these codes that are related to timing and the format of date timestamp, I do use the Microsoft resources. But I believe there’s actually a link in the Slate Knowledge Base, as well. So we’re going to rename our location and I’m going to just call my URL here, “reg link”.

Now we have all of our components inside our query that we want to iterate out into the iteration of the three events that we’re going to show. It’s important to note that we only want three records to come out of here, so we can do a Row Limit of three. And in order for this to be able to come out in merge for each of these events against one record, we want to actually create this as a Dictionary output. This is something that we talked about last time briefly, but this is a perfect use case for using a Dictionary output.

Now I’m going to go ahead and put a label on this particular export. And this is important when it comes to being able to iterate this information out. This becomes the node by which we’re going to do our Liquid loop. So we’ll call this “Upcoming Events”.

Now that we have all of the componentry here that we need for our query, we can run over to our message. Again, we have a template in place that we are going to use for our message. So we can pull in things like our name; we can highlight this and use Preferred. And we can put our email address in just as we normally would, and we’ll create a fun label, check out these future events.

Now here comes the fun part. Remember our export is actually going to iterate three versions of the next upcoming events, and so we need to create the ability for this to iterate those three events out. I’m going to jump over to the [HTML] source real quick—and I know this is scary for a bunch of people, but bear with me. We have some coloring differentials here that are going to help out, and in this particular case, I’m looking for some black text that specifically says “event title”, and “visit”. Here’s “event title”, and here’s my “visit”. This is the section that is going to be repeated over and over again for the three different events, so here is where we’re going to start our For Loop.

Now, this is where it’s important to know what we used for our export. In this particular case, we’re going to identify an alias that we’re going to call “Event” in our export, which is called “Upcoming Events”. What this is basically doing is creating this alias called “Event”, in which we’re able to then tie to the merges that were inside of our Dictionary loop. So I can come over here to our event title, and I can create a merge field called “event.title” I can come down here to our visit area, and I can put in what we want our button in this particular case to be, which is going to say “Visit on” merge “event dot”—in this particular case I’m using my timestamp s-date2. And that was the one, if you’ll remember, that was just the two-digit month with the slash and the days. On my event s-date3, which was if you’ll recall, my hour and minute.

And so I’ve constructed inside my link: visit on what I anticipate to be the day at what I anticipate to be the time. And now we have effectively our loop which is going to have our title, and it’s going to have the button that is our event by date and time. And so if we save this, it’s important to note that we have to close our For Loop. Let’s come over here and find the end of our final spacer, just here, and we close off the loop. What we should have, we should expect to see something that looks like this.

And when we go and save this and we apply it to our student record, we’re going to see that we’ve got our campus visits iterating out for the next upcoming visits. Now, what we see here is that we have our visits but they’re not ordered correctly. So we go back to our query and we take a look at our export. I was remiss in actually putting in a sort for our date and making that an ascending one so we’re going to see the next future events. And if we come back to our event, we’re now going to see the upcoming next visits.

Now I’m going to pass it back to John Michael to go over our next exciting topic, which is using Related and Non-Record query bases.

John Michael:

Up to this point in our series, we’ve really focused on the Records type of Configurable Joins query. Again, there are three types of Records, Related and System. We’re revisiting our “List for Ants” as we move into the Related category, where you can see there are a lot of table choices here. These choices in the Related category are query bases that relate back to some parent record. Remember, you choose your base upon what you want your result rows to represent. So always stop and ask yourself, “I want one row per blank.”

Perhaps you want a query of Gifts. You start with a related base query, that returns one result row per gift, and this might have information about the date of the gift, the fund of the gift, the amount of the gift. Then you can use this inside of Slate’s Report Builder or an external reporting tool to do yearly reporting on gifts received in a fiscal year.

How about we break the universe with a query of Queries? In the Related category there is a choice labeled Query where you can build a query of queries, where you could output, say, the name of all of your queries, the user of queries, the date that the query was created. A use case for this might be, you’re looking for queries from a user who formerly worked at your institution. You need to see all the queries that that person had built with ease without having to navigate through the other interfaces in Slate.

You could build a query of Mailings. Those are the messages inside of Deliver. A use case for that might be, let’s say, that you have communication-building processes around having the date filled in on upcoming Deliver messages. You can build a Mailings-based query inside of a Deliver message, looking for other Deliver messages that don’t have a date configured, and then sending a notification internally to some of your users alerting them to say, “Hey, this Deliver message exists but it doesn’t have a date yet, so you might want to fill in the date, because otherwise it won’t appear on the Deliver calendar.”

To take us through our Related base example, Abraham’s going to work with us on the build this time.

Abraham Noel:

For my part of the presentation, I’m going to drill into one of the things that John Michael brought up, which is the Related tables and Related base queries.

If we go back to John Michael’s list of types of queries that you can run, generally speaking they’re going to be in three large buckets. One is going to be the Record-related queries. There’s going to be the primary records of the system. So applications, it’d be person records, it could be organizational data set records, organizational context, so forth and so on. Underneath it then are Related records. Related records are typically going to be records that support those records in the sense that they’re going to provide additional detail. So you think about things like Tags or you think about things like Decisions if you’re working with an admission system, or think about things like Deliver communication where you could have multiple messages to a single person or Form Registration. In this example, I’m going to dig into that one specific thing.

So what is a form registration, really? Well, when you look at a form inside of Slate, you typically have a configuration like this where you have this at the top and then you see the registrants at the bottom. Listings can be customized to a point, but really this is how it’s built. So we have a single form as a concept, and then underneath that we have potentially multiple registrants. Now this idea applies not only to forms, but also to events as well as the Scheduler. You can take what I’m going to show you and apply that across all three of those categories. But to keep it as simple as possible for the demonstration, I’m going to talk exclusively about forms today.

Specifically, what I want to be able to do in my usage case is, I have two RFIs. I have an internal RFI and a sample RFI. And between those two forms, I have eight records, so seven on one and one on the other, and what I want to be able to do is write a single query that will query across both forms simultaneously. Now, if you’ve ever gone into a form, you’ll notice that there are these two buttons over here. And if you click them they actually generate a query for that individual form. The main limitation of that, though, is that that will only work for that single form, so you can’t use that button actually reporting across both.

Now, I do want to point out one thing if you haven’t done this. This New Query button actually will generate all the fields in the form and pull back for each one of the records. It’s using this older method or the method that was pre-Configurable Joins. The second one, though, if you click it, this actually gives you access to a full Configurable Joins query, and you’re able to take this then and further customize it. But the main limiting factor here is, it’s against that base of the actual form itself. So in this case, the form is called Internal RFI, and I can’t really go beyond that. It’s only going to allow me to report records against that single form. To build one where you can query against multiple forms, you have to go to the full query tool, and you have to use a couple of different methods.

So the first one I’m going to demonstrate is the Form Response Related base. And what that is, is we’re actually querying against it to go back to the form, all the different registration responses in that form and actually every form in the system. To do that, I’m going to go to Quick Query, and I’ll pick my Configurable Joins, and I’ll pick my Related category. And I’m going to pick a base of Form Response.

Now, it’s really important that you pick this correctly because this is the one thing you can’t change on a Configurable Join when you create it. If you have to change it, you have to go back and redo the query. So I’m going to pick Form Response. And you’re going to see that under that there’s a couple of options. So we can pick “All forms and events” or we can pick a “Specific form, event, or template.” There will be cases where you’d want to use that and in a way what this does is it replicates that button on the actual form itself where it’s going to pull back records against a specific form. For this case, though, because we want to query against two forms, we want to pick “All forms and events.” So I’ll go ahead and build the query, and what you’re going to see then is a large number of records.

Just looking at this and comparing to what’s actually on these forms, they’re not even close. So we only have eight records between the two here, and here we have about 2,800 records. What I have to do out of the gate is to do some joining and some filtering. The first thing is, because this is Form Responses I have to join to the form itself. So this would be analogous to going from these records, which are many, to this one record, which is the form. To do that, I’m going to go to Join, I’m going to click on the Form Response. From Form Response to Form I’ll click Continue, and I’ll save it. And now by virtue of doing that, I’m going to have access to the title of the form. So there it is.

And I can say, let’s go ahead and pull back the folder that the form is in. The reason I’m going to show you the folder is, if I pull this as it is now, I’m going to pull back all the different forms that are in our system, and what I really want to do is just get it down to these two. In that case then what I want to do is use the Folder filter. You’ll see that they both happened to live in the same folder, which is called “Inquiry”. So what I’ll do is, after I’ve joined to the form, the form joined down here. I can access the Folder filter. I’ll go ahead and add that, and I’ll find Inquiry. And now we’re down to eight records. That’s a little bit more reasonable and it exactly matches what we would expect between the two.

So if I pull that back, I’m just going to get records for each one of those. Now, what I need to do is actually, if I want to pull who’s registered on those forms, I have to join from the Form Registration to the Person table or Person concept. Once I’ve done that, now I can access the information on a Person record. So I’ll pull up the Reference ID and the Name, and I can pull back all sorts of other things. I’ll reorder it so it looks a little bit better and I’ll go ahead and I’ll run this. Now you can see that we’re pulling back the records with a name for each record, and we’re pulling it across multiple forms.

So this is really powerful, because imagine a scenario where you need to do a report against multiple RFI forms, or probably a more accurate example or a more common example would be, you have many different types of events and you want to know everybody who’s registered for all those different events. You would be able to use a query like this to quickly do that.

Now let’s say for example, I want to take this and I want to pivot it the other direction. Instead of seeing the detail behind each one of these forms, I now want to pull back some counts on each one of these forms. So it’s going to be a similar technique, but instead of starting with the Form Response base, we’re going to actually do it against the Form base. I’ll go ahead and pick a new one, and I’m going to create a new query. I’m going to do Configurable Joins like I did before, Related. And I’m going to go ahead and now pick Form.

So if I do this, I’m now pulling back every form that exists in the system. I want to provide a filter and I happen to know that these forms live in a particular folder called Inquiry. I’ll click on that Folder. I’ll find Inquiry. I now see my four records. So there’s probably some other forms in there. And what I’m going to do in this case, is I’m going to pull back Title, and that would be it. And if I run that, I’m going to see the four forms that are in that one particular folder.

But maybe the question is, I want to know how many people are registered for each one of these. How many records do I have underneath that? To do that then, I’ll go back to my query and this is where you’re going to use a dependent subquery. So I’m going to pull this in. And with dependent subqueries you always are going to need to join again, inside the subquery itself. I should say for this type of particular scenario where you’re trying to tell the system to count registration, you have to tell the system how to get at those counts. So what I’m going to do in this case is to go down and I’m going to join, and…I’m going from Form to Form Responses. This is analogous from going from here down to these records down here. So I’ll click on that.

And, now, you’ll notice that this one has a little hat next to it, the wizard cap, if you will. This tells me that there’s some configuration that’s going to take place. But with this particular type of record, I also happen to know that these are many records. So it’s similar to this but there’s some additional configuration we can do. So I go ahead and click Continue, and what I’m going to do here is like before—I’m not going to pick a specific form or event, I’m going to pick “All forms and events”.

If I do that and I save, now I have access to all the records connected back to the form itself. The final step is to go in and pick an Aggregate output because I’m doing a count, and I’m going to go ahead and pick my Count. If I run this, I should see a list of the forms and then the registering count for each form. So this is a way of pivoting that information and showing it to you in a different way.

And you could expand this, and I’m not going to do that because we don’t have time to go through it all. But you could expand this where you could join from these registering counts to individual Person records and then look at the status of those records so you could get the number of inquiries or applicants—you could get the number of people from a decision standpoint, who enrolled, there’s all sorts of stats you can pull. So this is the basics of how to do that. With Configurable Joins, this is a really powerful technology that allows you to make changes, and quickly set this up in a way that wasn’t possible before, or it was more difficult in terms of how to write custom SQL and other things like that. With that said, I’m going to close on my part and I’ll turn it over to my colleague [John Michael], and they’ll continue with the presentation.

John Michael:

One last time, we’ll revisit our list to talk about the third and final category of Configurable Join query type, the System type. These are System tables inside of Slate. And even though some of the names are similar, if not identical to what you see in the Related type section, they are indeed giving you different results.

So Checklist, for example: Checklist in the System category will give you one result row per Checklist item as they exist in your database, whereas in the Related category, the Checklist base will give you one result per row per Checklist item, as it exists across all Application records. In the Related category, those are the individual checklist items across all of the applications. In the System category, a Checklist base is going to give you results very similar to what you see when you go to the Checklist area in the Database tool.

I want to highlight a very special choice in this category. It’s the System choice. You’ll see it in the fourth column, third row, just after Workflow and before the World choices. This special System choice allows you to do something very interesting in Slate. Normally, when you pick a base you notice that the Matching Rows indicator next to Filters is usually some number much greater than one. Thousands of decisions, hundreds of thousands of people, tens of thousands of applications, but sometimes you only just want one result row. When you pick the System type and then the System base, that’s going to lock the query down to just one single result row. And then the magic happens when you start adding independent subquery exports to the System query.

I can get very high level information about my instance, and query for the number of Person records in totality in the instance, then total number of Application records, the total number of Form Responses, the total number of Released Decisions. I only need one row to tell me that information. But these independent subqueries are going to give me an aggregate count of the information I seek.

The part that you see edited in the pop-up is my “Number of Released Decisions” independent subquery. I’ve chosen the Related category and the Decision base, which is going to count individual decisions, and the only filter that I’ve specified is that they have to be released or received because received decisions have also been released. When I run this query, here’s what my output will look like. It’ll tell me that the number of persons in my instance is 167. The number of applications in my instance are 30. The number of form responses, 2,867. And the number of released decisions, 13.

Megan:

Alright. Thanks so much to all three of you for breaking down some of these more complex concepts so that we can really connect the dots between all of these components that come into play with Configurable Joins. Of course, that conceptual framework gets us started on a path to apply what we’ve learned over the course of these three webinars. But, we also want to take that a little bit further. The four of us have spent a lot of time in Slate instances with a lot of varying business processes, and a lot of varying goals. So we’ve seen a lot of really robust use cases for Configurable Joins, and I think it’d be great if we could talk about that a little bit more. How do we do more in Slate with Configurable Joins?

And so John Michael, I’ll start with you. I know you’ve thought a lot about how Configurable Joins can simplify a Slate user’s experience, and one of the things that you’ve looked at is how we can consolidate, or enhance for that matter, our index and header rules. Can you unpack that a little bit more?

John Michael:

Sure, Megan, thanks. Index and header rules kind of this primordial world of some of Slate’s origins where you’re having to construct them with formulas or custom SQL. Let me toggle this on really quick so you can see.

So, here’s an example of an index header rule in our RHB instance where I can actually toggle back and forth here between a Custom SQL view and a Formula view. But I would wager that a lot of people’s index header rules—the rules that help populate the omni search in the top corner—are built this way. And they could be a little bit difficult to decipher even if I toggle it over to Formula. Yeah, it’s a little bit easier. I can pick some exports, and then write my formula in SQL and concatenate that way. But you know, this formula is actually not going to always work for me. It has a kind of limited shelf-life because I have to come back in here and add more exports, and change things around. And sometimes Slate doesn’t like if you throw a no value in or if it receives a no value in one of these spots, that can throw off that rule and you don’t get your expected results.

This is an example of an index header rule that is built in Configurable Joins, and you’ll notice that the setup changed. I’ve got all of the Configurable Joins icons where I can join from this particular data set and cross over to any other spot in the database, any join pathway that I can follow. But look at how simple the formula is. It’s just this one variable, and I’ve got this subquery export doing all the heavy lifting for me.

In this Employee data set, I’m joining over to the service record for this employee that’s stored on the record. And I’m doing it in descending order so that it grabs the most recent one. And I take the year with the space, their position, and then some other information on campus if it happens to exist. But then all I have to do, in the end, is just call that one that export. I don’t have to do any complicated SQL down here in the Formula area. So it makes lifting with my index header rule so much easier, and actually so much more capable and powerful, because I could just join through to other different data points throughout the database and work that into what’s put into my index header area. So that’s a neat little power that Configurable Joins can give you in your index header rules.

Megan:

Yeah—and certainly can simplify a lot of other rules beyond just that. But that’s a really good example of one way to consolidate down. Before it was probably really hard to maintain, especially, if you don’t have folks who have a solid understanding of SQL, who are users in your instance. And being able to take it into something that’s a little bit more easy to translate for anyone who’s looking at it.

In terms of some of the other objects that exist within Slate, we look at entities and how much they’re able to do. They’re able to capture so much information, but that is truly a one-to-many relationship, no matter what. So it seems like it’s a really good use case for using Configurable Joins. Let’s talk a little bit about how we might use Configurable Joins as we’re managing entities, reporting off of them, exploring some of the data that exists within them. I don’t know, Dom, do you want to start with that?

Dom:

Sure. Thanks, Megan. Yes, it’s interesting.

I think of entities as effectively containers for related fields, right, and so in a lot of ways that we’ve already discussed as it relates to Configurable Joins. Unpacking that container—so going into the container and spreading around and looking at all the data that’s inside of it—is exactly what we’re doing when we use Configurable Joins to join to an entity. Just as an example, something like financial aid data: we’re going to have an award year and an amount and a name, potentially a couple other data points there as well. But it relates to an application, or it relates to a person. And so when we start at our Application base and we do that join into the entity, we’re going to have an opportunity to expound on all those data points contained within one row of the entity. And then pull out all of the rows potentially through subquery exports, concatenating those data points and those sorts of things.

So there are a couple of other interesting use cases. Scholarships is another one that’s been broadly talked about in the past, but these are exactly what those entities are going to do. And having that mechanism of being able to tap into those entities directly, to pull that out is a great use case for Configurable Joins.

Megan:

Yes, and I think the more we can think about how we can take these relationships that have always looked a little bit more complicated and recognize those opportunities to simplify them down, the more we’re going to be able to start to realize, oh, it doesn’t just apply to entities. Then you look at something like, you know we’ve talked a lot about schools. Like, Schools is probably the most popular example for how you’re using Configurable Joins. But taking it beyond that I think about Sports, for instance, where you can have those which kind of function like an entity in a lot of ways, and thinking about how that can revamp some of the ways that you’re using, for instance, like the Coaches Portal and things like that.

Portals are one of those things that are such a hot topic in Slate right now—like portals, portals, portals. We love portals over here. I think we’re building 500,000 of them right now, maybe a little less than that, but it feels like it’s about that. And I know that I’ve seen questions come in over the course of these sessions as well about, “Well so what do we do with portals and Configurable Joins?” So, what do we do with portals and Configurable Joins? What are some of those opportunities that exist?

Abraham, I know you have lived a good amount of your RHB life in portals since you joined us about a year ago. So what would you say are some things to think about there? And I think all three of you probably have something to say about this.

Abraham:

Yes, most certainly. I think that it comes back to the idea of the “configurable” part of the join. So if you think about what you’re doing when you’re translating between two different query bases that are unrelated or partially related, or kind of connecting different parts of the system that are perhaps inherently related, that’s where Configurable Joins are really powerful. Because you can do things like, you could connect from a Person record to a School record, and then connect back to different Person records, or you connect to a different data set, those kinds of scenarios.

Configurable Joins give you the ability to do that dynamically on the fly. Whereas in the past, you really would have had to script those out using probably custom SQL, frankly. I mean, there would have been examples of some of that in Technolutions’ library, where you would be able to leverage that. But if you built anything that was a custom dataset, you’re in a situation where you’re going to have to replicate the custom SQL somehow and change some things to get it there. You as an end-user representing your institution have to do that.

With a Configurable Join it’s completely built in. So you basically spool up the datasets, you create those, you then build some relationships, and you refresh your Library, and then all of a sudden you have access to all these other things as long as you have built the connections correctly as far as fields. It’s really powerful and really flexible in that regard. And when you have that, then in terms of what you can do with the portals, think of, for example, a Volunteer dataset where you might have a situation where you have volunteers that are affiliated with an aspect of your outreach, whether it be advancement or admissions. And you take that, and you use that as the basis of a portal. In the past, you would have had to probably build it using custom SQL, or you would have had to build it using a combination of other things. But you take that and you use Configurable Joins. You make that the basis of your queries that then supply your views, and you’re able to do all sorts of different connections. So it’s an incredibly powerful tool for portals because of that one thing.

And actually I’d say…I think others are in this place too. I like to default to that whenever possible to build portals. It feels like it’s the contemporary method and it’s what we should be using when we do that work because of all the things that Technolutions has added on top of it, and the flexibility. The ability to dynamically spin up these connections that in the past would have to be done in custom SQL or direct SQL.

Dom:

Yeah, I think I’ve got to add to that. So when we think of components inside portals, so our static content blocks or pulling in the checklists items or pulling in, heck, even the application selector, we’re afforded the opportunity to create filters on those parts to display them under certain conditions. But those conditions aren’t often just directly related to the application. It might be coming through a decision or it might be coming through a related data point that takes it to a super extreme example similar to what we talked about earlier, where it’s actually the value in an application field but we wanted to make sure that it matches a value that’s on the Person record. Probably more so Volunteer Portal-ish type connection there as opposed to an Application Status Page or something like that.

But these other data points that exist outside of the core of the portal, are oftentimes critical to being able to dynamically display certain pieces. And without having that dynamic join nature, sometimes we don’t get to what we want. So it’s maybe not the first most-recent decision, it’s the second most-recent decision. Well, the decision exists on the record somewhere in the list, but we don’t know where. And that’s all business-process specific and those sorts of things. But we can go in and say, “We want the decision anywhere on the record except when the most recent decision is something else.” And in Configurable Joins that’s a whole lot easier to do than trying to mix and match different things out of the Library, yes.

Megan:

Yeah, I was thinking about what you’re saying about, so often the data that you want to display it’s not quite right in that object. One of the places that I’ve been using Configurable Joins myself a lot recently has been, as merge fields of forms as well, where you’re running into, oftentimes the same kind of situation for Reply Forms, for instance. Like, if you’re trying to display a whole lot of information sometimes to a student when you’re presenting them with the response for their offer of admission, being able to pull in some of those merge fields has been really useful. I think that ties back again.

You think about things like whether you can display scholarship information and, that lives in an entity, or other data that might be far-flung in the system—being able to have that all consolidated there, that’s something that I’ve seen schools want to do more and more of in ways to have the information all up front in one place. But being able to do things like customize your forms more with that data because of the fact that you can use Configurable Joins to get to it, I think is another thing that we should always be considering when we’re looking for new opportunities to use that information more effectively in front of the student as well as behind the scenes within Slate.

Dom:

Yes, exactly. But it’s actually funny because John Michael and I were working on something the other day and literally sent a message that said, “how did we ever build forms before we could merge data in,” right? Adding on Configurable Joins to that opens up the entire student record, the entire application record. Or taking it to our independent subquery. The entire database all can be dropped right into that form.

Megan:

All of this can be yours! You’ve just got to learn those Configurable Joins first. You have to understand what you’re doing.

Abraham:

Megan, I think you talked a little bit about the merge fields, the ability. Let me say one other thing. I think [about] the ability to have user-maintainable setups. You think about information density: maybe in a column you want to put five data points and of three of them need to be conditional. That’s super helpful, to be able to do Configurable Joins because that interface is much easier to deal with. In the past that would have needed to be a formula or a custom SQL. With the Configurable Join setup, it’s frankly one of the easier functions you use—it’s the concat function, right? So you set it up in the independent subquery or the dependent subquery, you put your fields in and you move on. And that’s really powerful because if something changes a year from now, you don’t need the programmer to change it. It could be done by an end-user with a little bit of knowledge about how to do it.

Megan:

You’re queuing things up so nicely by talking about user-maintained content, because one of the things that I think we do want to touch on—we only have a couple of minutes left—but as we’re thinking about what’s ahead, is this the new Query Library that’s being rolled out as well and the opportunities that that’s going to afford. John Michael, you’ve spent a little bit of time already configuring one for us in our instance of Slate. What are some things that you’ve noticed so far as you’ve dabbled in that?

John Michael:

There’s a lot to be said. I think the Query Library’s functionality is going to quickly become a spinoff episode of this series that I think we’ll be putting together for another round. I’ve gone in a couple of instances and have been working with Query Libraries a little bit. And it does involve a little bit of setup but it really, I think, will start to feel comfortable to a lot of people because it recreates that Slate Template Library functionality that you’re used to and that you’ve grown accustomed to. And it does require that you understand Configurable Joins to get in there and build those elements to begin with. But some folks and lots of instances have jumped in very quickly and recreated the, you know, “Not in Test Record” functionality and filters…

Megan:

That was a tricky one for folks!

John Michael:

…filters around their decision stacks, quick filters that they want to reach for. In some advancement instances, for example, institutions, organizations…have data where their constituents get to select two titles and two suffixes. And you know that out of the box, Slate has a factory default: one title, one prefix, one suffix. But using Query Libraries, these institutions can create like a “Title Complete” export, and a “Suffix Complete” export. With all of the intelligence to know: does this person have one title, one prefix, or two? Same thing with salutations and addresses and stuff like that. You can build all of that intelligence into the exports themselves across all of the bases and share them between different places. There’s a lot of discovery to do there. And it truly is the road ahead, the way to get back to some of those things that you can package up, bundle up, put on the shelf and make them available to your users, without them necessarily needing to know how exactly is the house built.

Megan:

Right, yes. I think we could probably keep talking about this all for—I don’t even know, hours, probably, between the four of us, quite frankly—but we do need to wrap things up for now. So I’ll say thank you, thank you, thank you, thank you to all of you who’ve been a part of today’s event. Thanks for viewing this. Thank you for all of your questions. Thanks for making us a part of your Configurable Joins learning adventure. We’re so glad you did.

Once this event has concluded, you’re going to see a brief survey pop up in your browser. And so if you have feedback, we would love to hear it. We’re actually currently building out our plan for future webinars, and so anything you share is going to help us to create events that are useful, and informative and valuable to you. We will post this recording plus all of those other session materials. Those will be up on the RHB Insights blog; keep an eye on that. We will send you an email about all of that. And of course, if there’s any way that we can be of assistance to you as you work to optimize the ways you use Slate, please don’t hesitate to reach out to us.

For now, though, it is time to say goodbye. We wish you so much success as you take all of this information and go and put it into practice within your Slate instance. Happy Joining, and good luck.