On March 30, 2021, the RHB Slate team hosted the final session of their three-part webinar series, Build a Better Instance with RHB: Configurable Joins. Building off the Configurable Joins fundamentals introduced in part one and the intermediate skills discussed in part two, in “Expanding into New Spaces: Advanced Configurable Joins Concepts”, team members John Michael Cuccia, Domenick Rozzi, Abraham Noel, and Megan Miller walked attendees through some of the more specialized applications for Configurable Joins within our Slate instances.

The discussion centered around the following key themes.

Independent subqueries allow us to access (almost) everything.

Our second webinar was all about dependent subqueries, which allow us to access data that’s connected to the base we’re querying on. But sometimes within an export we might need to include data that isn’t related to our query base. For instance, we might want to send a Deliver message that includes registration information for the next three virtual information sessions; that’s not data we can access in a dependent subquery.

Enter independent subqueries! These subquery exports allow us to create an entirely new query within the one we’ve already begun building, using whatever base we might need. Independent subqueries include all of the same functionalities as a dependent subquery and can be added at any level of a subquery export—great news for those who find themselves building subqueries that are several layers deep.

In the example we mentioned above, we would create a subquery export, then select an independent subquery within the Related category of Form. In the subquery, we’d filter for forms that are within our Virtual Information Sessions event folder and that have a start date greater than today, and then we would use a Dictionary export to select the appropriate data points for our message. We’d sort ascending by the Start Date/Time field, and we’d limit the output to three rows. Within our Deliver message, we would then wrap this export in Liquid looping and voila! Our result would be an email that will always send the most up-to-date event options without requiring maintenance on our part.

There’s a whole world of bases that go way beyond Records.

Record-base queries are likely to guide the bulk of our work within Slate; after all, the Person and Application tables are central to the majority of our enrollment processes. But there are many times when we’ll need to look at data from another angle that takes us beyond these familiar bases, and within Configurable Joins, we have two other categories to work with: Related and System.

Related bases are those that connect to our records in some sort of way. Address, Device, Material, Message, Form Response, and Sport are all examples of related datasets within Slate. Each of these connect to the Person table, but by querying on any of these Related bases, we’re able to return one row per object in that dataset. This allows us to find the event registration forms that include a special request, export the details for each verified GMAT score, or build a report on how well a Deliver mailing performed with its recipients.

While Related bases are examining the data connected to records, System bases provide insights into our instance itself. Many of the bases we see within the System category are similar to those in the Related category, but instead of looking at an applicant’s most recent decision or students with the VIP tag, we’re accessing the metadata of these objects. We can find all the Reader bins that have a specific review form attached to them, or all the decision letters that are configured as the default for a decision code, or all the Deliver mailings that are not in a folder. This makes System bases particularly useful for maintaining our instance and identifying where updates are needed (what a great resource for Cycle Prep!).

Configurable Joins are transformative for nearly any aspect of our instances.

We’ve heavily discussed the sophisticated querying opportunities that are found in Configurable Joins. But the implications for CJs go far beyond new ways to comb through your data.

Because queries play such a major role in so many of our Slate processes, implementing Configurable Joins queries can optimize many of these functionalities. Configurable Joins transform the external-facing elements of Slate. With more precise recipient lists and more customized export fields, our Deliver mailings become far more effective. By adding strategic merge fields, forms are suddenly highly personalized. And through the dynamic Configurable Joins queries, the portal user experience is elevated to a new level.

But beyond these front-facing upgrades, we’re able to benefit in our day-to-day work within Slate. WIth Configurable Joins, we can increase the efficiency of our rules and exclusivity groups in the Rules Editor and develop incredibly precise retention policies. Our dashboards can become more robust and our reports can be more detailed. Projects and Tasks can be utilized more fully, and user permissions can be better managed. The level of customization that we enjoy with Configurable Joins creates numerous opportunities for us to not just be more efficient, but to also break new ground in our instance.

If you’re ready to explore all this in greater detail, you’ve come to the right place! Watch our full webinar, read the transcript, and download the slides to begin exploring how you can leverage Configurable Joins’ advanced features. 

Bonus: Answering the questions from the session

During this webinar, the team also responded in real time to the questions asked using the event’s Q&A feature. Here’s what they shared with attendees.

Q: How do you know if you should join a table within a subquery export/filter versus at the base query level (e.g., let’s say you wanted two or three different exports in separate columns from an independent subquery. Would you create two or three subquery exports and join each one individually to the independent query base within each export, or would you create a join at the top query level?)
A: You want to avoid repeatedly making the exact same join across several subqueries, so if you’re using it across multiple exports or filters, do it on the main query level. Join once at the top level of the query, and then place each of the individual exports in their own subquery export.

Q: How do you know when/if it’s necessary to refresh the Configurable Joins Library?
A: As a general rule, once you build new fields, refreshing the Library is a good idea. This would be applicable to new entity or dataset fields as well.

Q: In your example of using an independent subquery to list upcoming events in a Deliver mailing, you filtered for the events that were in a specific folder. What if the events are not in the same folder?
A: When the forms are not in the same folder, you will start at Form Response and then join to Form, which will then allow you to use a folder or template to pull in multiple forms.

Q: So it’s all about the folders? How do I break it down within the folder?
A: In Abraham’s example, he used the form’s folder to limit his results in the particular query, but you could filter by template, by start date, by status—any of those data points.

Q: The two forms I want to join are in different folders and do not use a template. Easiest solution: Put them in the same folder :).
A: That’s one solution, but another solution would be to create two subquery filters (one for each form) and then place an OR between them. Keeping them in separate subquery filters also gives you some additional flexibility to say “in one form, but not in the other.”

Q: And would that be a dependent or independent subquery?
A: It would be dependent. The Form depends on the Form Response row.