ColdFusion In Context: Active Database Display

Suppose you want to begin to build a workflow application. To do this, you need to keep a list of open tasks in front of your users. However, if you simply display the result of a database query, the display is frozen in time and won't refresh as new tasks come in. If you've used E-mail that requires you to push to send/receive button to see if you have messages, you begin to see the problem with displaying a static task list. Refreshing the page every few seconds would work, but it would be distracting to have the entire screen redraw when there aren't any changes for no reason and would waste bandwidth. "Client push" techniques are usually blocked at the firewall. Yet, there must be a way. The ability to do this is central to business workflow.

Here's a technique you can use to create an active database display that changes as the database is changed. Along the way, you'll work with frames, javascript, and ColdFusion.

Overview

There are four pages in this broadcast message example: framer.cfm, drive.cfm, show.cfm, and tasker.cfm. framer.cfm nearly fills the window with show.cfm; it relegates drive.cfm to the bottom edge. show.cfm presents a list of tasks which the user can click on to signal completion. drive.cfm refreshes every few seconds to compare a message ID in application memory with its counterpart in session memory. When the two don't match, drive.cfm makes them match and then uses javascript to refresh the task list: show.cfm. tasker.cfm lets you add tasks for this demonstration. When you add a task, tasker.cfm increments the message ID in application memory. This causes drive.cfm to update show.cfm, which is the display.

Build the Task Table Structure

To feed your active database display, you'll need a table. Make a table named "Task" and give it the following fields: TaskID (numeric), OPR (Office of Primary Responsibility; text), Task (text), and Done (text in this example which will be '1' when done). Define Done in such a way that it's allowed to be empty for this example. Don't worry about adding data. You'll use tasker.cfm to do this later on.

Build the Frameset

The frameset is simple. This one specifies that its frames will appear in rows. The last row will be ten pixels high - you could use a percentage instead - and the first row will take up the remaining space in the window. The last row can't be scrolled or resized; it belongs to drive.cfm. The first row can be scrolled and resized; it belongs to show.cfm. The names are arbitrary except that you must use the same name to refer to these frames later on. Because the frameset must be in an unusual position, BETWEEN the head and body, the other HTML elements often omitted from ColdFusion pages are fully defined for this example to make the position of the frameset clear. 

<html><head>
<title>My App</title>
<!--- Place frameset BETWEEN head and body --->
</head>
<frameset rows="*,10">
<frame name="display" scrolling="auto" src="show.cfm">
<frame name="pulse" scrolling="no" noresize src="drive.cfm">
</frameset>
<body>
<noframes>
Please use a browser that understands frames.
</noframes>
</body>
</html>

Build the Driver Page

The driver page, drive.cfm, is the heart of this technique. It sets defaults, updates show.cfm if conditions are right, and changes background colors as it refreshes itself so the user can be assured that it's still working.

It begins by enabling session management and setting defaults for the message ID if they don't exist. Then it checks a URL variable and changes the local state of that variable so that later code will cause the variable state (and the background color) to alternate with each refresh. To perform the refresh, the page must know where it is, and this varies with your installation.

Before writing anything to the screen, the code uses a meta tag that will cause the page to refresh a given number of seconds after it finishes loading. Because the local version of the URL variable is part of this tag, the value of the variable in the URL will alternate as mentioned previously.

Before the refresh takes place, the code checks the value of the message ID in application and session memory. If the value in session memory is less than the value in application memory or is zero, the code makes the value in session memory equal the value in application memory, sets a background color, and updates show.cfm. The word "display" in the javascript isn't special; it is the name given to show.cfm by the frameset. It could be "fred", as long as it matched the name of the frame in the frameset.

If show.cfm doesn't need to be updated, drive.cfm alternates between two background colors based on the value of the URL variable mentioned previously. 

<cfapplication name="Task" sessionmanagement="yes">
<cfparam name="session.msgID" default=0>
<cfparam name="application.msgID" default=0>

<cfif isDefined("url.p") and ("#url.p#" gt 0)>
  <cfset pulse=-1>
<cfelse>
  <cfset pulse=1>
</cfif>
<!--- Set this path for your environment --->
<cfset self="http://127.0.0.1/context/activedb/drive.cfm?p=#pulse#">

<cfoutput>

<meta http-equiv="refresh" content="5 URL=#self#">

