Vet CJ Trainings/Notes from FGI

Vet Slate Query Training #1 10/1/204

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.

0.0 introductions

8:15 overview of query base: one row per thing you are reporting on

10:30 shift from prebuilt to CJs

13:30 joining between tables

21:30 why did Slate make us do this?

24:10 sample build on person query to get to school

51:30 sample build on app base

58:00 subtabs on right side of profile are a pointer to the tables that are linked to person

Vet Slate Query Training #2 10/8

1:00 recap

2:00 top level filters, joins, and exports

4:00 subquery exports and filters

25:00 rebuilding things that Patty wants all the time: CJs library

32:45 other things that we look for existence in

39:40 sample application base query

54:00 race data locked down

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/15 Slate Query Training #3

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.

John asked about ways to limit the exports that appear by default when clicking 'New Query' directly from an event form. I did some digging, and while there isn't a global setting to control this, if the form uses custom list exports, the query that's generated will default to including only those list exports. In testing this with events, this seems to be respected when the query is started from the event template, but not from a specific event, so just something to keep in mind. And, as I shared, you can also start the query directly from the query tool using the form response base if you don't need the default exports. 

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.

0:00 recap of the test tag

4:30 other bases

5:30 what base for finding out how many events someone has attended? (many responses per person)

17:30 join to the applicant status

19:30 scheduler queries have been used longer-term than event registrations in Slate

26:25 add form response information to Scheduler/Event based-query

31:00 (backing it up to avoid doing comparisons)

33:00 sample build: which even did someone attend first?

10/22 Slate Query Training #4

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: 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.

0.0 why use a subquery filter

3:40 aggregates

8:00 enrolling class average gpa

12:45 max/min/average within one person

14:00 concatenate

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