| |
Generating Choice Lists
with SQL Statements
Normally the choices in drop down lists are statically configured by an administrator.
But it is also possible to generate the list dynamically at run time with a SQL Query.
These instructions describe the process for setting up 'Custom Choice SQL', a
Miscellaneous option.
|
|
1)
Log in as 'admin', select 'System Configuration' from the Tools menu,
and enable the Custom Choice SQL Miscellaneous Option. Click OK back
out to the main screen to save your changes.
|
 |
|
|
2)
Select the Project you want to configure, and press CTRL-T to open
the Template editor. Click on the Fields tab, doubleClick on the
Field you want to configure, and click the Choices button.
|
 |
|
|
3)
When you click the 'Retrieve Choices with a SQL
query' option, the normal buttons
are replaced with a sinlge 'Edit SQL' button.
|
 |
|
|
4) When
you click the 'Edit SQL' button you'll get a dialog where you can
enter a SQL query. You can query any table in your
database, and the values from the first column returned will be used
the Choice values for this field. If you use a query to
populate the Choice List for a Contacts field, and you
get the DisplayName from the contacts table (as in
this example), then Properties and
Notifications will work against the choices as well.
|
 |
|
5)
Here are some other example queries:
To use the IDs from the Requirements project as choice list values:
Select ID from _Requirements
If you wanted the 'Fixed In' versions to come from the 'Found In' versions in
the 'Change Requests' Template:
Select [ChoiceName] from [ChoiceValues]
where [Template] =
'Change Requests'
and [FieldName] =
'Occurred In'
Note how in this example the Field and
Table names are surrounded by square brackets. This is only
necessary if there is a space in the identifier, or if it
is a reserved word.
|
