Figure 2 (top): The results entry window displays county-by-county information for each candidate in a contest. Figure 3 (bottom): The output window lets users search for the races they want then output them in page-layout style or HTML.

Newsroom writes application to enter and print election results

As in most newsrooms around the country, the evening of Nov. 7 brought more excitement and uncertainty to the Atlanta Journal-Constitution (AJC) than anyone had expected. One thing that didn't add to that confusion, though, was the AJC's new election results system, to which this writer made a humble contribution.

About 30 metro staffers used a new desktop application to enter county-by-county results for local and statewide races. They updated races continuously throughout the night. Just prior to each edition's close, a layout editor would use the same application to compile the entered results and place them on pages.

When it comes to getting local election results into print, most papers are faced with similar problems. How do you quickly enter results and format them on a page? How can you create a system that several reporters or clerks can use simultaneously? Once those results are in, how can they be automatically processed?

Many papers already had an election solution in place during the '80s and '90s when they used minicomputer-based systems -- from companies like System Integrators Inc. and Atex, to name two -- to compile and format. The introduction of desktop pagination to newsrooms did away with many of those systems, forcing each paper to come up with its own solution.

Because the Journal-Constitution's newsroom works in an Apple Macintosh desktop environment, we came up with an AppleScript-based application that could run on as many Macs as needed. The desktop application sends information to and from a Sybase relational database where the data is stored.

This new application had a few added bonuses over the old way of compiling results. It was written in a way that it could be used in any other newsroom in Cox Newspapers, since the entire group has standardized on Macintoshes. It also was made to produce HTML (HyperText Markup Language) formatted content for our web site, ajc.com. Lastly, it was built so it could be set up and administered by the newsroom, taking it out of the hands of programmers.

Roots in the '90s
The results application had its beginnings during the 1998 elections. Christian McDonald -- a former AJC staffer who now works for a Cox paper in Texas, the Austin American-Statesman -- asked our publishing technology group to help him come up with a results entry solution. Jenny Brandt, manager of our publishing technology department, worked with McDonald to create an AppleScript to send results to a remote computer, using FaceSpan, a rapid development tool that makes AppleScript easier to use, for the interface. FaceSpan was acquired in 1996 by publishing systems supplier Digital Technology International (DTI) of Springville, Utah.

Mark Cantrell, an AJC programmer, who specializes in Perl (a scripting language that is prevalent in UNIX and Macintosh environments), wrote Perl code to process and output the results.

That solution for '98 worked well, but all agreed that it took too much involvement from programming and systems staff. For example, each contested race needed a separate FaceSpan window that contained information about candidates and precincts. Those windows were set up, one by one, by McDonald. Once the election was over those windows were obsolete.

The solution for 2000 needed to use a database to store contest and candidate information. Then the windows for each race could be dynamically created using the information from the database.

The application made its first appearance during last summer's Georgia primary, then went through some major renovations for the November general election.

In March of this year, Brandt worked with me to flesh out the concept for the project. Because of its Sybase element, the project would be a first for us in several ways. It would be the first time we had created a separate Sybase database just for a specific project, and it would be the first time I had ever done Sybase SQL programming.

Why Sybase, from the eponymous company of Emeryville, Calif.? The AJC uses an editorial and pagination system from DTI, which uses Sybase databases to store and process the elements used in the production of the paper: stories, ads, photos and graphics. So the Sybase architecture was already in place and well supported.

The idea to create a separate database came out of discussions between Brandt, Director of Technology and Operations Support Jim Davis, Senior Database Administrator Rob Osattin and Systems Editor Jacki Rudd. We didn't necessarily have to go with a separate database. We could have added a few tables to the already existing pagination databases used by the DTI applications. There was a major problem with that, though: I would be learning to program with SQL on our live databases -- a stomach-churning scenario for any of our database administrators. They liked better the idea of what they called a "sandbox," which would be the separate elections database where I could "play" while I learned how to work with Sybase SQL.

The sandbox concept was an unusual opportunity for me. I was able to make mistakes -- big mistakes, and lots of them -- while I learned. Osattin eased me into the concepts behind designing relational databases. I'd do my best to design the database's tables then he'd critique what I had done and point me toward a better design. I'd build the tables, add columns, delete columns and delete tables. Even when I thought I had the design nailed, we'd uncover a better way and I'd delete everything and start over.

