Slate Spotlight: Configurable Joins with Alexander Clark

Change.

Do.

Epicenter.

Back in bed.

Open the.

Bing.

Hello and welcome everybody. It's great to have members of the Slate Family Re convening yet again. It's been some time. I indeed it has but we've got a wonderful treat in store for you today. There's been a lot that's been happening with slate with the slate configurable joins an I'm eager to share not only what has happened in the past and catch you guys up on all of the amazing things. Configurable joins can do.

But also take you through a little bit about history of how even got here and then show you some of the future that is actually already live in your own databases. So we're going to have some fun today, so let's get right into this and get started. I would like to share, sort of how we arrived here today. This year marks the 21st year of Slate. That means, I suppose, that it's legal and able to drink now, because that's never happened at a summit.

It's also 21 years of queries in slight slight does not exist without queries. An indeed in year one there were queries but they looked very different than queries may look today, and indeed we have had 21 years of query evolution and this year continues to mark further evolutions in this query landscape and I look forward to sharing more about those with you 2 day. So as we look back to the earliest years of slate when Slate first came into being.

Queries were all pre determined. What that meant was that we had to go and write custom SQL an gave you certain types of filter options and we just kind of hoped that you would only ever want to filter on entry turn and perhaps the prospect status, although I don't actually even think the prospect status existed back then. Then of course we evolved that further and let you write your own custom SQL queries. This was pretty huge because now you could go and if you have the requisite SQL knowledge.

You could go and query against anything in Slate, but of course the operative part of what I just said was that you needed that requisite SQL knowledge. It wasn't open to everyone, and that's something that we thought we want Slate to be accessible to. All of the business users. The folks that are closest to a process and to be able to move outside of that realm of custom SQL. You'll even see in this sort of real snippet. There we were even calling like stored procedures way back that, Oh my, but of course.

We advanced further from there and we introduced what became known as the Querybuilder, the fun little thing about this particular view is that this is not even the first version of it because it continued to expand. So for example what we have right here. You see the little scrollbar right here. This was all of the exports from which you could choose because of course no one ever needed more than like 30 right? We then added sorts because sorts weren't originally possible.

But you would never need more than three sorts, I presume, and of course those would be columns in your particular export. Then filters I came along we had filters where you could choose them, but you couldn't do Boolean operators. There weren't parentheses, or or XOR knots, then their work, and you could actually arrange the filters and that was pretty groundbreaking to be able to have a query where you didn't have to just choose from a very limited set of filters for a particular thing. You could actually build something new as part of that.

Then in 2015, which is now six years ago, we launched query beta. This was a redesigned concept that introduced a lot of new things to the query system. Not only could chew I search against all of the ever increasing numbers of exports and filters, but we also introduce something known as the slight template library. This was an acknowledgement that there were a lot of different ways in which folks might want to do something they might want to have.

The second highest AC T score, although I'm not sure if people know what test scores are anymore, but perhaps they wanted to go and find what was the 2nd address or their permanent mailing address and we had to preconceive of all of these different ways in which someone might want to be querying, but the site template library did this huge amazing thing because all of a sudden all of your own fields and test scores. They all had their own custom exports and filters that just magically appeared.

We even introduced something called configurable exports which started to bend the concept of an expert and say, well maybe you could configure it in a particular way, but of course as we saw the slate template library continuing to grow and to become ever larger, we started to realize this isn't going to be sustainable because if we continue to add on to the slate template library eventually there would be just thousands upon thousands and 10s of thousands of potential things from which to choose.

And like the intro song we wanted you to be able to go your own way because how you wanted to pull out data was this ever diverse array of possibilities and you might want to do it one day in a particular way in another day in a different way. And there were infinite possibilities to how you might want to shape things, and so along in 2017 at this late innovation Summit.

Which we were at in Chicago that particular year during the Friday Rd ahead talk. I shared the concept of what we were going to be calling configurable joints. It didn't exist yet, but it was something where I it was a conceptual almost sort of vision that we were laying out to say. This is where we want to go. This is where we want to take things. I suppose that's a direct quote from me. How many understood that I may have to ask the same question?

