Find Forms Using Old STL Conditional Logic

This has been lightly tested. Please email us at support at reworkflow.com if you find inaccuracies!

There have been reports of STL/Local query based conditional logic on forms and events breaking spontaneously, so we recommend proactively replacing such logic with Configurable Joins-based logic.

The following queries can help you locate forms using the older-style logic.

Conditional Logic Filters

This finds conditional logic filters - i.e. the filters at the very bottom of the Edit Field dialog.

SELECT f.[summary] [Form Title]
    ,f.[name] [Form Internal Name]
    ,f.[id] [Form GUID]
    ,'https://apply.school.edu/manage/form/build?id=' + dbo.toGUIDstring(f.[id]) [Link]
    ,f.[category] [Form Folder]
    ,coalesce(ff.[labelText], ff.[type]) [Field Name]
    ,ff.[export] [Export Key]
    ,ff.[position] [Field Position]
    ,lq.[name] [Filter Name]
    ,lb2.[name] AS [Base]
FROM [form.field] ff
INNER JOIN [lookup.query] lq
    ON lq.[id] = ff.[config].value('(/p/v/w/@id)[1]', 'nvarchar(50)')
INNER JOIN [lookup.base] lb
    ON lb.[id] = lq.[base]
LEFT JOIN [lookup.base] lb2
    ON lb2.[id] = lb.[related].value('(t/text())[1]', 'nvarchar(50)')
INNER JOIN [form] f
    ON f.[id] = ff.[form]
WHERE 1 = 1
    --CJ filters are joinable
    AND lb.[joinable] = 0
    --Exclude archived
    AND f.[archived] = 0
    --Exclude inactive fields
    AND ff.[active] = 1
    --Exclude Canceled/Tentative forms except future dated events or non-archived templates
    AND (
        (
            f.[type] IN (
                'event'
                ,'interview'
                )
            AND (cast(f.[dtend] AS DATE) >= cast(getdate() AS DATE))
            )
        OR (
            f.[type] IN ('form')
            AND f.[status] IN (
                'CONFIRMED'
                ,'TENTATIVE'
                )
            )
        OR f.[category] IN ('Templates')
        )
ORDER BY f.[id]
    ,ff.[position]

Prompt Conditional Logic

This finds prompt conditional logic filters.

SELECT f.[summary] [Form Title]
    ,f.[name] [Form Internal Name]
    ,f.[id] [Form GUID]
    ,'https://apply.school.edu/manage/form/build?id=' + dbo.toGUIDstring(f.[id]) [Link]
    ,f.[category] [Form Folder]
    ,ff.[labelText] [Field Name]
    ,ff.[export] [Export Key]
    ,ff.[position] [Field Position]
    ,ff.[prompts].value('(/t[contains(., sql:column("lr.[prompt]"))])[1]', 'nvarchar(256)') [prompt]
    ,lb.[name] AS [Base]
FROM [form.field] ff
INNER JOIN [form] f
    ON f.[id] = ff.[form]
INNER JOIN [lookup.requirement] lr
    ON ff.[id] = lr.[parent]
        AND lr.[base] = f.[id]
INNER JOIN [lookup.base] lb
    ON lb.[id] = lr.[query].value('(base/text())[1]', 'nvarchar(36)')
WHERE 1 = 1
    AND lr.[type] = 'prompt'
    --CJ filters are not joinable
    AND lb.[joinable] = 0
    --Exclude archived
    AND f.[archived] = 0
    AND ff.[active] = 1
    --Exclude Canceled/Tentative forms except future dated events or non-archived templates
    AND (
        (
            f.[type] IN (
                'event'
                ,'interview'
                )
            AND (cast(f.[dtend] AS DATE) >= cast(getdate() AS DATE))
            )
        OR (
            f.[type] IN ('form')
            AND f.[status] IN (
                'CONFIRMED'
                ,'TENTATIVE'
                )
            )
        OR f.[category] IN ('Templates')
        )
ORDER BY f.[id]
    ,ff.[position]