Textbox with Autosuggest

Shows a textbox. When the user types in the textbox a list of suggestions is shown. The user can choose one suggestion from the list or continue writing. The control accepts values not in the list of suggestions.

Start value:
Return value: The text from the textbox.
Supported in: NewEdit Parameter page Multirow Editable Grid

Appearance

alt text
The control in its initial state.

alt text
When the user starts typing, the matching alternatives are listed, and the first match is inserted as auto-complete.

alt text
The list and auto-suggest is only just that, a suggestion. The user can still type something else.

alt text
When the control loses focus, the text entered will remain unchanged.

SQL

SQL Call: Get suggestions (mandatory)

This call is made repeatedly as the user types into the textbox, and returns a list of suggestions that can autocomplete to the typed value.

To make the control have the proper responsiveness it is imperative that the procedure returns quickly, preferably in less than 100 ms.

May modify database: No

Parameters

@TopN int
Max number of suggestions to return. For better performance it is necessary to limit the number of suggestions sent from the database to the client.

Always use the exact value of @TopN in the TOP-clause. Returning more values will cause an immediate exception. Returning fewer values when more values are present can cause unpredictable behavior.

You can use the Control height (rows) setting to control how many rows the list of suggestions contains.
@Value string
The text entered into the textbox. All returned suggestions must start with this string.

Resultset: Suggestions

The suggestions to autocomplete.
Table count: repeated exactly once
Row count: zero or more rows
Columns
<column with ordinal 1> mandatory string
Text to autocomplete the textbox with. Must start with @Value. Values must be distinct.
<column with ordinal 2> optional string
Text displayed in the suggestion list. Use if you need to include some extra information together with the suggestion.
Default: If not present the values from the first column are used, and the values in the suggestion list are exactly the values that will be autocompleted.

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

Textbox With Autosuggest

"Textbox with autosuggest" works like a "Textbox with dropdown" because it allows any text to be entered, even if it's not included in the list. But it also works like a "textbox with popup" as it continously searches a stored procedure for suggestions. The ”Auto” part is that the popup is opened automatically as you type and not on "tab".

The control is best suited to autocompletion of names and search words. If you want to replace a "Textbox with popup", then you should use "Textbox with autosearch" instead.

CREATE PROCEDURE Example.TextboxWithAutosuggestTable_TextboxWithAutosuggest
	@TopN int = 7,
	@Value nvarchar(max)
AS
BEGIN
	-- You have to use the escape function below, or else the control will crash if you search for %, [], [^] and _
	SELECT @Value = ISNULL(SoftadminUtil.String_EscapeLikeWildcards(@Value), '') + '%';

	SELECT DISTINCT TOP (@TopN)
		T.TextboxWithAutosuggestTableName
	FROM
		(
			VALUES
				(1, 'alpha'),
				(2, 'beta'),
				(3, 'gamma'),
				(4, 'delta')
		) AS T (TextboxWithAutosuggestTableId, TextboxWithAutosuggestTableName) 
	WHERE
		T.TextboxWithAutosuggestTableName LIKE @Value
	ORDER BY
		T.TextboxWithAutosuggestTableName;
END;

Best practice

Escape @Value

Escape searched value.
SELECT @Value = SoftadminUtil.String_EscapeLikeWildcards(@Value);