I after today's webinar, but we'll see. But of course then in 2018 at this late innovation Summit, that's when we started to introduce the first prototypes of configurable joints. There's little me up on stage showing things, and we had some initial types of query basis that were there is some initial capabilities, and it was pretty amazing and powerful. In 2018. Flash forward to today. It's grown considerably. There's so many more possibilities, so many more capabilities.

Then I look forward to telling you all about I as part of this last year. I guess it's now low how many years ago is this? Two years ago it may. I sent an email to my colleagues here at Techno Lutions about how I saw configurable joints and I thought it was appropriate to share just this little snippet with you as to how I described configurable joints back then. Because this is very much what we have always intended it to be and something that really we have been so pleased to see as being achieved with this.

So when I said it was configurable, joints will be one of the most effective things we've ever introduced to rendering obsolete the need for any custom SQL. The concept of a slate template library which I was just speaking about, goes away almost entirely by creating basis for each of the types of things and adding on basic exports and filters to those a user can through configurable joints, query against anything in any sort of way. We don't have to preconceive of the ways that they will want to query on something, nor do we have to be engaged if they want to query on something in a way we haven't thought of.

It's not an exaggeration to suggest that this will become the most powerful query tool designed for end users in any type of enterprise software in any industry. And therein lies the distinction SQL itself is a very powerful language, but it's not something manageable by many end users and many simplified query interface is designed for end users. Don't provide them with the power that they need. Configurable joins solves this Goldilocks problem, and that's what we're going to see today. We're going to kind of like I can. Tina Turner.

Said in their cover of Proud Mary, we're going to take it nice and easy at the start. But then we're going to change the tempo on the web and R and start going into some really fun things too. I, as part of that, I will also mention that I ask, we move quickly. Everything is being recorded. You don't have to follow along in real time. Just try to sort of absorb that. The power in the capabilities and the concepts that will be I imparted as part of this.

I will share the recording afterwards so that you'll have easy ready access to it and can share it with other colleagues that you may have and will of course be introducing some brand new features. What's really exciting about this webinar here today is it's our second largest event of all time, with more than 25 registrar 2500 registrants. It's larger than all but our 2019 summit that we had so some fun things. As part of that. So thank you for.

Joining us in real time today I will also go ahead and plug some future learning opportunities that are out there will be continuing to host a lot of webinars. I'll be doing a number of webinars in the coming months ahead. My colleagues will be doing some webinars but we also have documentation on these things. We're creating new documentation, updated documentation, expanded documentation, will have new courses in the Learning Lab which is our online learning platform and you'll be hearing a lot more about that in the coming months as well.

We also have an ever growing number of organizations in our slate. Preferred partners community and several of them are hosting webinars coming up in the immediate term. Some of those that have upcoming webinars, an HRV and strata information group.

About four webinars coming up, all within the next couple of months. So exciting things to stay tuned too. If you're looking for. I just even other types of perspectives on what you can do because I was something this versatile and flexible. There are a lot of different ways in which you can slice and dice everything, so we'll be launching right into the screen. Share in just a moment, but just because it's been awhile since I've seen you guys.

I know I can't see all 2500 of you right now, but here I am. So as you can see, my hair is gotten a little bit longer, but I was originally going to cut it. I guess during that or right after that two week flatten the curve and so then I said I'll just kind of wait till this end of this thing. So here it is. So by the time we next want to see one another in person, perhaps it will be a wee bit longer. So I great to have everyone here today and let's get right into it.

I OK here we go and I like this late scrunchies sure we'll have quite a number of things. We've also actually thought for a slate spirit shop not to give away a potential future product, but we may introduce our own line of hair care products someday called techno lotions. So just stay tuned for all of the new developments that will have coming forward. So OK, let's jump right into it. OK, so again, everything is being recorded, so as we go through this again, don't feel as though you need to.

Absorb absolutely everything in real time. You can go back and slow me down to 25% and maybe that will help a little bit. So let's go and do something that perhaps folks already know how to do. Let's build an old query and So what that means is when we went to build a conventional query as part of that, we might be selecting from a base like a prospects or an applications on here. And this was always a little bit confusing because if someone wanted to go and query for.

