Find Unmapped Prompt Values in Source Formats

Although it's possible to query for Source Formats with unmapped prompt values, such a 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.

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

See also: Dump Mappings from a Source Format (if you want a report of your existing mappings)

A query to find unmapped prompt values in Source Formats (with the caveats above):

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

Get a Configurable Joins example from our broader Analysis Suitcase: 4e5a7b48-104c-481d-8857-b5ce1148e9e0:rwf. Look for "Source Formats with Unmappped Prompts."

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 across all source formats:

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