“If you master [subqueries], you can do an enormous range of things, in terms of being able to return multiple records and really be able to get the system to pull out complex relationships…you have the capability to manipulate a lot of different data and present it so that it is digestible in a variety of ways.”

In this transcript from RHB’s second webinar in a three-part series on using Configurable Joins in Slate, we tell about how and why to use dependent subqueries as a means of better exploring the data living within our Slate instances. Our hosts are Senior Integration Consultants Megan Miller, Abraham Noel and Dom Rozzi, and Integration Consultant John Michael Cuccia. You can use this transcript to follow along the video (with closed captioning) of this event; you can also get a recap of the three main takeaways and read the question-and-answer from the event here. As well, you can register for our third webinar on March 30 and also review resources from the Feb. 2 event: the recording, Megan’s event recap and the transcript.

Transcript:

Megan Miller:

Alrighty, let’s go ahead and get into this.

Hi, everyone, and welcome to the second webinar in our series, “Build a Better Instance with RHB: Configurable Joins”. Today we’ll be “building up”, discussing those Configurable Joins intermediate skills and use cases, and we’re really glad you’re here today. My name is Megan Miller, and I’ve got three of my RHB colleagues with me today: Abraham Noel, Domenick Rozzi and John Michael Cuccia. We’re really looking forward to sharing with you today.

This is the second part in our three-part series that examines the opportunities that are available to us Slate users with Configurable Joins. Back on Feb. 2, we looked at Configurable Joins concepts and basics, walking through the structure of the Slate database and exploring how to build one-to-one top-level joins. Today, we’ll continue down this path, taking that knowledge from session one and applying it to one-to-many queries. And then on March 30, we’ll conclude this series, at least for now, with those advanced concepts, examining topics like independent subqueries and how to utilize Related and System bases.

Some quick notes for today: we are recording this session and we will post that, plus the session transcript, slides and key takeaways, after this event is over. Also, there is a question-and-answer section within this webinar platform, and so we invite you to submit your questions there as we go along. We will be responding to your questions during the session, so please feel free to ask away.

And here’s what we will be accomplishing today. First, this webinar will help you to understand the conceptual framework of dependent subqueries: what they are and how they work. Second, we’ll discuss the key functions that are available in subquery exports and filters. And finally, we’ll walk through how to actually build one of those one-to-many queries by using dependent subqueries both as exports and as filters.

Let’s do some really brief introductions as we get started. RHB is a higher education consultancy that’s now in our 30th year. In that time, we have guided more than 200 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 going to be sharing with you today. Abraham joined RHB last year and was previously at Macalester College in Minnesota. John Michael joined at RHB in January, coming from Rice University. Dom, who is managing the question-and-answer today, has been with RHB since 2019 and was previously with Franklin and Marshall College. And I came to RHB back in 2019 after working at Seattle Pacific University.

Before I hand things off to John Michael, we’re going to very, very briefly recap the key concepts from our first session last month. What were those main takeaways from that discussion?

First, we talked about how Slate is configured. It’s what you call a relational database, where our data is stored in tables that aren’t so different from an Excel spreadsheet. Here, the columns are the fields that identify what data we have in the table, and the rows are each individual record. Slate stores a lot of information on more than 200 tables, so that requires us to have an effective framework for accessing and managing that data, which we can do by joining tables together.

Second, we noted that we have actually been joining all along; we just didn’t know that we were. Local and Slate Template Library queries join tables behind the scenes with query bases, exports and filters all potentially connecting us to other data tables. However, Local and Slate Template Library queries can be really difficult to customize for our specific needs, and they’re designed with some prebuilt assumptions which may or may not be useful for us, depending on the use case. Configurable Joins queries, therefore, allow us to access exactly the information we need. We just need to learn to ask our questions in a new way, starting with, “What type of record do I want?” and “What do I want to know about that record?” to answer how we get to that data.

Finally, we explored the resources that are available to help us join effectively. There’s the Slate Data Dictionary within the Standard Query Library, which tells us what Slate tables exist and what their column headers are. And there’s also the Configurable Joins Base Explorer, which shows us how all of those tables connect and relate to one another with a really handy visual diagram to help us really grasp those concepts.

If you missed our first session or would like to revisit any of this, you can find all of those resources on our website, rhb.com. So, please check that out after we’re done. But for now, let’s talk about relationships—the data relationships, that is. And for that, I will turn things over to John Michael.

John Michael Cuccia: 

Thanks so much, Megan. Good morning everyone, again. We’re so glad to have you back here, and like Megan said, we’re going to extend what we talked about in the first session. It’s all recapped; you can replay it online from our website. Last time we told you that it’s all about the relationships, and we went through the fundamentals of Slate as a relational database.