While database programming was new territory for me, AppleScript and FaceSpan were not. I was first introduced to AppleScript in 1997 at my previous newspaper, Iowa's Des Moines Register. The Register was converting to the DTI pagination system and I was a copy editor at the time, plucked from that job to be one of the trainers on the DTI conversion project. I was allowed to do a lot more than train, and in the process I was introduced to AppleScript. I built several applications that interacted with DTI's Mac-based applications.

I came to the AJC in 1998 and have continued writing FaceSpan applications for the newsroom, but the election application would be a lot more involved than anything I had done previously.

What it had to do
I couldn't do much with the FaceSpan-AppleScript part of the project until the database was in place. (For more on the database's design, see the sidebar and diagram.)

Once the database was built, it gave me an outline for what I needed the application to do. It had to give users a way to do three things: populate the database with contests and their candidates, enter results for each contest and its candidates and output the results in a structured way.

To populate the database, the newsroom compiled two Microsoft Excel spreadsheets, one for the contests and one for the candidates. Each line in the contest spreadsheet contained all the information about that race -- things like the names of the races, which counties and how many precincts were involved. I also had them include a unique number to identify each race. The candidate spreadsheet listed on each line the candidate's name, party and whether they were an incumbent, and it also listed the unique number that corresponded to the candidate's contest. That unique number is how the application paired candidates with their races.

We used this setup process for the elections, but will probably do away with it in the future in favor of a simpler approach I've created. I built windows in the application that let the newsroom add races one at a time then let them add candidates to those races. This approach seems a little more straightforward than the spreadsheet workflow.

Once the contests and candidates were loaded into the database, the users needed some way to access that information. A search window would let them locate the race they wanted by name or county. The county search worked well because those entering results were typically entering only for races in one county. Double-clicking on the race in the search window would open the results entry window (Figure 2, Page 4).

This window has fields for entering vote and precinct totals, but it's a little more complex than that. During the July primary, this window was much simpler. The county-by-county fields for precincts and votes didn't exist. I only gave them fields for total votes and total precincts in each race. It wasn't until a week or two before the election that the newsroom's election coordinator told me that they desperately needed separate fields for each county's votes and precincts. By then it was too late, but I rewrote the application for the general election to include those fields.

To keep users from introducing incorrect information or running into errors, I included a few safeguards with this window. FaceSpan window elements can be made to prevent the entry of certain types of information. Users are only allowed to enter numbers in the vote and precinct fields, they can only enter a "y" or "n" in the absentees and runoff fields and they aren't allowed to make any changes to the candidate, party or totals fields. I also used a field in the database to register a race as being checked out when a user opened it. Only one user could have a race open at a time, which prevented one user from overwriting the other's results.

To output results and get them into DTI's pagination system, the newsroom needed a search window to pull up the races they wanted. Once they found the races, they could order them then click a button that would pull all the information for each race, one after the other.

Figure 3 shows the layout of the output window. Users can search on the race's name or county, which dumps the search results into the "races found" area. From the races found, the user clicks the buttons in the center to move desired races into the "races to output" area. When they click the output button, it pulls all the results from the database, formats them with DTI coding and dumps them into a new story window in DTI's page-layout application.

A few unanswered questions
What about ballot issues, referendums? These were handled like any other contest, except the candidates involved in the contest were named "yes" and "no."

What about uncontested races? The output window has a feature that looks at races and determines if they're uncontested; it strips out the uncontested races if you choose. It can also make a list of only the uncontested races.

What about primaries? Candidates of differing parties are competing during the general election, but during the primaries the candidates are only competing against foes in their own party. To solve this, each entry in the race table is declared as "primary" or "non-primary" in the isPrimary field (Figure 1). During a primary, the enter results window sees the isPrimary entry and then displays the candidates grouped by party.

The application performed well during July's primary and November's general election. It was slow at times, but not so slow that it caused any problems. It can quickly pull information from the database, but once it pulls it, most of the processing is done in AppleScript, which slows things down. Even so, its search and output functions are the only places where it gets bogged down. Searches sometimes take 15 seconds. Outputting an entire county's race results, which could be as many as 45 races, could take up to 40 seconds.

In the process of building this application, I learned some valuable lessons I can use in future projects.