Dates and times look great in Excel until you try to concatenate them within a formula. Then you see Excel's messy raw date and time values. I have found it necessary to use the YEAR, MONTH, DAY, HOUR, and TIME functions to extract these values and then format them for a SQL query.

For example:



First attempt (didn't work)
="INSERT INTO `tour_dates` SET `datetime`='"&D2&" "&E2&"';"

The result:

INSERT INTO `tour_dates` SET `datetime`='38260 0.375';

Boo!

Second attempt (worked!)

="INSERT INTO tour_dates SET `datetime`='"&YEAR(D2)&"-"&MONTH(D2)&"-"&DAY($D2)&" "&HOUR(E2)&":"&MINUTE(E2)&":00"&"';"

The result:

INSERT INTO tour_dates SET `datetime`='2008-10-1 9:0:00';

Yay!



tags: excel, mysql, date, time, datetime

I have adjusted this blog template so that posts appear before sidebar content, especially for mobile devices. If this does not display properly in your browser, please let me know. It should look something like this.




tags: css

Our alumni office is digitizing all of the items in the school archive this summer to better document what we have and make the collection more available to the school community. I have begun to configure Drupal to store this collection of images and descriptive information. This way, we have control of our data, we spend no cash on a commercial solution, and we can customize this as much as we want/can.

I could use the feedback of more experienced Drupal folk on this design. Many thanks!

I created a custom content type and attached the following taxonomy categories. I am thinking that heavy use of taxonomy will allow for easier navigation of the database than custom fields/exposed filters.



The first one, "Category," is an internal term used by the alumni office to identify to which broad part of the school program the item belongs.

I only added two custom fields to the content type.



ImageField allows easy image upload. Additionally, all of the uploaded files end up in a subdirectory of /files, so that it will be easy to move the archive elsewhere should we decide to do so one day.



I configured ImageCache to automatically create a thumbnail from the uploaded image, display it in the node teaser view, and link it to the full-sized image.



(this is just a sample I was using)

For my last trick, I installed Auto Nodetitle and wrote a little PHP to automatically generate archive ID numbers. For some reason, this didn't work properly when the title was hidden, but it is likely better to leave it visible so that the archivist may manually override the automatically generated value if necessary.

Here's the code I used for Auto Nodetitle.


<?php
$token = '[title]';
if (empty($token)) {
$sql = "select `field_id_value` from content_type_archive order by `field_id_value`";
$result = db_query($sql);
while ($row = mysql_fetch_assoc($result)) {
$id=$row['field_id_value'];
}
$newid=$id+1;
return 'Catlin Gabel Archives: Item '.$newid;
} else {
return $token;
}
?>

Finally, the office would like users to be able to search by decade. I am already capturing the date as a custom date field and class year as a taxonomy term. How would I set up a search of either of those fields by decade? Is there an easier way than setting up a calculated field and searching on that?


tags: drupal, archive

Last summer, I migrated our school curriculum map from a commercial system to a homegrown one. This project required careful attention. Teachers have put years of effort into the task of articulating their curriculum in a standard format. 5,000 individual entries comprise the map, describing each course unit by several dimensions such as essential questions and habits of mind. Anyone can view the map on our public web site -- it represents the core work that our school undertakes in a highly visible venue. The old system suffered from an outdated graphic design, lack of integration with other school information systems, and challenging user interface. Users had developed low expectations for the system, for which we were paying thousands of dollars each year in software maintenance fees.

I found it most challenging to build a new Perl script around the old system's database architecture. I couldn't throw it out altogether but rather had to come up with original ways to navigate information that was not provided to us in a normalized database structure. I found it unnerving to migrate a vast quantity of critical information, but I feel relieved that all of the recent feedback has been positive.

Essential to this process was good user testing, which I didn't get until just last month. Happily, teachers identified and I resolved major bugs, and now updating the maps appears to be proceeding smoothly. I appreciated the eagerness of the teachers to invite me to their editing sessions so that I could see problems, fix them immediately, and add requested features within a day or two.

Positive feedback from a variety of sources suggests that the new system is a great improvement from the old. We now control the appearance, user interface, and data of the system.

This article describes my efforts this weekend to build out a new Drupal site. I post it here as a record to myself, a resource for others, and a request for feedback from more experienced Drupal developers.

I built out a new Drupal site this weekend, a clone of a server-side include site that I launched a few years ago. The company, run by friends, wants to provide more direct insight into their trips through blog posts and photo galleries. Using Drupal may also prepare us for the next step beyond that, the development of an online community on the site for guides and customers. For me, I appreciated taking the next step in my knowledge of Drupal, moving beyond my intranet experience of last year to the public face of a small business. I also remain impressed with Drupal's power and versatility. One can build so many different kinds of sites with Drupal, yet enough depth/specialization exists to do the job right no matter what the task. Amazing.



Theme
I took two tries to get the theme mostly right. I find it challenging to merge the CSS styles of the original site with the built-in Drupal ones. It has helped a lot to start with a simpler theme (this time, Chameleon) than a richer one with more to correct. I still find CSS styling nonintuitive, what with the different techniques used to embed CSS styles in div, span, p, a, ul, and li tags.

Regions
I finally gained some understanding of how theme regions work. I saw first-hand for the first time region definitions inside a theme and began to clue in that I can place a region wherever I like on the page, create a new block with content specific to that region, and then only display the content on one or more pages that I want. For example, I currently have weather (using the plugin of the same name) loading in the right-hand column. I want to eventually display it in the main content area but mix it with other, editable content there.

Displaying Perl Output
I didn't want to rewrite the Perl components of the reservations, calendaring, and book order system, so I went looking for a way to display Perl output within Drupal. I found a neat JavaScript trick but took a while longer to determine that each document.write instance in Perl had to output a single line of code -- no line breaks. In the end, it worked -- JavaScript calls the Perl script from a Drupal block, which chugs through its operations and then returns the HTML to Drupal using document.write.

Image Galleries
For image galleries, I have before tried image_gallery and embedded Menalto Gallery. Both have had their downsides. This time, I used image_gallery but laid Lightbox v2 on top, and boy does it look great. I also wanted to give Flash Gallery a shot, but it died with script errors during install. For this client, FTP transfers of files to the remote host will be fine, but eventually I would like to implement something like Image Publishing in order to uploads dozens or hundreds of images at a time into Drupal, as Gallery does so effectively.


Lightbox v2

PHP Memory Limit
PHP memory limit is giving me a hard time. Currently, I can only upload photo gallery images of less than 100kb without producing "out of memory" errors. This happens no matter whether I use image_import or create content->image to upload the image. I suspect that our web host may restrict PHP to 16MB. I have run into errors trying to push their config any higher. I am using every module I have installed, so reducing overhead doesn't look promising.



Taxonomy and Content Types
It took me a good while to understand how Taxonomy (Categories) works when I first started with Drupal just over a year ago. Now, I have barely proficient, at least enough to determine a strategy for how to organize trips in this site. Using the Content Construction Kit (CCK) and Taxonomy, I will have created three new content types (trip, itinerary, and equipment list) and a taxonomy called "trip type." I can now produce lists of trips for each category, which I will next link to their related itineraries and equipment lists. Having each trip be a separate node will also allow us to open comment-based dialogue about the trips.

Preserving Referring Links
I am going to edit the .htaccess file to automatically remove .shtml for any old-style requests. As long as I follow a similar naming convention in Drupal as I did with the old HTML pages, we won't lose links from referring sites when the Drupal-based one launches.

Unexpected Line Breaks
As I migrated trip descriptions from the old site to the new, I encountered bizarre line breaks where I didn't expect them. Turns out that I found a slight conflict between TinyMCE and Drupal's automatic line breaks. Though it sounds counterintuitive, it seems that one must disable line breaks in the Drupal input type definitions in order to avoid these unwanted line breaks. TinyMCE handles the conversion of line breaks into HTML tags on its own.

Weather
The weather module works great. What a gift. And to think that, two years ago, I gave up on a Perl-based weather solution I was trying to install on this site.

Integrated New Features
Our friends at the guiding company will have to think about how much home page real estate they want to give to the blog and image galleries. Right now, the two new features they want are buried. It's not easy to shove the introductory content aside in favor of more specific, time-sensitive material.

Temporary Files Location
The temporary directory setting in File System died quietly, without Drupal error. On this shared hosting system, I moved the temporary directory to within the Drupal file system, and now it works fine. In other words, don't use /tmp.

File Storage
I originally wanted to be rigorous and keep the Drupal file storage system outside of the public HTML directory, even though I don't yet have any private files to store there. It worked great while logged in as admin but not for an anonymous user. For example, photo gallery images would silently not display for anonymous users, despite the proper permissions settings. This appears to have something to do with anonymous users not being able to access scripts in system/. I migrated the file store to the public web directory, and now it works fine. I will eventually need to figure this out for other sites.

Additional Modules Installed
CCK
Image
JQuery_update
Lightbox v2
TinyMCE
Views
Weather

Optional Core Modules Enabled
Blog
Contact
Help
Menu
Path
Search
Statistics
Tracker
Upload

This week, I introduced Drupal blogs to senior project students looking to publish weekly reports of their work to their project advisors and the school community. Drupal seems a little thin on email notification features. Comment Notify appears to only permit comment followup, and Subscriptions is known to be buggy. I installed Subscriptions -- do you have first-hand experience with it? The similarly-named "Subscription" module seems dead.

Subscriptions installed and ran smoothly in my tests and seems to provide exactly the flexibility that I want for our users. Is this the best way to do this?

Update 10/25/08: We have been running Subscriptions for nine months now with no apparent ill effects. That said, we have not tracked whether it sends notifications every time that it should.

tags: drupal, subscriptions

A beautiful Drupal moment -- I've been meaning to create this for a while, an ultimately it took me less than an hour to tweak this as I wanted.

Employing my usual stepwise development process, I have just created a bare-bones electronic portfolio content type to serve as a prototype for testing with a small group of teachers. The content type includes the required title and body plus the optional link and attachment fields. This allows users to post a piece of work, introduction, or reflective statement, attach a piece of work such as a word document or image file, or link to a piece of work already posted somewhere else. In the future, I may add content type fields for Image, Audio, and Video, though it may be simpler for the user to link to them instead. I then created a view to show a user his/her portfolio items in a table view. The view only worked once I installed Content Access and allowed users to see portfolio content type items that they themselves had created. I should at some point make it easier for users to search Drupal for their own content using an autoselect field.

The electronic portfolio tool is designed to be versatile. You could use the text field either to include the body of a piece of work (such as a poem), an introduction to a piece, or a reflection on the work. The link and attachment options are especially valuable. You could attach a Word document or JPG image, or if the work is already posted somewhere, you could link to it (e.g., Gallery image). I can add any number of other text fields, including categories, and even image, audio, and video media fields if we want the portfolio to contain these items directly. Anyone here could use the tool: students to collect exemplary academic work, teachers to reflect on their professional practice or organize a self-evaluation.

I have asked a small number of interested teachers for their help to grow this tool to the point that it supports the electronic portfolio needs that they anticipate having in the future. As usual, my approach is to release a prototype, invite a few people to use it, develop the tool into a mature version, make it available to everyone, and then invite all to decide how much value it has and how much we should encourage other people to use it. I will seek conversations that allow me to learn more about their teaching objectives, needs from the tool, improvements that may be made, and examples of people experimenting with use of the tool.

At least one of the elements of this tool is borrowed from DrupalEd (thanks, Bill!). Why not use DrupalEd entirely instead of building out this site from scratch? First, I find it easier to build up from Drupal core than to tear down from a rich, unique distribution. I am starting in the place where all knowledge about how this system works is widely shared and then only adding and configuring modules and objects in a way that any user would. Second, DrupalEd supports user, group, and community-wide content. In our school, Moodle supports group content, Drupal supports community content, and the portfolio piece is the first part that is individual. We operate on slightly different assumptions of needs than a school starting from scratch and seeking a complete content management system.

It finally happened. After five years of working in Blackbaud schools, I have taken the plunge. It took a web application to do it, of course. Over the last few weeks, I have migrated the admission inquiry form from my previous school to this one, except this time I set up all the data import routines. The script collects basic applicant details and allows them to schedule a parent tour and school visit. It produces a set of import files that you may then use to get the web data into Education Edge. It's pretty slick once we work out all the intricacies of our process and import exceptions.

Many of the error messages in Education Edge are pretty nondescript, but I am finally beginning to get familiar with the conventions. I realized today that "Action for Import ID" meant that I should find the Import ID for the applicant, an automatically generated string of numbers and dashes that ensures that the tour/visit action ends up in the correct applicant record. This was easy to pick up with "select import id from ea7records where userdefinedid = [applicant id]." "Wrong field type" means that whatever Education Edge shows in the user input screen for a field does not necessarily match the format it uses during an import! Some guesswork may be required. For example, "Faculty/Staff Member" just becomes "Faculty/Staff" for the purposes of import. I am still searching for the correct format of the "send reminder to" field. In the UI, it's simply the logon username for that individual. However, that fails the import as "invalid."

I didn't think I would ever say this, but some aspects of Blackbaud are beginning to look extremely powerful, and dare I say ... elegant? ... at least until it breaks the next time!

We have just launched a bookstore point of sale system that I wrote as a web script. Why not use a commercial point of sale product? We want our students to be able to self-checkout at a computer workstation without requiring the presence of a staff member. The main student-facing page is a simple screen that allows one to log in using web site credentials and then buy one or more books using a barcode scanner. Easy, right?



I didn't appreciate until neck-deep in the project that this was really more of an accounting project than a sales front-end! The business office can download sales from the script's admin interface. The download file includes the student's Education Edge ID, the EE code for the book purchased, the price, and the date of the sale. It includes a few dates -- date of sale, post date, and due date, which are part of the billing process. In order to know the retail price, the script needs to keep track of the purchase price, which changes during the year as new editions are ordered and retail prices change. In order to know the total value of inventory for accounting purposes, the script needs to keep track of inventory and book cost -- the price the school paid for the book. My goodness! It took a while to get all of these details sorted out and debugged. We have 270 students each buying a handful of books. After a couple of years of selling books, we will surely come out ahead in terms of time spent.

Two years ago, I developed an admission inquiry form in about 60 hours of dedicated time. This past May, I migrated the script from my previous school to my current school in just a few hours. I felt pretty good about the ease of getting this script to run in my new school's server environment. Then, I met with the admission office to adapt the script to Catlin Gabel's admission process. This part is taking much longer. This school has four divisions, two admission officers, and more multi-kid families applying for admission. It is not taking quite 60 hours, but I bet I have spent 20 hours making changes to the script to bring it to my current programming standards and adapt it to Catlin Gabel's admission process. Check it out.

I recently discovered that osCommerce does not automatically remove credit card information for old orders. Here is a little script that I dropped into admin/orders.php that silently masks credit card numbers for orders older than 14 days. One day, I will learn how to create a legitimate module.

$cc_numbers_query = tep_db_query("select orders_id, cc_number from " . TABLE_ORDERS . " where orders_status=3 AND last_modified < (DATE_ADD(NOW(), INTERVAL -14 DAY))");
while ($cc_num = tep_db_fetch_array($cc_numbers_query)) {
$fullcc = $cc_num['cc_number'];
if ($fullcc && (!strstr($fullcc, 'x'))) { // hasn't previously been processed
$newcc='';
for ($a=0;$a<(strlen($fullcc)-4);$a++) {
$newcc .= 'x';
}
$newcc .= substr($fullcc,-4);
$editcc_query = tep_db_query("update " . TABLE_ORDERS . " set cc_number = '" . $newcc . "' where orders_id = " . $cc_num['orders_id']);
}
}

I updated our insideCatlin design layout over the past few weeks to use the new school publication colors and improve legibility. This is in line with trends among web 2.0 tools to increase font size and simplify page layout. I had used the old design for five years between two schools!

I started by applying the Aberdeen theme to Drupal and adjusting the colors to the Catlin web palette. Then I applied the Chameleon theme to Moodle and adjusted the font and color settings to very nearly match the Drupal theme. Finally, I adjusted the HTML template that our custom Perl and PHP scripts use.

Comments welcome.

Old design



New design

I recently moved a site from a shared server that had the Perl library Image::Size installed to one that had Image::Magick. Though Magick has more commands, I had trouble finding a relatively simple function: image size. I wanted to get the image dimensions so that I could create a floating div appropriately sized to an image's dimensions. After many attempts at Googling and experimentation, I finally found the answer in the Get command. I hope you find this tip useful.

use Image::Magick;
$image = Image::Magick->new;
($width, $height) = imgsize("$file");

sub imgsize {
$image->Read(@_[0]);
$height = $image->Get('height');
$width = $image->Get('width');
return ($width,$height);
}

While migrating some data from Excel to mySQL, I came across the following life-saving tip.

create a second column =SUBSTITUTE(SUBSTITUTE(A2,CHAR(13),""),CHAR(10),"
"). This will handle both UNIX and Windows carriage return-line feed combinations.
source

Two years ago, I wrote a little post on the amount of time needed to create an online admission inquiry and application form to rival other Blackbaud compatible offerings. Today, I migrated the same web script to a new school in two hours. I spent most of that time converting MSSQL database handles to mySQL DBI syntax and making text edits for the change of school. Two years ago, the script had no cash costs for the school. Today, we continue to reap its benefits. It is still a very custom application -- it would require a lot more work to make a flexible, customizable application that would be useful to many schools. That's the job of the for-profit companies or some well-funded non-profit workers!

On the long road to achieve single-sign on among open-source applications on a small, school network, here is a tip. You may create a new local user in Moodle or Drupal with mySQL statements.

We use this to register parents on our intranet web site. I maintain a small Perl script that verifies new parent users against Education Edge by email address and then creates the new account in both Moodle and Drupal, the two content-management systems we are using right now.

Drupal 5.1

INSERT INTO `users` SET `name`='$name', `pass`='" . md5_hex($pass) . "', `mail`='$mail', `init`='$mail', `created`=UNIX_TIMESTAMP(), `data`='a:():{}', `status`='1'

Moodle 1.8

INSERT INTO `user` SET `auth`='manual', `confirmed`='1', `policyagreed`='1', `mnethostid`='1', `username`='$username', `password`='" . md5_hex($password) . "', `email`='$email', `idnumber`='$eeid', `firstname`='$firstname', `lastname`='$lastname', `description`='$description'

Of course, you have already escaped illegal characters from your scalars by now in order to avoid SQL malicious code injection! Also, these statements use the Perl MD5 library. PHP syntax will be slightly different.

Since the dot com bust, graphic design from the web has had to make a slow climb back to priority position on people's want lists. Does design make a difference to the effectiveness of a site? You bet! We just switched the Maru-a-Pula site from a stock Web Site Baker template to a professionally-designed look and feel (by Elavacion, Inc.). Check out the traffic results.



The content is identical. The school did not send out an announcement of the new design. People just plain started using it more. What do you think?

Before:




After:

As part of my move to Portland, I recently found out about the open-source lab at Oregon State University (OSU). A first glance suggests that they are involved in all sorts of key open-source projects around the world and at different levels. First off, they lead a number of exciting projects on their own: mostly network management and bug tracking but also the Oregon Virtual School District and a digital library project. Second, they provide hosting services to a wide range of popular projects. I first got a clue about the OSL when I realized that this was where my Drupal download was coming from. Finally, they have an impressive suite of sponsors.

I wonder what potential there is for collaboration with an organization that is already so open with their affiliations and projects. For example, their knowledgebase is powered by a wiki with a better oganizational focus than I have seen before: Confluence. The only drag is that it's very expensive! So much for walking the talk!

Update August 25, 2007: I have got this to work in Drupal with contributed modules and no core modifications! Read the article.

So, you want a walled garden for your community web site, but you still want feeds? We are getting closer to rolling out a podcasting platform for class projects. We want the web site to remain private, because we want the freedom to post student full names and copyrighted content posted under fair use guidelines, but we also want people to be able to subscribe to the student podcasts from iTunes. The solution: a small check for the user agent when determining whether to enforce the walled garden.

For Elgg, I modified line 156 of includes.php to add the conditional statement

!stristr($_SERVER['HTTP_USER_AGENT'],'iTunes')

Now that section reads as follows:

// Walled garden checking: if we're not logged in,
// and walled garden functionality is turned on, redirect to
// the logon screen
if (!empty($CFG->walledgarden) && (context != "external" || !defined("context")) && !logged_on && (!stristr($_SERVER['HTTP_USER_AGENT'],'iTunes'))) {
header("Location: " . $CFG->wwwroot . "login/index.php");
exit();
}


This is a clumsy hack (aren't they all?), because it doesn't allow any other feed reader besides iTunes. Here is a nifty list of user agents from PGTS in Melbourne. And yes, it does open a possible door into the walled garden, but the wall is only meant to provide a little security. If it were meant to be bulletproof, then further security on the web server itself would be required. But at least we can do this podcast project now and still keep the podcasts open to those to whom we provide the addresses!

Another solution would be to remove the walled garden but set the default content permission to 'logged in users.' However, this would give our middle schoolers control over their own content security, which is not exactly what we would prefer to do.

I recently switch our Moodle to databased sessions in order to make it easily available to other queries. Instantly, user sessions would time out very quickly. I eventually found out that this is a Moodle bug, easily correctable by a few lines of code. My solution differed a little from the one in the Moodle bug tracker.

In /lib/setup.php line 305, insert the last three lines listed below.

} else { /// Database sessions
ini_set('session.save_handler', 'user');
// allow config to override PHP if using database sessions, too (Kassissieh)
if (!empty($CFG->sessiontimeout)) {
ini_set('session.gc_maxlifetime', $CFG->sessiontimeout);
}

If I had been blogging back when I made QuickTime VRs, I would have posted these notes a long time ago.

For best results, you need be able to:

• Shoot wide-angle or fisheye shots
  
• Keep the camera level
  
• Shoot at the same angles every time
  
• Stitch the shots into a panorama
  
• Convert the panorama into a QuickTime VR movie
  
• Link different QuickTime VR movies together into a tour.

I did this once with a class a few years ago. I had good experiences with a Kaidan tripod mount and three-stop mount ring, PanoWeaver, CubicConnector and CubicConverter. PanoWeaver was probably the weakest link in this chain, and there are many other stitching applications out there now. The tour itself got out-of-date and was removed, which is something to consider when you do this!

Recommended VR tools: 1 | 2

I have just completed migrating a parent account self-registration script from my old school to the new. It took a bit of work to complete the migration, as I wanted to allow parents to create unique usernames instead of using email addresses, and I wanted the store the accounts within Moodle (on our Linux server) instead of DAF (on a Windows server).

Parent self-registration is one key to fully including parents in a school intranet. With individual logins, parents can access protected resources such as course web sites, information, and forms. Next week, I hope to build consensus for making our internal employee and student photo directories available to the entire parent body. Parents are also immediately identified when using interactive features such as the volunteer registration tool. This makes it possible to have parents to update their personal information online, an enormous time-saver compared to sending out paper forms that parents must complete and return.

This is all possible without much tech support effort. The system registers parents through email verification, and it presents the ubiquitous "forgot password" function when that inevitably happens. If a parent does not have his/her email on file, then they update the appropriate academic office with that information. Flawless.

Our school just made the daring move of changing the primary colors used to represent the school in print and web materials. Note that we didn't actually change our school colors of blue and white, which are still used on uniforms, the school flag, and memorabilia. Most schools chose their colors well before they began to take graphic design seriously.

Microsoft Windows is blue. Mac OS is blue. IBM was once called "big blue." Boys wear blue. Blue is a safely inoffensive color that carefully avoids inspiring passion or enthusiasm. Our new marketing colors evoke the boldness, energy, and warmth of this institution. This statement from the field of color theory explains nicely.

Blue is the color of the sky and sea. It is often associated with depth and stability. It symbolizes trust, loyalty, wisdom, confidence, intelligence, faith, truth, and heaven.

Blue is considered beneficial to the mind and body. It slows human metabolism and produces a calming effect. Blue is strongly associated with tranquility and calmness. In heraldry, blue is used to symbolize piety and sincerity.

You can use blue to promote products and services related to cleanliness (water purification filters, cleaning liquids, vodka), air and sky (airlines, airports, air conditioners), water and sea (sea voyages, mineral water). As opposed to emotionally warm colors like red, orange, and yellow; blue is linked to consciousness and intellect. Use blue to suggest precision when promoting high-tech products.

Blue is a masculine color; according to studies, it is highly accepted among males. Dark blue is associated with depth, expertise, and stability; it is a preferred color for corporate America.

Avoid using blue when promoting food and cooking, because blue suppresses appetite. When used together with warm colors like yellow or red, blue can create high-impact, vibrant designs; for example, blue-yellow-red is a perfect color scheme for a superhero.

Light blue is associated with health, healing, tranquility, understanding, and softness.
Dark blue represents knowledge, power, integrity, and seriousness.

Source: Color Wheel Pro

There is nothing like serving one of your own to draw attention to a neglected issue. I have spent a lot of frustrating time in the last few weeks investigating accessibility in news web sites for our school counselor, who cannot see. George is a whiz with email, but he has never surfed the web due to the frustrations of getting around. What sighted people take for granted -- quickly scanning a page for important visual items -- is a completely different experience with a screen reader. George's program, JAWS, works sequentially from the top-left of a page, which means that it often gets stuck in dozens of links on a page before reaching any useful content at all.

The technology needed to make web sites accessible to George is extremely easy to implement. Simple alt key shortcuts can be implemented on any link. Although the W3C standards include detailed provisions for accessibility, they do not mandate the use of alt key shortcuts, perhaps because they were not in regular use when the standards were first written. In the absence of clear standards, a number of universities in the U.K. publish accessibility guides for their sites, in which they list the alt key shortcuts they have decided to use. No so for U.S. news sites.

One theory out there is that web site developers have been quick to hop on new technologies, such as popup menus, Flash content, and AJAX layers. However, there seems to be a pretty high level of disinterest at play when the New York Times web site does not publish an easy-to-find accessibility guide or use alt key shortcuts. Making sites accessible to the vision impaired does not seem to make the feature list for most major sites.

Fortunately, my search for accessible online news sites ended with a small silver lining. I recall learning a little bit about PDA formatted web sites a few years ago, so I tried to search for PDA versions of popular web sites. These days, these sites are pretty well-hidden from the regular web browser. The Blackberry site does not return a HTTP response. The New York Times site requires TimesSelect login. Fortunately, both Yahoo! and Google news sites work. The Yahoo! site seems the better of the two, storing articles within the site and presenting numbered alt keys that are easy to use.

Another upshot of this experience is that I am going to ensure the accessibility of our Catlin Gabel web site when we redesign its architecture and technology this summer. When you store all your content within a database, it is simple and imperative to provide an accessible formatting option. Even better than allowing visually impaired users to jump over the navigation, why not present a text-only layout with most of the navigation at the bottom. Other options exist as well, such as using div tags to change the order in which screen elements are read. We will learn more about these when we reach the development phase of this project.

Related articles:

Blind Surfers Sue For Accessibility (Oct 2006)

Web News Still Fails Blind Users (Sep 2001)

Here is a tiny hack to show full names for logged in users and first names only for guests. This setup, which is not available in Moodle config, allows the public to view some courses in our Moodle without getting too much information about student identities.

Modify function fullname at line 2301 of /lib/moodlelib.php

if (!isguest()) {return $user->firstname .' '. $user->lastname;} else {return $user->firstname;}

If you use a different fullname format (e.g., lastname, firstname), then you should modify a different line of that function.

Next, I would like to enable a "guest" option for registered users, so that they don't have to enroll in a course in order to view its contents. Or, I could automatically enroll users in courses they select without asking, but that would not be as smooth.

Today, I released a new portal home page based on the one I ran for four years at University High School. This upgrades the previous system in which Moodle handled everything, including home page presentation.



The new home page was inevitable in order to coordinate links to multiple services as we introduced new tools besides Moodle. I accelerated the release of the new page in order to solve a few problems with Moodle-as-CMS. If you have enrolled in a course, then Moodle presents you with a link to it on your home page. However, if you have not yet enrolled in a course, then its link is hopelessly buried among dozens of other courses. An independent portal home page is essential to bring important schoolwide programs to the surface where they belong.

Now for the "portability" part. In this design, I successfully created a feature I had wanted to introduce for a while: a customized home page with links directly to the user's own resources. This is possible through mySQL queries to the databases for different services. In the left column, I decrypt Moodle's userid cookie and then query Moodle to find the user's courses. In the middle, I query our custom public-facing web site to display the school's current calendar and news items. On the right, I query Active Directory in order to customize program links by one's security group (e.g., upper school library link only for upper school students). I could also query Blackbaud here for one's schedule information, but that is on a sub-page for the time being.

Content portability is essential to the success of a good school intranet. As long as data is trapped within each separate system within the site, then content remains fractured. Users have to first know the format of a particular kind of content before they can go get it (e.g., "I know this content is in Moodle, so first I click on Moodle). Since it is unlikely that you can find a single solution that will meet all the needs of a school, multiple solutions are necessary. Getting the multiple solutions to talk to each other is the challenge. Thankfully, good people are working to make it easier. Authentication and subscription plug-ins are becoming more common in major open-source releases, and initiatives such as OpenAcademic promise to make single-sign on and object portability a standard part of commonly available packages.

Security is handled in a manner similar to the Yahoo! model. The Moodle cookie persists longer than the Moodle session, so it is available for use more of the time. Even when you are not logged into Moodle, the portal still knows who you are. This enables the site to personalize your home page content even before you have logged in. Once you actually request one of these personal resources, then you log in. This gets you access to non-sensitive personal resources as quickly as possible.

I am making great progress on our self-made alumni web site. Having dropped NetCommunity a few weeks ago, this solution will provide most of the functionality of NetCommunity while we build a more ambitious long-term vision for the site. This project well predates my arrival to the school, so I think that the alumni and the development office will be pleased to have a site up and running shortly.

I spent the first few hours refining a test query to pull most of the necessary information for one record in Raiser's Edge. I still have to add a few items (ensure alum is not deceased, for instance), but here is the query so far. You may find this useful if you are considering writing your own queries against Raiser's Edge. The optional RODBA (read-only database assistance) module was very useful in helping me piece this together.

SELECT
RECORDS.ID, RECORDS.FIRST_NAME, RECORDS.LAST_NAME, RECORDS.MAIDEN_NAME, RECORDS.MIDDLE_NAME, RECORDS.FULL_NAME,
ADDRESS.ID as ADDRESSID, ADDRESS.ADDRESS_BLOCK, ADDRESS.CITY, ADDRESS.STATE, ADDRESS.POST_CODE, ADDRESS.DATE_LAST_CHANGED,
PHONES.PHONESID, PHONES.NUM,
TABLEENTRIES_phones.LONGDESCRIPTION AS PHONETYPE,
EDUCATION.ID AS EDUCATIONID, EDUCATION.CLASS_OF,
TABLEENTRIES_school.LONGDESCRIPTION AS SCHOOL,
CONSTIT_RELATIONSHIPS.RELATION_ID AS RELATIONID,
CONSTIT_RELATIONSHIPS.IS_EMPLOYEE,
CONSTIT_RELATIONSHIPS.IS_SPOUSE,
CONSTIT_RELATIONSHIPS.DATE_FROM,
CONSTIT_RELATIONSHIPS.DATE_TO,
CONSTIT_RELATIONSHIPS.POSITION,
RECORDS_relations.FIRST_NAME AS RELATION_FIRST_NAME,
RECORDS_relations.LAST_NAME AS RELATION_LAST_NAME,
RECORDS_relations.ORG_NAME AS RELATION_ORG_NAME,
TABLEENTRIES_industry.LONGDESCRIPTION AS INDUSTRY,
TABLEENTRIES_profession.LONGDESCRIPTION AS PROFESSION
FROM
RECORDS,
CONSTIT_ADDRESS,
CONSTIT_ADDRESS_PHONES,
ADDRESS,
PHONES,
TABLEENTRIES AS TABLEENTRIES_phones,
EDUCATION,
TABLEENTRIES AS TABLEENTRIES_school,
CONSTIT_RELATIONSHIPS,
RECORDS AS RECORDS_relations,
TABLEENTRIES AS TABLEENTRIES_industry,
TABLEENTRIES AS TABLEENTRIES_profession
WHERE
RECORDS.LAST_NAME = 'Kassissieh' AND RECORDS.FIRST_NAME = 'Richard' AND
RECORDS.ID = CONSTIT_ADDRESS.CONSTIT_ID AND
CONSTIT_ADDRESS.ADDRESS_ID = ADDRESS.ID AND CONSTIT_ADDRESS.PREFERRED = '-1' AND
CONSTIT_ADDRESS_PHONES.CONSTITADDRESSID = CONSTIT_ADDRESS.ID AND
CONSTIT_ADDRESS_PHONES.PHONESID = PHONES.PHONESID AND
PHONES.PHONETYPEID = TABLEENTRIES_phones.TABLEENTRIESID AND
PHONES.DO_NOT_CALL = '0' AND
EDUCATION.RECORD_ID = RECORDS.ID AND
EDUCATION.SCHOOL_ID = TABLEENTRIES_school.TABLEENTRIESID AND
RECORDS.ID = CONSTIT_RELATIONSHIPS.CONSTIT_ID AND
CONSTIT_RELATIONSHIPS.RELATION_ID = RECORDS_relations.ID AND
CONSTIT_RELATIONSHIPS.INDUSTRY = TABLEENTRIES_industry.TABLEENTRIESID AND
CONSTIT_RELATIONSHIPS.PROFESSION = TABLEENTRIES_profession.TABLEENTRIESID";

I came across two lovely sites today.

asdf I like for the sheer originality of the idea and the play on geek culture. It also happens to be a design company, but that's completely beside the point.

asdf.com













826nyc is the New York spinoff of 826 Valencia, a student writing center that Dave Eggers founded in San Francisco. I really like the graphic design and usability of 826nyc -- a good example to keep for the web design files. The design is coherent and pointed in its emphasis, a welcome change from the trying-to-reach-every-audience flavor of most sites these days.

826nyc.org

A mistake in a SQL query I wrote against Raiser's Edge produced the following result today.

Kassissieh, Richard
Catlin Gabel School (spouse)

Is the server trying to tell me something?

After much deliberation, we have decided to abandon implementation of Blackbaud's NetCommunity product. Over the past year, my colleagues put in a tremendous amount of time and effort to get NetCommunity working to our satisfaction. Much of the hangup was caused by poor control over template design. It was impossible to get NetCommunity to look like the rest of our web site, despite paying Blackbaud to help and carefully examining the dozens of available style sheet modification screens. Another issue was that the web front-end is not attractively designed. For example, directory entries appear in widely spaced columns, with the list sorted by last names but only the first names linked. Finally, NetCommunity does not have career networking capabilities, which are important to us.

We realized that we would get to launch more quickly and present a better interface to our community if I migrated the alumni script I wrote for University High School to the Catlin Gabel web site. My script does the job, is free, uses a single template file, and has more of the features we want. The main advantage of NetCommunity all along was trouble-free import into Raiser's Edge. Now with our alumni director's experience importing data into Raiser's Edge and my experience creating custom web site export files, this is no longer such a great advantage over other options.

Here is an advertisement for NetCommunity from Blackbaud's web site:



Ha ha! Don't you have better ways to spend your time than defeating my critical commentary?

The text underscores a missing piece in Blackbaud's web strategy. Getting data into your database does not by itself lead to a closer relationship with alumni. Instead, turn the equation around and evaluate the web site experience from the alum's point of view. As an alum visiting a web site, what is there of interest? What would motivate you to visit this web site more often? What tangible benefits can this web site provide you? A stronger sense of belonging to a community of former and current teachers and students? A referral to an individual who could help you get a job? A lead on an ultimate frisbee get-together this weekend? A description of what has changed since you left the school, and perhaps more importantly, what hasn't?

Before you ask me for a copy of my alumni web site script, note that it is a lot easier to create a web script for one institution than it is to create it for many. Part of the complication of Blackbaud's product comes from the goal they have to build one alumni web site community tool that will work for a wide variety of schools with different needs. They also cannot consider all the other available resources within the school. For example, we already process credit card transactions manually. We therefore do not need to pay additional service charges to process transactions online. I have already written separate volunteer and event registration scripts. We therefore do not need to duplicate these features in our alumni web site tool. I have found it easier to provide interesting people with attendance or volunteer scripts than with larger, more heavily featured online community scripts such as this alumni tool.

On the technical side, my first objective is to test MSSQL queries using the read-only user that is automatically created during the Blackbaud install process. Limiting this user to only the necessary database tables is a good first step for ensuring data security in our web site. Luckily, I also have a crew of talented web hackers who will test the integrity of our site!

Ultimately, adopting my free, custom tool gives us the flexibility to consider more broadly what we would like to provide to our alumni community. One desirable feature missing from many alumni web site tools is a social networking component. Starting from career networking, we need to develop more ways for alumni with similar interests to get in touch with each other. This could involve using some input fields to create a tag taxonomy and then allow users to search on it. This year, we will undertake consideration of what features would really excite alumni and get them to come to our site more often. It's going to be a fun process!

It also helps enormously that we will stop paying $6,000 a year in license maintenance and support costs and free ourselves from Blackbaud's preferred feature set.