We’ll build up on that today by first talking about core components of joining tables together, relationships that are one-to-one and relationships that are one-to-many. Let’s start with one-to-one. In a one-to-one relationship, there is only ever one record on the other side of the join. Let’s illustrate this in an example. Starting in the Application base, we begin the query and we add in exports from the Application base.

Remember, those exports are columns in our data. We get one result row for each Application record. There’s a one-to-one relationship between Application and Person. That is to say: an Application record only ever belongs to one Person record, never more than one. We can augment our base query of Application data by joining from Application to Person. And then we can bring in exports pertaining to the person to whom that application belongs.

We can continue making our results set even wider with more exports from more tables. We can join from Application to Lookup Round so that we’re able to pull in information about the application’s round. Application and Round have a one-to-one relationship; every application only ever has one assigned round. We can join from Lookup Round to Lookup Period and pull in period information about the round like period year or period active status.

Finally, in this illustration, we can join from Application to Current Bin. There is a one-to-one relationship between an application and its current bin. Now note right here, we’re speaking of the bin in the Default Reader, not Workflows.

As we said in our first session, these different tables become related to each other by having pieces of information in common like globally unique identifiers, GUIDs. Here’s a quick glimpse of what this looks like in the query builder. We started a query in the Application base. At the bottom, we set up our one-to-one joins, from Application to Person, from Application to Lookup Round, from Lookup Round to Lookup Period, and from Application to Current Bin, then we pull in the exports we’d like to see from all these one-to-one relationships.

Here, we’ve got the application’s creation date; submission date and status; the person Slate ID and the person’s name coming from the Person table; the round name and round key coming from the Lookup Round table; the period year from the Lookup Period table, and the name of the bin to which the application is currently assigned in the Default Reader from the Current Bin table.

In a one-to-one relationship, there was only ever one record at most on the other side of the fence from the starting record. Note that there does not have to be a record on the other side. For example, the Current Bin name export here might be blank for some of our result records indicating that the application isn’t currently in a bin in the Default Reader, but just because that bin assignment does not exist, does not mean that the number of result records for the query changes.

This leads us to one of our most important callouts to re-emphasize that we touched on in our first session: Joins never change the number of results records. They are not filters. You still have to deliberately add filters to change the results set of the query.

Let’s look now at one-to-many relationships; let’s switch up our base. This time let’s start with Person. Each person can have a different number of applications. There could be one, there could be two, there could be many, there could be none at all. Each application still has one and only one round, but let’s slide this over a little bit and make room because each person can also have a different number of schools. One school, two schools, maybe even no schools. With these one-to-many relationships, we can return information about the many, like a list of every school name on a Person record, or we can return information about a subset of that many, like just the name of the Rank One School. But how do we go about architecting this in the query builder?

Well, the answer lies in that how and where you establish a Join matters, whether you do that at the bottom of the main query or whether you employ a subquery. And simply put, it depends. There are two types of subqueries: dependent and independent. This was a highly emphasized question that came in with the first registration as well as those who joined us, who are joining us just for the first time in the second session: the difference between dependent and independent subqueries.

Now, let’s lead with the majority of the subqueries that you’re going to use in Slate [which] will be dependent subqueries, so that’s where we’re going to spend most of our time today, and in subsequent sessions, we’ll talk about examples and use cases for independent subqueries. Now, when we say that a subquery is dependent, this means that the subquery always relates in some way to the base of the main query. If we add a dependent subquery export in a query that starts in the Application base, the subquery will always relate to that specific application result row. We’ll see this in action shortly.

Subquery exports must always have an assigned function. Here’s a quick preview of each of the functions that are available, and we’ll demo a few of these today. There is a Knowledge Base article that has additional explanation and even some working examples that you can see visually, how some of these are constructed inside of the query builder.

The top two choices, Concatenate and Aggregate, are very frequently used. With Concatenate, you can combine multiple values together, like the name of the school and its CEEB code, together as one export column rather than two separate ones. With Aggregate, you could return a count of the number of schools a person has or the average composite SAT score that a person has. Advanced users who are coordinating integrations between systems like between Slate and your SIS: you might have used [this] for some of the specialized choices like Formulas and XML and JSON. Remember, these are just exports. They return data about our records.

Likewise, subquery filters also require a choice of function. We’ll demonstrate a few of these and you’ll find that some of them will feel familiar. Maybe you’d like to filter for person records who do not have a school record or persons who have a school record but the school does not exist as an organization in your Organization dataset. Maybe you’d like to look for applications belonging to persons who have an average GPA of 3.0 or higher, or maybe you’d like to compare an application’s submission date to a particular specific date to see how many of those were submitted within a certain timeframe. These subquery filters and all of these functions in subquery filters will change the number of records in our results set.

