I'd like to thank Adam Tuttle and the Philadelphia CFUG for inviting me to speak last night on CFSpreadsheet. While the thought of listening to someone talk about spreadsheet generation from an hour would only make a true techie's heart race, I think those who were there got a good overview of what this new functionality can do for them. If anything I hope that I lowered the bar for many in attendance while simultaneously increasing pressure on their bosses to buy those licenses!

My preso focused on the basics (here's the tag, here are the functions) up front and followed up with 6 examples of how these new functions can be used to manage spreadsheet creation and editing no matter how mangled or ugly. If you want the CFSpreadsheet presentation assets and (commented) sample code, feel free to pull this ZIP. I will be updating it this weekend with Derby databases so you can just unzip and run everything. For now, unpack the zip into a folder in your webroot. You may need to comment out the ORM settings in Application.cfc to make it run. Ping me with any questions either here or via twitter (@scrittler).

I also had the opportunity to touch (very lightly) on the new ORM features of ColdFusion 9 during the presentation. One of my demos (not in the zip file) is the start of an app that is intended to replace a spreadsheet with a web app. After 90 minutes of work I had the spreadsheet imported to a db using Hibernate, a quick add/edit form put together and a re-export (since the end users still cling to Excel) of the current db. If I had to write all that SQL it could have easily taken far longer. Finishing the app should be trivial from here, so props to Adobe for putting all the right pieces in the box for me to play with!

It's pretty clear that the whole concept of using an ORM engine is very, very new to most of the ColdFusion world and is definitely holding back adoption. You don't need to know terribly much to use Hibernate with ColdFusion but letting go of having to write SQL is certainly showing to be more difficult than expected. It's a different way of thinking, one that the user group community can definitely help to encourage. The time savings and maintainability improvements alone are well worth starting the trip. I know the Philly CFUG calendar is pretty well packed, but I hope we can put together some talks on this topic (preferably hands on) soon.

Again, thank you to the CFUG for inviting me to speak. I look forward to seeing everyone at the next meeting on Feb. 25!

CFSpreadsheet pretty much rocks the house when it comes to simplifying the task of creating spreadsheets (Excel and Open Office compatible) from ColdFusion. On Thursday January 14 I'll be giving a talk at the Philadelphia CFUG meeting on this topic, but something I was working on last night has me particularly excited and requires a blog post.

Several of the spreadsheets we currently generate for clients have some degree of pretty formatting associated with them. The way we've typically done it is to export a decorated "source" Excel file in Excel's XML spreadsheet format, chop it up and jack the appropriate data into the document, setting style attributes on the cell tags appropriately based on server-side logic. But what if you wanted to create the spreadsheet such that the conditional logic was built in to the spreadsheet itself? The people that use these spreadsheets like to play with the data and it would be awesome if the conditional formatting (highlight in some color, change font size, whatever you like) would apply to their edited data.

It didn't seem immediately possible using CFSpreadsheet or any of the 37(!) functions exposed in ColdFusion...but then I remembered that this functionality is built on top of Apache POI, so there had to be a way to get down and dirty with the Java internals. It's not hard to get there, but whoa the code to implement conditional formatting is definitely verbose.

What I want to do is set up a conditional formatting rule on the 6th column (F) such that if the value is greater than 10 the background is set to yellow.

Then it's just a matter of inserting the spreadsheet contents as usual.

The end result:

Checking the conditional formatting panel in Excel we see that it's legit and not hard coded.

I'll post more as I dig in and build more complex examples. This is a simple expression, but it should be enough to get you started if you need to do this kind of thing with ColdFusion and a generated spreadsheet!

Adam posted the January 2010 meeting announcement to the CFUG website today -- I'll be doing a presentation on the "powerful simplicity" that is CFSpreadsheet (and associated functions) on the 14th at Penn.

I'm especially excited about this topic because of the frequency with which I generally have to work with(against) Excel. Pretty much every project of ours uses Excel as a report output format and we've tried everything from building tabular data in HTML and "faking it" to saving Excel files in XML format and rebuilding on the server side. Both of these options had limitations (the first being stylistic, the second being fragility), so now that ColdFusion has a simple way to build them I'm a happy camper. I didn't really want to get in to messing with POI.

Long story short: we will always have to work with spreadsheets because that's what "business users" understand (read: as flat as possible). ColdFusion 9 has built-in features that make reading and creating spreadsheets much easier than it's ever been. I'm excited about this and think you should come out to the next meeting on January 14, 2010 to learn more!

CounterMarch Systems is 5 today! I have spent the last few days thinking back over projects we've done, clients we've worked with and what I've learned simply by living it. It's been the most challenging thing I've ever done, not always fun but I can't imagine working any other way. Self employment, given the option, is an opportunity everyone should have for themselves at least once. Steep and sharp curves are the most fun!

So here's CounterMarch Systems, a retrospective, at 5 years old:

Origins

CounterMarch Systems started on a couch in a 3rd floor apartment in Langhorne, PA in September 2004. At the time it was just me (Steve), three clients, and my trusty IBM T41 laptop. I even had a little routine - work from home on Monday and Friday, work on campus with a client Tuesday and Wednesday (crashing on a grad student friend's couch overnight) and camp out at my dad's office on Thursday. It felt like I was living out of my car. All of my client files were in a crate in the trunk right next to my sleeping bag. I think having a routine yet varying work environment kept me sharp, though I'm pretty certain that my coffee/latte intake was fairly extreme. Weekends, well, let's just say those were a blur too.

Those first three clients led to almost every single client and project we've had since. Lesson learned: don't underestimate the power of working your ass off and having someone else talk you up!

Help Wanted

Through a series of fortuitous connections I was introduced to Matt Cass later that year, who started as a pseudo-intern and very quickly got tossed into the deep end of one of our most important projects. By January we had a comfortable workflow going, so much so that it didn't make sense for him to be anything less than full time. The "offer" was made at a career networking reception in Philly after which our circle of friends adjourned to the bar to socialize and celebrate (something that became a repeating event in our work week). Through every single project since Matt has been my partner and friend, developing technical chops and helping to beat some sense into my ideas that have been absolutely key to our success. Confidence in our ability to work together has allowed the company to consistently take on bigger and better projects.

I'd be entirely remiss to complete this section without mentioning those who worked with us but have since moved on. Chris Hamilton, our first real intern and indespensible Linux resource, is absolutely brilliant. On one project he had to work out a very tricky integration that we could only theorize would work - he didn't quit until it was working. He's just like that. Graduation took him away from us, but we'd hire him away from his current employer if we could. He likes playing with hardware too much. Chris, be warned...we're going to keep coming after you until you cave! Also on our team for an extended period was Dayne Mickelson. I can't say enough about the fanatically disciplined approach to software development that Dayne brought to the task. I'm pretty sure that if I printed out his code and shot it that the bullet wouldn't go through the paper - it was, literally, bulletproof. He contributed to two of our most important projects, one of which was responsible for a good deal of financial processing so it had to be good (and was!) The good news is that Dr. Mickelson will be coming to a hospital near you in a few years - we wish him well and miss our weekly cheap pizza lunches that we discontinued in his honor when he fled for Washington. Both Chris and Dayne came to us from client referrals too!

The connections that led to Matt's employment (and full partnership) and our other employees has led to the growth of the company in ways I could have never expected. Lesson learned: choose your partner(s) wisely and recruit without recruiting.

Getting Good

We've operated through five years of projects, finding our focus, adjusting to follow the market and getting a lot better at what we do. ColdFusion was always a staple technology with us, but now we've added proficiency with Flex and AIR. From a business standpoint, we've developed a deep understanding of alumni relations, fundraising, benchmarking, CRM and (oddly, but only from a thematic perspective) semiconductor manfuacturing. You can't pick up a single book and be good at any of those areas. Only the experience that comes with either being a practitioner or working side by side with a practitioner in those areas would you ever know enough to implement the right solution. We have been able to do that on every engagement by being fully present when listening to our clients and playing "idea volleyball" back in the office on the best technical solution to the issue at hand. It's great fun to solve a persistent problem with a combination of technical wizardry and clear business understanding. I'd say that our athletics recruitment system is the finest example of this, bar none.

Long term client engagements have led to in-depth business knowledge, which has in turn allowed us to develop software that meets the business need and hits all the technical requirements of the project. Lesson learned: listen fully, question gently, debate vigorously and implement wisely.

Work Hard, Play Hard

Much like golf (which we never have time to play), it's important to swing through the ball. In our case, just becoming good consultants and the maintainers of software we've delivered is not the final step of our engagement with any of our clients. No good developer (or development shop) should ever stop sharpening their saw, no manager should ever stop seeking out ways for staff members to improve and no salesman should ever let the phone go cold. We have clients who have been with us through all five years, working through several projects and the challenge of brining new code to life. I don't think we'd be in a position to do that if we weren't great technologists, if we weren't great self-managers or if we weren't great at standing behind our work. The better we get at learning the more proficient we are and the more value we can deliver with every project. That's terrifically exciting and I intend to keep up the pace, delivering better stuff every single time. It's FUN to do what we do and if we didn't love it we couldn't be nearly as effective.

We learned that in college and it's just as true in business. Lesson learned: work hard, play hard.

Now for our next act...

Five years is an awfully long time to be doing anything and far exceeds the typical lifespan of a startup. That we have been fortunate enough to be successful at it through the highs and lows is due to the trust, guidance and (let's be honest) money of those who think we're worth it. We're proud of the company and reputation we've built.

Matt and I have a few really big, exciting goals for the upcoming year and a very full pipeline of projects to get done (plus a big announcement very, very soon). I'll be sure to blog about them as we get there; Twitter isn't great for everything!

So from two guys in an office with more PCs per person than OSHA would think appropriate, thank you for your support and your business. We look forward to working with you, learning with you and enjoying whatever comes next!

Earlier this year one of our projects went from soft-launch mode to officially "live" mode: The CASE Benchmarking Toolkit, a system we designed and implemented for CASE, the Council for Advancement and Support of Education. CASE represents over 2000 institutions of higher education worldwide with over 75,000 members.

The Toolkit enables the CASE research division and interested communities of practice¹ to build quantitative benchmarking surveys, collect data, generate reports (text and graphical) and design summary dashboards through an easy-to-use interface. We wanted to share a little bit about what it does, why this is great for the alumni relations and advancement profession and why we as Adobe fans are so proud to have built it.

History

The origins of this project start back in 2005 with our friends at PCUAD. We implemented a surveying and benchmarking system for them that reduced the amount of time required to collect, output and analyze the data gathered from thirty higher education institutions. The "Alumni Relations Assessment and Measurement Program" (ARAMP) was the first attempt by any community of practice organization to quantitatively measure the performance of an alumni relations operation. It was an overnight success and continues to be used by PCUAD. About a year later we replaced the HTML UI with a Flex-based UI for use by the North East Alumni Relations group (NEAR).

Word got out pretty fast that PCUAD had not only created a great survey to 'measure' Alumni Relations but also the software to back it up. PCUAD was all too happy to help us get connected with the team at CASE to see if we could push this forward as a global benchmarking system. Once the decision was made to move ahead, we set about redesigning everything - the data model, the middleware and especially the user interface to work seamlessly with the CASE CRM system (iMIS - also a ColdFusion-powered product!)- using the experience we gained supporting PCUAD and NEAR to inform our decision making.

We delivered the system to CASE in February 2008 for beta testing, flipping the "go-live" switch in May.

Perhaps more importantly, we delivered the system to them on time and under budget.

Fast, integrated survey creation

Administrators can whip up a survey in next to no time. The CBT supports a wide variety of question types including question matrices. The system strongly encourages question reuse and enables the cloning of questions, sections and even entire surveys. It is through this capability that year-over-year data can be compared and analyzed. Since the user directory is fully integrated with iMIS, assigning permissions can be done anywhere within CASE which makes provisioning and access control a piece of cake.

Pain-free survey taking

For starters, this product is not Survey Monkey. Responses to surveys are intended to reflect institutional (not individual) performance; for any given survey many people from an institution can work on it but there is only one submission per school. This allows us to aggregate and filter the reports by specific institutional characteristics. Assistive text is available for each question in a survey, which when it comes to financial reporting is absolutely crucial. There are many ways to tabulate donations, donors, volunteers and many other aspects of a modern-day advancement organization.

Reporting and Exporting

Reporting is also a breeze. We know that advancement professionals use a lot of Word and Powerpoint documents in their line of work so we made it really simple to get exactly what they want from the available survey data. Not only can you select which of the available criteria to filter on, but you can view charts and graphs, calculate aggregate values and export it all to Excel (report data) and jpg (charts). We found that using ColdFusion to generate the appropriate data and Flex to render it was a very efficient, powerful combination.

Filtering report data was done through a very straightforward interface based on research and design we did for our RecruitWeb product. Criteria are displayed down the left side of the screen for selection; each item carries with it additional parameters that are user-defined. Use of colors to indicate active filters and the translation of selections to "plain English" gives the user a very good idea of what they can expect to see in the requested report.

Aggregate values are calculated for multi institution reports and privacy is protected for specific types of surveys under certain circumstances. We learned just how protective advancement professionals are of their financial data yet found a way to present actionable and useful information without compromising privacy.

Screenshots

What's ahead

Overall, we're extremely pleased with what we were able to deliver. ColdFusion made the middleware and business logic a snap. Flex allowed us to prepare a rich, responsive user interface. Using Flex also provides the server with some additional scalability. Similar to an ajax-powered application, the less we have to generate and send over the wire the more resources are available to support a larger number of connected clients.

Thinking ahead, it would be great if we could deliver an AIR application to enable push messaging - new survey notifications, even faster desktop reporting and possibly even survey completion alerts. The more data this system contains the more valuable it becomes to all of the CASE member institutions. The more tools we can provide that encourage people to participate the better!

1. A "community of practice" is an assembly of peer institutions connected by similar characteristics like alumni population, annual fund budget, or undergraduate population - not the traditional groupings like "Ivy League" or "Big 10." This ensures an "apples to apples" comparison of data for each discipline represented by the toolkit.

An article in this past weekend's New York Times profiled our alma mater's amazingly successful alumni career solutions program.

Undergraduate career services are widely known but not nearly as many folks know about similar services that the truly class-leading alumni associations under visionary leadership started providing earlier this decade. Alumni databases existed long before the days of Facebook and LinkedIn; leveraging that information (and the requisite institutional affinity) to keep as many alumni employed as possible seemed like an obvious win-win. Lehigh's alumni director Chris Marshall (now at Cornell) saw the need and hired two great people to staff this new program; the next challenge was giving them the right tools to maximize their impact. Undergraduate populations are easily served (they're all in one place) - serving a globally-distributed alumni body...not so much.

Lehigh's program has been able to assist far more people due to their heavy use of a system we recommended to them: Adobe Acrobat Connect.

Every week, Lori and Robin put on multiple web seminars on topics immediately useful to alumni in transition like salary negotiation, interview skills and grad school prep. These aren't pre-recorded sessions; they are presented live at a variety of times so that alumni can sneak it in over their lunch hour or later in the day at home. When all you need is a web browser to participate, the barriers to getting help are significantly lowered. Acrobat Connect has kept the cost of providing this service to an affordable minimum while maximizing the number of people served every month.

We're happy to have pointed them in the right direction so they can help as many alumni as possible, especially in these challenging economic times. Congratulations on getting some well-warranted recognition, Lehigh!

In part one I dipped my toes into the world of ORM in ColdFusion 9. Today I'm diving in...and I know there's far more to this than what I'm about to try, so there may be a Part 3 yet to come. As with part one, I'm writing this as I go...mistakes and all.

Today's challenge: get the rest of the spreadsheet imported into a more relational format.

The source data has a set of columns for payment information, which is great if you only want to keep the most recent payment information. Likewise, there's one column called "comments" that looks like it'll grow to be kinda nasty over time.

Ideally there's a one-to-many relationship between a person and payments and a person and comments. So how do we do that in an ORM configuration?

Step one is to create a CFC for a comment, as that is somewhat more simple.

Once again we've defined the CFC as "persistent" so Hibernate knows about it. As before we set the primary key field by defining the fieldtype and generator attributes on it.

The second property is the fun one. Usually when we're building a database, we'd reflexively put "personID" as a foreign key on the table. Remember, though, that what we're trying to capture here is the relationship between two objects. A comment "belongs to" a person.

Deconstructing the attributes:

• cfc: the actual cfc object that this property references (person is in the /chiomega/mms folder)
• fkcolumn: I like to use the PK of the referenced object as the FK column name in the database.
• fieldtype: truly magical - there are "many" comments for "one" person.
• lazy: don't get the data for the person until I ask for it

The options for Lazy Loading are many...and extremely worth the read. Any code where I can include an attribute lazy="extra" is beyond awesome to me! Quite honestly, though, how many times have you written a very large SQL statement with many join conditions, knowing full well you won't use most of the data but prefer to use one "getter" statement? Too many, right? No more. Let Hibernate take care of getting the data for you at the appropriate time. I could write a blog post purely on the subject of lazy loading and how great this is.

Back to the topic at hand: now that we've got a "comment" object with a relationship to "person" defined, let's look at the "person" and set the inverse relationship.

That last property says that each person has a property called "comments" that is an array of "comment" entities of type "chiomega.mms.comment".

Whoa. Break it down:

• singularName: in a "one-to-many" scenario, what we're referencing is plural...but there's also a need (which I'll clarify momentarily) to reference the items individually.
• type: valid types are array and struct. An array makes sense for this example.
• fieldtype: the reverse of what we had on the "comment" entity. Each person can have 0 or more "comment" objects.
• cfc: the type you're referencing, in this case the cfc we just created
• fkcolumn: the column in the "child" table that will represent this "person"; recall that we set fkColumn in the "comment" entity to "personID"
• cascade: When I save a "person", save any child comment records that exist (insert or update) on the person. Also goes for delete operations.

singularName is incredibly useful: this is used to define the auto-generated add/remove methods for the related objects. Read more on what's created for you in the ColdFusion 9 docs.

The "cascade" attribute was a trial-by-error discovery. There's a cascade options section in the docs on your choices here - basically it defines how the relationship behaves on save/update operations. If you get a object references an unsaved transient instance - save the transient instance before flushing error, this is why -- go back and set a cascade on your parent entity so that child operations work properly.

That should do it for configuration. Let's tie it together and import people and comments from the spreadsheet again.

"obj" is my person. If we want to import a comment, we'll create a new entity of type "comment". Recall that these entities have getters and setters automagically created for us, so I populate the note and createDate properties of the comment with values from the spreadsheet.

Once the comment is populated, I "addComment" to the person - the addComment is one of those automatically generated methods. Something I learned since the last post is that calling ormFlush() isn't necessary - at the end of the request it's called implicitly.

That was easy! Following the same pattern, I added an entity for payments:

...and configured my 'person' to reference them...

Then tweaked the importer to add those as well:

And, best of all, I haven't written a single line of SQL nor have I gone in and created tables/columns/relationships/constraints in the database, yet I have this ready and waiting for use:

Up next: building an actual application using what we've just created. I'm pumped...this is working out great so far!

So the challenge tonight is to bang out a quick little member management system using the ColdFusion 9 ORM tools. Basically, a test of the "look, ma, no code!" approach to software development.

Step 1: create blank database:

Ok, so that was boring. Next step: make a basic CFC using ColdFusion Builder's CFC generator. All I specified were properties that I expected to map to columns in the database. Still haven't written code - here's what was generated for me.

So far, pretty simple. Had to fight the urge to create getters and setters for each property...we'll let the ORM engine take care of that for us. I saved this CFC in person.cfc under the application's home directory in my webroot.

Next step: setting up Application.cfc.

The only three lines that are different from our "usual" config are this.ormenabled, this.datasource and this.ormsettings. The first tells ColdFusion to pay attention to our CFCs and automagically configure the ORM mappings for us. The second is another new feature of ColdFusion 9 - it sets a "default" dsn for all cfquery and cfstoredproc tags so we don't have to type datasource="#request.dsn#" anymore. Rock! Also, the ORM engine uses this value as the database to persist CFC data in. The third tells the ORM engine that I want it to create the necessary database tables and (when the time comes) log all the sql statements fired by the generated code. You'll see those log entries fly by if you start up CF from the command line.

Total code CF code written so far: three lines. Awesome.

Consulting the docs, I now need to add a few attributes to the cfcomponent tag to have the table created in the database for me.

Let's look at what I added:

• persistent="true" - think of it to mean "please store the properties of this cfc as columns in a table you create for me"
• fieldtype="id" - this is the primary key field
• generator="increment" - I want it to auto increment for each new record (I'm using MSSQL05 for this example)

So let's take a peek at what happens when I run a simple script.

Nothing too special - it's just a cfc. But this is where the new hotness resides:

So let's jack some data into the db using this new hotness. Please note: the spreadsheet I'm using has the typical "not created by a programmer" column names with spaces, so I'm using array syntax to extract the field data.

Those last two lines are the fun part and should be explained. entitySave causes Hibernate to store the object in its own cache for really, really fast retrieval. To actually get the data written to the database, we call ormFlush(). If the object has been changed, it gets pushed to the database. In this example, 245 records are created in the cache, then all written out to the database.

Our database:

So now that all of that info is loaded into the database without having written a single line of SQL (c'mon, don't act like you're not impressed), let's slap together a quick browse interface. Do not use this code as an example of "how to write CF"...it's a hacktacular approach just to show the basics. Please.

In "old school" code we would have had to write a query to get any of this data out of the db. Instead, we're asking the ORM engine to do all the heavy lifting. You can see that we're adding a match between the personID field and the form field of the same name. You can specify as many matching criteria as you wish, again without writing any <cfif> this or <cfelse> that.

The result (blurred for privacy reasons):

In my next post, I'll create the edit form AND start working on the one-to-many relationship between a person and a "comments" table. This is where we'll get the most utility out of ORM, but also where we'll find the most complexity.

So far, though, I'm totally impressed. This really was my first experiment with ColdFusion's ORM capabilities. There's plenty more to learn and I'm sure I'll refine my technique even more as I dig in to it.

It really is this simple in ColdFusion 9:

No more cfx_Excel2Query, no more hacks, no more funky parsing...just straight up Excel to a native ColdFusion recordset.

Up next: creating a simple app using the ColdFusion 9 ORM capability. This is tonight's programming challenge.

Short version: I had the same problem this guy had. A nice, functional Flex 2 app, recompiled under Flex 3 suddenly lost the labels under the columns in a ColumnChart. Our resolution was no solution at all.

Apparently a ColumnChart in a Module is unable to use/find/render embedded fonts (wouldn't it be great if this triggered an exception? we thought so.). We tried dropping a hidden ColumnChart in the root module which didn't work (though had been a handy resolution to other singleton vs. module issues in the past) on the off chance it would somehow capture the proper font settings.

Rather than continue to fight, we realized that Arial was good enough for chart labeling so the simple resolution was to just set the fontFamily on the ColumnChart and leave the rest of the app to render in Gill Sans. Happily enough the app looks good, the client is no longer upset about the lack of chart labels, and we didn't have to spend a day digging through the Flex framework to resolve the issue.

So...if you run in to this...take my advice and drop down to a common font like Arial for your chart labels. It'll be interesting to test this more thoroughly under Flex 4 with all of the improvements you get with the text layout framework. For now, though, Flex 3.x + embedded fonts + charts in modules is a combination best worked around.