version

Multirow

This control is not suitable for use on mobile devices.

Displays one or more columns of controls, each with its own field information. The number of rows can either be fixed or rows can be created and deleted dynamically.

The fields can depend on other fields on the same row, previous row and on fields outside the multirow control. Use the syntax {fieldname} to refer to a field on the same row, {fieldname(-1)} to refer to a field on the previous row or {../fieldname} to refer to a field outside the multirow control. The {fieldname} and {../fieldname} syntaxes are supported in SQL, default value and JavaScript. {fieldname(-1)} is only supported in default values.

Observe that default value dependencies always updates all rows in the control when the value of the referred control changes. Previous row dependencies will cascade through all rows when the value of a referred control is changed.

Changing the number of columns or meta data for columns during updates of the multirow control is not allowed.

Start value: Used in SQL to retreive row values (@id)

Return value:

Supported in: NewEdit

Appearance

alt text

Return values

A multirow will either be saved as a ¤-separated string or a temporary table, depending on the setting Use temp table. When consumed as a field dependency it will always be a string.

String

The multirow will be represented as a series of values separated by the ¤ character, with no extra indication of when one row ends and a new row begins. The string is usually parsed by the SoftadminApi.Multirow_Parse procedure.

Temporary table

A temporary table with columns corresponding to the fields and rows in the control is created. The table will also contain the column RowPosition containing zero-indexed row numbers. An additional parameter @xxxTableName containing the name of the temporary table is passed to the InsertUpdate-procedure.

Data types

Decimal and numeric fields in temporary tables will get their precision from the actual values during insert/update. This means that e.g. an empty multirow will have precision 0 for its decimal/numeric fields.

Columns that were date, datetime and datetime2 will all be returned as datetime in the temporary table.

In JavaScript

When referencing a multirow in JavaScript, it returns an array of its rows. Each row is as an object, where the field names are converted to lowercase and mapped to their respective values.

[
    {
        "orderId": 123,
        "productId": 456,
        "amount": 100
    },
    {
        "orderId": 124,
        "productId": 457,
        "amount": 75
    },
    {
        "orderId": 125,
        "productId": 458,
        "amount": 150
    }
]

SQL

SQL Call: Retrieve fields to edit (mandatory)

SQL-statement that returns the columns and rows to be displayed in multi row component.

May modify database: No

Parameters

@id string

Indicates which database record to collect rows from.

Resultset: Rows to edit

Resultset that fills the multi row control with data. Each row in the resultset corresponds to a row in the control. Column names determines which field information to use. The returned values will be the start value of the controls in the multirow, but null values in the resultset will cause the default value to be computed and displayed in the cell. The field names rowid, rowids and controlchanged are reserved by the multirow control and cannot be used as column names.

Table count: repeated exactly once

Row count: zero or more rows

Columns

<xxx> optional string

Column to edit

row_CanDelete optional bit

This special column is used to indicate which rows can be deleted. This column will not be visible in the multirow control.

Default: By default all rows can be deleted if delete buttons are enabled in the properties for the multirow control.

Possible value	Description
0	The row cannot be deleted, i.e. the delete row button is disabled.
1	The row can be deleted.

row_CanUpdate optional bit

This special column is used to indicate which rows can be edited by the user. This column will not be visible in the multirow control.

Default: By default all rows can be edited.

Possible value	Description
0	The row cannot be directly edited by the user, i.e. all controls on the row are disabled.
1	The row can be edited by users.

Resultset: Dynamic field information (optional)

Specify extra field information for the columns in the multirow.

Table count: repeated zero or more times

Row count: zero or more rows

Columns

AdminFieldName mandatory string

The name of the field to apply the information to. The presence of this column indicates that the resultset specifies field information.

AllowHtml optional bit

Allow HTML.

ButtonJavaScript<xxx> optional string

Inserts a button next to the control that is used to execute the JavaScript supplied here. <xxx> is an arbitrary text that can be empty if only one button is required.

ButtonJavaScript<xxx>_Label optional string

The label for the JavaScript button <xxx>.

CellAlignment optional string

The alignment of grid columns and InfoSQL values.

Possible value	Description
center	Only applicable to grid columns.
left
right

ColumnTooltip optional string

Sets the tooltip on the column title. Only supported by the Grid component.

DefaultValue optional string

The default value for the control or if prefixed by 'SQL:' the sql to run to determine the default value.

DefaultValueSql optional string

SQL that evaluates default value for field. Can contain other fields for value dependency.

Description optional string

The description for the field.

EnabledJavaScript optional string

Javascript that controls the enabled status of the field. The control is only enabled if the expression specified here evaluates to true. The expression is evaluated every time the value of a dependent control is changed. This is only available to control types for which the enabled javascript field is visible in the user interface.

FieldGroupId optional int

The field group to use.

FieldInfoId optional int

ID of field information to base the dynamic field on. All properties not explicitly overridden by dynamic field information will be copied from this field. The referenced field must be found in the menu item's field tables.

FieldLabel optional string

If present the field label is used as the heading for fields instead of the field name.

FieldType optional string

The name of the control type to use. It is usually recommended to use FieldInfoId instead unless the column is used to make fields hidden or uneditable.

Possible value	Description
boolean checkbox	Legacy alias. Use "checkbox" instead.
boolean dropdown
chart
checkbox
checkbox tree
colorpicker
date
datetime
dropdown
file
file upload area
heading
heading with checkbox
hidden
html	Legacy alias. Use "html editor" instead.
html editor
info text
listbox
multi-autosearch
multi-listbox
multi-picker
multirow
password
picture
radio buttons
radio cards
signature
textarea
textbox
textbox with autosearch
textbox with autosuggest
textbox with dropdown
textbox with popup
time
uneditable text

