Multi-picker

Displays a hierarchical list of possible values in a tree structure to the left and an ordered list of selected values to the right. The user can select nodes from the tree on the left and move them to the list on the right. It is also possible to reorder the list of selected items using arrows on the right.

Appearance

A Multi-picker can contain a hierarchial list. The left pane shows the available items. When an item is picked it shows up in the right pane.

SQL

Default value

Validation

Examples

Multipicker procedure

Example procedure for a multipicker. In the example selected nodes are shown with their path.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE ContactLabel.MyMultiPicker
	@Id					varchar(max) = null,
	@ServersideExpand	int = NULL,
	@Values				bit = 0
AS
BEGIN

	-----------------------------------
	-- Fetch already selected values --
	-----------------------------------

	IF @Values = 1
	BEGIN

		;WITH
		LabelFullPath AS
		(
			SELECT
				L.LabelId,
				L.LabelIdParent,
				L.LabelName,
				CONVERT(varchar(max), NULL) AS [LabelNameFullPath]
			FROM
				ContactLabel.Label L
			WHERE
				L.LabelIdParent IS NULL

			UNION ALL

			SELECT
				L.LabelId,
				L.LabelIdParent,
				L.LabelName,
				CONVERT(varchar(max), COALESCE(LFP.LabelNameFullPath + ' - ', '') + LFP.LabelName) AS [LabelNameFullPath]
			FROM
				LabelFullPath LFP
				JOIN ContactLabel.Label L ON
					L.LabelIdParent = LFP.LabelId
		)
		SELECT
			LFP.LabelId AS [Id],
			LFP.LabelName AS [Label],
			LFP.LabelIdParent AS [Parent],
			LFP.LabelName + COALESCE(' (' + LFP.LabelNameFullPath + ')', NULL) AS [SelectedLabel]
		FROM
			SoftadminUtil.Number_ParseList(@Id) X
			JOIN LabelFullPath LFP ON
				LFP.LabelId = X.i
		RETURN

	END

	------------------------------------------
	-- Fetch nodes for the multipicker tree --
	------------------------------------------

	;WITH
		LabelFullPath AS
		(
			SELECT
				L.LabelId,
				L.LabelIdParent,
				L.LabelName,
				CONVERT(varchar(max), NULL) AS [LabelNameFullPath]
			FROM
				ContactLabel.Label L
			WHERE
				L.LabelIdParent IS NULL

			UNION ALL

			SELECT
				L.LabelId,
				L.LabelIdParent,
				L.LabelName,
				CONVERT(varchar(max), COALESCE(LFP.LabelNameFullPath + ' - ', '') + LFP.LabelName) AS [LabelNameFullPath]
			FROM
				LabelFullPath LFP
				JOIN ContactLabel.Label L ON
					L.LabelIdParent = LFP.LabelId
		)
	SELECT
		LFP.LabelId AS [Id],
		LFP.LabelName AS [Label],
		LFP.LabelIdParent AS [Parent],
		LFP.LabelName + COALESCE(' (' + LFP.LabelNameFullPath + ')', NULL) AS [SelectedLabel],
		CONVERT(bit, 1) AS [Selectable],
		NodeInfo.HasChildren AS [ServerSideExpand]
	FROM
		LabelFullPath LFP
		CROSS APPLY
		(
			SELECT
				CONVERT(
					bit,
					CASE
						WHEN EXISTS
						(
							SELECT
								*
							FROM
								ContactLabel.Label L1
							WHERE
								L1.LabelIdParent = LFP.LabelId
						)
						 THEN
							1
						ELSE
							0
					END
				) AS [HasChildren]
		) NodeInfo
	WHERE
		-- When @ServersideExpand is NULL we only want nodes that have no parent
		LFP.LabelIdParent = @ServersideExpand OR
		(
			@ServersideExpand IS NULL AND
			LFP.LabelIdParent IS NULL
		)
	ORDER BY
		LFP.LabelName

END

Best practice

Always use an explicit ORDER BY

For stable and predictable behaviour, always use an explicit ORDER BY clause.