Control: Multi-picker

 Show all Hide all

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.

Start value: Id value used to fetch selected values from database via SQL-statement.

NOTE: If this is null or an empty string the preselected values database call will not be made.
Return value: comma(,)-separated set of values.

Control container: NewEdit

Supported in: NewEdit Parameter page Multirow

SQL

SQL Call: Fill tree (mandatory)

SQL-statement that fills the tree with possible values to select.
Supports custom errors: No
May modify database: No

Resultset: Tree nodes

Resultset that fills the tree with possible values to select, each row contains a node in the tree.
Repeat mode: repeated exactly once
Row count: zero or more rows

Columns:

<Column with ordinal 1> mandatory string
Value of node in the tree. May only contain the characters 'A'-'Z', 'a'-'z', '0'-'9', '_' (underscore), and '-' (hyphen).
<Column with ordinal 2> mandatory string
Visible text of node in the tree
parent optional string
Column that refers to the parent nodes value. This column is used to place a node under another.
By default the node will be a root node.
selectable optional bit
Column that controls whether this node is selectable or not.
If omitted the value will not be selectable.
SelectedLabel optional string
Label for selected nodes.
Same as column with ordinal 2
ServerSideExpand optional bit
Column that controls whether this node does a server-side roundtrip when expanding. The node is then expandable regardless of it having underlying nodes or not.
If omitted the node is not expandable.

SQL Call: Expand tree branch

SQL-statement executed when control makes server-side roundtrip for adding childs to a node when expanding it.
Supports custom errors: No
May modify database: No

Parameters:

@serversideexpand mandatory string
Value of node that is being expanded

Resultset: Tree nodes

Resultset that fills the tree with possible values to select, each row contains a node in the tree.
Repeat mode:
Row count:

Columns:

<Column with ordinal 1> mandatory string
Value of node in the tree. May only contain the characters 'A'-'Z', 'a'-'z', '0'-'9', '_' (underscore), and '-' (hyphen).
<Column with ordinal 2> mandatory string
Visible text of node in the tree
parent optional string
Column that refers to the parent nodes value. This column is used to place a node under another.
By default the node will be a root node.
selectable optional bit
Column that controls whether this node is selectable or not.
If omitted the value will not be selectable.
SelectedLabel optional string
Label for selected nodes.
Same as column with ordinal 2
ServerSideExpand optional bit
Column that controls whether this node does a server-side roundtrip when expanding. The node is then expandable regardless of it having underlying nodes or not.
If omitted the node is not expandable.

SQL Call: Preselect values

SQL-statement executed when control asks database for the values that are preselected.
Supports custom errors: No
May modify database: No

Parameters:

@id mandatory string
Identifier that developer uses to determine which values that should be preselected. If more than 1 value should be preselected use a comma separated string.
@values mandatory bit
Set to '1' when the control asks for preselected values.

Resultset: Preselected values

Resultset that fills the selected list with values, each row corresponds to a preselected value.
Repeat mode: repeated exactly once
Row count: zero or more rows

Columns:

<column with ordinal 1> optional string
Value of preselected value
<column with ordinal 2> optional string
Visible text of preselected value

Default value

SQL Call: Default value

Retrieves the default value for the control.
Supports custom errors: No
May modify database: No

Resultset: Default value

Repeat mode: repeated exactly once
Row count: exactly one row

Columns:

<column with ordinal 1> mandatory string
The default value

Validation

SQL Call: Validation

Performs field validation when the user leaves the field or one of its dependencies is changed. This call is only made if there is a field validation of type SQL set for the field info.
Supports custom errors: No
May modify database: No

Parameters:

@Value mandatory string
The value of the field, the procedure will not be called if value is NULL.

Resultset: Validation messages

Repeat mode: repeated zero or one time
Row count: zero or one row

Columns:

Error optional string
Error message to display. Blocks the user from saving.
Info optional string
Informative message to display. Does not block saving.

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
			dbo.ADMIN_ParseStringOfIntegers(@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.