So, with this high-level scene setting, let’s build up on our foundation from the first session. Abraham is going to take us through building some dependent subquery exports and some dependent subquery filters. Abraham, the stage is yours.

Abraham Noel:

Thanks. John Michael. [I] appreciate the introduction and the conceptual framework to talk through this subject.

Now, working with clients and sort of working with this tool for a while, I think is one of the more confusing things about Configurable Joins, because it requires you to think about relationships in a way maybe in the past you didn’t have to think about it as much if you’re using the older version of the tool.

So, what I’m going to be doing today is trying to take what John Michael talked about and give you very, very specific examples around a particular type of record. Now, what’s important to say is that everything that we do at this point can be applied to many different types of records.

So with that said, let me go over to my screen, and we’ll get started. So here we go.

What I’m going to start with today is kind of picking up a little bit from our last presentation. In that presentation, I built at the end of the presentation, basically, a first CJ query to demonstrate how to do various things. So if we go back to that query and we run it, you’ll see that inside that CJ query, we have schools by rank overall, schools have a different type, different information about schools.

So this really goes back to what John Michael was talking about, where with this build of the query, you’re going to have a single record on the left and then you’re going to return one record per column on the right. And that is really conceptually done, if you edit the query, by looking at it at the bottom.

So what we’re doing is, we are joining at the bottom, and I’m talking specifically about school records, and we’re joining by School by Rank Overall—you’ll see that right here. What that’s going to do, again, is, this particular query is based around an Application with a join to a Person, it’s going to return per column—one school—per record, regardless if they have one school, no schools…well, if they have no schools it returns nothing, but it will return one school per person per record. And if they have one school or 10 schools they are only returning that top-ranked school.

The other thing John Michael talked about a little bit is: what happens if a person doesn’t have a school? Well, you’ll notice here’s my test record. It’s in here twice because I have two test applications and I have no school. And you’ll see that this does not inherently filter. And that’s really important to understand because it’s a base-level join that has no impact on the base record. So, you’re still going to get a full set of records. In this case, it’s a full set of the Application records for the entire dataset. Now, you can filter at other places based upon the records, but it’s something that inherently it doesn’t do.

And for the SQL people in the room—and for those who don’t care about SQL you can ignore this—but what we’re talking about is a Left Outer Join. Slate is actually writing SQL behind the scenes and it’s basically using that or other types of Outer Joins to allow the records on the left to appear even if a right record doesn’t appear.

So, okay, so now you have that and, hopefully at this point, you understand how that would work. So again, we’re returning one record per person, not multiple records. What happens, though, if you have a situation where you want to return multiple records per person?

Well, what you can do then, is you can actually take the query and what I call pivot it. So this is taking it from the concept of a tall query. I would call this a tall query, actually, let me reverse that. I would actually call this a wide query. So, you have the record over here and you have the records going to the right. What we’re going to do is, we’re going to convert it to a tall query. So, we’re going to return one record per school per person.

To do that, then what we need to do is go back to the query base or go back to the initial query creation area, and I’m going to create a new query. And I’m going to call this “School-Scoped Configurable Joins Query”.

Now, again, it’s really important that you pick the right base, because once you build a base into a Configurable Joins query, you can’t change it. So, that’s the one thing that you really are truly locked in. If you make a mistake with that or find out later that you can’t use that base, you will have to rebuild the entire query from scratch.

To find that Schools query, you’ll notice that when I look at records, it’s actually not listed here. Records are sort of set up to be the sort of primary areas of where records are in Slate. So, your team is going to see Applications; if you have an Advancement instance you’re going to see some other things, Person records, a couple of other things. We have some data sets in here. What we want to do in this case is, since we’re going to go and we’re going to look at School records and we’re going to pivot the query, we’re going to actually go to the Related category. We’re going to go down and we’re going to look for Schools.

So now what have I done? Well, I’ve taken, and I’ve pivoted, the relationships. Now I’m going to return one record per school record for each person, and if they have multiple schools, the person’s going to show up multiple times, in our test instance, for example, when we only have 165 records with school records.

To demonstrate that, I’ll go ahead and I’ll put in the name of the school, maybe the code, we’ll put the city, the region, the country, and what I’m going to do next then is to give you the Person information. This is where I have to do every join to the Person from the School record. So again, I’ll go in here at the bottom, I’ll put in the Person.

And if that works correctly, what we should see then is the Person name information. We’ll put that in, maybe a reference ID. And now when I run this—and in this case, I’ll go ahead and drag this Person name and Person reference up to the top—you should now see a list of every person with school information next to them. And if a person has multiple school records, you’re going to see multiple person names listed.

So you’ll notice right at the top, we have somebody named John O’Shaughnessy, and we’re going to talk about him specifically as part of the example, getting into some of the more advanced things with this. So you see John here is listed four times. Again, why would he be listed four times?

