ColdFusion In Context: Event Status
Ever have to complete tasks that have due dates? Ever wonder how to quickly determine which ones better receive that last push soon?
This example assigns color-coded status to tasks based on the percentage of time remaining relative to the overall time originally assigned for the task. Along the way, you'll see do some thinking about date comparison in ColdFusion.
Manually Populate a Table
Creating forms to update a database table is outside the scope of this example. Populate a table called status with sample data like this, but change the dates so some are past and some are future. The top row consists of column names: Task, AssignedDt, DueDt, and DoneDt. Don't create a status column; you'll generate that dynamically in a few minutes.
Task AssignedDt DueDt DoneDt
Project A 03/20/01 05/20/01 04/23/01
Project B 03/20/01 05/10/01
Project C 03/20/01 04/15/01 04/23/01
Project D 01/01/01 05/10/01
Project E 03/30/01 05/30/01
Project F 03/15/01 04/15/01
Write a Query and a Table Header
This project uses only one page: showstat.cfm. It starts with a simple query and header. The "!!" column is where the status goes. You can name this something else that's short and appropriate.
<cfquery name="getStatus" datasource="MyDB">
select *
from status
</cfquery>
<table border="2" align="center">
<tr><td>Task</td>
<td>Assigned</td>
<td>Due</td>
<td>Done</td>
<td>!!</td>
</tr>
Generate a Row for Each Record
By placing this row inside a query-driven output tag, the row tags will repeat for each record in the table. Since we hope you aren't timing your deadlines down to the minute, the date/time fields from the database are formatted as simple dates.
<cfoutput query="getStatus">
<tr><td>#Task#</td>
<cfif '#AssignedDt#' is ''>
<td> </td>
<cfelse>
<td align="center">#DateFormat('#AssignedDt#', 'mm/dd/yy')#</td>
</cfif>
<cfif '#DueDt#' is ''>
<td> </td>
<cfelse>
<td align="center">#DateFormat('#DueDt#', 'mm/dd/yy')#</td>
</cfif>
<cfif '#DoneDt#' is ''>
<td> </td>
<cfelse>
<td align="center">#DateFormat('#DoneDt#', 'mm/dd/yy')#</td>
</cfif>
Compare Time Left to Time Assigned
It's not enough to say that the deadline is five days away. If you were given seven days to complete the task, five days left is a lot. If you were given a year to write a report, you'd better have mailed it last week if only five days remain. Therefore, status based on time remaining to complete a task is relative to the amount of time you were given for the task in the first place.
So you'll compare two dates. It wouldn't be a big deal, except that you want date comparisons, not date/time comparisons, and "now" has a time as well as a date. In the code below, "now" is converted to a string prior to date comparison, and only its day, month, and year are read back in. This sets the hours:minutes:seconds of the date to midnight.
If this were not done, comparisons expressed in whole days with dates from the database (assumed to start at midnight) wouldn't do what users expect; because, "now" would often be less than a full 24-hour day away from tomorrow. Status would be driven to "red" unexpectedly in the middle of the day.
<cfset daysleft=DateDiff('d', '#lsParseDateTime(dateFormat(now(), 'mm/dd/yyyy'))#', '#DueDt#')>
<cfset daysof=DateDiff('d', '#AssignedDt#', '#DueDt#')>
<cfset ratio=daysleft/daysof>
Display Status and End the Row
Now that you have a ratio with which to determine status, it's up to you to decide where your excitement factor is. To make this example last a few weeks from the time it's published, I've set it to alert the user a little sooner than I would for my own use. In this example, tasks with 30% of the original time or 7 days remaining have no status color; there's nothing to get excited about yet. Tasks with a smaller percentage of the assigned time or fewer days remaining are given a yellow status to catch your eye. Late tasks are flagged in red. A finished task, regardless of when it was finished, is green; you don't have to worry about it anymore.
Once the row's complete, close the CFOUTPUT tag (which will repeat the row for each task) and then end the table.
<cfif '#DoneDt#' is not "">
<cfset flag="G">
<td align="center" bgcolor="Green">#flag#</td>
<cfelseif ratio LE 0>
<cfset flag="R">
<td align="center" bgcolor="Red">#flag#</td>
<cfelseif ratio LE 0.3 or daysleft LE 7>
<cfset flag="Y">
<td align="center" bgcolor="Yellow">#flag#</td>
<cfelse>
<cfset flag=" ">
<td align="center">#flag#</td>
</cfif>
</tr>
</cfoutput>
</table>
Do More
Imagine extending this idea to let individuals across the globe update and view the status of tasks which are part of a larger project and to shift their focus automatically to those tasks which are close to becoming overdue. Because this approach uses status letters (Y, R, G) in addition to colors, the status is retained when users highlight the table and copy its cells directly into a spreadsheet for their specialized reporting needs. Who says good things don't come in small packages? =Marty=
[The line containing lsParseDateTime should have been quoted within the dateDiff function and was not; CFMX caught this and threw an error; the line has been fixed in this version.]