Find Forms Not Covered by Origin Source Types ☂

This query finds forms that are Person- or Application Creation-scoped and are not included any any origin source type.

This query does not take the very broad All Events/Templates source type into account.

It’s possible to build this in Configurable Joins, but the resulting query is slow and requires custom SQL anyway.

SELECT f.[id]
	,f.[category]
	,f.[summary]
	,f.[name]
	,f.[scope]
FROM [form] f
LEFT JOIN [form] fp
	ON fp.[id] = f.[parent]
LEFT JOIN [lookup.origin.type] os
	ON (
			os.[type] = 'form_folder'
			AND os.[source2] = f.[category]
			)
		OR (
			os.[type] IN (
				'form'
				,'event'
				)
			AND os.[source] IN (
				f.[id]
				,fp.[id]
				)
			)
WHERE 1 = 1
	AND (
		coalesce(f.[scope], fp.[scope]) IS NULL
		OR coalesce(f.[scope], fp.[scope]) = 'application_new'
		)
	--Form has a recent submission
	AND EXISTS (
		SELECT *
		FROM [form.response] fr
		WHERE fr.[form] = f.[id]
			AND cast(fr.[created] AS DATE) >= '2021-09-01'
		)
	AND os.[id] IS NULL
ORDER BY f.[category]