Exercise 3: Extract data from web forms PostCast can completely automate data processing which you are receiving through your web forms. Web forms form an integral part of each and every web site. Regardless of whether it concerns the guestbooks, a 'subscribe' and 'unsubscribe' request for your newsletter, a fresh order for your product or any other form of web form at your web site, PostCast will automatically include all the received data in the database. For example, imagine that you have set up a form at your web site which has order processing for your product as its primary aim. Assume that every time someone orders your product or services, you receive an e-mail that looks like this: Example 1 Registration fee: US$ 79.95 Number of licenses: 1 Shipping & handling: US$ 5.00 Total: US$ 84.95 Last Name: Johnson First Name: Rick Company: Advanced Communications Street: 10235 Panorama Ave City: Portland, OR 97233 Country: USA Phone: 444-317-4937 Fax: 444-317-4939 This is the most common way to forward a web form! A guest to your web site fills the form using his/her data and clicks the 'Submit' button. The CGI script residing with the server simply inputs the received data to the e-mail message, and then sends you the message. Some CGI scripts running the web forms have the option of entering data in the text file. Such files are usually called Flat File Databases. If you are using such a CGI script, then you can import your collected data in your tables quite easily. Take a look at Exercise 2 in order to learn how to import data from the text files. PostCast will process the message, input the data to your database as well as retain a copy of the message in one of the e-mail folders if required. Here is how you can set up the program: 1. Click the 'Import' button from the toolbar. All settings related to data import from e-mail messages into your database can be saved as Import Job. Thanks to this option, data can be extracted from many different model messages respectively. You should create new Import Job. Just click the 'Add' button. Unselect the 'From', 'To', 'Subject', 'Date', 'X-Mailer' check boxes. Using this option will enable you to import the default E-mail Headers from the e-mail message, but since we are relying on the database from Exercise 1, we have no defined columns wherein to enter this data: 2. Click 'Add' button. An input box saying 'Type new value' will appear. Now we shall input the parts of e-mail messages from Example 1 that do not need to be processed by the program. Input them in full. 'Registration fee: US$ ' and click 'OK'. Do not enter the (') characters. They are used here only to distinguish the string. Make sure to input the identical text with a single space character (space) at the end. Here is the basic idea behind this: the program must know which characters and which part of the line it is supposed to leave out when processing e-mail messages. When you click 'Add' and enter the value 'Registration fee: US$ ' the program will use the line: 'Registration fee: US$ 79.95' ..to extract 'Registration fee: US$ ' from it and consider only the price of: 79.95. Note: In this case the string 'US$ ' before 79.95 should be extracted. The column which you will import 79.95 is the Currency type. 'US$ 79.95' is an incorrect Currency data in relation to Access database, which is why we have to extract the number only. Always pay attention to the column type, as in certain situations you would not be able to input the data properly. 3. Define the other space in your e-mail message as well (again, do not enter the ' characters): 'Number of licenses: ' 'Shipping & handling: US$ ' 'Total: US$ ' 'Last Name: ' 'First Name: ' 'Company: ' 'Street: ' 'City: ' 'Country: ' 'Phone: ' 'Fax: ' Your window should look like this: 4. Click the 'Compare' tab. In order to set up the program successfully you should position identically the named columns on the left within the same plane with those on the right. For example, you would click Last Name column on the left and, using the up and down arrows which can be found at the bottom of the screen, move the inscription until it coincides with the Last Name column on the right. You should do likewise with other columns. Now, your window should look like this: Click the 'Filters' tab. 5. This screen enables you to define which messages to import. If you do not define a single keyword, the program will thus process all the messages being received. On principle, such an approach should not pose a problem; if the program fails to find the defined spaces within the e-mail message, it will import nothing to the table. However, let us assume that you would like to process messages sent to you by the CGI script, having subjects such as 'Order #19234'. Click the 'Add' button and type Order # in the input box. Exclude the order number (19234) as it will be constantly vary, thus unfulfilling your criterium. Click the 'Advanced' tab. 6. Leave all options inactive except for 'Automatically import data from the messages to the database'. It must be active in order to make the program aware that it should import the data during the mail receipt. Click 'OK' to close this window and to add your new Import Job to the list. 7. Click the 'Settings' button from the toolbar. We should now set up your data: the POP3 server name and your mailbox data. Click the 'E-mail' tab to reach the data input section. This time I shall not explain how to fill in the text box, as such settings are a standard procedure (read Add a mail account topic for more information). 8. Save all the input data by clicking the 'Save' button from the toolbar. 9. Now we shall test this tool and input the data to your table: - create a message using your favorite e-mail program. Paste the entire text from Example 1 (see above) in the body of your message, or utilize the text to be found in the 'Exercise 3.txt' file located in the PostCast directory. Name the subject: Order #2935. - send the message to the specific address i.e. to the mailbox predefined in the 'Settings' program section. - wait a minute for the message to reach its destination, and then click the 'Start' button from the toolbar. Once the program receives and processes the incoming mail, click the 'Database' button from the toolbar. You will thus open your 'Customers' table. Click the 'Refresh' button to display all the records and mind the new row in the table. If you have followed these steps, you have just recorded a new order and customer data in your table - COMPLETELY AUTOMATICALLY! Now you already have three records in the table. In the section which follows we shall see how you can contact your customers using e-mail, and in such a way as to enable each one of them to receive a completely personal message i.e. as if you had written them personally. Exercise 4: Send personalized e-mail