Retrieve Lookup List Values From The Database

SQL database can be used as a data source of the lookup list. Then the list values are linked with specific columns in the database table by using 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 does not match, the comboboxes behave the following ways:

Combobox	The value selected earlier from the list of combobo will be set as empty if the new value in 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 does not match.

List values:

Lookup list values are cached to the CUSTOMTOOLS database. The lookup list value cache in the CUSTOMTOOLS database is updated on the CUSTOMTOOLS startup and, when the user exit the CUSTOMTOOLS Lookup List Wizard using the Finish command.

In case your lookup list is getting the values from an external data source like a linked database, you need to do one of the following steps to update the Lookup List values available in the Properties that the Lookup List is being used with:

Open the lookup list for editing in the CUSTOMTOOLS Options and then exit the Lookup List Wizard using the Finish command. You do not need to make any changes to the list values on this procedure.
Restart SOLIDWORKS. If you are using for example the Export feature of CUSTOMTOOLS to update values in the database linked to the Properties trough the Lookup List, you can update the value cache in CUSTOMTOOLS database by restarting SOLIDWORKS because the CUSTOMTOOLS add-in is about to get restarted too on startup of SOLIDWORKS.

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 one Value column and up to 15 Key columns for them.
	Hierarchical List	Retrieve several columns from the data source in an hierarchical order. As in the Key-Value Lists, the Values in Hierarchical Lists could have up to 15 Key values too.
	The lookup list cannot be saved if the SQL query retrieves more or less columns than allowed for the selected list type.
	Example SQL Query
	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.
	Another SQL Query Example
	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 colums 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 appears into 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 another places.

Retrieve Lookup List Values From The Database

List values:

Related Topics