Find Unmapped Prompt Values in Source Formats

Although it's possible to query for Source Formats with unmapped prompt values, this query is not as useful as one might hope. "Refresh values" must be clicked on each Source Format to make Slate aware of new values.

Configurable Joins example Suitcase: 4e5a7b48-104c-481d-8857-b5ce1148e9e0:rwf

Super cool portal by Lloyd Lentz: fbf78e0b-e1f7-464a-8f3d-61104a09e508:mls
https://github.com/lloydlentz/slate-tips/tree/main/portals/admin-refresh-source-format

Relevant SQL:

SELECT *
	,[remap].value('(/transform/map[val[not(@dst)]]/@src)[1]', 'nvarchar(max)') AS [First Field with Unmapped Value]
	,[remap].value('(/transform/map/val[not(@dst)]/@src)[1]', 'nvarchar(max)') AS [First Unmapped Value]
FROM [source.format]
WHERE 1 = 1
	AND remap.exist('//val[not(@dst)]') = 1

A more complex example (not in the Suitcase) that finds unmapped prompts for a specific destination field (as set in the variable):

DECLARE @dst_field NVARCHAR(100) = 'device:type'

SELECT *
FROM [source.format]
WHERE 1 = 1
	AND remap.exist('//map[@dst=sql:variable("@dst_field")]/val[not(@dst)]') = 1

Get a list of all destinations in use:

SELECT DISTINCT map.value('@dst', 'VARCHAR(100)') AS dst_value
FROM [source.format]
CROSS APPLY remap.nodes('//map[@dst and not(contains(@src, "static:"))]') AS maps(map)
WHERE map.value('@dst', 'VARCHAR(100)') IS NOT NULL
	AND [active] = 1

UNION

SELECT DISTINCT map.value('@dst2', 'VARCHAR(100)') AS dst_value
FROM [source.format]
CROSS APPLY remap.nodes('//map[@dst2 and not(contains(@src, "static:"))]') AS maps(map)
WHERE map.value('@dst2', 'VARCHAR(100)') IS NOT NULL
	AND [active] = 1

UNION

SELECT DISTINCT map.value('@dst3', 'VARCHAR(100)') AS dst_value
FROM [source.format]
CROSS APPLY remap.nodes('//map[@dst3 and not(contains(@src, "static:"))]') AS maps(map)
WHERE map.value('@dst3', 'VARCHAR(100)') IS NOT NULL
	AND [active] = 1