Well, if you go over to John’s record, you’ll see that John here has multiple school records. So he attended here for high school and then went here and here for his undergrad and then moved over to here for his grad degree. That’s reflected here because we’re doing what I would call a tall query. We are returning multiple records based upon the scope in this case, the School scope. And you’ll see that there are numerous versions of that in this particular query.

Now let’s really get to what you’re here for, which is the dependent subquery. This is not really a dependent subquery in the true sense because we are still returning a single record per School. Let’s go back to the example that John Michael hinted at: what if you have a Person record and you want to see all the schools that they’ve attended for that Person record? That’s typically how we’re using a dependent subquery. So, what I’m going to do to demonstrate that is I’m going to go back to my queries area, and I’ll generate a new one.

All right, so with this one we’re going to start with the Person record.

So why Person? So again, I’m going to go back to something that I demonstrated in the last session, which was the Configurable Joins Base Explorer. I find that’s really helpful to get you sort of familiar with the Slate database because what you do here is you’re able to take in and sort of see what are all the connections.

So in this case, the question I’m answering is, [what if] I want to be able to see for John, a single record for John, but then, a way of seeing all the different schools that are in John’s record? You’ll see John has four records: one undergrad or sorry, one high school, two undergrad and one graduate. That is probably not unusual with your records, that you would see something like this.

Now what’s important though is that I’m going to talk a lot about schools today, but this same concept can really apply to lots of different things. There’s a great article that talks about table rank. And we can get into that a little bit. We got that a little bit in our last presentation, but as you get into multiple records, table rank becomes more and more important.

So, what I’ll do is I’ll share this with the webinar participants, but I wanted to just show you in a general sense, when we talk about multiple records, hopefully all of these look familiar to you if you’ve been using the Slate for a while, so Addresses, Applications, References, Courses, Decisions, so forth and so on. Everything that I’m going to talk about today could be applied to all these different groups. And there are additional areas of Slate that even have more. So, we’re really just sort of hitting the very top level of this today as part of the example.

With that said, let me just share that. Oh, always technical things here. I just got myself logged out. Let’s try again. All right, well, we’ll come back to that. That’s no problem. We’ll share that link later in the presentation or email it to you as part of the recording that comes out. But the point there is that that article has a great example of all the different table ranks and how they work relative to Configurable Joins and sort of the different pieces, different ways of seeing the data.

Now what I’m going to do is I’m going to go over and build that scenario that I was talking about where we had a single record, so a Person record, and we’re going to then take and show sort of the totality of these school records associated with that person. You could use this same concept for interactions, addresses, decisions, any number of different multiple records associated with the individual.

If we go back over to queries and we’ll build this out, again, we’re going to start with the Person record and we’ll save it. And I’m going to start with my export, which is going to be, let’s just start with the name. So, here we are, 167 records in our test instance. And what I’m going to do to sort of demonstrate this today is I’m going to focus again on this test record that we’ve built up that has four School records. So to do that, I’m going to apply a really simple filter. We’ll use the ref ID filter on the Person record. Yeah, and if that works right, we should have only one record return.

Ah, thank you John Michael for forwarding that link on the table ranks.

All right, so here we are. We have a single Person record, and it should be our test record. If we preview results, we should see John here. So now let’s work through the different iterations of dependent subqueries specific to the question of the school records that you see here on the profile tab. First, what I’m going to do is, I’m going to add the Join, and how I’m going to do that is: I’m actually not going to put the Join down here.

This is where I begin to use this particular icon over here, this tool, the subquery tool. So, you’ll see this in Slate where you see this symbol and hopefully on your end, you can see what I’m highlighting with my cursor, but basically it has a little red box with two blue boxes to the right.

I’m going to take this, I’m going to drag it, and I’m going to name this something meaningful. So in this case, I’m going to call this “Schools”. This, then, is where you’re actually going to do your Joins for this particular situation. So again, the goal here is to have a single Person record but show all the different School records for John.

What I’m going to do first is, I’m going to go from this, and I’m going to click on “Join” down here and I’m going to look for “School”.

Now, this is really important, this next bit: you’ll notice that when I type in “School”, I get about four different options. If you go back to our first presentation, the options I was picking at that point really were the rank options. These are only going to return one record per school, so you have to determine what type of ranking you want to use and under what circumstances. And you’ll see here with the School we have three different flavors of that ranking.

What we’re going to do today though is really focus on this multiple Schools record, and a hint that this is a multiple record is this right here—you’ll see this icon.

