Administrators can create new SQL List prompts by using any job property including existing SQL List prompts via substitutions. Just as the word indicates, “substitution” is the action of replacing a prompt with another to look up and retrieve the data a user needs to accomplish a specific workflow. Using %substitutions%, Administrators can replace values in their DSN, Query parameters, for example, in a WHERE clause, a text string, a column, or table name making SQL queries more robust and dynamic.
Note: The hierarchical order of the job properties in the Server Administrator may impact the execution of a query statement. Ensure the job properties in both the Server Administrator and query statement coincide.
The following example shows how substitutions are used to get Client and Matter data when legal documents are distributed in
Note: This example is for illustrative purposes and may not reflect your unique workflow configurations.
Client and Matter Example
A legal firm is responsible for managing the US and International contracts of different clients. Prompts have been set up for the following data.
-
Client ID
-
Client Name
-
US Matter ID
-
International Matter ID
Users must specify the Client ID, Client Name, US Matter ID, or International Matter ID when creating distributions in
Server Administrator setup
Administrators create the following job properties.
-
Client_ID as a text prompt
-
Client_Name as a text prompt
-
US_Matter_ID as a SQL List prompt
-
International_ID as a SQL List prompt
User instructions are provided for each prompt.
See the Job Property Client_ID dialog box example below.
Note: The
In addition, the prompts are enabled for various groups to use.
See the Group > Features > Prompts dialog box example below.
DSN and Query parameters
Administrators specify the following DSN and Query parameters for the US_Matter_ID SQL list prompt.
Note: The DSN and Query parameters must reflect your actual DSN and Query values.
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.
See the Query syntax example below.
Example: SELECT US_Matter_ID as prDisplayName, US_Matter_ID as prValue FROM USMatter WHERE Client_ID='%Client_ID%'
-
US_Matter_ID as prDisplayName is the column name that will be displayed.
-
US_Matter_ID as prValue is the column value that will be passed through.
Note: In this example, no default column was specified.
-
FROM USMatter is the database table that will be queried.
-
WHERE Client_ID=’%Client_ID%’ represents the substitution that will take place. The Client_ID text column value is substituted with the Client_ID column value from the database table.
Note: Substitutions are specified with a percent symbol in the beginning and at the end, for example, %Client_ID%. Single quotes ( ' ) that enclose the %Client_ID% substitution are part of the WHERE clause.
See the Job Property US_Matter_ID dialog box example below.
Substitutions in action
Client ID and US Matter ID
See the
| Client ID prompt | US Matter ID prompt |
|
|
The Administrator verifies the Client ID and US Matter ID are passed through correctly by using the Messages > In History folder in the Server Administrator.
See the Messages > In History folder > Recipient Properties > Job Properties dialog box example below.
Client Name and International Matter ID
Another group of
See the Job Property International_Matter_ID dialog box example below.
Note: This job property is similar to the US_Matter_ID job property. However, the database table and substitutions are different. In this example, the values used in the Query statement are related to Client_Name and International_Matter_ID.
See how the prompts work in the
| Client Name prompt | International Matter ID prompt |
|
|