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) 

Dec 13

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.

Dec 13

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]))))

1. You start out by replacing all occurrences of __r with fr__JID_

2. If you haven't already, add your join to the form table at the bottom of the query.

3. 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)

4. 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).

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:

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]))))

https://vet.admissions.tufts.edu/manage/query/query?id=14d30b4a-0e4c-4433-aed6-62698b85168f

From <https://app.slack.com/client/TFTG51QAW/threads/thread/CFUUKHULW-1677269823.903069>