Inforama tutorial
You can download the Inforama project files for this example by clicking the attachments link at the bottom of this page.
In version 1.3 a dedicated mailshot wizard dialog was introduced into Inforama Studio. This has given greater control over how mailshots are sent. In this step we're going to create an email template using OpenOffice and attach a rental history PDF document to the email.
Create the Datasets
To begin with we'll create the main dataset containing the customers which we wish to target with the mailshot. We will only be targeting customers in a single district so you will also need to create a parameter called district.
Open the dataset dialog and create a dataset called customer_district with the following SQL statement.
select customer_id, first_name, last_name, email from customer, address where address.district = '$P{district}' and customer.address_id = address.address_id
Next we need to create the dataset which will produce the rental history table so create another new dataset called customer_rentals_list with the following SQL statement.
select film.film_id, film.title, rental.rental_date, film.rental_duration, rental.return_date from film, rental, inventory where film.film_id=inventory.film_id and inventory.inventory_id=rental.inventory_id and rental.customer_id = $F{customer_district.customer_id} order by rental.rental_date
As you can see the second dataset references the first, using the customer_district.customer_id field to produce the table for the correct customer.
Create the Documents
Create a new letter template called MailshotCustomerRentals similar to that shown in figure 1.
Figure 1: The rental history letter template
The first line simply outputs the customer's first and last names. The second line is the instruction to create the table using the customer_rentals_list dataset and the single cell table simply outputs the fields in the dataset. If you preview this document now it will appear as shown in figure 2.
Figure 2: Preview of the rental history document
Next create an email body template called RentalsEmail as shown in figure 3.
Figure 3: The email template
Note that we use the same dataset, customer_district, that's being iterated.
Next create the compound document, CustomerRentalsEmail, that includes these documents by opening the Document manager dialog as shown in figure 4.
Figure 5: Select the document to be sent
Move the CustomerRentalsEmail document to the list to be sent and click the Next button to display the screen shown in figure 6.
Figure 6: Select the dataset to iterate
Select the customer_district dataset that we created earlier and click the Next button to display the screen shown in figure 7.
Figure 7: Select the email field from the dataset
Click OK on the dialog and the To email field will populate automatically as shown in figure 8.
Figure 8: The To email field populated
The To email field is automatically populated with the required syntax in order to send the emails to the email field from the dataset.
Click the Next button to proceed to the screen in figure 9.
Figure 9: Specifying actions for the mailshot
After each email in the mailshot is sent we want to insert a row into the mailshot_history table that we created earlier. The syntax we use for this is the following.
sakilaDB.execute("INSERT INTO mailshot_history (customer_id, mailshot_id, date_sent) VALUES ( $F{customer_district.customer_id}, 3, now())")
We use the sakilaDB datasource as the database connection in order to execute the SQL statement. The current customer_district.customer_id field value is used as the value to insert into the table.
Click the Next button and the screen shown in figure 10 will be displayed.
Figure 10: Specifying the generated document filename format
This screen allows you to specify the format for the filename of the generated documents. We'll see how this can be used later when the documents have been created. In this case the format is the following
$F{customer_district.customer_id}-$document
In this case we want the document name to begin with the value in the customer_district.customer_id field.
Click the Next button to proceed to the screen shown in figure 11.
Figure 11: Specifying parameters for the mailshot
This screen allows you to specify the parameters which are required to generate the documents. In this example we're going to send the mailshot to all users located in the district of Texas.
Click Next and the mailshot documents will generate and display in the list shown in figure 12.
Figure 12: List of the generated documents
This screen has a list of mailshot recipients with an entry for each row in the filtered dataset. The mailshot has not yet been processed and this screen allows you to preview the documents before they are sent. Before we preview the documents click the Save script button and the dialog shown in figure 13 will be displayed.
Figure 13: Save the mailshot script
Enter a name for the script and click the OK button to save the script.
Double-click a row in the table in figure 12 and the dialog shown in figure 14 will be displayed.
Figure 14: Mailshot details dialog
The To address contains the email address to which the email will be sent. Now click the Show Document button and a preview of the document will be displayed as shown in figure 15.
Figure 15: Preview the generated document
Now click the Show Email button in figure 14 and the email preview will display with the plain text version of the email as shown in figure 16.
Figure 16: Email preview dialog
Now click the Show events button in figure 14 and the dialog shown in figure 17 will be displayed.
Figure 17: Preview the generated events
You can see that the SQL statement which inserts into the mailshot_history table is generated with the correct value for the customer_id field.
Next click the Save Documents button in figure 12 and select an empty directory to save the documents into. The documents will be saved with the name format we specified in figure 10. Figure 18 shows the saved documents in windows explorer.
Figure 18: The saved documents
Now we'll go ahead and process the mailshot by clicking the Next button in figure 12. The list in the mailshot dialog will update as each email is sent as shown in figure 19.
Figure 19: Sending the mailshot
The emails are sent and will appear in the recipients inbox as shown in figure 20.
Figure 20: The delivered email
That completes the mailshot so now close the mailshot dialog and reopen it. In the screen shown in figure 5 click the Open Script button and select the district_script from the list presented. Now go through the mailshot again by clicking Next on each screen and you will see the values in the screen populate automatically.
