LISTSERV Maestro 8.1-4 Help

 Forward >> << Back Table Of Contents

Target Group Definition: Based on Database Access By LISTSERV Maestro

This Target Group Definition wizard lets you define a target group of the Database access by LISTSERV Maestro type that can then be used in the Define Recipients wizard.

The wizard for a target group of the Database access by LISTSERV Maestro 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:

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:

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:

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:


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-2017 L-Soft Sweden AB. All rights reserved.