Height optional int

The height of the control.

IgnoreOnSave optional bit

Do not pass the field value to the insert/update procedure.

InfoSqlLayout optional string

Where the label is shown in relation to the contents in InfoSQL.

Possible value	Description
Default	Inherit layout from menu item.
LabelAbove	Full width, label above.
LabelLeft	Label to the left.
NoLabel	Full width, no label.
Standard	Deprecated. Use LabelLeft instead.

MandatoryJavaScript optional string

JavaScript that controls the mandatory status of the field, this overwrites nullchoice if set. This is only available to control types for which the mandatory JavaScript field is visible in the user interface.

MaxDate optional date

Only applicable to the Date and Datetime controls.

MinDate optional date

Only applicable to the Date and Datetime controls.

NullChoice optional bit

Specifies whether the control allows null values.

NumberOfDecimals optional int

The number of decimals to display for numeric values.

OnChangeJavaScript optional string

Javascript run when the value of the control has changed.

OutputFormat optional string

Special formatting to be applied when the field is displayed.

Possible value	Description
Hyperlink
MailToLink
PhoneLink

Placeholder optional string

Text shown when the field is empty.

Sql optional string

The SQL that determines the control's behavior. (Refer to control documentation for more information)

TextDirection optional string

Which direction the script is written in. Not to be confused with the CellAlignment property.

Possible value	Description
default	System default. Not useful unless you are trying to override an already explicit text direction on existing field information.
ltr	Left-to-right (for example English)
rtl	Right-to-left (for example Arabic)

ThousandDelimiter optional string

The thousand grouping delimiter for numeric values.

TimePickerFrom optional string

Only applicable to the Time and Datetime controls.

TimePickerTo optional string

Only applicable to the Time and Datetime controls.

Width optional int

The width of the control.

At one point, this was a pixel value. Back when Softadmin used Verdana 10px, and before fields had width-categories. Now, it is just a value that is converted to a width category.

The possible values listed below are just suggestions. For example, both 1 and 30 will be converted to shortest, and both 500 and 9999 to longest.

Possible value	Description
150	Medium-long
30	Shortest
300	Long
500	Longest
60	Short
90	Medium short

VisibleJavaScript optional string

JavaScript that controls the visibility of the field. The control is only visible if the expression specified here evaluates to true. The expression is evaluated every time the value of a dependent control is changed.

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. Fields used in an editable grid do not use this call.

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

Multirow procedure

Example multirow procedure. Field info for the fields that make up the multirow is set with regular field info as you would with any other field.

CREATE PROCEDURE Example.MultiRowTable_MultiRow
	@Id		int = NULL
AS
BEGIN

	SELECT
		-- Used when updating, hidden using field info of type "hidden"
		MRT.MultiRowTableId,
		-- Columns to edit
		MRT.MultiRowExampleColumnA,
		MRT.MultiRowExampleColumnB,
		MRT.MultiRowExampleColumnC
	FROM
		Example.MultiRowTable MRT
	WHERE
		MRT.OwnerObjectId = @Id
	ORDER BY
		MRT.MultiRowExampleColumnA

END

Multirow InsertUpdate example

Example of how to handle data from a multirow in an InsertUpdate procedure. Note that we assume the name of the temp table in order to avoid using a dynamic query.

A CTE is used in the MERGE statement to ensure that we only handle relevant records, ie. we don't want to delete records not found in the source belonging to other RowIds.

CREATE PROCEDURE Example.MultiRowTable_InsertUpdate
	@Id							int = NULL,
	@ExampleMultiRow			int = NULL,
	@ExampleMultiRowTableName	sysname = NULL	-- dummy, the temp table is assumed to be named #ExampleMultiRow
AS
BEGIN

	-- We operate on a CTE to avoid doing anything to rows belonging to other OwnerObjectIds
	;WITH
		-- T as in Target, the table into which we want to MERGE data
		T AS (
			SELECT
				*
			FROM
				Example.MultiRowTable MRT
			WHERE
				MRT.OwnerObjectId = @Id
		)
	MERGE
		T
	USING
		-- S as in Source, the source data for our MERGE
		(
			SELECT
				*
			FROM
				#ExampleMultiRow
		) S ON
			S.MultiRowTableId = T.MultiRowTableId
	-- If no match was found in the target for a row in the source 
	-- we want to add that row since it is new
	WHEN NOT MATCHED BY TARGET THEN
		INSERT
		(
			OwnerObjectId,
			MultiRowExampleColumnA,
			MultiRowExampleColumnB,
			MultiRowExampleColumnC
		)
		VALUES
		(
			@Id,
			S.MultiRowExampleColumnA,
			S.MultiRowExampleColumnB,
			S.MultiRowExampleColumnC
		)
	-- If a match was found between source and target 
	-- we want to update the target row with new data
	WHEN MATCHED THEN
		UPDATE
		SET
			MultiRowExampleColumnA	= S.MultiRowExampleColumnA,
			MultiRowExampleColumnB	= S.MultiRowExampleColumnB,
			MultiRowExampleColumnC	= S.MultiRowExampleColumnC
	-- If no match was found in the source for a row in the target 
	-- we want to delete that row since it has been removed and is no longer valid
	WHEN NOT MATCHED BY SOURCE THEN
		DELETE;

END

Best practice

Always use an explicit ORDER BY

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

Name of stored procedure

The stored procedure should be named "<Schema>.<TableMultiRowGetsRowsFrom>_MultiRow".

Limit number of rows

A web page will get slow if too many controls are shown at the same time. Try to keep the number of rows to a maximum of about 20 rows.