As you navigate the Slate database in the Configurable Joins tool, if you see this icon, this is a clue that this is a multiple record and you can further sort of see that by looking at the Convertible Joins Explorer. So if you go back to “Records” and you pull up the Person record and we then go to the diagram which I favor—I tend to be a more of a visual person—you’ll see here, we have the person and we have [the] School. You’ll notice that the line here is kind of thick, that indicates that this is a multiple record. So, it’s a one-to-many relationship.

So, going back to what John Michael said before: one Person record to multiple School records.

Let’s go back and work on the period. I’m going click on “School”. I’m going to go ahead and add that and join. And then inherently here, nothing will happen.

If I just leave this as it is and I run the query, what happens? Well, I get an error. With Configurable Joins, you are going to occasionally get errors. And if you’re not familiar with SQL, this can be something that can be a little bit frustrating, frankly. What you need to do, in my opinion, with this, is always go back and look at what you did in the last step if possible. So why I say that is you’ll notice as I’m going through this demonstration, I’m using “Preview Results” a lot. And by doing it this way, checking things as you go, I think you’re going to potentially remove errors or quickly be able to resolve errors.

In this case, what this error is telling me is the Concat function or Concatenate function, requires some number of additional fields. I’m going to go ahead and put those fields in. And where I’m going to do that, it’s actually under the “Export” option here.I click on “Export” and what I should see, if everything works right, is you should see now a listing of the school’s information.

So let’s say, for example, I want to see the name of that school. And I want to see the code for that school. If I do that and I click “Continue”, by default, we’re in the output of Concatenate. What this is going to do is, it’s actually going to return the name and code for each one of those records. I’ll go ahead and do that, and let’s take a look and see what we get. So here we are: we have John’s record and you’ll see that we have multiple School records.

You might say, though, that “that’s not very readable.” So, what do we need to do to fix that so it is readable? Well, alright: this is where you then begin to get into the Concat function of how you can manipulate it a little bit using some of the options in the tool.

So the first thing that I might want to do is, I might want to take the school name and the school code and put some space between them. What I would do in that case is use this “Literal” option here, drag it down. I think what I want to do is, I actually want to put a divider like this.

Now, it’s important to understand that the name part of this tool does not actually do anything. It’s just what you see internally. The literal is actually what will appear. You’ll notice that I actually put in a space, a dash and a space. There’s also an indicator now that was added relatively recently for special characters that you can use.

These are kind of the shortcuts to be able to add some special characters, special cases. So, keep those in mind as you’re doing this. If I do that and I click “Save” and I preview my results again, now it’s a little bit better.

So, you can see that we have a space between the code and the name of the school, but between individual records we don’t—we still have that sort of, it’s sort of smushed together. So how do I deal with that? Well, I come back to the tool and I’m going now look for the row separator, which is this one.

And again, we have these special cases, so we can insert lines or tabs. In this case, though, I’m just going to use one of these, and you’ll notice that I have a space after it. By doing that, and if I click “Preview Results”, now I’m able to see the records a little easier. So I see my list of my record, and I have a nice space and separator, and it follows the same pattern all the way down through the entire return records. This is the basics of how the Concat tool works, or the Concatenation tool. And you’ll see that right here.

If you can sort of master this with these, you can do enormous amounts of things, in terms of being able to return multiple records and really be able to get the system to pull out complex relationships. Think about a scenario where now I’m showing School records here, but let’s say we talk about scenarios with Addresses where you need to pull multiple addresses for a Person record or perhaps different emails. So, you have the capability with this to manipulate a lot of different data and present it in a way that is digestible by a variety of things.

John Michael is going to talk about a specific case instance where you can use it to cause the system to format it in such a way for Liquid looping and a couple of other things. If you don’t know what Liquid Looping is, that’s okay. Today, we’re really going to focus on the query aspect of this, but this becomes the basis for things that can be used for more advanced functions. Think about emails, where you want to return multiple records in a single line in an email, or perhaps down the line, you’re building a portal or modifying a portal. Portals commonly use multiple records that are manipulated in this manner and then use looping to kind of return those records in a way that present things so the user can interact with them. So, if you can master this one thing, you’re going to be able to begin to understand the rest of those things.

Let’s go a little further than this. Let’s say I wanted to go in and add information about where the school is. So, we continue to build on what was demonstrated here, how we would do that is we go into our export in the subquery, like so, click on that. And then let’s say we wanted to add the city, the region and the country, pretty common things to include with the school. So, I’ll go ahead and put those in.

Again, if we run this as-is, we’re going to kind of have a … now let’s see if it’ll pull up here. We’re going to have this unreadable—I call it a mush, but it’s basically taking all the data points and smushing them together. We want to break this up a little bit further so it’s easier to read and can be looked at by a human being without taking a lot of time pulling things apart. To do that, we’re going to go ahead and add in some more literals. So maybe after the code here, I’ll add in a literal that is this, like so. And then we’ll throw a literal in that—splits up the city and the region, like so.

