🏗️ Recreating Slate Template Library Filters in Configurable Joins
Whether you love configurable joins or not, they’re often the best option. But what about the times when the pesky matching rows in a query aren’t matching the local or STL version? This article is here to help.
📘Instructions for recreating STL filters
Specific Decision Letter
Missing Checklist Items
Within the Slate Template Library there is a filter called Checklist Missing.
It takes a few steps to recreate this filter in a configurable joins query. Assuming you start from the CJ- Applications base, you’ll need to build a subquery filter and join to Checklists.
Unlike the STL filter, section and subject are separated, so they need to be filtered on separately. This is especially important to note when you have checklist items with the same name in two different sections. The “missing” portion of the STL filter means that the checklist item is both in awaiting status and not hidden, so both of those filters will need to be added in order to completely replicate the functionality of the Missing Checklist Items filter.
Letter of Recommendation / Reference Data
You may get in the habit of thinking of letters of recommendation as materials, but that sort of thinking will not help you when attempting to get at data related to letters of recommendation via configurable joins. Instead, you’ll want to join to the Application References table and not the Materials table.
Payment Complete
In local/STL queries you have the ability to filter on complete payments, but when you go to do the same thing in CJ’s it’s not immediately clear what to do. I tried filtering on payment due, but that just showed me everyone with a payment due activity, which includes people who later had payment waived or payment received activities. And filtering for payment waived or payment received activities missed people who later had their payment refunded and thus, once again owed money.
So, let’s say you are starting from the CJ - Application base. You’ll create a subquery export and join to Payment Activities within it. Filter for the relevant account. Add an export for Net Amount. Then update the aggregate to Sum and set the Value to 0. The screenshot below should get you where you need to go.
Short Value
At a previous institution we had prompts for which we used the short value in most cases, but when creating a subquery export comparing that prompt value to that same prompt value via an independent subquery, I couldn’t get at the short value. It turns out that the way to get there is to select “Extended Value” as the export value. I never would’ve made that connection unless someone told me, so that’s why this made it into this article.
Checklist Items Required For Reading Status
The STL filter offers two options, Complete and Incomplete. Here's how to rebuild the Complete option to get a list of applications that have no "required for reading" outstanding checklist items.
This STL filter only considers the "Materials" section of the checklist.
Create a subquery that looks for checklist items "Not Exists." Join to Lookup Checklist to access the "Optional (For Reading)" filter. Also filter on "Status IN Awaiting," "Section IN Material," and "Hidden = Active."
To get the "Incomplete" option, just flip the Aggregate from "Not Exists" to "Exists"!
No Comments