Tcl Scripting and Advanced Mail Merge

Introduction

ListManager utilizes a built-in scripting language which can be used to customize message content. In this context, scripts are short computer programs that can be used to perform some function. In this case, the result of the execution of a script is a string of text that is inserted into an email message. These scripts can range in complexity from simple statements to complex programs. This section and the sections that follow will guide you from the simplest forms of mail-merge that simply insert the contents of a database field to the more powerful scripts that can perform SQL queries and format the results in HTML.

Tcl

Scripting in ListManager is supported using an embedded interpreter for a language called "Tcl" (pronounced "tickle"). Tcl is a powerful, text-oriented scripting language that has been around for many years and has a strong following and support base. It is important to note that, due to the context of where it is used in ListManager, the Tcl language used in ListManager differs slightly from standard Tcl. The differences are detailed in the section ListManager Tcl vs. Standard Tcl.

This section of the manual will get you started with Tcl and show you how to use many of the scripting commands added in ListManager. The information you'll find here may be all you need to create complex and highly individualized messages. If you wish to learn more about Tcl and leverage some of the more advanced text formatting and processing features, there are many good books available. More information about this powerful scripting language can be found at: http://www.scriptics.com/scripting/ .

Delimiters

Tcl scripts are embedded inside email messages by enclosing them between the Tcl delimiters. The delimiters used to start and end a Tcl tag are both "%%". All text between and including the delimiters will be replaced with the result of the script evaluation. If you wish to use these characters inside your email message and you don't want them interpreted as a script delimiter, you must put a backslash ( \ ) before the first % symbol. For example, if you want the text: "Scripts are delimited by %% characters." to show up properly in your message, insert a backslash before the first '%' symbol:

Scripts are delimited by \%% characters.

This backslash will be removed when the message is processed.

Commands

Fundamental to the Tcl programming language is the concept of a command. A command is used to pass data to an internal function and then return the results of that function as a text string. To modify how the command functions, you add arguments to the command. The results returned by the command replace the script including its delimiters. It is possible to add your own commands to Tcl. These types of commands are generally referred to as procedures. Sometimes the terminology blurs the difference between these two concepts since calling a procedure is essentially identical to calling a command.

The simplest form of a Tcl script is a single command call:

%%command argument1 argument2 ...%%

where "command" is what you want to do and "argument1" and the following arguments give the command more information about how to do what is required of it. Note that some commands take no arguments.

The 'merge' Command

The merge command is the most commonly used Tcl command and also one of the most useful and flexible. merge performs the common mail merge function, meaning it can merge in personal information into a message. In general, this personal information will be data stored in a database table. This data can be stored in the ListManager members table or it can be from other tables that ListManager has access to.

The merge command takes a single argument which is database column name. The common form of this command is:

%%merge tablename.fieldname%%

where tablename is any table that ListManager has a key into and fieldname is a field in that table. The standard tables that ListManager has access to are:

members_ stores information about each member in a list
lists_ stores information about a ListManager mailing list
topics_ stores information about the topic that this list is a member of
sites_ stores information about the site that this topic is part of
inmail_ stores the incoming mail message
outmail_ stores the message that is currently being sent.
subset_ stores information about the list subset that the current message was sent to

For example, to merge in a member's email address, you can use the merge tag:

%%merge members_.EmailAddr_%%

If you send to a subset, you can also access any tables that were part of the join ( that is, specified in the FROM and WHERE parts of the SQL statement ) in the subset. For example, if you joined to the table demographics in a subset, you can merge any field from that table. For example:

%%merge demographics.ZipCode%%

If no table name is specified, the members_ table is assumed. However, you must make sure that the field name is unique among any tables you have joined to. For example:

%%merge EmailAddr_%%

will work in general but if, as in the previous example, demographics had a column named EmailAddr_then this would generate an error.

Additionally, the argument to merge can be any SQL that is valid in the SELECT part of an SQL statement. In this case, the argument must be encapsulated in curly braces ( { } ). For example:

%%merge {'"'+members_.FullName_+'" <'+members_.EmailAddr_+'>'}%%

will be replaced with:

"John Smith" <jsmith@somedomain.com>

The argument to merge can also be a Tcl variable or a command.

Variables

Tcl is a text-based language. That means that almost every aspect of Tcl is based on string manipulation. Strings are returned by commands and are stored in variables. A variable is name for an internal storage area for a string. You can put something into a variable, manipulate the variable, and then return the results. You can also put numbers into variables and perform numerical operations on them as described in a later section.

The most basic way to put something into a variable is with the set command. For example:

%%set message "Hello world!"%%

will put the string "Hello world!" into the variable message. In Tcl, strings are generally delimited using double-quotes ( " ). If you wish to use a double-quote inside of your string, prefix it with a backslash ( \ ). This is called escaping a character and is common in programming languages. You must also escape certain other characters in order to use them in a string. These include the dollar sign "$" and the open and closed square brackets "[" and "]". Here is an example using escaped quotes:

%%set message "I said \"Hello world!\"."%%

will return

I said "Hello world!".

You can retrieve the contents of a variable by putting a dollar sign ( $ ) in front of it. For example:

%%return $message%%

In this case, the contents of the variable message are returned from this script by using the returncommand. The return command will be the last thing executed in a script and the argument to this command is the text that is to be returned. It is important to note that if you don't specify a returncommand, the last string to be manipulated will be returned. For example:

%%set message "Hello world!"%%

will not only set the variable message but it will also return the text that was assigned to it. The importance of this will become evident in the next section. It is recommended that you always use the returncommand at the end of your scripts to return the desired result. In the merge examples described previously, this is not required, but it will become necessary for more complicated scripts.

Scripts

Scripts can, and generally do, have multiple lines and statements. In ListManager's Tcl implementation, each statement must be terminated by a semi-colon ( ; ). This differs from standard Tcl, where a semi-colon is optional; the end of a line can also end the statement. The last command in a script does not need the semi-colon at the end, although it doesn't hurt to include one. The following is a short Tcl script that demonstrates the features described so far and introduces one new programming concept.

%%
set email [merge members_.EmailAddr_] ;
return $email ;
%%

In this example, we set the variable email to be the contents of the members_.EmailAddr_field and then returned the contents of that variable. We have also introduced the use of square brackets( [ ] ). These brackets tell the Tcl interpreter to execute the command contained inside and return the results.

Variables can be used inside of other strings very easily. Simply include them in the content of the string and prefix them with a dollar sign as shown above. For example:

%%
set world "world!" ;
return "Hello $world." ;
%%

In this example, the variables, hello and world are inserted into the string which is returned from this script. Similarly, you can also execute commands like merge inside of a string. For example:

%%
set result "Your email address is [merge members_.EmailAddr_]." ;
return $result ;
%%

These simple features of the Tcl language in combination with the merge commands may be all you need to do highly personalized email messages. The next section introduces some important concepts in the message send and Tcl evaluation process.