Armonk Players Contacts Manual – Page 2
Friends of the North Castle Public Library, Inc.Armonk Players Contacts Manual
Ronald Aaronson
2/6/2011 (updated 5/19/2012)
Table of Contents
Introduction 3
Running the Application 3
Basic Operations on Table data 6
Sorting on a Column 6
Add a New Row to a Table 8
Edit an Existing Row 12
Delete an Existing Row 13
How to “Filter” Data to Look at a Subset of Rows 14
“Super” Filters 18
Logging Off 19
Refreshing the Contacts Table 19
Exporting Data 19
Preparing for and Sending E-mails 21
Introduction
The Friends Web Site Documentation details the Armonk Players mailing list database and its understanding is a prerequisite to using this application.
Running the Application
All that is required to use the application is a compliant browser. The application has been tested on Internet Explorer and Firefox. It is purported to work on Safari (at least on some versions). To be able to perform the Print/Export function, Internet Explorer is required.
Going to https://www.friendsncpl.org/armonkplayers/contacts/cgi/login.php will bring you to the login page:
Enter the password in the obvious place and either hit the enter key or click on the Login button. You will see:
Initially, just the CONTACT and EMAIL_MERGE tables are shown (with caption Contacts and Email Setup respectively). Initially the contact data is displayed in ascending order by Last Name (note the little triangle to the left of the header caption for the Last Name column – this indicates that the rows are being displayed in ascending order by Last Name.
There are columns that are currently out of view that may be seen by using the horizontal scroll bar:
To see all of the skills for a contact, click on any member. Here I have scrolled down to the row for Ron Aaronson and clicked on it:
The SKILL table rows pertaining to Ron Aaronson are now displayed.
Basic Operations on Table data
We have already discussed scrolling and clicking a Contact row to display all of the skills for that contact. Now we will consider other operations.
Sorting on a Column
You may sort the rows in either table on a particular column simply by clicking on the appropriate column header. To sort the Contact rows in ascending order by the Date Modified column, click on the Date Modified caption (you will have to scroll the Contacts table to the right to see this column):
The triangle next to the Date Modified caption shows you that it is this column that is being used to sort the table and in ascending order. Note that whenever the Contacts table is re-displayed due to vertical scrolling, column sorting, etc., the associated Skills table is made once again invisible as any contact that had been selected is now deselected. Click on the Date Modified caption one more time and the rows will now be sorted by Date Modified in descending order (not the inverted triangle):
Clicking on this caption will toggle between sorting ascending and descending.
Add a New Row to a Table
You can add a new row to either table by right-mouse clicking anywhere in the table other than the caption row. You will be presented with a popup menu. Here I have right-mouse clicked in the Skills table:
Left-mouse click on the menu entry “Add new skill” and you will be presented with:
The Contact ID text box (into which you cannot enter any data – it is automatically filled in for you) is one of those bookkeeping columns you do not need be concerned with. Select one of the skills from the drop –down selection box. Make sure you choose a skill that is not currently assigned to the selected contact. Then click on the Save skill button:
You can see that the new skill has been added. You can, of course, add a new contact by right-mouse clicking on a row in the Contacts table and then clicking on the Add new contact menu entry:
Fill in the appropriate fields and click on the Save contact button. You should normally not set the Last E-mail File Sent and Last E-mail Date Sent fields, as these are normally filled in programmatically when you actually send e-mails.
Edit an Existing Row
To edit an existing row in either table, right mouse click in the table on the row you wish to modify. Here we will edit the Ron Aaronson entry by right-mouse clicking on the appropriate contact row and selecting Edit this contact. You will be presented with:
Change whatever you wish and click on the Save contact button.
If you are modifying data in the Contacts table, the current contact will be de-selected, i.e. history data that may have been displayed will no longer be visible without first clicking on a contact row again.
Delete an Existing Row
To delete an existing row in either table, right mouse click in the table on the row you wish to delete. You will be presented with the same pop up menu as before, only this time select the Delete this contact menu item (if you are working with the Contacts table). You will be presented with:
Click OK to proceed with the deletion.
How to “Filter” Data to Look at a Subset of Rows
Let’s assume that we want to look at only those contacts with mailing addresses in Armonk. There are several ways to do this. Method 1 is to find a row in the Contacts table whose City column contains the value you are interested in. Then in that “cell” (which contains Armonk), right mouse click to get the popup menu and left click on the “Filter by: City” menu item. You will be presented with a submenu form which you will select “Include only this value” by left-mouse button clicking:
The Member table will now only display those contacts with Armonk mailing addresses:
Note the “funnel” icon on the City column heading that reminds you that the table is being filtered on this column.
The second method of filtering Armonkians does not require you to first find a member row with the desired value. Instead, right-mouse click in the City column on any row that contains a contact. This time select the “Contains keyword…” submenu item. You will be presented with the following popup:
In this example you simply enter Armonk and click on OK. In general, you can enter any string of characters that the column must contain for selection. A * character may be used as a “wildcard” – it matches any string of characters. For example, if I wanted to select members whose Last Name contained either Alan or Allen, I might enter as the filter Al*n.
By the way, filters are NOT case-sensitive.
You can, of course, apply filters to multiple columns.
To remove a filter, right-mouse click somewhere in the column that contains a filter (but not on a column heading) and select either “Remove filter” to remove the filter for just this column or “Remove all filters” to remove filters for all of the columns that have them.
“Super” Filters
Currently three additional easy-to-use filters have been provided to make the most common filtering operations convenient:
These filters are additional to any column filters that may have been previously described (the “Remove all filters” popup menu item do not affect these filters). To apply a string-matching filter to the Last Name column, simply type the filter into the text box labeled Last Name Filter. The * character may be used as a wildcard here too. When this textbox is empty, no name filtering is performed (unless you have applied “regular” name filtering as previously described, but why would you?). There are also super filters for the Type and E-mail columns.
Logging Off
Click on the Logoff button at the top of the page to logoff when you are all through
Refreshing the Contacts Table
Click on the Refresh button at the top of the page to force the Contacts table to be reloaded. This is useful when you believe the table is being modified by another agent (for example, when e-mails are being sent, the Last E-mail File Sent column is updated programmatically.
Exporting Data
When you right-click on a contact row, the popup menu will have Print/Export submenu. This feature works only with Internet Explorer and will not be described here (though you could probably figure it out). Instead:
There are two times you might wish to export data. To produce mailing labels, the Microsoft Word mail merge function may be used. Its input can come from various sources, including an Excel spreadsheet. Click on the Export for Labels button and a popup window will be displayed (at least in Firefox):
You should save file labels.txt to the C:\Players directory. This file will contain all the contact rows with mailing addresses sorted by zip code. File Mailing Labels.docx in the same directory can be used to print out the labels.
You might also wish to export all of the Contacts-related data. There are scripts on the server for producing MySql dumps of the data that is suitable for restoring into a relational database (such as MySql!). But if you wish to have, for example, a spreadsheet version of the data, then click on the Export All button. A popup window will be opened and you will see:
Thee three tables, CONTACT, SKILL and EMAIL_MERGE are dumped as HTML tables. If you click on the Armonk Players Skills Inventory link you will see:
The above section was produced by joining the SKILL and CONTACT table together and sorting by SKILL and contact name.
Preparing for and Sending E-mails
All e-mails must be sent from our web server. Normally the EMAIL_MERGE database table (displayed in this application as the E-Mail Setup table) is initialized appropriately and the emailMerge program is run at the host via an SSH shell. The document to be e-mailed, typically an HTML file, and possibly an attachment and/or embedded images, is moved to the web server so that emailMerge can find it. The locations of the e-mail and attachment documents are stored in the EMAIL_MERGE table. Here is a description of all of the columns in this table:
SUBJECT: Subject that will appear on e-mail.
MIME_TYPE: text/html if the file containing the e-mail is an html file, else text/plain if it is plain text.
FILE_NAME: Complete path to the e-mail file to be sent.
ATTACHMENT: Optional (may be empty) complete path to file that will be attached to the e-mail (e.g. a ".doc" file). If empty, then no attachment will be sent. Generally not used.
SELECT_CLAUSE: condition that entries must satisfy to have e-mail sent. For example, to send to all entries in the Contact table, leave this empty. To send only to "thespians" specify TYPE = 1. Only those entries that have email addresses (column EMAIL in table CONTACT) will be e-mailed.
FROM_NAME: The name of the "sender" (should normally be The Armonk Players).
FROM_EMAIL: e-mail address of the "sender" (should be ).
TO_NAME: The name of the primary receiver (should normally be Armonk Players Subscriber). This value is important when COPY_COUNT (see below) is not 1.
TO_EMAIL: e-mail address of primary receiver (should probably use same value as column FROM). If the COPY_COUNT parameter (see below) is not 1, the value of TO_EMAIL is the actual recipient and the contacts themselves are e-mailed using "blind copy".
COPY_COUNT: The number of e-mail addresses blind copied on each e-mail sent. If this value is 1, then the TO_EMAIL value specified is ignored and one e-mail per recipient will be created. This value is normally 1.
PAUSE_SECONDS: The number of seconds to pause between sending subsequent e-mails. The COPY_COUNT value and this value should be chosen so as not to exceed the 500 e-mails per hour limit. When COPY_COUNT is not 1, the actual number of recipients for each e-mail will be COPY_COUNT + 1 since the value specified for TO_EMAIL counts as an additional recipient. Reasonable combinations are COPY_COUNT = 1 and PAUSE_SECONDS = 8 resulting in 450 recipients per hour and COPY_COUNT = 7 (for a total of 8 recipients per e-mail) and PAUSE_SECONDS = 60 resulting in 480 recipients per hour of which 420 are actual subscribers. If you are sending only to "thespians" (SELECT_CLAUSE specifies TYPE = 1) and there are fewer than 500 thespians), then you can safely set PAUSE_SECONDS to 0.
EMAIL_LIMIT: If greater than 0, then the maximum number of subscribers to send to for the current invocation of the mailing list program. This is convenient if you want to process the list in multiple runs of the mailing list program.
SMTP_SERVER: A list of parameters, enclosed in parentheses, for accessing the SMTP server to be used. The first parameter is TCP/IP address of the server. This is optionally followed by keyword/value pairs, described below. SMTP_SERVER should be specified as a perl language list expression that can be evaluated. For example:
('my.smtp.server')
The optional keywords recognized are:
· Port: The port address the server is listening on i f not the standard default of 25.
· AuthUser: If the server requires authentication, the user id required to authenticate.
· AuthPass: The password associated with the authenticated user.
· SSL: Non-blank or not 0 if the server is using an SSL socket.
For example:
('my.authnticated.smtp.server', SSL => 1, Port => 465, AuthUser => 'MyUserId', AuthPass => 'MyPass'
On a Unix/Linux host, this may be left empty in which case the system sendmail command will be used for sending e-mails. But it may be just as efficient, if not more so, to use:
('localhost')
The application uses the SELECT_CLAUSE column to narrow the initial set of recipients (i.e. CONTACT table rows) for the e-mail. Then it looks at the LAST_EMAIL_FILE_SENT column for each candidate CONTACT and compares its value with the value of the FILE_NAME column of the EMAIL_MERGE table. If they are the same it means that this contact has already been sent the e-mail and the e-mail will not be resent to this contact again. Otherwise, the e-mail is sent to this contact and the CONTACT LAST_EMAIL_FILE_SENT column is updated with the FILE_NAME just sent. In this way the e-mail program can be run multiple times without duplicate e-mails being sent to the same contact. Of course, there are times when you want to force duplicate e-mails to the same user (e.g. doing an e-mail blast for a play every couple of weeks to all contacts). Then you can click on the "Clear Last E-mail Sent" button and this will clear the LAST_EMAIL_FILE_SENT column for every contact. You can also, of course, edit an individual CONTACT entry and clear the LAST_EMAIL_FILE_SENT column just for one user. To "test" a new e-mail it is useful to set the SELECT_CLAUSE column to something like