<cfif (application.msgID gt session.msgID) or (session.msgID is 0)>
  #session.msgID# #application.msgID#
  <cfset session.msgID=application.msgID>
  <body bgcolor="999999" onload="javascript:parent.display.location.href='show.cfm'">
<cfelse>
  <cfif pulse gt 0>
    <body bgcolor="99AAFF">
  <cfelse>
    <body bgcolor="AAFF99">
  </cfif>
</cfif>
</cfoutput>
<br>
</body>

Build the Display Page

The display page, show.cfm, is a simple form which posts the database as tasks are completed and lists each active task. If a taskID has been submitted by the form, then the postTask query sets the value of "Done" for that task to 1. The listTasks query shows only active tasks and lists them in descending order so the most recently assigned tasks are at the top. Because of the placement of the cfoutput tag (which outputs a row for every row in the query), this form has as many submit buttons as the query has rows. If there are no rows, the page says no tasks are due. Otherwise, it says what to do. When the user clicks on a taskID, the task is posted as "Done" and vanishes immediately from the display. When new tasks are inserted by tasker.cfm, drive.cfm refreshes this page automatically to update the display. 

<cfif isDefined("form.TaskID")>
  <cfquery name="postTask" datasource="myDB">
  update Task set
  Done = '1'
  where TaskID = #form.TaskID#
  </cfquery>
</cfif>

<cfquery name="listTasks" datasource="myDB">
select * from Task
where Done <> '1'
order by TaskID desc
</cfquery>

<cfif not listTasks.RecordCount>
  No tasks currently due.
<cfelse>
  Click button when task is done.
</cfif>
<form name="doTask" action="Show.cfm" method="post">
<cfoutput query="listTasks">
<input type="submit" name="TaskID" value="#TaskID#">
#OPR# :
#Task#
<br>
</cfoutput>
</form>

Build the Tasker

The tasker, tasker.cfm, changes the message ID (in memory) and the database together so drive.cfm will know about the database change. The tasker begins by enabling session management so it can increment the message ID in application memory when a record is added. It sets the message ID to zero if it doesn't exist. If a task is defined by the form, the query getLast gets the last TaskID, adds one to it, and the query add Task uses the incremented TaskID when it inserts the new task. Because the cftransaction tag surrounds this pair of queries, if either one fails, both will be rolled back. (This could be made more robust but works well enough for this example.) Submitting the form causes form.task to exist, causing the code above to increments the message ID and add the new task to the database. 

<cfapplication name="Task" sessionmanagement="yes">
<cfparam name="application.msgID" default=0>

<cfif isDefined("form.task")>
  <cfset application.msgID=#application.msgID#+1>
  <cftransaction>
  <cfquery name="getLast" datasource="myDB">
  select max(TaskID) as MaxID
  from Task
  </cfquery>
  <cfif len(getLast.MaxID)>
    <cfset MaxID=#getLast.MaxID#+1>
  <cfelse>
    <cfset MaxID=1>
  </cfif>
  <cfquery name="addTask" datasource="myDB">
  insert into Task (TaskID, OPR, Task, Done)
  values (#MaxID#, '#form.OPR#', '#form.Task#', '0')
  </cfquery>
  </cftransaction>
</cfif>

<form name="adder" action="tasker.cfm" method="post">
OPR: <input type="text" name="OPR" value="" size="52" maxlength="50"><br>
Task: <input type="text" name="Task" value="" size="52" maxlength="50">
<input type="submit" name="OK" value="Task">
</form>

Put it Together

Copy the four files as shown, modifying the datasource and URL to match your platform. Open a browser to framer.cfm. This will automatically open show.cfm and driver.cfm in a frameset. Notice that the color at the bottom of the window (driver.cfm) changes every few seconds. Then open tasker.cfm and add tasks. When you add a new task, the main portion of the frameset (show.cfm) will refresh after a few seconds, and the color at the bottom of the window will continue to alternate once more. If there are problems, double-check your filenames; they are the most likely point of failure.

Extension

Now that you know this technique works, extend the idea. Consider using structured variables for the message ID so that there is a different place to put the current message ID for each ROLE (worker, manager, advisor...) (Add a role element to the table, too.) In this manner, the task list for each ROLE can be displayed and updated as appropriate. Add date fields and automatically post when the task was assigned, who completed the task, and when it was completed.

Too easy? Try displaying links to dynamically generated pages instead of showing the rows themselves. Restructure the database to add decision flow. Then share your work. =Marty=