Let's say applicants and prospects. Well, they should really use prospects because it really meant people, but it wasn't necessarily called that. And to the earlier points of Slate template Library in some of these other features, as he would go and select the different types of items that you might want, it was pretty cool. You could go and say, oh, I want like email address and that's all great. And then maybe I wanted to filter to find people who lived in just a particular state and I could find everyone who lived in Connecticut on here.

And I could start building out my query and putting on some different exports. Then let's say I wanted their active address. That's great, I could go and select from that, but what if I wanted like their second address? Or what if how we were calculating what is a mailing address? What if it's a little different than how you calculate I? What is the mailing address? What if you wanted to get a count of how many mailing addresses they had, or how many addresses they had in different states all of a sudden?

That starts to breakdown within here and so perhaps you would turn to things like these custom SQL options or you created a whole custom SQL query, or you had to go and create your own exports and filters for those and that created a level of technical complexity to be able to hit those edge cases. And as we tried to move more and more away from those edge cases and build it out on the slate template library as you could see, we had to introduce things like paging.

I under these because it just kept getting longer and longer and maybe we could show you what are the three sort of highest overall scores in a particular category. But what if you wanted the 4th and so it was really one of these sort of insurmountable challenges? And that's really where configurable joins comes in for that. So let's go ahead and build a configurable joins query. So the first thing that we're going to do is instead of starting from a local or slight template library, type.

We're going to go and choose this option for configurable joints at some point in the future. Something like local or slight template library likely go away. That doesn't mean that your query existing queries wouldn't continue to work. We would grand forefather them so that they continue to work really in perpetuity, but we've been devoting all of our efforts to adding things to configurable joints instead of the slate template library and so configurable joins really is the future of all queries within Slate.

And all of the things that use queries so think deliver mailings and rules and form logic in all of these other types of pieces. So there are a lot of good reasons to be paying attention and observing things today. So as we go and select configurable joints, there are three primary categories of types of bases that we have. What we call records, then related and then system. What records are what we would call sort of the primary units of things. So tends to be like.

People so person I, application organizations or any other types of datasets that you might have. So maybe you have an alumni volunteers, data set or perhaps you have a companies and foundations data set, but where it's ultimately a record that is personal organization like or something that I is typically one of these sort of top level things that you would want. These are also probably the most common types of basis that you're going to use for configurable joins.

We have a related category and these are things that are related to those types of records, or maybe their own types of things. It's almost kind of a secondary category here, because the list is long of different types of things that you might want, and when we're talking about a base, what that really means is it's one record per something. And when you're running a query, think of it as a spreadsheet that you're ultimately returning where there are columns and there are rows. And what is it you want as your rose?

Do you want one line per? In the case of person, one line per person and then you could still pull information about their most recent application or their most recent submitted application or application one and then application to. But you ultimately have one line per person. If you run a query for say, application, you're getting one line per application. If you are running a query for gift, you're getting one line per gift. So is it that you want one line per donor or is it that you want?

One line in this sort of top level output per gift is part of that. This also includes a number of other types of things, including forms and will be going into those in more detail on this web and arm. I will also point out that because it's being recorded, it's not a hard stop that we have coming up in approximately 39 minutes because I have a feeling that there's just a lot of content to get through on here. But again, like Ike and Tina Turner, I will pick up the pace as we move along.

Alright, so this is my slow. Just to be clear, I then system are things that are those look up tables. Those different types of validation tables that might be out there. Do you want a list of what are the types of materials you have or are there other types of things that you might want to be pulling out where you want a list of all of the countries because you want to pull that out for a feed going to your student information system so there may be a number of different occasions where you do want this even at this top level where you want to pull out.

Well, I want to know what all of our material types are, because maybe there's some additional information that's associated with that, or even fancy types of almost report like things that you can do with these. But for the initial demonstrations that I'll be going through, we're going to be starting with person because that's something that can relate to everyone, whether they're on the admissions or student success or advancement side. They're all working with people, so we're going to go ahead and start and build a query from person. Now if we go back to what we were just thinking about with that sort of previous prospects example.

We can start very simple with these and if we go and click export to add on the different types of output columns that we're going to have on our particular query, these are the things where it's going to show US data. So an export really is something that will show US data, so maybe it's like a first name or last name or an email address or their most recent academic institution that they had attended, but then a filter is where instead of getting all of the records of whatever type that it might be. So this could be.

