v4.01 © Fantoosh Software 1998-2001

2.  Statement templates

Paperweight Stationery Suppliers Inc. wish to send out a monthly statement to their customers showing their 30 day, 60 day, 90 day and older balances. 

The accounts department has decided that customers with zero balances should not be sent a statement, and that customers who spend less than $1,000 per month and have owed more than 10$ for more than 90 days should receive a stern warning!  

1. Run Dynamic Mailer (ODBC).  
2. In the database tab, select "DMDemo" from the DSN drop down.  Leave User ID and Password empty as this is not a secured database.  Click "Test Connection" to check that all is well.  
3. In the SMTP settings tab enter your SMTP server name and port (normally 25).  This information can be obtained from your ISP, or from your email software.  Click "Test Connection" to check that the settings are correct.  
4.. Select the Subscriber Selection SQL tab.  Under Test List HTML copy and paste the following SQL Statement (Use CTRL+C to copy and CTRL+V to paste).  

SELECT *, Format$([Account_Balance],'$#,##0.00') AS FAccount_Balance, Format$([30_days],'$#,##0.00') AS F30_days, Format$([60_days],'$#,##0.00') AS F60_days, Format$([90_days],'$#,##0.00') AS F90_days, Format$([Older],'$#,##0.00') AS FOlder
FROM tblCustomers 
WHERE Account_Balance>0 AND HTML=TRUE

SELECT * tells the system to select all fields in the table.  Even if you set fields as being currency, when selected in a SQL statement they will not be formatted - hence why all the money fields are specifically selected and formatted.   

The above statement tells the system to select these fields from the tblCustomers table where the Account Balance is greater than zero, (remember - the accounts department decided not to send statements to customers who have a zero balance), and where the HTML flag is true. 

Click "Test SQL Statement"  The system should tell you that 4 records match (there are 5 records in the sample database, but 1 customer has a zero balance) and the data should be displayed in a grid. 

Notice that the system has correctly identified that Email_Address is the email address and ID is the Unique ID field. 

5.. Copy the following SQL statement into Test List (Text)

SELECT *, Format$([Account_Balance],'$#,##0.00') AS FAccount_Balance, Format$([30_days],'$#,##0.00') AS F30_days, Format$([60_days],'$#,##0.00') AS F60_days, Format$([90_days],'$#,##0.00') AS F90_days, Format$([Older],'$#,##0.00') AS FOlder
FROM tblCustomers
WHERE Account_Balance>0 AND HTML=FALSE

This statement has been modified so that it selects records where HTML=FALSE.  Click on "Test SQL Statement" and it should correctly identify that no records match. 

6.. Go to the Layouts tab.  Click "Select HTML Template" button and select "statement.htm" from the samples folder. 
7.. Click "Select Text Template" button and select "statement.txt" again from the samples folder. 
8. Click on the "Send Mail" tab and click "Prepare to Send Mail".  Click on the "Select Log File" button and enter a name for the log. 
9. After a few seconds the system should show that HTML Test list is available and has 4 matching records.  

Click "Show Sample HTML" - in a few seconds an example email will be shown in your browser.  
10. Click the "HTML Test list" check box, and leave "Maintain Log File" checked.  
11. Click "Send Mail" 
12. If all steps have been followed correctly the system should send you 4 emails! 

Proceed to next step