Table – ConfigLists

Purpose

Used to cache exploded configuration lists used by the Dynamic Page Builder.

Configuration Lists are created within Functionality maintained by the UX Creator application. They are hierarchic lists that contain an ordered set of values that can be rendered as options in list fields by the Dynamic Page Builder.

Definition

CREATE TABLE [dbo].[ConfigLists](
	[ListGUID] [UNIQUEIDENTIFIER] NOT NULL,
	[ItemGUID] [UNIQUEIDENTIFIER] NOT NULL,
	[Level] [INT] NOT NULL,
	[Name] [VARCHAR](500) NOT NULL,
	[Sequence] [VARCHAR](50) NOT NULL,
 CONSTRAINT [PK_ConfigLists] PRIMARY KEY CLUSTERED 
(
	[ListGUID] ASC,
	[ItemGUID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Column Definitions

The ListGUID stores the InstanceGUID of the Configuration List.

The ItemGUID stores the InstanceGUID of the Configuration List Item.

The Level indicates the hierarchy level of the Item within the List starting at 1.

The Name contains the InstanceName of the Configuration List Item Instance record.

The Sequence determines the ordering of the Item within the List.

Key Functionality

The ConfigLists table is maintained by the GI_RefreshConfigLists TSQL Stored Procedure.

CREATE OR ALTER PROCEDURE [dbo].[GI_RefreshConfigLists] 
(
	@SessionGUID AS UNIQUEIDENTIFIER,
	@UserGUID AS UNIQUEIDENTIFIER,
	@InstanceGUID AS UNIQUEIDENTIFIER	
)
AS -- DECRYPT
-- WITH ENCRYPTION AS
BEGIN

	DECLARE @ReturnStatus AS INTEGER
	DECLARE @Rows AS INTEGER

	BEGIN TRY
	
		-- Try to validate the session data...
		EXECUTE @ReturnStatus = dbo.GI_ValidateSession @SessionGUID, @UserGUID
		IF @ReturnStatus <> 0
		BEGIN
			
			-- Log and raise the error...
			EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'dbo.GI_RefreshConfigLists - Invalid session', 1, NULL
			RAISERROR ('dbo.GI_RefreshConfigLists - Invalid session.', 16, 1)
			RETURN 100 + @ReturnStatus
				
		END

		TRUNCATE TABLE dbo.ConfigLists

		INSERT INTO dbo.ConfigLists (ListGUID, ItemGUID, [Level], Name, [Sequence])
		SELECT CL.InstanceGUID, CI.InstanceGUID, 1, CI.Name, FORMAT(CI.[Sequence], '000')
		FROM dbo.vw_ZSO_ConfigList CL WITH (NOLOCK)
		INNER JOIN dbo.vw_ZSO_ConfigItem CI WITH (NOLOCK) ON CL.InstanceGUID = CI.ParentGUID

		INSERT INTO dbo.ConfigLists (ListGUID, ItemGUID, [Level], Name, [Sequence])
		SELECT CL.ListGUID, CI.InstanceGUID, 2, CL.Name + ' ' + CI.Name, CL.[Sequence] + FORMAT(CI.[Sequence], '000')
		FROM dbo.ConfigLists CL WITH (NOLOCK)
		INNER JOIN dbo.vw_ZSO_ConfigItem CI WITH (NOLOCK) ON CL.ItemGUID = CI.ParentGUID
		WHERE CL.[Level] = 1 AND CL.Name IS NOT NULL AND CI.Name IS NOT NULL AND CI.Sequence IS NOT NULL

		INSERT INTO dbo.ConfigLists (ListGUID, ItemGUID, [Level], Name, [Sequence])
		SELECT CL.ListGUID, CI.InstanceGUID, 3, CL.Name + ' ' + CI.Name, CL.[Sequence] + FORMAT(CI.[Sequence], '000')
		FROM dbo.ConfigLists CL WITH (NOLOCK)
		INNER JOIN dbo.vw_ZSO_ConfigItem CI WITH (NOLOCK) ON CL.ItemGUID = CI.ParentGUID
		WHERE CL.[Level] = 2 AND CL.Name IS NOT NULL AND CI.Name IS NOT NULL AND CI.Sequence IS NOT NULL

		INSERT INTO dbo.ConfigLists (ListGUID, ItemGUID, [Level], Name, [Sequence])
		SELECT CL.ListGUID, CI.InstanceGUID, 4, CL.Name + ' ' + CI.Name, CL.[Sequence] + FORMAT(CI.[Sequence], '000')
		FROM dbo.ConfigLists CL WITH (NOLOCK)
		INNER JOIN dbo.vw_ZSO_ConfigItem CI WITH (NOLOCK) ON CL.ItemGUID = CI.ParentGUID
		WHERE CL.[Level] = 3 AND CL.Name IS NOT NULL AND CI.Name IS NOT NULL AND CI.Sequence IS NOT NULL

		INSERT INTO dbo.ConfigLists (ListGUID, ItemGUID, [Level], Name, [Sequence])
		SELECT CL.ListGUID, CI.InstanceGUID, 5, CL.Name + ' ' + CI.Name, CL.[Sequence] + FORMAT(CI.[Sequence], '000')
		FROM dbo.ConfigLists CL WITH (NOLOCK)
		INNER JOIN dbo.vw_ZSO_ConfigItem CI WITH (NOLOCK) ON CL.ItemGUID = CI.ParentGUID
		WHERE CL.[Level] = 4 AND CL.Name IS NOT NULL AND CI.Name IS NOT NULL AND CI.Sequence IS NOT NULL

		INSERT INTO dbo.ConfigLists (ListGUID, ItemGUID, [Level], Name, [Sequence])
		SELECT CL.ListGUID, CI.InstanceGUID, 6, CL.Name + ' ' + CI.Name, CL.[Sequence] + FORMAT(CI.[Sequence], '000')
		FROM dbo.ConfigLists CL WITH (NOLOCK)
		INNER JOIN dbo.vw_ZSO_ConfigItem CI WITH (NOLOCK) ON CL.ItemGUID = CI.ParentGUID
		WHERE CL.[Level] = 6 AND CL.Name IS NOT NULL AND CI.Name IS NOT NULL AND CI.Sequence IS NOT NULL

		TRUNCATE TABLE dbo.LU_ConceptParents

		INSERT INTO dbo.LU_ConceptParents (ConceptGUID, ParentConceptGUID)
		SELECT DISTINCT F.InstanceGUID AS ParentConceptGUID, dbo.GI_GetXMLAttributeString('ListConcepts', 'GUID', D.InstanceData) AS ConceptGUID
		FROM dbo.vw_ZSO_FormDefinition F WITH (NOLOCK)
		INNER JOIN dbo.vw_ZSO_FieldDefinition D WITH (NOLOCK) ON F.InstanceGUID = D.ParentGUID
		WHERE F.Subform = 'Concept'
		AND D.Subform = 'ChildList'
		AND dbo.GI_GetXMLAttributeString('ListConcepts', 'GUID', D.InstanceData) IS NOT NULL

		RETURN 0

	END TRY
	BEGIN CATCH

		-- Log and raise the error...
		DECLARE @ErrorMessage AS VARCHAR(1000) = ERROR_MESSAGE()
		EXECUTE @ReturnStatus = dbo.GI_LogEvent '00000000-0000-0000-0000-000000000000', 'dbo.GI_RefreshConfigLists - Unknown error', 1, @ErrorMessage
		RAISERROR ('dbo.GI_RefreshConfigLists - Unknown error', 16, 1)
		RETURN 1
	
	END CATCH

END

The GI_RefreshConfigLists stored procedure is called when a UX Creator wishes to execute either “Clear Configuration Lists” or “Clear Application State” commands.

The ConfigLists table is used by the Dynamic Page Builder when it is asked to render a Form Definition that has not yet been cached in the DeepXML table.