Vet #1 10/1
We covered the concept of joining between two different tables/query bases in Slate, looking at a test record that has multiple schools, multiple addresses, multiple applications etc. to show why it's important to configure the join to tell Slate which one we want. We talked about the idea of rank in Slate (for example, how Slate decides which school is School 1 or School 2) and I shared this Knowledge Base article. For our first two queries, we compared what happens when you build a query on the Person base, joining to the rank 1 application, and what happens when you build a query on the Application base instead, joining to Person. This helped illustrate the difference between Direct Exports (those available to us because of the query base and joins we've already added) and Extended Exports (those suggested to us as possibilities, if we were to join out to additional tables).
Rebecca also mentioned the need to be able to query for people who have multiple applications for the same term but in different rounds, which is a great use case that is now possible in Configurable Joins. We'll cover that as an example when we get to subqueries in a later session.
Vet #2 10/8
We got our first look at subqueries in Configurable Joins, both exports—for example, concatenated lists of missing checklist items, aggregate counts of the number of schools attended—and filters—returning person records who have at least 2 applications or where the 'Test Account' tag exists. We also discussed how fields like 'Round' belong to the application record, so if I'm interested in querying based on round, that's usually going to indicate that I want a query base of 'Application' rather than 'Person'. The query base by default will include the exports and filters for the data specific to that base (in this case, applications), and then to bring in data about the person record (name, birthdate, citizenship, other information that wouldn't likely change if someone reapplies) I just need to join to Person at the base of the query to make that available.
10/1415
We started off with a recap of the test tag subquery filter, then jumped into discussing event data in Slate: how to pull aggregate information, like how many events someone attended, as well as information about their first event attended or most recent event attended.
...
For next time, I'll plan to walk through each of the 'Output' options under subqueries and share some examples of when you might want to use each.
10/22
To recap, we focused this time on defining each of the output options for subquery exports, talking about use cases for concatenations, existence exports, and aggregates; less common options like coalesces and splits; and some of the possibilities for formulas. We also looked at dictionary exports and the idea of liquid looping, with the example of a missing checklist items email. If that's of further interest, I'd recommend reviewing the following Knowledge Base article: https://knowledge.technolutions.net/v1/docs/en/liquid-markup-looping . For next time, we'll plan to do the same for subquery filters, looking at more examples of how you might filter on "the many" of something belonging to a record. I can also walk through some troubleshooting examples looking at a few common types of misconfigured queries.
10/29
We covered the options for subquery filters in more detail (count filters, comparison filters, and formulas), then dove into a Reader use case where I demonstrated how it was possible to export the reader review form submitter name if it existed, the reader queue assignment if that existed, and how to coalesce the values if we wanted a single column that pulled whichever of those two were present. Then I also walked through how to set up a query on the user base to get the counts of how many applications a reviewer currently has in their queue and how many review forms they've submitted.
11/5
For this week's session, we walked through an example of how to pull decision data—people who initially have the alternate decision, then who have enroll, and compared the outputs when using a subquery filter to check whether any of the decisions meet the criteria or when using a base join for the most recent decision or the first decision. I mentioned that these will be part of the library that FGI provides, which I'll aim to have set up in the Vet instance for next week's meeting. We set up a simple query library example using the v29 Reader Review form, showing how this reduces the number of clicks and saves you from having to select a specific form to get the review form fields you need. Also for next time, I'll take a closer look at the formula query Rebecca mentioned and we'll plan to spend some time going over how to set that up in Configurable Joins so you don't need to export it to Excel to run the calculation.
11/12
11/19