Control: Textbox with popup

 Show all Hide all

The control type Textbox with autosearch is recommended instead of Textbox with popup.

Lets the user search for possible values by entering search phrases into a textbox. The search is performed when the user leaves the control either using the mouse or using the tab-key. The search can result in one of four scenarios:

  1. There is only one match for the search phrase, in which case that one hit becomes the active selection and focus is moved to the next control.

  2. There is no match to the search phrase, in which case the previously active selection is restored to the text and focus is retained so the user can perform a new search.

  3. There is more than one match to the search phrase, in which case a list of possible matches is displayed and the user has to choose one of them before moving on to the next control.

  4. An error occurs during the search, in which case an error message can be shown to the user.

Start value: id value or empty string.
Return value: id value or empty string.

Control container: NewEdit

Supported in: NewEdit Parameter page Multirow

SQL

SQL Call: Friendly value lookup (mandatory)

Used when initializing control to determine the textbox value belonging to the supplied id.
Supports custom errors: No
May modify database: No

Parameters:

@id mandatory string
Id value to lookup.

Resultset: Friendly value (compatible with lookup sql)

This form of the result set can be used if you want to return data in the same format as the lookup sql call.
Repeat mode: repeated zero or one time
Row count: exactly one row

Columns:

<column with ordinal 1> mandatory string
Id value.
<column with ordinal 2> mandatory string
Text to display in textbox for supplied id.
<column with ordinal 3> mandatory string
Text to display in listbox for supplied id.
<column with ordinal 4> optional string
Text to display in tooltip for supplied id (overrides column three)

Resultset: Friendly value (only text)

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

Columns:

<column with ordinal 1> mandatory string
Text to display in textbox for supplied id

SQL Call: Popup (mandatory)

SQL-statement that fills the popup-control with possible values.
Supports custom errors: No
May modify database: No

Parameters:

@value mandatory string
Current text entered in the textbox.

Resultset: Values

Values for control. If one row is returned it is automatically copied to the textbox. If more than one row is returned they are displayed in a listbox. If no rows are returned the last valid entry in the textbox is restored.
Repeat mode: repeated exactly once
Row count: zero or more rows

Columns:

<column with ordinal 1> mandatory string
Id value which is returned from control but never shown
<column with ordinal 2> mandatory string
Displayed values in textbox
<column with ordinal 3> optional string
Displayed values in listbox (also shown in tooltip if column four is not present)
<column with ordinal 4> optional string
Tooltip text.
Column three is used as tooltip if this column is not present.
ListColumn_<xxx> optional string
Each column where the name starts with "ListColumn_" augments the value displayed in the listbox with a left aligned column of text. No headings are shown in the list.

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

Textbox with popup procedure

Since Softadmin 6.0-ish you only have to handle two cases. Lookup and popup.

The tool tip is only displayed if tool tip is enabled in "Edit field info".

The search is limited to 50 matches since more matches would take a long time to load and having such a large result would not be user friendly.

CREATE PROCEDURE Example.TextboxWithPopupTable_TextboxWithPopup
    @Id                 int = NULL,
    @Value              varchar(300) = NULL
AS
BEGIN

    ----------------------------------------------------------------------
    -- Look up the displayed value in the textbox for an existing value --
    ----------------------------------------------------------------------

    IF @Id IS NOT NULL
    BEGIN
		SELECT 
			T.TextboxWithPopupTableName
		FROM
			(
				VALUES
					(1, 'alpha'),
					(2, 'beta'),
					(3, 'gamma'),
					(4, 'delta')
			) AS T (TextboxWithPopupTableId, TextboxWithPopupTableName) 
		WHERE 
			T.TextboxWithPopupTableId = @Id;
    END;

    ---------------------------------------------------
    -- Retrieve entries matching the search criteria --
    ---------------------------------------------------

	SELECT @Value = SoftadminUtil.String_EscapeLikeWildcards(@Value);

    CREATE TABLE #Results
    (
        Id              int,            -- Id value that will be sent to the InsertUpdate procedure
        TextboxValue    varchar(300),   -- Text to be displayed for the selected entry in the textbox
        ListboxValue    varchar(300),   -- Text shown in the listbox containing matches
        Tooltip         varchar(300)    -- Tooltip displayed for entries in the listbox
    );

    INSERT INTO #Results
    (
        Id,
        TextboxValue,
        ListboxValue,
        Tooltip
    )
    -- Limit to no more than 50 matches to avoid slowing down the newedit page and making the user wait
    SELECT TOP 50
        T.TextboxWithPopupTableId,
        'Textbox: ' + T.TextboxWithPopupTableName AS [TextboxValue],
        'Listbox: ' + T.TextboxWithPopupTableName AS [ListboxValue],
        'Tooltip: ' + T.TextboxWithPopupTableName AS [Tooltip]
    FROM
        (
			VALUES
				(1, 'alpha'),
				(2, 'beta'),
				(3, 'gamma'),
				(4, 'delta')
		) AS T (TextboxWithPopupTableId, TextboxWithPopupTableName) 
    WHERE
        -- Retrieve entries whose name matches the search text
        T.TextboxWithPopupTableName LIKE '%' + @Value + '%'
    ORDER BY
        [ListboxValue];

    ------------------------------
    -- Send data to the control --
    ------------------------------

    SELECT
        R.Id,
        R.TextboxValue,
        R.ListboxValue,
        R.Tooltip
    FROM
        #Results R
    ORDER BY
        R.ListboxValue;

    --------------
    -- Clean up --
    --------------

    DROP TABLE #Results;

END;

Best practice

Name of stored procedure

The name depends on the usage. If the control is used to retrieve values from a lookup table then the procedure should be named "<Schema>.<LookupTable>TextboxWithPopup". If it is not and the values are not directly linked to rows in a table or the functionality is specific for the current field (say for instance when you want to exclude already connected entries) then the procedure should be named "<Schema>.<Table><Field>_TextboxWithPopup".

Always use an explicit ORDER BY

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

Limit the number of hits

Always limit the number of hits by using TOP 50 or another number that is appropriate for the situation. The reason for this is that large number of results are not very user friendly and also that they take a very long time to display.

Make sure that the user can empty the textbox

Make sure to handle a value of NULL in the parameter @Value. If you don't the user won't be able to clear the textbox by doing a search using an empty value.

Escape @Value

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