Retrieve Lookup List Values From The Database

SQL database can be used as a data source of the lookup list. The list values are linked with specific columns in the database table by using an SQL query.

When retrieving values to the lookup list by using an SQL Query, you need to ensure that the value selected from the combobox property still matches with the value that comes from the database. If the values do not match, the comboboxes behave as follows:

Combobox	The value selected earlier from the combobox will be set as empty if the new value in the database does not match with the selected one anymore.
Editable combobox	The new value that comes from the database overrides the existing value if the values do not match.

List Values

SQL query to retrieve values

Specifies an SQL query to be used to retrieve the values from the database into the lookup list.

Allowed count of columns for the list types:

Simple	Retrieve one column.
Key-Value Lists	Retrieve a one value column and up to 15 key columns for them.
Hierarchical List	Retrieve several columns from the data source in hierarchical order.

NOTE: The lookup list cannot be saved if the SQL query retrieves more or less columns than allowed for the selected list type.

SELECT name, display_name FROM ct.v_users

An example query above retrieves values of columns name and display_name from the database table called ct.v_users. A key-value in this example case is the value retrieved from the name column.

SELECT * FROM (
SELECT kdi AS id, '' AS parent, RIGHT('0000' + ltrim(str( kdi )), 4) + '. ' + teksti AS value, 0 AS sort_order, kdi AS [key]
FROM [NOVA LINK].[YR0].[dbo].[v_tuoteryhma] WHERE paakdi IS NULL
UNION
SELECT kdi AS id, paakdi AS parent, RIGHT('0000' + ltrim(str( kdi )), 4) + '. ' + teksti AS value, CASE WHEN paakdi IS NULL THEN 0 ELSE paakdi END AS sort_order, kdi AS [key]
FROM [NOVA LINK].[YR0].[dbo].[v_tuoteryhma]
WHERE teksti IS NOT NULL AND teksti <> '' AND paakdi IS NOT NULL ) AS rs

An example query above retrieves values of the columns located in an external database in hierarchical order so that the SQL Query result could be used as a data source of a hierarchical lookup list.

Hierarchy of the list values is based on the column called in results as sort_order which is always zero (0) on the very first level. On the other hierarchy levels, the sort_order value comes from the database table column called paakdi if its value is not empty, otherwise the sort_order is zero (0) also on the other levels of hierarchy. All key values in the results are formatted so that the final values retrieved from the database are four (4) characters long. Zeros are added into the beginning of column values until the length of four characters is reached in all values.

Values you can retrieve e.g. into the combobox type of Properties using this SQL Query comes from the database columns called kdi (id/key), paakdi (parent) and teksti (value). A text inside the brackets on the column names above are the names of the columns the values are retrieved from the database table using the example query.

Test SQL

Executes the SQL query which is specified in the SQL query to retrieve values box. If the query works properly, the results of the query appear in the value table.

You can also select the desired part of your query to execute only that part of SQL query.

TIP: Visit MSDN for further help with SQL queries.

TIP: Right-click the desired value in the lookup list value table and select Copy to copy the selected value to the Windows clipboard so you can paste the value into other places.

Retrieve Lookup List Values From The Database

List Values

Related Topics