And you’ll notice when I do this, I’m actually putting some spaces in after the character that I’m entering. You want to think about it in terms of how it sets up. I want this to be readable, so I’m actually just going to put an actual space. With that, you just, you can go ahead and do that. And that will put the space in.

Now, Slate will throw a kind of a strange error if you leave this blank or even put a space in. What I typically will do is something like this, and you’ll see it like that. Now if that works correctly and we go ahead and preview our results, we should see it should be a little easier to read.

What’s important about all this—and I’ve been really focusing in on schools—think about this with this scenario where you’re reporting multiple test scores for an individual. With that kind of a scenario, you would have lots of subscores. This technique can be used for that scenario. And it’s very useful for that. It allows you then to break up the subscores in such a way that they’re very legible and readable by the individual, but they’re very really tight in that it fits into a single column. So, I just want to give you that sort of secondary case example, and you could use this for lots of other things, but the same technique applies with any sort of multiple records that you’re pulling back in a single column.

The final thing I want to talk about with this specific example, is let’s say, hypothetically, you wanted to make this follow the USPS standard, which in the United States typically is going to be that you’re going to suppress putting the United States at the end of the address. In that sort of hypothetical scenario, what you could do then to do that is you can use a subquery filter.

To make that work, we’re going to go to the country, which in this case is not filtered, and I’m going to put in an additional subquery. This is sort of the subquery within a subquery, some people call this the “Inception Approach”, right? So you’re kind of layering different layers in, and you really can do many layers with this. You can get very sophisticated subqueries within subqueries within subqueries. But in this case, we’re going to keep it really simple.

We’re going to take and we’re going to drag a subquery export and we’re going to call it “Country”, like so, I’m going to pick up that same export of “Country” from the original Join. What I’m going to do next is, I’m going to put a filter. This particular filter is going to, again, filter on country.

I’m going to find “United States” from a picklist, and in this case, I’m going to say, do not show this field if “United States” is the data point. So, now if the country is the United States, we’re going to not show this and we’re going to suppress it from the view we have up here.

Now, one thing you might be wondering is, what about the space up here? Do we want to show that space when it’s blank? We really don’t. So what I’m going to do is remove that, and I’m also going to remove the original country field. I’m actually going to put that literal inside this subquery within a subquery, like so. I’ll type in my space again, or that really doesn’t have to be that, but it has to be something. I’ll go in and put my literal in.

What this is going to do now, is that this literal and the country should be suppressed. It’s going to hide the space and you’ll see how that works here in a second. I’ll go ahead and save again. If I preview the results and everything works correctly, you should now see that for this last example, it’s basically suppressing the country code or the country, and we are doing similar things up here. I don’t have a good example of showing you an international school. I didn’t set one up for this example but hopefully with this sort of example, you get the idea.

With that said, I’m going to turn over the screen to John Michael to talk a little bit about a specific case where we’re looping through data and it is going to be presented in such a way. John Michael, do you want to take it?

John Michael:

Sure, thanks, Abraham. Very quickly [I] wanted to show you the Dictionary export and how a subquery function export and that might be used. The example that I’ve got built together for you is wanting to return information about missing checklist items for an application.

Now, traditionally, if you’re building a query in what I lovingly call the “Classic Bases” at this point, the Local bases and the STL bases, you’re typically reaching for an export that’s like “Missing Checklist Items” or something like that. And there are ways that you can reconstruct that using Configurable Joins but also employing your own selection of the data that goes into that type of little cluster of a list.

Here in this example, what I’ve put together is I’ve chosen, I’ve started a subquery. I’ve built a subquery export. The output is “Dictionary” and it’s an Application-based query. I’ve joined over to Checklists. I don’t need this join to School. I can get rid of that because I don’t need it anymore. I’ve joined to Checklists and filter down for Checklist items that are “Awaiting” status. I’ve told the export to give me the section name of the Checklist item and the subject of the Checklist item.

Now, if this were set as Concatenate as Abraham demonstrated, it would just mush these two things together without any other spacing or anything else involved. But I’ve picked “Dictionary” which is a little bit of a special secret sauce.

If I save this, I’ve also added a filter to look for applications that have missing checklist items. Abraham hasn’t gone into the filters yet, so I’m not going to pop the hood on that one. But look what this does. Now, this may just look like gibberish. What it’s doing is creating these little PKV nested structures to all of the data, and where you can then employ this type of structure is inside of a Deliver message.

So, here’s a sample Deliver message that I’ve put together with “Dear Preferred” because at the bottom I joined over to Person. Remember, Application only ever belongs to one person, so I can join down here at the bottom. There’s only one record and all the others, ever on the other side of that. “Dear Preferred, we’re currently missing these items.” Show me the Checklist item section and the Checklist item subject line and some additional texts. “It’s due Friday. Please get it to us.”

