Vet CJ Trainings/Notes from FGI
- Elizabeth Storrs
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
11:30 some names of fields have changed
12:20 FGI query library for most used things
13:30 joining between tables
16:10 renaming joins
18:00 base joins don’t change the number of rows returned
21:30 why did Slate make us do this?
24:10 sample build on person query to get to school
40:00 exports appear in the list based on order of joins, most recent at the top
42:30 joining makes the relevant exports available
47:00 direct export v extended exports
51:30 sample build on app base
51:30 joining to person
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
4:40 aggregates
7:30 multiple rows caused by different base: person versus application
9:45 subquery filters: people with 3 or more schools
12:00 how many people went to a specific school--school 1
14:45 inactive schools not included in ranking
16:00 any school is U of Miami
17:45 how to keep test records out of your results
19:45 how to get to the round information
25:00 rebuilding things that Patty wants all the time: CJs library
30:30 the old filters that were “has x thing” are all subqueries
32:00 old prospects base excluded opt out tags
32:45 other things that we look for existence in
starting on application versus person
39:40 sample application base query
43:45 subqueries for checklist item missing
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
2:30 problem with using a base join when there are multiple possible outcomes
4:30 other bases
5:30 what base for finding out how many events someone has attended? (many responses per person)
7:00 sample count of events attended query
11:00 list of registrants for a particular event
12:30 how to prevent the autopoulated fields in a query from a form
17:30 join to the applicant status
19:30 scheduler queries have been used longer-term than event registrations in Slate
still events, even through not using Scheduler this year
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?
36:45 joining to only events, which is a property of Forms
39:00 sorting
42:30 filter to only those who have an event registration: subquery join to form and form response from person
45:30 renaming joins for clarity
48:15 same thing, only using exists to filter on something that exists within those forms
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: 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.
0.0 why use a subquery export
3:40 aggregates
8:00 enrolling class average gpa
system query across all gpas application>GPA>round
12:45 max/min/average within one person
14:00 concatenate
20:00 concatenate details from multiple form submissions (incl using literals)
23:00 coalesce
27:00 existence export
28:00 formula; example: how long it between admit dates
sample build: formula to get a number 50% reader review form and 50% (undergraduate) gpa
40:00 case formula to convert X, X- etcetera into a number for a calculation
42:00 split breaks a field value at a space, punctuation, etcetera, e.g. term value Fall 20xx returns just “Fall”
44:00 dictionary export
sample: AVM missing checklist items
49:00 liquid markup in letter
55:45 rank
10/29 Slate Query Training #5
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.
0.0 subquery export review
1:15 subquery exports
2:45 comparison to STL
6:20 comparisons
8:20 format masks
13:30 rabbit hole: subquery embedded in date comparison within a subquery checking creation dates for app and person records
16:20 formulas less common in filters, but are available
17:15 count distinct
18:00 math functions
18:30 Patty question about adding on a workflow query--how many are currently in any reader bin, count of those that are read and those that are in progress--coalesce to return the name of the queue-er, or the person who submitted the form
38:45 can do the counts in Slate
41:00 Tristan building on the user base a query showing each reader, the number in their queue, and the number of forms submitted (uses Ind Subquery, comparing Form Submitter name with User name)
11/5 Slate Query Training #6
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.
0:00 Sample query build: finding people who were alternates and what their final decision was
different rank joins at the bottom; adding
31:00 Creating a new library for the DVM Reader Review Form and adding access; exploring how to use it
43:00 reader selection spreadsheet--can that be done in Slate?
11/12 Slate Query Training #7
0.0 discussions on how to add permissions to Query Library, and why people do not have Admin All Access (Tristan reached out to me about setting up Query Library custom permissions, which are now in place)
9:30 continuing on reader spreadsheet query
13:00 building base join to GPA, filtering to undergrad GPA for one and GPA grad to the other
17:00 formula build: doing math. Make sure math fields are integers or real number formats!
20:00 embedding formula in case statement, and tweaking it
34:00 min and max gpa range calculation to get range: independent subquery
40:00 this query is getting complex--might be getting close to timeout, but Tristan will continue to poke
46:00 wants more efficiency/change proofing--might be better to do in excel?
11/19 Slate Query Training #8
1:00 Replicate mailing query as a CJ in the for Thank you for your application email
use app scope, since mailing might have been used in previous years
8:30 subquery filter to find one among the many (decisions, tags)
16:30 review of query library
33:00 going back to RR’s min/max calc and the independent subquery--very advanced for typical end user!
39:45 Recommend materialized view for calculating values and referencing them elsewhere
41:30 attempt to build it anyway!