ColdFusion in Context: Meeting Schedule

Suppose you have weekly meetings to which multiple people contribute in a structured manner. You could pass around a spreadsheet in E-mail. However, not everyone has the same version of the same spreadsheet program, and by the time you route a document through a dozen or more people, it's time for the next meeting. Here's a better way.

Meeting Structure

Toastmasters International has thousands of clubs worldwide. Club meetings have a predictable set of roles. Some of those roles are the meeting organizer known as the Toastmaster of the Day (TMOD or TM of Day for short), impromptu speaking organizer known as the Topics Master, and prepared speakers. Speakers deliver topics of their own choosing to meet objectives within a time limit (often 5-7 minutes) in various project manuals. To know how much time is available for speaking without any advance notice (an exercise known as Table Topics), we need to know how long the speeches are expected to take. Members need to be able to indicate when they will be away; so, they won't be scheduled to participate. Special events sometimes preempt or dictate speeches. Notes on future events sometimes encourage participation beyond the club level and provide ideas for speeches. This is the background for the example that follows.

Make a Table

Make a table named Meeting. Give it a MeetingID field and tell the database engine to automatically increment the field. Give it a MeetingDt field and give it a date type. Give it the following text fields; suggested lengths are shown in parentheses: Event (30), TMOD (15), TopicsMaster (15). Also Speaker1 (15), Length1 (8), Manual1 (30), Nr1 (5), Title (35). Repeat this group, adding Speaker2, Length2, Manual2, Nr2, and Title2. Because this is a lunchtime club, two speeches is about all you normally have time for. For an evening club, you would make provision for a third speech; a weekend club might provide even more opportunities for prepared speeches. Finally, add two more fields: Away (150) and Notes (150). (Don't call it Note; some engines are picky about this.)

Then use your database engine GUI to add six months or so. It's not a big deal. You only need to fill in the date. (You'll probably code a short page to do this after you've used this idea for a while.)

Overview

The user can see and update the entire schedule at once. If the form has been submitted, the page's first task is to loop through a list of record IDs provided in a single string in the form. For each ID, it will update the associated record. For example, it will set Speaker1 to the contents of a field named Speaker1_{ID number}. The thing that makes this possible is that for each ID, the code that builds the form adds the ID to a list of IDs and automatically creates all the fields needed to update the record identified by the ID.

Update

All code goes in meeting.cfm. Begin by checking to see if the user wants to exit the form. If so, change the location to a page of your choosing. If the user wants to save the form, begin to loop through a list of record IDs provided by the form. When creating field names on the fly, it's awkward to use them in the same tag that creates them. Therefore, for simplicity, create a structure whose keys are the column names for this table, assign the new field names as their values, and then use the evaluate function to get the real values. For example, set chg.Speaker1 to form.Speaker1_#ID#, then set chg.Speaker1 to evaluate(chg.Speaker1). Although you could use cfset for these assignments, it takes less typing for the programmer to use a cfscript tag.

It's tempting to delay the evaluate step until you're inside the query, but if you do this, the evaluate function will produce a raw string that ColdFusion will assign the string to the database "as is". We rely on ColdFusion to automatically escape single quotes when assigning variables to a database. So, do yourself a favor and create the variable first as shown here; so, ColdFusion can do the right thing during the query. (If you don't, the meeting schedule will break sometime around Secretaries' Day.) It's mechanical and therefore easy; just do it. 

<cfparam name="form.Exit" default="">
<cfif len(trim(form.Exit))>
  <cflocation url="menu.cfm">
  <cfabort>
</cfif>

<cfparam name="form.Save" default="">
<cfif len(trim(form.Save))>
  <cfset chg=structNew()>
  <cfloop list="#form.IDList#" index="ID">
  <cfscript>
  chg.TMOD="form.TMOD_#ID#";
  chg.TMOD=evaluate(chg.TMOD);
  chg.Event="form.Event_#ID#";
  chg.Event=evaluate(chg.Event);
  chg.TopicsMaster="form.TopicsMaster_#ID#";
  chg.TopicsMaster=evaluate(chg.TopicsMaster);
  chg.Speaker1="form.Speaker1_#ID#";
  chg.Speaker1=evaluate(chg.Speaker1);
  chg.Length1="form.Length1_#ID#";
  chg.Length1=evaluate(chg.Length1);
  chg.Manual1="form.Manual1_#ID#";
  chg.Manual1=evaluate(chg.Manual1);
  chg.Nr1="form.Nr1_#ID#";
  chg.Nr1=evaluate(chg.Nr1);
  chg.Subject1="form.Subject1_#ID#";
  chg.Subject1=evaluate(chg.Subject1);
  chg.Speaker2="form.Speaker2_#ID#";
  chg.Speaker2=evaluate(chg.Speaker2);
  chg.Length2="form.Length2_#ID#";
  chg.Length2=evaluate(chg.Length2);
  chg.Manual2="form.Manual2_#ID#";
  chg.Manual2=evaluate(chg.Manual2);
  chg.Nr2="form.Nr2_#ID#";
  chg.Nr2=evaluate(chg.Nr2);
  chg.Subject2="form.Subject2_#ID#";
  chg.Subject2=evaluate(chg.Subject2);
  chg.Away="form.Away_#ID#";
  chg.Away=evaluate(chg.Away);
  chg.Notes="form.Notes_#ID#";
  chg.Notes=evaluate(chg.Notes);
  </cfscript>

When you have defined every column of the row to be updated, run the update query. Because the structure contains values, the update is easy. For example, set Speaker1 in the table to '#chg.Speaker1#'. When you have done this for all columns, clear the structure so it can be used again, end the loop, and end the cfif statement. The where statement deserves special mention. It's good practice to assign the value of numeric variables to the database so that specially hacked ID strings don't do more than you bargained for. 

  <cfquery name="updMeeting" datasource="ic">
  update Meeting set
  TMOD = '#chg.TMOD#',
  Event = '#chg.Event#',
  TopicsMaster = '#chg.TopicsMaster#',
  Speaker1 = '#chg.Speaker1#',
  Length1 = '#chg.Length1#',
  Manual1 = '#chg.Manual1#',
  Nr1 = '#chg.Nr1#',
  Subject1 = '#chg.Subject1#',
  Speaker2 = '#chg.Speaker2#',
  Length2 = '#chg.Length2#',
  Manual2 = '#chg.Manual2#',
  Subject2 = '#chg.Subject2#',
  Nr2 = '#chg.Nr2#',
  Away = '#chg.Away#',
  Notes = '#chg.Notes#'
  where MeetingID = #val(ID)#
  </cfquery>
  <cfset dummy=structClear(chg)>
  </cfloop>
</cfif>

Show and Get Entries

To fill the meeting schedule, query the table and create a form from the query. In the query, order by the meeting date (MeetingDt). Define the form action as this same page; set the method to post. Open a table, give it a border, reduce cell padding, and tell the table to use just 2/3 of the window width. Why? Because users will probably want to print the result (once anyway), and therefore it has to fit the printer. Empty an ID list (IDList). Begin to output the query. 

<cfquery name="dayList" datasource="ic">
select * from Meeting
order by MeetingDt
</cfquery>

<form name="Meetings" action="meeting.cfm" method="post">
<table border="1" cellpadding="0" width="65%">
<cfset IDList="">
<cfoutput query="dayList">

Append the current MeetingID to IDList. Set a data cell to span the table. If this is the first week in the month (date less than 8), display the date followed by submit buttons: one to save the entire form and one to exit the form. If it is not the first week in the month, display just the date. Precede the the date with slashes slanting up and follow it with slashes slanting down to suggest a peak or a folder tab to get across the idea that associated data is below rather than above the date. 

<cfset IDList=listAppend(IDList,MeetingID)>

<tr><td colspan="6">
// #dateFormat(MeetingDt,"d mmm yy")# \\
<cfif datePart("d", MeetingDt) lt 8>
    <input type="submit" name="save"
  value="Save entire schedule: refresh">
  <input type="submit" name="exit"
  value="Ignore unsaved changes: exit">
</cfif>
</td></tr>

Put TMOD, Event, and TopicsMaster fields in the same cell, spanning the table. Set the size of the fields to something appropriate based on anticipated font size, the length of the typical entries you expect, the appearance of the page, and the maximum length. Set the maximum length to match the column length (or less if you're still experimenting; you can fix the database later). 

<tr><td colspan="6">
TMOD:
<input type="text" name="TMOD_#MeetingID#"
value="#TMOD#" size="13" maxlength="15">
Events:
<input type="text" name="Event_#MeetingID#"
value="#Event#" size="30" maxlength="30">
Topics Master:
<input type="text" name="TopicsMaster_#MeetingID#"
value="#TopicsMaster#" size="13" maxlength="15">
</td></tr>

Set a header and provide the speech information in tabular form. In the header, use two pound signs, not one, as the abbreviation for number. ColdFusion will convert this to a single pound sign for display. Again, set sizes for appearance (within the column length), and set maxlength to the length of the column. 

<tr><td>..</td><td>SPEAKER</td><td>TIME</td>
<td>MANUAL</td><td>##</td><td>SUBJECT</td></tr>

<tr><td>1st:</td>
<td><input type="text" name="Speaker1_#MeetingID#"
value="#Speaker1#" size="13" maxlength="15"></td>
<td><input type="text" name="Length1_#MeetingID#"
value="#Length1#" size="4" maxlength="5"></td>
<td><input type="text" name="Manual1_#MeetingID#"
value="#Manual1#" size="26" maxlength="30"></td>
<td><input type="text" name="Nr1_#MeetingID#"
value="#Nr1#" size="1" maxlength="1"></td>
<td><input type="text" name="Subject1_#MeetingID#"
value="#Subject1#" size="37" maxlength="35"></td></tr>

<tr><td>2nd:</td>
<td><input type="text" name="Speaker2_#MeetingID#"
value="#Speaker2#" size="13" maxlength="15"></td>
<td><input type="text" name="Length2_#MeetingID#"
value="#Length2#" size="4" maxlength="5"></td>
<td><input type="text" name="Manual2_#MeetingID#"
value="#Manual2#" size="26" maxlength="30"></td>
<td><input type="text" name="Nr2_#MeetingID#"
value="#Nr2#" size="1" maxlength="1"></td><td><input type="text" name="Subject2_#MeetingID#"
value="#Subject2#" size="37" maxlength="35"></td></tr>

The Away field and Note textarea each get their own row, with the data spanning most of the table. "Note" in its singlar form tells the user to do something. Hence, it is used in the singular form as a label. However, the underlying column name is plural to avoid database engine conflicts. (The label and column should usually have the same basic name; this is a rare exception to this principle.) Set the size and maxlength as appropriate. 

<tr><td>Away:</td>
<td colspan="5"><input type="text" 
name="Away_#MeetingID#" value="#Away#" size="98" maxlength="110"></td></tr>

<tr><td>Note:</td>
<td colspan="5"><textarea name="Notes_#MeetingID#"
cols="75" rows="2" wrap="virtual">#Notes#</textarea>
</td></tr>

Wind down. Close the output. Close the table. Set a hidden field equal to the IDList that you've building while looping through the query for display. (Remember to surround the variable with cfoutput tags now that you're no longer within a pair of cfoutput tags.) Close the form. 

</cfoutput>
</table>
<input type="hidden" name="IDList" value=<cfoutput>"#IDList#"</cfoutput>>
</form>

Review

Browse meeting.cfm and start planning your meetings. It's fast. It works. It's intuitive. Enjoy. =Marty=