The SQL List prompt is a job property that enables connectivity to a SQL database resource for data lookup and retrieval. It can be applied to the following Client applications based on your workflow configurations.
How it works
The SQL list prompt when loaded runs a SQL query that dynamically populates a list. The list of values comes from executing a SQL DSN (connection string) and query specified in the Server Administrator. The query performed can show the following data columns: Display Name, Value, and Default. Administrators can test their DSN and query parameters. The test will indicate whether the connection and query parameters succeeded or failed.
Note: Extremely large lists may cause slow performance on various devices. Devices will vary in the amount of values that can be shown on a list. This is based on memory and other device-specific details.
SQL and Generic List Comparison
There are two types of lookup lists available in the Server Administrator. The SQL List type provides Administrators with a more robust lookup functionality.
See the SQL and Generic List characteristics and differences in the table below.
|
Characteristics |
SQL List |
Generic List |
|---|---|---|
| SQL database connectivity | Yes | No |
| Dynamic lookup capability | Yes | No |
| Automatic updates | Yes | No |
| List definitions required during setup | No | Yes |
| Import capability for list definitions maintenance | No, Not applicable | Yes |
| Manual updates required | No | Yes |
Before you begin
Administrators must have the following information available for setting up the SQL List prompt.
-
DSN parameters
-
Query parameters
Note: In addition, you will need to determine which groups require the SQL List prompt.
Setting up SQL List prompt
You can use Configuration > Job Properties in the Server Administrator to set up the SQL List prompt.
To set up the SQL List prompt
-
Start the Server Administrator.
-
Go to Configuration and expand if necessary.
-
Select Job Properties.
-
Right-click User. The Properties shortcut menu appears.
-
Select New > Job Property. The Job Property dialog box appears.
-
Ensure the General tab is selected.
-
Type a prompt name in the Name box.
Note: Do not use spaces in the job property name if using the Device Client application. Various devices may not be able to show database values if the job property name contains spaces.
-
Ensure one of the following properties is selected.
-
Single-valued property (default)
-
Collection property
-
Table property
-
-
Select the Prompt UI tab.
-
Select SQL List on the Type drop down list.
-
Specify the instructions you want the user to see in the Instructions box (optional).
-
Specify the DSN parameters in the DSN box.
See the DSN syntax example below.
Example: Driver={SQL Server}; Server=; Database=
Parameters are separated by using the semicolon. Your values must be entered after the equal (=) symbol.
-
Specify the Query parameters in the Query box.
prDisplayName represents the prompts users will see. prValue represents the data that will be passed through. A default value can also be included in the query if necessary.
See the Query syntax example below.
Example: SELECT Name as prDisplayName, Value as prValue, Def as prDefault
FROM [SQLList].[dbo].[LookUP]
In the example above, the Query parameters must be replaced with your actual parameters. [SQLList] is the name of the database you specified in the DSN parameters. [dbo] is the default user created by the database server for each database. [LookUp] is the name of the table being queried.
14. Select the Test button. The Test Lookup dialog box appears.
15. Specify your Test parameters.
16. Select Test to ensure your results are accurate.
17. Select Close.
Expand to view more information.
Applying the SQL List prompt to user groups
You can apply the SQL List prompt to specific user groups by selecting Configuration > Groups in the Server Administrator.
Note: Applying prompts to user groups is a standard step during configuration.
-
Start the Server Administrator if necessary.
-
Go to Configuration and select Groups.
-
Right-click on the appropriate group. The Properties shortcut menu appears.
-
Select Properties.
-
In the left pane, select Features > Prompts.
-
Select Enable members of this group to prompt…. check box.
See the Group Properties > Prompts dialog box example below.
7. Select the appropriate SQL List prompt.
8. Select OK.
See also
Configuring a Lookup Job Property