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.

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.
Supported in: NewEdit Parameter page Multirow

User friendliness

This is an advanced control that may be difficult to understand for inexperienced users.

Appearance

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


alt text
Here the user has selected "Nordic countries", and then expanded "Benelux" (clicked the plus-sign) to select "Belgium".


alt text
To pick the selected items, you click the right-arrow in the middle. Note that the item "Nordic countries" is a parent node. That means it's presence in the right pane implies that all it's child nodes are also considered as picked by the user.


alt text
Sometimes the order of the picked items matter. In this example the user has clicked "Belgium" and then the up-arrow to put it on top of the list.

SQL

SQL Call: Fill tree (mandatory)

SQL-statement that fills the tree with possible values to select.

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.
Table count: 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.
Default: By default the node will be a root node.
selectable optional bit
Column that controls whether this node is selectable or not.
Default: If omitted the value will not be selectable.
SelectedLabel optional string

Label for selected nodes.

Default: If this column is omitted then a copy of the value in column 2, but with all HTML stripped, will be used.
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.
Default: 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.

May modify database: No

Parameters

@serversideexpand 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.
Table count: 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.
Default: By default the node will be a root node.
selectable optional bit
Column that controls whether this node is selectable or not.
Default: If omitted the value will not be selectable.
SelectedLabel optional string

Label for selected nodes.

Default: If this column is omitted then a copy of the value in column 2, but with all HTML stripped, will be used.
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.
Default: If omitted the node is not expandable.

SQL Call: Preselect values

SQL-statement executed when control asks database for the values that are preselected.

May modify database: No

Parameters

@id 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 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.
Table count: 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
SelectedLabel optional string
Label for selected nodes.
Default: Same as column with ordinal 2

Default value

SQL Call: Default value

Retrieves the default value for the control.

May modify database: No

Resultset: Default value

Table count: repeated exactly once
Row count: exactly one row
Columns
<column with ordinal 1> mandatory string
The default value

Validation

SQL Call: Validation

This call is only made if there is a field validation set for the field info and the field has any content.

Live Validation

Performs field validation when the user leaves the field or one of its dependencies is changed, initial values set by default value and initial values in edit-mode are not validated.

Save Validation

When saving the validation runs server side if the field value has changed. A field value is considered changed if in new mode the value is anything other than NULL. In edit mode it is considered changed if it has a value that was not returned by the GetEditFields procedure.

May modify database: No

Parameters

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

Resultset: Validation messages (optional)

Table count: 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.
Warning optional string
Warning 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
			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.