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.
I must say that using a datagrid greatly simplified and facilitated my development efforts. First, the datagrid made it possible to display both a "master" list of records and a "record details" section on the same page. When the user selects an item in the datagrid, the details of the selected record appear in the various Flash Form controls on the page. This eliminates the need for separate "master" and "details" templates in the application. The second benefit of using a datagrid is that the amount of code that I had to write was substantially reduced. Numerous HTML and CFML tags were replaced with just three tags: <CFGRID>, <CFGRIDCOLUMN>, and <CFINPUT>. All right, it's really four tags if you count the <CFTEXTAREA> tag. Another benefit of using a datagrid is fewer calls to the database and better performance since the master-detail interface in the Flash Form uses the query data stored in the datagrid, not the database, to populate the form controls.
I soon realized, however, that I had another problem. The "Title" and "Summary" text areas contained large amounts of text and consumed too much vertical space in the form. This made the page too big to see without vertical scrolling. Once again it was Flash Forms to the rescue, this time via the Tab Navigator layout container. The code in Listing 3 shows how I used a Tab Navigator container to distribute the text areas (and other content) among three distinct "tabbed" sections in the form. The content in any of these tabs could be accessed simply by selecting the appropriate tab.
Now, with the Tab Navigator container dividing the content evenly among three tabbed sections in the form, there was plenty of room for all of the form controls, including the two large text areas.
Once I had figured out how to bind the text areas and text input controls to the datagrid, the remaining development work went quickly. I had to consult the Real Estate Sample Application article once more to figure out how to bind checkboxes to the datagrid, but that wasn't too difficult. By now the application was almost ready and I began testing it to ensure cross-browser compatibility. One of the greatest benefits of Flash is its ability to render consistently across a variety of different platforms and browsers. This alone is sufficient reason to consider using Flash Forms for your next ColdFusion project.
Next, I added two <CFSELECT> list menus above the datagrid to let users filter records in the datagrid. The first of these menus filters salient issues according to their confidential status, e.g., Enforcement Confidential, Business Confidential, etc. The other menu allows selection of salient issues that are one, two, three or four weeks old for display in the datagrid. By setting up the page so that the Flash Form submits to itself, I was able to keep it all to a single page. However, in the interest of giving users a "print version" option, I created two "print version" templates that use several URL variables (passed from the main page) to display detailed information on any salient issue in FlashPaper, Acrobat, or HTML format. In the third tab of the tabbed section in the main page (see Listing 3) I employed three <CFFORMITEM> tags with the Type="html" attribute to create three dynamic HTML links. Thanks to the <CFFORMITEM> "bind" attribute, the URL parameters in these links change according to the record that's selected in the datagrid. These links pass the necessary parameters to the "print version" templates, allowing the latter to query the correct salient issues database and retrieve data on the desired record. The code for this is shown in Listing 4
Any salient issue displayed in the datagrid could now be printed in HTML, Acrobat, or FlashPaper format by clicking on the appropriate link in the third tab of the Tab Navigator.
Figure 1 shows the completed Flash UI for the application. I added some fictitious records so that it would look similar to the actual application. By using a Flash Form to handle the data presentation, I had more time to focus on the database queries and other application logic. Frankly, I was amazed that such a sophisticated interface could be implemented so easily and with so little code. But then I remembered that this was ColdFusion, and we all know how ColdFusion excels at making even the most complex tasks seem trivial. Indeed, that's what I like most about ColdFusion, its unique ability to make quick work of complex programming chores so that developers can get real work done. Now, whenever I start a new ColdFusion project I ask myself, "Will Flash Forms work here?" I strongly encourage you to do the same because Flash Forms can save you precious time, simplify your code, and deliver a compelling user experience.
Beyond Flash Forms: Flex 2
As you may have
guessed, over the past few months I've been on a mission to
"Flash-enable" my applications, and I didn't want that mission to end
with Flash Forms. So I began experimenting with Flex 2. My experience
with Flash forms made working with Flex much easier. Flash Forms and
Flex are quite similar and have much in common such as the controls
(e.g., datagrid), data binding, Actionscript, compiled .SWF file
output, etc. Therefore, many of the things I learned while using Flash
Forms could be applied to my Flex development. Once again I decided to
start slow and tackle a relatively simple project. The project I chose
was to convert one of our existing ColdFusion-generated permit pages (www.epa.gov/reg3artd/permitting/petitions2.htm)
to a Flex page. I say "ColdFusion-generated" because this page really
is generated automatically, on a weekly basis, by our ColdFusion server
via the <CFSCHEDULE> tag, but that's a story for another article.
Flex applications can't interact directly with a database as ColdFusion does, so the data for Flex applications must come from data providers, i.e., services that Flex recognizes and can interact with. Data providers include arrays, Web Services, HTTP services and external XML files to name a few. That last option interested me the most. If I could somehow use a separate XML file as the data provider for my Flex page then I could update the application simply by replacing the XML file. Plus, there would be no need to concern myself at all with the presentation code when performing data refreshes. The idea of creating a Flex application with separate data and presentation code was very compelling, and so I fired up Flex Builder determined to create a Flex page that gets its data from a separate XML file.
I had previously studied the tips and resources available in the Adobe Flex 2 Developer Center, and the Flex Builder IDE made it easy to construct my Flex application. Working primarily in Source mode, I added panel, datagrid, form, textinput, and other components and used data binding and Actionscript to bind everything together, similar to what I had done when working with CFML and Flash forms.
After some trial and error writing MXML (Flex's XML-based markup language) and Actionscript code, my Flex application was finished. I have since put it on my division's public Web site at www.epa.gov/reg3artd/permitting/flex/t5permits2.htm. The .SWF file, which is the application's Flash UI, is invoked by an HTML "wrapper" page that will prompt the user to download and install Flash Player 9, if needed. A basic wrapper page is automatically created by Flex Builder when an application is compiled, but I modified it to look like a standard EPA Web page so it would be consistent with the rest of our site. I think that creating a wrapper page is a good idea because some users may not have the latest version of Flash Player installed and Flex applications require Flash Player 9.
I'm happy to report that my Flex page uses a separate local XML file named "permits.xml" (www.epa.gov/reg3artd/permitting/flex/permits.xml) as its data provider. So it's possible for even a Flex novice like me to create Flex applications that use data from an "external" source. I even wrote a special ColdFusion template that queries our internal Title V Operating Permits (Oracle) database and constructs the permits.xml file from the query results. When I want to refresh the Flex application with new data, I simply run this ColdFusion template and replace the old XML file with the new one generated by the template. I think that in the future we'll see a lot of database information being converted into XML since Flex can't interact directly with a database but can easily access and retrieve data "packaged" in XML.
Separating presentation and data code certainly has its advantages, but for me the best thing about Flex is that no ColdFusion or other server-side support is needed for basic standalone Flex applications (like mine). Basic Flex applications can be deployed just like HTML files and will work on a standard (non-ColdFusion) Web server. This is important in my situation because, as I've mentioned before, the cost of ColdFusion support on EPA's public Web server is high. Therefore, my Division's public Web site doesn't have ColdFusion support at this time. However, with Flex I can still present our data via a Flash interface.
I've already begun thinking of ways to enhance my Flex page with images, links, or even charts. But I'm out of space for now and this will have to wait for another article. Thanks for reading and I hope that I've given you some interesting ideas on how to use Flash Forms and Flex to revitalize your own applications.