Posted on Slack:
Does the old {{form-report-link}} that links the faculty directly to their review form for their interviews exist somewhere query-able now? My old queries that have it are still working, but querying on the interview template itself is now automatically a CJ query, and as far as I can tell that field only exists as a construction and isn't saved anywhere. Has anyone figured this out yet? (edited)
...
You start out by replacing all occurrences of __r with fr__JID_ [only relevant when working from non-CJ code]
If you haven't already, add your join to the form table at the bottom of the query.
Click on Display SQL to find out what table alias was assigned for the form table. (In my sample query, it's f_ec86 and fp_ec86 for the parent). In these queries, it will look something like this:
| Code Block |
|---|
from [form.response] fr__JID_
left outer join [form] f_a3c3 on (f_a3c3.[id] = fr__JID_.[form])
left outer join [form] fp_a3c3 on (fp_a3c3.[id] = f_a3c3.[parent]) |
Replace all occurrences of __f with the form table alias from your query (so f_ec86 from my query). Replace all occurrences of __fp with the form parent table alias (so fp_ec86 from my query). Note that for this, these are the same number!
Danielle Buczek (Brandeis)
...
From <https://app.slack.com/client/TFTG51QAW/threads/thread/CFUUKHULW-1677269823.903069>
Models:
Based on the Interview Template base:
https://gradhlthsci.admissions.tufts.edu/manage/query/query?id=001d0103-fea7-4987-a29a-45e9254a34b3
(select [value] from [config] where ([key] = 'https')) + '/register/?id=' + dbo.toGuidString(isnull(f_569c.[internal], fp_569c.[internal])) + '&person=' + dbo.toGuidString(fr__JID_.[record]) + '&h='+ dbo.toGuidString(dbo.md5(convert(varbinary(max), dbo.salt() + dbo.toGuidString(isnull(f_569c.[internal], fp_569c.[internal])) + dbo.toGuidString(fr__JID_.[record]))))
the pieces in red are what need to be updated based on the table aliases
Table aliases will look something like:
from [form.response] fr__JID_
left outer join [form] f_a3c3 on (f_a3c3.[id] = fr__JID_.[form])
left outer join [form] fp_a3c3 on (fp_a3c3.[id] = f_a3c3.[parent])
https://vet.admissions.tufts.edu/manage/query/query?id=14d30b4a-0e4c-4433-aed6-62698b85168f
...
Conversation with consultants
This was working fine to get the link starting from the Interview Template, but I was having issues building from a person or applications base. Tristan Devaney said: It looks like the issue is the generic base join to Form Responses. Try using the specific option for the IMM template:
...
Your custom SQL export should work using that approach if you were to update the aliases. That said, while there's not a direct export to recreate the link, all the components are available with the right joins, so you should also be able to build this with a regular formula (without having to display SQL and hunt for the aliases). I mocked up a quick example here: https://appgradhlthsci.admissions.slacktufts.comedu/clientmanage/TFTG51QAW/threads/thread/CFUUKHULW-1677269823.903069>query/build?id=790f3f0e-4720-45b4-b909-ca3eeb42c705 (there's a join to the Report Form associated with the scheduler event, a system export for your database URL, and the form response already gives you the record GUID for the salted hash).
