I'm a Webmaster for the Air Protection Division (APD), EPA Region 3 in Philadelphia and in April 2006, I wrote an article for CFDJ entitled "How ColdFusion MX 7 Made Me a Hero at the Office" (Volume 8, Issue 4). That article described how I harnessed the power of ColdFusion to improve access to our most vital business information.

Now, I'm back to fill you in on how I've been able to use Flash Forms and Flex to revitalize our applications and make them even more potent than before.

Since writing my previous article I've been busy reading up on and learning everything I can about Flex 2, MXML, Actionscript, and Flash Forms. I've also been hard at work incorporating these new technologies into my applications. I say "new" because although they've been around for some time, I haven't actually used these technologies in my own applications until now. First, I'll describe how I used a Flash Form to create an application that unified and enhanced our salient issues ColdFusion applications. Then I'll explain how I used ColdFusion and Flex to create a "kicked up" version of our permits Web page (apologies to Emeril). Last and most importantly, I hope to inspire you to experiment with Flash Forms and Flex for yourself, as well as give you ideas on how to breathe new life into your applications.

In my previous article I described how ColdFusion MX 7 enabled me to create the Air Salient Issues ColdFusion Application (ASICA) and three similar applications that together provide our employees with access to over 5,000 salient issue records on our intranet. Salient issues are short (one-page) Microsoft Word documents that describe our most significant activities, accomplishments, issues, and events. Typically, a total of 30 to 40 salient issues are written each week. Currently, there are four salient issues applications on our intranet, one for each of the Region 3 Division offices APD, EAID, WCMD and HSCD. We also have four Microsoft Access databases, one for each salient issues application. In case you're wondering why we're using Microsoft Access instead of a "real" database management system as the back-end for these applications, I have just two words for you: limited budget. The good news, however, is that we haven't encountered any serious problems using Access; in fact, so far it has exceeded our every expectation. Template execution times in the ASICA rarely exceed 250ms even with the large number of database records that we have.

Ready, Set, Flash
During the past year I developed (pun intended) a keen interest in both Flash Forms and Flex because I recognized that these technologies could, if used properly, greatly enhance the user experience while enabling developers to write less code. So, I began reading everything I could find about Flash Forms and Flex on the Web, in books, and of course, in CFDJ. That's when I saw Laura Arguello and Nahuel Foronda's "Completing the Real Estate Sample Application" article in the February 2006 issue of CFDJ (Volume 8 Issue 2). Using this article as a guide, I began building a new kind of salient issues ColdFusion application, one that would integrate and unify our salient issues databases and give users a rich, interactive Flash interface. To facilitate development of this new application and, frankly, keep the learning curve manageable, I decided to concentrate solely on the data presentation and leave the administrative (data entry) templates alone, at least for now. The first problem I faced was the large number of records and the fact that they were stored in four separate databases. I considered copying the records into a single large database but that would be too troublesome plus it might create other problems such as synchronizing data between multiple databases. I'd also read that Flash Forms were not the best solution for applications that must display thousands of records. Somehow I had to drastically reduce the number of salient issue records so that a Flash Form data presentation would be feasible while working with the four databases that I already had.

I resolved this dilemma by deciding that the new application would retrieve, process, and display only the most recent records from each of the four databases. In other words, this new application would retrieve and display only those salient issues that were published over the last month. This approach reduced the number of records the application would have to display from over 5,000 to about 150. Having reduced the record count to a reasonable number, the application was now a candidate for a Flash Form interface. And, of course, users looking for older salient issues that were published more than a month before could still access them by using one of the original salient issues applications.

The toughest challenge in writing this application was getting the most recent salient issue records out of the four databases and into a single query result set that I could then display in a Flash Form control. The first step was to query each salient issues database separately and retrieve only those salient issues that were published in the last month. The query to accomplish this is shown in Listing 1.

The query in Listing 1 is run four times, once for each salient issues database. The Now(), DateAdd, and CreateODBCDate functions are nested to create an "SQL-friendly" comparison date (i.e., it can be safely used in an SQL statement) one month in the past that can be compared to the date values in the PubDate column. The "WHERE" clause then retrieves only those records having a publication date later than (or equal to) the comparison date. In other words, the WHERE clause eliminates the vast majority of records that have publication dates more than a month old. This approach eliminates unnecessary records early on so that it reduces the processing burden for later queries. When executing multiple queries it's a good idea to run your most restrictive queries first. This minimizes the number of records that must be processed by later queries and boosts application performance. The record set generated by Listing 1 will have eight columns. The first seven columns come directly from the salient issues database, but the eighth column named "IssueType" is created "on-the-fly" by using an alias in the SQL statement; in Listing 1 the IssueType column is given a constant value of "APD." The other database queries, which are similar to Listing 1, will each have a different (but still constant) value for the IssueType column (i.e., "WCMD," "EAID," or "HSCD") depending on which database is being queried. I'll explain why the IssueType column is so important in a moment.