I in this case all people, but it might have been all applications or all gifts. How is it that I want to restrict that population? So I want to instead of everyone I want just people with a particular prospect status or just people who live in a particular state. So we're going to start simple and say maybe I want to pull an bear first and last name and email address and what I'll point out is you can of course type in any type of a search I it allows partial searches here and it will hold on to all of your selections.

So don't feel as though you need to go and keep trying to Scroll down or even go through pages will hold on to what you selected and you can go and add them. So as I click continue on this initial example, we're going ahead and adding on those output columns that are here. And again it looks just like the normal data. Kind of like a normal query, nothing too different yet.

But now this is where we can start thinking about things like I want an address we now operate on this presumption that we don't know what type of an actress you might want. I you really might want any type of an address and we want you to be empowered to be able to have any type of an address or any of their gifts or any of their applications that they're going to have. And so when we are initially pulling on what we call direct exports or direct filters and a direct filter could be something where.

Maybe I'm just going in and pulling on, oh, let's see something like particular filters that are going to be apart of this. I and maybe what I want is I'll pull on. Let's just say some direct filters on here and maybe I want to filter just by entry term or by a particular field that they're going to have on here as well. And what I could do with this is, oh, let's see, you could be a little bit of time. I will let this come up in just a second.

But what I can go and filter down to are things where maybe I'm going to pull on something like an entry turn and pick out a few different types of data points that I want to. I have as part of that, so I could go ahead and start from those different types of pieces that we want to present on here and then filter down accordingly with them as I go and continue through my entry terms. I could go and see in real time exactly how many records I'm going to be receiving as part of its. And maybe I want to find just folks that are in a particular entry term.

And again, very similar to exactly how we have built queries in the past, but now I want things that are outside of just a particular person and what I really may be seeking defined on these are things like an address, so let's go ahead and join in and address what a joint is going to be is. It allows us to move from person to something that is related to this particular person that I'm pulling up or from the application to something that's related.

To that particular application itself, so maybe I want to go in find their addresses so I could type in something like address and I could search for that, and I could either choose some of the things that might be familiar, things that are like ranked in a particular way, or a subquery, but we're going to get into those in just a moment, so we're going to start in. Maybe say I want their sort of most recent address that they have their active address on here, so I can go and click continue and it's going to prompt me to add this Join Now, because I might also want their second address.

Instead of just calling it something generic, I can go and specify exactly what it's going to be. I want their rank one I address as part of this, so now I have an address on here as I go and click export to add some additional data points. I now see that not only can I access everything related to the person, but I can also access all of these different data points that are related to the address so I can go and choose things. Maybe I want the city, the region, and the country.

For good measure so I could add these to my query and if I go and just take a quick little peek at what I'm getting, I'm now seeing something very equivalent to what I would have been pulling out before where I'm getting it for their rank one address. But maybe it's not just the rank one address that I want, perhaps I also want now a second address so that I can show that so I could go and add a join for address and I could go and see. Not only do I have exports for address 1 now, but I also have app.

Exports for address too, and I could go and pull on the data points that are of relevance to me and I can now see everyone with their active address an their next most active address that they're going to have as part of this. Now as I go through and add a lots of different types of exports just so it doesn't get too crowded, I can go and remove some, just as a fun little trick you could go and delete things individually, but you can also go in either control, click different types of exports, or even do a control shift click.

And it can select an entire range that you might have, and you can say be gone. You exports and poof you can delete all of those types of things. You can do this in a lot of places in site now, including even deleting things out like those joints that you might have had. But let's leave on our address 1 join that we might have as part of that. I could also go and let's maybe put back on something like County as well right here. And maybe this time I want to go and find everyone who lives in a particular state so I could type in things just to kind of clue me into what I'm looking for.

Or I can see the entire list that's presented right here, and I could go and for my address one I could go and filter down on to things that are going to be of particular relevance to me. So I'm interested in state on here and I want to find just people who are in Connecticut and because I'm getting 0 rose, I'll go and remove just that other filter that I have and I can see that I'm getting 680 records as part of that. Now this is helpful to be able to see people who live in this particular area.

