Target Group Definition: Database or LDAP directory access by LISTSERV
- To access the definition wizard for a target group, select the desired target group in the Target Groups subtree, then select Edit -> Open Target Group Definition from the menu (or go via the right-click menu of the target group node in the explorer tree).
- To create a new "database or LDAP directory access by LISTSERV" target group, select New -> Recipient Target Group -> Based on Database or LDAP Directory Access By LISTSERV from the menu (or go via the right-click menu of the parent target group folder in the explorer tree).
The Target Group Definition wizard lets you define a target group of the Database or LDAP directory access by LISTSERV type that can then be used in the recipients wizard to define the recipients of a job.
The wizard for a target group of the Database or LDAP directory access by LISTSERV type has multiple pages:
General, Source, Parameters,
Input Layout, Input Preview,
Recipients Details, and Summary.
The top row of the wizard displays links to these pages. The page that is currently open is marked with a highlighted background color. Depending on the choices made on some of the wizard pages, other pages may become disabled or may be shown in different versions. If a wizard page is disabled, then it means that the page is not necessary with the current choices and can safely be ignored.
Parameters Page: Parameters in the SQL Statement or LDAP Query
This screen is only available if the SQL statement or LDAP query contains parameter tokens. The Source Page help describes how to supply parameter tokens.
At the top, the SQL statement or LDAP query is displayed, with each parameter that has been found converted into a clickable link. Each of these parameters must now be "defined" (see below for details). All parameters that are undefined are displayed with a highlighted yellow background. Parameters that are already defined are displayed without the highlighted background.
Click on any parameter (highlighted or not) to select it. The details of the selected parameter are then displayed in the lower half of the wizard page:
- Parameter: Displays the name of the selected parameter so that you know which of them you are currently editing.
- Label: Enter a label for this parameter. The label is mandatory. It will be displayed to the end user when asked to fill out the parameter. Enter a meaningful name, so that the end user will know what to enter, and how this input will affect the actual recipients selection.
- Description: Enter a description for this parameter. The description is optional. If present, it will be displayed to the end user when asked to fill out the parameter. In many cases, the label alone may not be enough for the end user to understand exactly what is supposed to be entered (or selected) for a certain parameter, so an additional descriptive text may be in order. Choose this text carefully.
- Input Type: Select the input type of the parameter. This determines the kind of input or selection the end user must make to enter or choose a value for this parameter. This may also limit the end user to a certain kind of input (for example integer or float number). See below for detail about the input types and their meanings.
When the end user selects a target group in the recipients wizard, all parameters that need to be filled out will be presented in tabular form:
Parameter Label: (Parameter Description here [if present], in parenthesis) Another Parameter: (Description of the next parameter) ...and so on...
Please see the Input Preview screen, which displays all parameters to the target group administrator in the same form they will be displayed to the end user in the recipients wizard.
Available Parameter Types
The following lists the available parameter types for each parameter context, with a list of the descriptions of each parameter type further below. Some parameter types are restricted to certain sub-selections, depending on their context in the SQL statement or LDAP query.
Parameters in an SQL Statement
For integer values, outside of any in-clause:
- Edit Field (Single Value) (Only available with sub-selection "Restrict value(s) to integer numbers".)
- Checkbox
- Selection List (Single Value)
For integer values, inside an in-clause:
- Edit Field (Multiple Values) (Only available with sub-selection "Restrict value(s) to integer numbers".)
- Selection List (Multiple Values)
For non-integer (quoted) values, outside of any in-clause:
For non-integer (quoted) values, inside an in-clause:
Parameters in an LDAP Query
All values:
Description of Input Types
Each parameter type is described below along with what the target group administrator is required to input to define them correctly, how they will be presented to the user, and how the user's input will be used to replace the parameter placeholder in the SQL statement or LDAP query.
Parameter Type Edit Field
The parameter type "Edit Field" will be rendered as a free input field and the input will be validated according to the sub-selection:
- "Restrict value(s) to integer numbers": only integer numbers are accepted as input.
- "Restrict value(s) to floating point numbers": only numbers (integer or floating point) are accepted.
- "Allow free text input": Any kind of input (including text) is accepted.
The input from the user will be used directly to replace the parameter placeholder. SQL only: If the parameter placeholder was quoted , then all occurrences of the quote character in the user input will be automatically escaped before the replacement. LDAP only: Any characters in the user input that need to be escaped for a valid LDAP query will automatically be escaped before the replacement.
For an SQL statement, there are two versions of the "Edit Field" type: Single value and multiple values. In the single value version, the input field will be a one line input field, and the value entered by the end user will be the value used to replace the placeholder. This type is only available if the matching placeholder is not inside of an in-clause context. In the multiple values version, the input field will be rendered with multiple lines, and the end user may enter several lines of text. Each line will be interpreted as a separate value, and the placeholder will be replaced with a comma separated list of all values (of all lines). Empty lines will be ignored. This type is only available if the matching placeholder is inside of an in-clause context.
For LDAP, only the single value version of the "Edit Field" type is available. See above.
Parameter Type Checkbox
The parameter type "Checkbox" will be rendered as a checkbox. The checkbox has two associated values,
one for the "checked" state and one for the "unchecked" state. Enter these values accordingly - they need
to be different values.
Depending on the input from the user (if the box is checked or not), the one or the other value will be used
directly to replace the parameter placeholder.
SQL only: If the parameter placeholders
were not quoted, then enter only integers as the two values, otherwise the values will not be accepted. If
the parameter placeholders were quoted, then you can enter any string for the values, and all occurrences of
the quote character in these will be escaped automatically before the replacement. Remember, do not escape
quotes yourself when entering the values.
LDAP only: Remember to escape any characters
in the values that would otherwise be illegal in an LDAP query.
Parameter Type Selection List
The parameter type "Selection List" will be rendered as a selection list with multiple entries.
In an SQL statement, depending on the context of the matching parameter, the user may select only a single entry, (parameter not in an in-clause context) in which case the list is rendered as a drop-down list. Alternatively, the user may select multiple entries, by holding down the SHIFT or CTRL key, (parameter in an in-clause context) in which case the list is rendered as a multi-line selection list.
For LDAP, only the single-select variant of the "Selection List" type is available. See above.
The entries in the selection list are specified manually on this wizard page.
Each value consists of two parts. The first part is the visible text in the selection list - what the end user actually
sees in the list. The second part is the invisible value associated with that entry. The invisible value associated with the entry
selected by the end user will be used directly to replace the parameter placeholder.
SQL only: If the parameter placeholder was
not quoted, then you must use an integer number as the internal value of an entry. Otherwise, the internal value will
not be accepted. If it was quoted, then you can enter any string for the internal value and all occurrences of the
quote character in the value will be escaped automatically before the replacement. Do not escape quotes yourself when
entering the values.
LDAP only: Remember to escape any characters in the internal values that
would otherwise be illegal in an LDAP query.
To add a new entry, click on the "New" link to the right of the list, then enter the visible text into the left one
of the two edit fields, and the internal attribute value into the right field. Next, click on "Save Entry". The new
entry will be added to the list.
To modify an existing entry, simply select the entry in the list, then edit its visible text and/or internal value
in the two edit fields above and click "Save Entry" to save the changes. The entry will be updated accordingly.
To change the ordering of the entries, select the entry you want to move and click "Up" or "Down" to move it
in the list.
Note that the list may only have a maximum of 1000 entries, in order to not overburden the user interface and to protect against abuse. If you require more entries than this maximum, consider using the "Edit Field" type for this parameter, letting the end user input the value manually, instead of selecting it from a list with too many entries.
Important: For a parameter which is in a multiple-select context (in an
in-clause context of an SQL statement), the potential maximum of 1000 selection list
entries entails a certain pitfall:
If the target group is later used in the recipients wizard, and the user selects multiple entries from
the selection list, then all selected entries will be used to form a list of discrete values for the
in-clause in the SQL statement. The maximum number of possible values in this discrete list depends on
the database and is usually a lot lower than 1000 (for example,
some databases have an in-clause value limit of 100 discrete values).
Therefore, if the generated in-clause contains more values than the database supports, a database
error will occur. This database error will only occur if too many entries are selected by the user (so that there are too
many values to include in the in-clause) and the fact if it occurs or not therefore depends entirely on the
behavior of the user who uses this target group in the recipients wizard. It is therefore not possible to
determine already during the definition of the target group if this error will occur or not.
For the person defining the target group, there are therefore two choices:
- If it is likely that users will attempt to select a lot of entries from the selection list (or even all of them), then you should not include more entries in the selection list than are supported by your database (i.e. the number of entries in the selection list should not exceed the maximum number of discrete values in an in-clause, for your database - see the database's documentation for details about this maximum).
- If it is unlikely that users will ever attempt to select more than only a few values from the selection list (if in the context of the parameter a selection of many or all values simply would not make any sense), then you may risk to include more entries in the selection list than are supported for your database (but you must be aware that if a user selects too many values, he may then experience a database error in the recipients wizard - which may however be acceptable since such a selection would probably be non-sensical in the given context anyway).
Parameter Type Date and/or Time
Choose sub-selections for "Date Input Format" and/or "Time Input Format". Choose at least one or choose both. The selection determines whether the user will be asked to input a date, a time, or both. It also determines how the input fields will be arranged. Input fields will be rendered as three input fields for the date (day, month, year ordered according to your selection), and/or as two or three input fields for the time (hours and minutes with or without seconds, according to your selection).
All end user time input must happen in the 24h format from 00:00:00 to 23:59:59. You cannot use an AM/PM input format.
In addition to defining how the date/time input will look for the end user, you also need to define how the input from the user is converted into a string that matches the date/time format used in your database. This is done by entering a format string into the specified edit field.
In that format string, use any characters you like (for example, separation characters like ":" or ",") and use any of the format placeholders listed to the right of the edit field. Each format placeholder will later be replaced with the corresponding date/time value, in the corresponding format. Possible format placeholders are:
- year4: Will be replaced with a four digit representation of the year value
entered by the end user (for example "2004"). Available only if a date input format was selected.
- year2: Will be replaced with a two digit representation of the year value
entered by the end user (for example "04"). Available only if a date input format was selected.
- month2: Will be replaced with a two digit representation of the month value
entered by the end user (for example "09" or "12"). Available only if a date input format was selected.
- month1: Will be replaced with a one or two digit representation of the month
value entered by the end user, with months January to September as one digit and months October to December
as two digits (for example "9" or "12"). Available only if a date input format was selected.
- day2: Will be replaced with a two digit representation of the day value
entered by the end user (for example "01" or "31"). Available only if a date input format was selected.
- day1: Will be replaced with a one or two digit representation of the day
value entered by the end user, with days 1 to 9 as one digit and days 10 to 31 as two digits
(for example "1" or "31"). Available only if a date input format was selected.
- hour2: Will be replaced with a two digit representation of the hour value
entered by the end user (for example "08" or "23"). Available only if a time input format was selected.
- hour1: Will be replaced with a one or two digit representation of the hour
value entered by the end user, with hours 0 to 9 as one digit and hours 10 to 23 as two digits
(for example "1" or "23"). Available only if a time input format was selected.
- min2: Will be replaced with a two digit representation of the minute value
entered by the end user (for example "04" or "59"). Available only if a time input format was selected.
- min1: Will be replaced with a one or two digit representation of the minute
value entered by the end user, with minutes 0 to 9 as one digit and minutes 10 to 59 as two digits
(for example "4" or "59"). Available only if a time input format was selected.
- sec2: Will be replaced with a two digit representation of the seconds value
entered by the end user (for example "06" or "59"). Available only if the time input format with seconds was selected.
- sec1: Will be replaced with a one or two digit representation of the seconds value entered by the end user, with seconds 0 to 9 as one digit and seconds 10 to 59 as two digits (for example "6" or "59"). Available only if the time input format with seconds was selected.
While you are typing the format string into the input field, the sample date/time "Sep. 1, 2002 08:04:06 AM" will continuously be converted into that format and the result displayed below the input field. For example, if you input "month2/day2/year4 - [hour2:min2:sec2]" as the format, then the sample will display:
09/01/2002 - [08:04:06]
The input from the user will be applied to the format string you enter in the same way as with the sample date,
and the resulting string will be used to replace the parameter placeholder.
SQL only: All occurrences of the quote character in the date/time string will
be escaped before the replacement. Do not escape quotes yourself when
entering the date/time string.
LDAP only: Remember to escape any characters in the date/time string that would otherwise be
illegal in an LDAP query.
Note that this database format always requires a 24h time format from 00:00:00 to 23:59:59. You cannot use this input type to generate a database time format that includes AM/PM information with hours from 1 to 12. It is also not possible to generate a database date format where the month or day of the week is given in long text, like "Monday, December 2nd, 2003". If you require such a time format, you should use the "Edit Field" type instead, and let the user input the date and time manually in the format required by the database (use the parameter's description field to tell users which format they need to use to be compatible with your database).