Form Report Links
- Elizabeth Storrs
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)
Danielle Buczek (Brandeis)
I assume you're trying to get this as an export? I haven't tried in CJ, but if you look at the SQL for an old query you might be able to figure out where it lives. I'm happy to peek at it if you share the SQL.
Beth Storrs (Tufts Health Sciences)
Unfortunately, this is what it looks like:
select
p.[name] as [Name],
__u.[name] as [Interviewer],
__u3.[name] as [Interviewer 2],
datename(weekday, __f.[date]) as [Day],
try_convert(date, convert(varchar, __f.[date], 101)) as [Date],
(case when (__f.[date] != convert(date, __f.[date])) then format(__f.[date], 'h:mm tt') end) as [Time],
(select [value] from [config] where ([key] = 'https')) + '/register/?id=' + dbo.toGuidString(isnull(__f.[internal], __fp.[internal])) + '&person=' + dbo.toGuidString(__r.[record]) + '&h='+ dbo.toGuidString(dbo.md5(convert(varbinary(max), dbo.salt() + dbo.toGuidString(isnull(__f.[internal], __fp.[internal])) + dbo.toGuidString(__r.[record])))) as [Eval Link]
from [form.response] __rinner join [form] __f on (__f.[id] = __r.[form])
left outer join [form] __fp on (__fp.[id] = __f.[parent])
left outer join [user] __u on (__u.[id] = __f.[user])
left outer join [user] __u2 on (__u2.[id] = __r.[user])
left outer join [user] __u3 on (__u3.[id] = __f.[user2])
left outer join [application] a on (a.[id] = __r.[record])
inner join [person] p on (p.[id] = a.[person])
left outer join [activity] __pd on (__pd.[id] = __r.[payment_due]) and (__r.[payment_due] is not null)
where
(
(__f.[parent] = '81EBD4F8-2A12-4ABB-AFFF-41D81FAC1063') and (__r.[pending] = 0) and (__r.[active] = 1)
and
(
/* Current Application Period */
(a.[round] IN (select _lr.[id] from [lookup.round] _lr inner join [lookup.period] _lp on (_lr.[period] = _lp.[id]) where _lp.[active] = ('0')))
)
)
order by p.[last], p.[first]
Danielle Buczek (Brandeis)
I don't have any interview forms set up in my instance, but generally in cases like this, I try to rebuild it as a custom SQL export, where you replace the table references as appropriate. So in this piece:
(select [value] from [config] where ([key] = 'https')) + '/register/?id=' + dbo.toGuidString(isnull(__f.[internal], __fp.[internal])) + '&person=' + dbo.toGuidString(__r.[record]) + '&h='+ dbo.toGuidString(dbo.md5(convert(varbinary(max), dbo.salt() + dbo.toGuidString(isnull(__f.[internal], __fp.[internal])) + dbo.toGuidString(__r.[record]))))
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:
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)
But each time you build a new query, you would have different table aliases, which is really annoying.
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://gradhlthsci.admissions.tufts.edu/manage/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).