Come Together Right Now
The next step was to figure out how to combine all four database queries into a single query result set that I could then display in a Flash Form control. This is where ColdFusion's query of queries (QoQ) feature was invaluable. For those unfamiliar with it, QoQ lets developers "re-query" queries that have already been run. You can even use QoQ to combine queries from different databases or re-query a query that was itself created with a QoQ. As shown in Listing 2, you set the DBTYPE attribute in the <CFQUERY> tag to "Query," remove the "datasource" attribute, and use the query name as a table in your SQL.

In Listing 2 the "UNION" operator is used to combine the two database queries "getRecentIssuesAPD" and "getRecentIssuesEAID" into a single result set named "getRecentIssuesR3." It's possible to combine more than two queries using two or more UNION operators, but for clarity's sake only two are shown in Listing 2. All four salient issues databases are identical except for the data, i.e., the databases are structured the same with exactly the same tables and columns, so there was no problem combining query results this way. The IssueType column, which was created using an alias in the four database queries (see Listing 1), identifies the program that each salient issue record is associated with: APD, EAID, HSCD, or WCMD. Since the query in Listing 2 mixes all the records together, without the IssueType column there would be no way of knowing which salient issue record was associated with which program. The IssueType column lets us label each record with the name of the program it belongs to.

The desired end product of these five queries is a single result set named "getRecentIssuesR3" that holds the recent salient issue records from all four salient issues databases. The ORDER BY clause puts the records in descending order by publication date and in ascending order by IssueType. This puts the most recent records at the top of the display where they belong and records having the same publication date are then ordered by IssueType.

The QoQ in Listing 2 obviously relies heavily on the SQL "UNION" operator to create the final (combined) result set. UNION tends to get a lot of negative attention because it can hinder database performance. In this case, however, the performance impact from using UNION was negligible. The average template execution time was consistently below 300ms, possibly due to the small number of records retrieved. However, just to be on the safe side, I implemented query results caching in all four database queries, which improved performance even more. The fact that our server is running CFMX 7 Enterprise Edition on a dual-Pentium 4 box with 2GB of memory doesn't hurt either. If you've been doing Web development for very long you know that sooner or later we all must make compromises to get our projects finished on time, on budget, and with the expected features in place. I have no doubt that using UNION was the best approach in this situation, but every situation (and application) is different so you should always weigh your options carefully, test thoroughly, and then make the decision that's right for your particular circumstance.

The Power of the Grid
With the queries and other business logic out of the way, I could now turn my attention to the "fun" part of this project, the (Flash) data presentation. While studying the Real Estate Sample Application article, I was pleased to learn that I could use a Flash Form datagrid to "store" query results and display a master-detail interface on the same page. Using the "bind" attribute of the <CFINPUT> and <CFTEXTAREA> form controls, I could easily display the details of each record selected by the user in the datagrid. Best of all, there would be no need to re-query the database each time a different record was selected in the datagrid because the entire query is stored in the datagrid. "This changes everything," I thought to myself once I'd grasped the significance of data binding in Flash Forms. For me, at least, Flash Forms were no longer something I could ignore in my ColdFusion development.

As it turns out, it was very easy to display ColdFusion query data in a datagrid. This simplicity is achieved largely through the datagrid's "query" attribute in which you specify the name of the ColdFusion query you want to display in the grid, e.g., <CFGRID QUERY="getRecentIssuesR3" NAME="myGrid">. Implementing basic datagrid functionality is really that simple. Next, I used the <CFGRIDCOLUMN> tag to specify three columns to be displayed in the datagrid: Publication Date, Program (i.e., IssueType), and Title. These are the only columns that users would actually see in the grid. However, all of the query data is stored in the grid and, therefore, is available for display elsewhere in the Flash Form through the use of data binding. For more detailed information on how to bind Flash Form controls to a datagrid, see the "Completing the Real Estate Sample Application" article.