v4.01 © Fantoosh Software 1998-2001
|
v4.01 © Fantoosh Software 1998-2001 |
|
|
3. How the templates work. |
You've seen how easy it is to use Dynamic Mailer (ODBC) and getting a taste of how powerful it is.
Setting up templates is fairly simple. Lets examine the code in statement.htm
| 1. | Load statement.htm (found in the samples folder) into your favourite HTML editor, or notepad. |
| 2. | Look at the source code. |
| 3. | Most of the template consists of standard HTML. If you are unfamiliar with HTML there are many excellent HTML books on the market. |
| 4. | Dynamic Mailer (ODBC) encloses its commands between
<~ and ~> - you will see that the first statement in this
template is:
<~ SQL1 SELECT Mid('This account is seriously overdue!!!!',1,(([Older]>10 and [Average_Monthly_Spend]<1000)*-37)) We will come back to the meaning of the actual SQL statement later. For now, all you need to know is that you can have up to 5 SQL statements numbered 1 - 5 (e.g. SQL1, SQL2, etc). |
| 5. |
The next statement as we work our way through the file is: <~NOW ~> This tells the program to insert today's date. |
| 6.. | Now look at:
<~FIELD0 Company_Name ~> This tells the program to insert the value of fields Company_Name, Salutation and Surname respectively - from SQL0. SQL0 is the statement that you entered into the Subscriber Selection SQL tab. |
| 7. | You will see that we have:
<~FIELD1 Message1~> Remember in point 4. we looked at SQL1? Well FIELD1 is used to retrieve the value from SQL1, likewise if we had a SQL2 we would use FIELD2 to retrieve the value. Get the idea? |
| 8. | So what does this SQL actually do? Well this
statement is a little complicated - don't worry if you don't understand
it, its just included to show the power of the system with a bit of SQL
knowledge.
Lets take it bit by bit ... ([Older]>10 and [Average_Monthly_Spend]<1000) This part of the formula looks at whether the value of the "Older" (the field that holds the customers balance outstanding for over 90 days) is larger than 10 AND their Average Monthly Spend is less than 1000. This formula will be evaluated, and set to -1 if is true, or 0 if it is false. Mid('This account is seriously overdue!!!!',1 .... Mid is used to retrieve a substring - the first parameter is the string itself, the 2nd parameter tells it which character to start at (in this case character 1 - i.e. the start of the string), and the 3rd parameter tells it how many characters it should retrieve. The length of the string "This account is seriously overdue!!!!" is 37 characters. Do you see what is going on now? The formula will return either -1 if true, or 0 if false. If we multiply that value by -37 then we end up with 0 or 37. So if the formula is true we show 37 characters of the message ... if it is false we show zero characters of the message! Remember the accounts department decided that this message should be displayed when the customer owes more than $10 for over 90 days unless their average monthly spend exceeds $1000. Well this is the code that implements that business rule. |
| 9. | @UID is a special field used to pass the ID value of the current subscriber. i.e. the formula is calculated for the current email recipient. |
| 10. | Have a look at the plain text template - statement.txt.
You will see that the code is identical, though layout is obviously
slightly different due to the limitations of plain text.
Incidentally: If you use an editor such as FrontPage it may change < and > to < and > respectively. Don't worry - Dynamic Mailer (ODBC) can cope with this! |