Behind the scenes in this Deliver message is a little lovely loop for that table row so that if I’ve got one missing item, it only loops through this one time. If I’ve got 10 missing checklist items, it’s going to loop through this 10 times and give me the whole list of sections and subjects for the missing checklist items. So, if I just dismiss this modal and look at this sample record…

Here’s the Checklist subquery export. It’s set as an Output type. I’ve brought in the section and subject. These are just exports. I filter down, the item has to be in “Awaiting” status. If I do my quick preview again, here’s my list of preferred names. And then here’s this singular export that’s got all the stuff mushed together of missing checklist item sections and subjects. And then if I toggle back over to my email, I’ll edit it one more time. There’s a little table.

And behind the scenes, there’s this little bit of magic that you can read about in the Knowledge Base, Liquid Looping, Liquid markup. It says, “For every item that’s in this checklist export, I want you to give me the section and the subject.” It’ll run this one time if I’m missing one item, it’ll run it 10 times if I’m missing 10 items, however many items end up in that subquery export.

Then this is what the output looks like. So Trevor: he’s got two missing items. They both happen to be in the Material section. One is the essay and one is the transcript, and it happens to be the transcript for Gateway Community College. This is just a quick demo of the Dictionary export. And most of the time you’re going to be leveraging that with Liquid markup. It has real superpowers with respect to being employed inside of the loop. Abraham, I’ll hand it back to you now.

Abraham:

Thanks, John Michael. Let me share my screen again here. And we’ll go back to John. Thanks for teeing up the different usage cases.

Let’s jump to the next topic, which is really Configurable Joins subquery filters. If you understand how Configurable Joins work from the standpoint of showing data that are multiple records per primary record—what you can do with that, then as you can begin to understand how you use that to filter records. A simple example of that would be, let’s go over and talk about this one scenario, which is pretty common.

If we go back, and John’s record here, John is a test record. So how do I get test records in or out of a query in Configurable Joins? Well, you would use a subquery filter. The reason for that is that if you think about what’s happening here, this is another example where we have a single record and multiple Tag records. John could be an Opt Out. He could be a Test record. He could be a VIP. If you have other tags, you’d have the same situation. So, you can’t simply take and filter it straight against this because it will not work correctly. What you’re going to need to do is use the subquery filter.

What I’m going to do to do that is I’m going to pull this down into my “Filters” area, and I’m going to put in a name that hopefully is meaningful, so I’m going to say “Is a Test Record”. Now, what I’m going to use is what’s called the “Existence” functionality. What “Exist” is, is it’s going to be testing for, does a filter return a record?

Then if it does, either show the result in sort of related records or don’t, and you can see that we have a couple of options there. Typically what we’re doing with Exist is “Exists” or “Not Exists”. What I’m going to do now is I’m going to take this and I’m going to join by Tag. You’ll see, we have that Multiple Record icon again, hopefully you can see that.

If I do that now, what do I get? Is that going to be giving me enough? If I click on “Filter”, you’re going to see that the Tags really just have the Date and the Updated Date. That’s actually not enough to do this. I’m not going to be able to filter against the presence of a Test Record tag or not, so what I need to do again is join from Tags to Lookup Tag.

And now if I do that, we should be able to see not only the date, but also the type of tag it is. So, I’ll click on a name and now I see my list. In this case, I’m going to filter against the test record, and you’ll see a nice clean Join. If I do that, John here should continue to appear because he is in fact a test record. But if I went back to John’s record and removed that, and refresh this query, now he disappears. This is a really simple way to use a subquery filter to be able to pull in different types of records or exclude certain types of record. Typically, we see this used for these kinds of various tags. I’ll go ahead and add this back in.

You can use this for all sorts of things. So, you could use this first scenario where you want to exclude records based upon the presence of a school record or particular type of interaction. You could use it for a scenario where you wanted to test for a record being in a particular geographical location. I think about maybe you have multiple address records on a person and you want to pick one of those multiple address records and see if they live in a particular geographical location. That’s another example where you have multiple records connected to a main record and you can use the filter to be able to do that multiple record filtering against a single record.

The final thing I’d like to show you today is how to do a record count using a subquery export. So, this combines a couple of things we’ve talked about and it’s going to build on the subquery export by itself but then also using a subquery filter. Let’s go back and look at our example record of John O’Shaughnessy here. John has four school records listed.

If we, in a query, you want to see the number four, that is easy to do with a Configurable Joins query. What you would do is you would pull in your subquery like we have in the past; title it. For the output, then, what you’re going to do is set it to “Aggregate”. So under the Aggregate, there’s a number of options that are really powerful.

