LISTSERV Maestro 11.1-3 Help Table Of Contents

Target Group Definition - Select Recipients from a Database or LDAP Directory

To create a new target group, select New Recipient Target Group... from the menu (or go via the right-click menu of the Target Groups node or a target group folder in the subscriber warehouse).
Note: If the new target group has not the desired type "Select Recipients from a Database or LDAP Directory", you can change the target group type on the source page.

This Target Group Definition wizard lets you define a target group of the "Select Recipients from a Database or LDAP Directory" type that can then be used in the Define Recipients wizard.

The wizard for a target group of the "Select Recipients from a Database or LDAP Directory" type has multiple pages:
General, Source, Parameters, Input Layout, Input Preview, Recipients Details, Duplicate Elimination, and Summary.

The top row of the wizard displays links to each of 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 this page is not necessary with the current choices and can safely be ignored.

Parameters Page: Parameters In The SQL Statement

This screen is only available if the SQL statement contains parameter tokens. The Source Page help describes how to supply parameter tokens.

At the top, the SQL statement 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.

For integer values, outside of any in-clause:

For integer values, inside an in-clause:

For non-integer (quoted) values, outside of any in-clause:

For non-integer (quoted) values, inside an in-clause:

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.

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. If the parameter placeholder was quoted , then all occurrences of the quote character in the user input will be automatically 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.

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.
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.

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.

The entries in the selection list can be specified in two ways: you can specify them manually on this wizard page, or you can enter a SQL statement. The SQL statement will then be used to retrieve the values that will appear in the list from the database. Use the option buttons to choose between the two methods.

The manual specification works like this:

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.
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.

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.

Or instead of specifying the values manually, follow these steps to use a database selection to populate the list:

Enter a SQL statement into the edit box. This statement will be executed with the same connection parameters as were specified on the Source Page of the wizard. The result set retrieved will be used to populate the selection list.

The values from the first column of the result set will be used as the visible text of the selection list entries. If there is a second column in the result set, its values will be used as the internal values for the entries. If there is no second column, then the values from the first column will be used both for the visible text and the internal values. Any further columns in the result set are ignored.

Make sure that the internal values match the quote context of the parameter. If the parameter placeholder is not quoted, then the internal values must be integer values (that is the values from the second result set column, or the ones from the first column, if there is only one column). In this case, if the result set contains rows where the internal value column has a non-integer value, then these rows will be skipped and they will not be used to populate the selection list. If all rows in the result set are skipped in this manner, the system will show an error message noting that no values have been found to populate the selection list.

This option is very useful if you do not want to enter all the possible selection values by hand, or if you do not yet know which values will be possible. For example, think of a target group that has the city where a recipient lives as one of the parameters so that you can do mailings only to the residents of a certain city:

select * from recipients where city='{{name}}'
You could give this parameter the type "Selection List" and then enter all possible cities manually. This approach requires a lot of work. Using this approach means that you would have to update the list manually each time a recipient from a new city is entered into the database. If you are actively collecting recipient data, most likely you will not know all of the cities that your recipients come from before the job is created or sent. To avoid all this time and effort, use an SQL statement like:
select distinct city from recipients order by city
This statement accesses the same table as the target group itself (see the first SQL statement above) using the same database connection settings, it generates exactly one column that contains all cities that are currently in the city column in the table, in alphabetical order. The end user can then simply select one of these cities. Any new information in the database will automatically be accessible and will not have to be added manually to the target group.

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.
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.

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).

© 2002-2023 L-Soft Sweden AB. All rights reserved.