But maybe I'm interested that they live in Connecticut in Colorado and so now I'm going to get some addresses where they're living in Connecticut or Colorado for their rank one address. But now let's start showing some of the things that you could do that start to get a little bit fancier than this sort of simplistic approach that we might have. I could still stay a little bit simpler and on my address 1 now that I've joined in the address, there might be things that I can know about their country that might be related to this.

So one option is that I could go and click on to join and I can see well where can I go from address one? I could actually go out to the country because maybe that has something of interest to me and so I could go in, join in sort of this address 1 country and as I go and pull this up I now could pull the ISO codes for that. Or I could even pull setup on what continent does this particular country reside so that maybe I want to be filtering to find everyone who's.

Lives in North America because maybe there is a staff assignment that's happening by continent for certain types of international populations that you might have. But also let's go back and say that before I had added this, I didn't actually have this particular join. I as part of this what I could do is click on to export and I could type in something that I think I might want and we can actually search beyond what joins. He may already have things that are sort of 1 degree of separation away.

I mean, we can say OK, well, you actually can get to this, but it's going to require a join and we can propose. What should that join be and so you could say, well, I want to actually pull it in for their address 1 country and so now as I save this I not only get my export so I can see my North America coming right in. I also got that nice convenient join added for me and so this is what we're calling an extended exports and extended filters. We oftentimes recommend that you.

Oh, and it will actually be smart if you try to delete it and it's being used somewhere we can call out that it's actually actively being used. And then you need to remove that on here as well, but there might also be things that we want to do where perhaps I want to add in a filter and seemingly that too could go and search for things, so maybe I want to filter by state, but I want to find people whose active address is not only in a particular state, but maybe it's a different address that they have so I could choose my existing one.

Or I could add a join and I could see my new ones, but I could also go to extended filters and what this will do is show me my existing ones. I could choose from, but I could also say you know I want their second most recent address to also be in Connecticut. And now this matching rows count that I'm seeing is really the intersection of all of my other filters, so I'm getting just that composite, but now it's become a little bit more sophisticated with some of the things that we can do because a lot of these, if they were more sort of preconceived.

I buy us before and we're in this late template library. What I'm building right now. You really could do before, uh, what you might not have been able to do before. Is this where you said I want the set of ranked #5 address as part of that but configurable joints? Let's see do some remarkable things through what we call Subquery exports, subquery filters, and subquery joints. Now what you'll see is when you have this little export thing over here, hover right to the right and there is a little button for a subquery.

You could either go in track this into a particular part in your export list, or you can just click on to it so that you don't have to drag it over and it will insert it right at the bottom of your list. So if you tend to build a query more sequentially, just very easy to sort of click instead of needing to drag that around. So let's say that we want to do something a little bit fancier, and I want to say how many addresses I do they have in Connecticut. Now what I'm telling sort of already is that I'm pulling people whose.

Rank one addresses in Colorado, Connecticut, but that's not telling me how many addresses that they might have. I just know that these people all have an address that is in Connecticut or an address that's in Colorado, but for the purposes of this, maybe we have a campus in Connecticut and I want to know how many different addresses are on their record in Connecticut. So what I can do is I can create a subquery in subqueries are really powerful things. When I was describing what a query bases before and then it's defining sort of 1 record for something.

What that means is that when you add in exports or you subtract exports, you're always. That's never going to influence the row count in any sort of way. Similarly, a filter should never expand the rowcount from. It's possible thing you're not going to get a duplication of values with that subqueries. Let you actually kind of break away from those constraints, which are very purposeful in the main part of a query where you wouldn't want to inadvertently join in, let's say, an address filter, and all of a sudden you're getting.

Duplication of a person because they have multiple addresses in Connecticut, but in a context of a subquery, you very much can do those types of things, and so I could say I want to pull in a joint and I want to find all of their addresses that they might have because I'm going to be trying to get account for this. I could give it a name, but again will auto suggest names for these and I tend to find that the names are pretty appropriate and so normally you can just go and sort of click save as part of this I could go and.

Filter against things in my subquery and so it's saying what it with a subquery for every single row that's going to be returned for every person. I want it to now sort of run almost a separate query to pull something of interest out to me, so I want to pull in for a particular US state. I want to see how many addresses they have been connected.