The one we’re going to do today is the very first one which is “Count”, but you’ll see a number of other options. If you’re someone that has worked with SQL, these are going to look familiar to you as grouping and aggregate functions. If you’re not someone that’s worked with SQL, don’t worry about that. They’re all documented in the Knowledge Base, and there are good examples about how to do what I’m going to touch the surface of today.

But what this is doing conceptually is, it’s taking and querying the records connected to the main record and then simply counting those records. Another common thing that you could do, is using a Sum; that’s going come into play if you’re trying to take records and perhaps sum them so you get mathematical totals. You’re able to take that, then, and do things with it.

If you think about a scenario where maybe you have multiple Reader forms and you need to take, perhaps, a calculated GPA off of each one of those Reader forms, you could use a subquery expert like this to sum them and then do division and do other sort of mathematical things to manipulate those numbers to get an average, or to get a weighted average or to do a variety of things. So, this is a very powerful set of features. And if you’re someone who needs those kinds of things, I would encourage you to look at the documentation for them.

But today we’re going to stick to Count. So we’ll click on “Count”. And if we just leave it as is, what’s going to happen then is that this record will simply count the number of records that we associated with. Now, an important thing is: just because you joined in a previous subquery or even at the bottom, you actually have to go through and join it here. So we’re going to go ahead and click on “Join”. And in this case, we’re counting School records, so again, we’re going to use the school’s record, and you’ll notice that Multiple Record icon.

That’s important: you use that and not one of these, because these only return one record per query pull. So, we’ll go ahead and click that, join it back end. You’ll see now it’s joined, and you could, frankly, leave it as this. This is simply going to count the presence of a record. If I click “Save” with this configuration and I click “Preview Results”, we should actually now see our counts results for schools.

So let’s say now, hypothetically, this is where the filter comes into play. We don’t want all the schools, but we want a subset of the schools. Maybe we want to count how many bachelor’s degrees that John says he has. What we can see here is that two of those schools have the bachelor’s degree in them. I’m going go back to my subquery and I’m going to go into the subquery export.

And this is where I’m going to add a subquery filter. I go ahead and add a filter and I’m going to look for “Degree”. And within this then I’m going to pick “Bachelor’s Degree”. So if this works correctly, what should do is return the number 2. And it does, because what it’s doing is actually going back into the set of records. It’s counting that only the two records that have the degree of “Bachelor’s Degree”. Hopefully with this, you can begin to envision how you could use this for a variety of things.

I’ll give you an example: think about test scores, where there’s potentially multiple test scores for a record. You could use this to set a range and say, maybe they have two instances of test scores between a certain range. Another thing you could do is use it for data integrity checks. Maybe you’re checking to see, does the person have multiple emails on their record. With this kind of thing, you’d be able to see if a person has a single email record or multiple email records and then use that to review those records and make decisions about which email you want to use. So, there’s lots of potential uses for this and it’s a really powerful feature.

And in concept with some of the other things you can do specific to the aggregation, you can see we get into some analytical functions, so, Standard Deviations and Variants. These are kind of newer features, but the one that I tend to see used the most is the Average. If you simply want to take an average of all the records and contain within a sub-select or a subquery, you can do that very easily using this functionality.

So that being said, thank you for your attention and being with us here today. I’m going to turn it back over to my colleague, Megan, and she’ll conclude our seminar.

Megan:

Alrighty. Thank you so much, Abraham. That was a really helpful walkthrough.

We are out of time today. We got some really great questions from you all as well. Thank you for participating so much. We hope that we are able to answer those questions in a way that helps you to better understand some of these concepts. They can be complicated at times, and it takes a little while to let those sink in. As I said, we’re out of time for today, so we’re going to wrap things up now.

In the coming days, we’re going to be posting this recording. We’re going to add the slides, transcripts and the key takeaways from this session. It’s going to be on the RHB Insights blog, and I promise you will receive an email when that is available for you to access. We would like to say thank you to all of you who chose to spend an hour with us today. You were great. Thanks for your participation. We really, really appreciate it.

We’re really looking forward to seeing you at our next session which is called, “Expanding into New Spaces: Configurable Joins Subqueries and Advanced Cases”. You can think of that as “AP Configurable Joins”. That will be at the end of this month on March 30, so if you aren’t registered yet, or if you know someone else who might want to attend, you can sign up on our event page, that’s at rhb.com/webinar.

Once this event has concluded, you’re going to see a very brief survey display in your browser, so if you have some feedback for us, we would really value that. Your input will help us to build presentations that are helpful and relevant to you. So, please feel free to offer your input there. However, that is all we’ve got for now today. So, we’re going to conclude by simply wishing you the very best as you take what you’ve learned today and put it into practice. Happy Joining and bye.