Now what makes this a a subtrees? I need to choose what I want to do with that, so we're going to start with this option that's called aggregate. This is what you may be familiar with in like the report builder or in certain types of Excel functions where you're getting a sum or account, or I've various types of statistical functions as part of that, and So what I want is I want just a a nice happy count of how many addresses I have in Connecticut, so I could go and add these.

And as I go and pull this up, we can see that it's finding all of these Colorado folks. One might have an address in Connecticut, but there may also be one square. Here's a colleague of mine who has two addresses in Slate in Connecticut. There may be others as well, and at some point I'll probably filter for Alexander Hamilton just 'cause I know we have a number of data points on that, and so now it's actually building this sort of a almost mini report if you will.

But inside of a query, the lines really really start to get blurry between queries and reports, because queries start to take on many of the capabilities that previously were only found in reports. But now let's start to do something else because maybe I want to find and will restrict this. Perhaps just to Connecticut now, and maybe I'll even go and filter down four or maybe the match on the person's name and let's just find people whose name is Alexander.

On here so that we can see are Alexander Hamilton, among some others that would be in here as well now, but I could go and do is maybe I don't want to just see a number of addresses that they have in Connecticut and this could be number of anything. It could be number of interactions, number of jobs that they've had, number of gifts that they've made within a certain period of time and we'll see more of those as we continue along. But I want to add another type subquery filter and this time let's do something.

I like a I pulling out of the number of states on their particular record that they're going to have. Or maybe I want to pull out one of the states that they have on their record so I could say states on person record and I could go back and join in addresses because again, maybe I want to filter this in some different type of way right now and this time I'm not going to actually put on any other filters because I don't want to find the Connecticut ones. I want to see what are all of the different states that they might have on the record.

And so I could go and pull in the region for this. And I could also maybe filter down just by country, perhaps just so then I have I United States and regions being returned. Now what I can do here is a concatenate and it concatenate is a way of stringing different values together. So maybe you want in your columns that are going to be coming out into your query alist of things. In my case a particular list of states.

So I could say I want it to be a comma space and then I want either all of the states. Or maybe I want to cap it at 5 where it's no more than five and that's on here and so I could go and hit save and I can go and preview this and see OK. I see here some folks that they have not only active addresses in Connecticut, but they also have some addresses in Massachusetts or California, and in this case Alexander Hamilton has two addresses in Connecticut. Sounds kind of great, but maybe what I really meant here.

What is what are the unique states in which the person has an address?

There is an option in Subqueries where you can say I want to add a particular group. This allows you to for the sequel folks that are folks that may have SQL familiarity. This is a group by. This is a way of or a pivot table creating a particular grouping that you want to have, and so I might say for these particular ones I want to pull in what is the particular region that they have on here and I want to group by that.

And so now what I'm saying is that Alexander Hamilton, who had see T comma see T before now, I'm just getting the unique values as part of that. And that's really adding almost this sort of distinct values, I think, but I could add in multiple group buys as part of that as well. But let's start pulling in some other types of things that we might want to do. Maybe now I want to seeing that. Well, maybe there are things that are a little more exciting than just pulling in what states are on a particular persons record.

So maybe I want to say, you know, top five recent interactions that have been had with someone, and maybe this is really just a notes field 'cause I wanted to go into a Mail merge of some kind and so I don't really need it broken out into all of these different columns. I really want to concatenate the the data that's going to come out of this so I could go and put on let's say an interactions. So now I'm going from person, which is my base over here to interactions and then maybe there's some things about interactions that I want to have.

So maybe I want to pull in something like maybe the date in the subject of those interactions and I could create a concatenation of these where I want. Maybe them not just separated by a comma. Maybe I've got a line break or something and you can do like a backslash.

Then we have this in our documentation, but there are a number of these, like backslash N means a line break. Backslash T means a tab character. We could also do things like just if it's a dollar sign or you want hash marks. You could certainly do those as well. You can also drag in what are known as literals because maybe it's not that. I just wanted to be Dayton subject, you know stuff together. Maybe I want there to be like a little, maybe a colon or something in between him or a colon space so I could do that now. Maybe for the date I want to format it in a particular way.

And again you'll see all of this.