Come join the Open Blackbaud group! Adam Gerson and I want to get together individuals who are writing SQL queries directly against Blackbaud database tables. We have found a number of people doing this sort of work on their own, so it seems like a good time to share our new knowledge. Already, Adam, Tom Phelan, and I have posted a number of queries that others may find useful.



tags: blackbaud, open, sql, query

Catlin Gabel is trying something new this year: localizing Blackbaud expertise within the departments that use it the most. One new hire and one internal transfer significantly increased our institutional knowledge of Blackbaud in the development and registrar offices.

Most schools debate the best way to support the administrative database needs of the school. At my previous school, our database administrator worked in the tech department. She wrote custom reports for users with specific needs, managed user accounts and database privileges, and performed annual database tasks such as promoting students during the summer and calculating GPAs. She also championed data entry standards across departments. She is a back-end expert. The best source of information for data entry questions remained Blackbaud itself.

Catlin is taking the opposite approach, cultivating local experts within each department. They have (or will build) knowledge of both user interface issues and back-end technologies such as queries and reports. Big unanswered questions remain. Will these local experts be able to solve most of their own issues in the future? Will they be as friendly to cross-department data entry standards without a tech department expert to champion standards? Will two advanced users have the time to assist the other Blackbaud users in the department? Will generalists in the tech department (such as me) build enough additional knowledge to maintain the server-side technologies and solve the most difficult technical issues? We shall see.

Some have asked how long it takes to write a custom web script for our school. This time, I kept track. It took 2-1/2 weeks of about half-time work to create our new admission script, a relatively large project. That's about 48 hours of work for 2000 lines of code. The project requirements are here.

By any calculations, two weeks of my summer are worth a lot less than the cost of a commercial application that will handle online admission applications and share data with Blackbaud.

I have given a lot of attention to our web-based attendance script this year, partly because knowing who is here is a critical function of the school and partly because we will be moving to 100% online attendance next year when our campus becomes more spread out. Today, I finished adding some new features to essentially the second major release of this script.

The biggest new feature is a more powerful interface for monitoring inputs into Blackbaud. You may know from previous posts that I want to avoid buying additional Blackbaud modules, because their products are very expensive, high maintenance, and not customizable. My little attendance script queries Blackbaud to get class rosters then collects teacher attendance marks for import back into Blackbaud. The only catch is that sometimes our Reception staffers would overwrite an excused absence with a teacher mark, or a teacher mark would not import without notifying us. The new import tool skips records that already exist in Blackbaud and monitors for records that have not successfully been imported in previous imports.

For teachers, the new release has a couple of useful features. First, a teacher may now examine the semester attendance record for any of his/her students. Second, if a student has already been excused for the period, the teacher will see the reason show up while taking attendance. I have wanted this feature for a while. If the school knows that a student is absent, now the teacher will know just as soon!

Students can now check their own attendance records directly, which should serve two purposes. Most importantly, students will be able to monitor their own timeliness and make corrections before an adult contacts them, which is an essential part of our mission to have students take responsibility for their own learning. Second, students will find the errors that we adults make and hold teachers accountable to take attendance every period!

Finally, I am happy that this year has served as an extended pilot for this product, increasing the chances that it will work seamlessly when all of our teachers use it next year.

Now that the school year is well underway, a number of people have proposed new web application projects for me. Thankfully, these appear pretty small-scale and easy to write, given the foundation that we have established with our existing scripts. Otherwise, I would seriously be wondering when I was going to find the time to get this work done.

Course registration: now that I can pull courses from Blackbaud, it should not be so hard to allow students to select courses. So far, it appears that Blackbaud does a good job of assigning courses to departments and years, which will be essential for course registration. I don't want to manually store any of this information from year to year. It should all build dynamically from the Registrar's work.

Diagnosed Learning Disabilities: this would allow teachers to look up the learning disabilities of only the students he/she teaches, in order to disseminate this information only to the people who need to know.

Learning Accommodations Agreement: this new form would allow students and teachers to work together to draft an agreement of the accommodations that a student would be employing in a typical class. Similar to the Independent Study script we employ, it would allow the Learning Specialist and Academic Dean to keep track of the many agreements that would be filed each year (15% of the student body x 5-6 classes per student).

Of course, I'm still tying up current development projects: online admission and attendance both have features that I have yet to write.

These all seem pretty straightforward, since they involve little data manipulation. Mostly, these applications would collect and store form data for later retrieval. Contrast them with an application like online admission, in which the data needs to be heavily evaluated and manipulated before presented for import into Blackbaud. Therefore, these should not take long to write.

Starting tomorrow, I am recommending that all Mac users run Camino or Firefox instead of Safari. It seems that the default Mac browser does not follow standards that much of the rest of the web world subscribes to, disabling useful functions in many critical web applications. And if only the user feedback were meaningful. Blackbaud Faculty Access for the Web times out with Safari. Moodle (or any web app using TinyMCE) doesn't display the text formatting buttons. I don't know what exactly the development team got wrong, but this browser is just not useful in a multiplatform world. Does anyone else know what happened at Apple?

Is there an Open Source Collaboration for Dummies guide? Thinking it would not be so difficult, I made an amateur attempt to start a collaborative open source project for independent high schools last year. I was happy with that which I had authored for UHS, but I wanted to make a better product through collaboration with other like-minded school technology developers. I hosted an open source workshop for interested school technology staff and shared the source code for two projects: community service learning database and student schedule display tool (reads from Blackbaud).

It must require a lot more effort and know-how to start an open source project, for this one never got going. High school tech staff do not have a lot of time to program, so our projects tend to serve our immediate needs. Quite likely, the immediate needs of my school do not perfectly match those of other schools. Also, attempting to start a project within a limited geographic area does not exactly match the strengths of the Internet, where I need to find more like-minded technology directors to get the ball rolling on such collaboration. Finally, I wonder whether my projects are too small in scope, compared to popular open source projects for secondary schools such as Centre and SchoolTool. Larger projects could attract more support.

I am still willing to share and co-develop a suite of co-curricular program administration tools and Blackbaud tie-ins. Let me know if you are interested in joining me.

I just finished a week training teachers to build course web pages. For the first time, I introduced other options than Dreamweaver: Moodle, Plone, and Nucleus blogs. Moodle was the runaway favorite, winning over five of the eight teachers there, most of whom had never seen it before.

Function Wins
Dreamweaver provides much tighter control over page layout and presentation of information, but teachers were excited by the potential to quickly update a site and provide students with places to produce work in their Moodle courses. Much of Moodle's appeal is grounded in its pedagogically sound roots. Moodle was invented by Martin Dougiamas, a Ph.D. student in Australia who wanted to design a better e-learning platform. Many of Moodle's activity modules were carefully designed to encourage students to construct knowledge.

On the technical side, I upgraded to the latest version of Moodle this year, improved my modifications to the graphic design, changed the default language to U.S. English, and improved the registration process so that it would automatically complete the user's profile using information from Blackbaud.

I am excited to see whether this group of teachers constructing courses develops into a critical mass this year.

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!

In July, I will relocate to Portland, the home of Bill Fitzgerald, Jim Heynderickx, and K12LTSP! I have accepted the position of Director of Information Services at Catlin Gabel School, a progressive, "relentlessly democractic," K-12 independent school of 700 students. They have a lot to get excited about: an established tech department of four, substantial technology expertise in the teaching staff, students interested in very technical work, and a pervasive culture of communication and enthusiasm. I will inherit a 1:1 student laptop program for grades 9-12, ironic since UHS just spent the past year considering implementing such a program. The proposal is still currently stuck at the Board level. Catlin Gabel runs Blackbaud, Exchange, and Moodle, just like UHS, and have successfully pulled Blackbaud data into custom web sites. Perhaps coolest of all, Catlin Gabel has a .edu domain! They must have jumped on board very early, before .edu was restricted to higher education.

I will work at UHS through June 30, bringing closure to works in progress and developing a transition plan for my remaining members of department and the future tech director. There will be lots of write about in this space and many thanks to give the good people of University High School and BAISnet before I go. As always, you may reach me through the contact link on this blog.

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!

It is always difficult to prevent summer web development projects from bleeding into the start of the school year, complicating an already very busy time. I gave myself four projects to complete this summer: a new admission inquiry and visit scheduling form for prospective applicants, a new attendance form for teachers, conversion of our student directory from mySQL to MSSQL with Blackbaud table structure, and conversion of the community service learning database from flat text file to mySQL/MSSQL.

Fortunately, I was able to finish new development on three of the four by the start of school and managed to complete the fourth over the Memorial Day long weekend. In addition, the work I have had to complete during school operation has been limited to bug fixes and little feature additions. The worst case scenario happens when the application is not quite finished and I am trying to complete new features while users are hitting the server and the application itself. However, it is also impossible to find all of the bugs until 500 users return from summer vacations and begin to use the scripts.

This portfolio shows major sites I have designed and built for organizations.

Catlin Gabel School (2009-present)
The school's main, public-facing website provides a complete content management system, multimedia publishing tools, user accounts for students, staff, parents, and alumni, and ties into the school's student information system. A layer of the site is login-protected, so that community members may publish articles, photos, and videos to the school community.



Tools: Drupal and Blackbaud, including custom PHP development.

www.catlin.edu


insideCatlin (2006-present)
This intranet web portal provides operational tools for the members of Catlin Gabel School. Teachers provide students with course materials and interactive discussion areas, departments publish commonly-used forms, and individuals schoolwide exchange information such as community service hours and textbook orders. The portal is organized by commonly-desired content and transactions, rather than by tool.



Tools used: Moodle, Drupal, Blackbaud database access, and custom Perl and PHP scripts.

inside.catlin.edu


Shasta Mountain Guides (2005 - present)
The guiding company uses this site to publish trip information, collect reservations, solicit customer questions, and sell merchandise. Customers may browse through trip descriptions, photos, and testimonials and then book a reservation online, including payment. The co-owners maintain the Backcountry Blog and photo galleries on the site. A live weather feed and equipment lists help hikers plan their trips.



Tools: Drupal, osCommerce, custom Perl scripts, custom graphic design

www.shastaguides.com


San Diego Hat Co (2001 - present)
This web site allows the company to quickly publish an online catalog of hundreds of items to wholesale customers twice each year. Within the login-protected site, customers browse or search for hat styles, zoom in to view close-up detail, and see available colors for each style. The site also includes static pages for company information and an online store for retail customers.



Tools: Web Site Baker, osCommerce, custom Perl scripts.

www.sandiegohat.com


Maru-a-Pula School (2006 - present)
The school upgraded its web presence with a content management system and custom graphic design (Elavacion, Inc.). I moved the hosting service to the U.S., to improve upon the reliability of Botswana-based hosting services.



Tools used: Drupal

www.maruapula.org


San Francisco University High School (2002 - 2006)
This site provided for all of the public-facing web site needs of this school community: a description of the school program, faculty and staff contact directory, admission inquiry toolkit, alumni profile and notes tools, and Arts department mini-site.



Tools: Dreamweaver, custom Perl scripts.


insideUHS (2002-2006)
This intranet school portal provided the community with communication and information tools: course web sites, athletics schedules, community service project database, independent study project database, community announcements, schoolwide events calendar, student photo directories, online file access, and student discussion forums.

Tools: Moodle, YaBB, phpBB, FileMan, Blackbaud database access, custom Perl scripts.



site snapshot at The Internet Archive (original site no longer available)


Gateway High School (1999-2002)
The graphic design of this site communicates the school's unique position as a public charter school in San Francisco. Learning Center and People receive high visibility, and student work is featured on the home page.



Tools: Dreamweaver

www.gwhs.org at the Internet Archive

tags: portfolio, webdesign, webdevelopment, perl, php, drupal, moodle, elgg, oscommerce, yabb, phpbb, education

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.

Position Title: DATABASE SPECIALIST
Department: INFORMATION TECHNOLOGY
Reports to: DIRECTOR OF INFORMATION TECHNOLOGY
Start Date: January 5, 2009
Full-time, Exempt

Catlin Gabel School is located in Portland, Oregon. Please apply at http://www.catlin.edu/employment/

Catlin Gabel School extensively and ambitiously incorporates technology into its academic environment as an education tool. Designed to continually pursue excellence in the development of student ability, individual creativity, and the enhancement of teaching practices, the Catlin Gabel technology program supports approximately 900 employees and students as they use 700 computers throughout campus. The school employs a team of skilled IT professionals and implements enterprise systems to support its operations. Catlin Gabel teachers and staff form a supportive community of professionals dedicated to individuality and lifelong learning.

POSITION SUMMARY
Administer the deployment, support, and maintenance of schoolwide database systems. Coordinate the use of database systems across campus. Provide database training and communicate professional development opportunities to administrative staff. Provide direct, general technical support to faculty, staff, and students.

ESSENTIAL DUTIES
•Manage schoolwide database systems and applications (primarily Blackbaud but also including Ceridian, AuctionPay, Follett Destiny, Nutrikids, open-source web applications, and others);
•Coordinate schoolwide database use with other staff members, especially the Schoolwide Education Edge Coordinator, the Development Services Manager, and the Admission Data Coordinator;
•Configure reports, exports, imports, and queries in response to division and department requests. Develop custom reports using Crystal Reports;
•Keep database systems up-to-date with latest patches and updates as appropriate;
•Maintain database integrity and security, including backup;
•Install and uninstall client and server database applications as needed;
•Assist users with data cleanup primarily through queries and search/replace;
•Support efforts to link web site and school database systems;
•Design data entry standards and monitor users’ adherence to them;
•Design and provide training to users of schoolwide database systems;
•Facilitate and encourage user access to third-party professional development opportunities;
•Create strategic plan for the evolution of database systems at Catlin Gabel;
•Evaluate new database technologies for their potential to support Catlin Gabel school operations;
•Integrate different database systems with an eye toward efficiency and practicality;
•Write and assemble documentation and training materials for the use of database systems;
•Troubleshoot and solve routine technical issues that faculty, staff, and students face;

SCOPE OF WORK
Manage schoolwide database systems, primarily interface with school administrative staff.

SUPERVISORY RESPONSIBILITIES
None

INTERPERSONAL CONTACTS
Daily interaction with faculty, staff, students, department colleagues, volunteers, and vendors.

SPECIFIC JOB SKILLS
•Strong skills and direct experience with Microsoft SQL Server essential. Familiarity with mySQL for Linux, and FileMaker Pro desirable.
•Application of best practices to database design and maintenance.
•Prior experience with Blackbaud Raiser’s Edge, Education Edge, and Crystal Reports highly desirable.
•Productivity applications including Microsoft Office. Ability to troubleshoot and resolve common Windows software and PC hardware issues. Ability to design and provide effective training to colleagues.
•Ability to articulate technical concepts and solutions to a diverse range of users;
•Capacity to cultivate collaboration and buy-in to schoolwide standards and practices;

EDUCATION AND/OR EXPERIENCE
Bachelor of Science in Computer Science from an accredited college or university, or equivalent experience. Training in computer programming, systems analysis/design and/or management, with a minimum of five years of database management or equivalent experience. Experience with Blackbaud applications highly desirable.

WORKING CONDITIONS
Daily interaction with faculty, staff, parents, vendors, trustees, and volunteers. On-call for problems. If a mission critical job in the school is jeopardized by a system or network malfunction, the problem must be resolved as quickly as possible. This could take place during non-business hours.

-----------------------------------------------------------------------------------------------------------

Catlin Gabel School believes that each employee makes a significant contribution to its success. That contribution should not be limited by the assigned responsibilities. Therefore, this position description is designed to outline primary duties, qualifications and job scope, but not limit the incumbent nor the organization to just the work identified. It is our expectation that each employee will offer his/her services wherever and whenever necessary to ensure the success of our endeavors.

Catlin Gabel is an equal opportunity employer committed to hiring and supporting a diverse workforce.

tags: database, specialist, dbadmin, jobs

Here the code we use to pull user name information from Education Edge into the Moodle user profile. This code starts at line 320 of moodle/user/edit.php. The bit about @sfuhs.org allows me to handle Moodle accounts for both AD users (students, faculty) and non-AD users (parents), who login with their personal email addresses.

// pre-fill user information
$user-
if (!strpos($user-
$query = " SELECT
EA7RECORDS.FIRSTNAME, EA7RECORDS.NICKNAME, EA7RECORDS.LASTNAME
FROM
EA7RECORDS,
ADDRESSLINKS,
PHONELINKS,
ADDRESSLINKPHONES,
EA7ADDRESSOPTIONS
WHERE
NUM LIKE '" . $user-
ADDRESSLINKPHONES.PHONESID=PHONELINKS.PHONESID AND
PHONELINKS.ADDRESSLINKSID=ADDRESSLINKS.ADDRESSLINKSID AND
ADDRESSLINKS.PARENTRECORDID=EA7RECORDS.EA7RECORDSID AND
EA7ADDRESSOPTIONS.ADDRESSLINKSID=ADDRESSLINKS.ADDRESSLINKSID AND
EA7ADDRESSOPTIONS.PRIMARYADDRESS='-1'";

mssql_connect ( "$eesrv", "$eeuser", "$eepass" );
$result = mssql_query ( $query );
$row=mssql_fetch_array($result);
if ($row['NICKNAME']) {$row['FIRSTNAME']=$row['NICKNAME'];}
if ($row['FIRSTNAME']) {$user-
if ($row['LASTNAME']) {$user-
mssql_close();

include("edit.html");


I then modify the edit.html template so that the name fields are not editable, consistent with the idea of our users being fully identified by name within our web intranet. This goes hand in hand with automatically capturing the user login from the environment variables, which is subject matter for a separate post.

I have written before about the practice of using environment variables to identify authenticated users in our school intranet. Now, finally I have come across an open-source package that uses this method, too! I have installed DokuWiki on our internal server for a collaborative project our diversity club students are starting on San Francisco neighborhoods. This will replace our old UseModWiki that we have had in place for three years.

DokuWiki automatically picks up the $_SERVER['REMOTE_USER'] environment variable, which is set on our IIS server for protected directories. Right out of the box, we get a wiki script that can stay private within our network and automatically identify the authors of all of the wiki edits.

Two small edits were immediately helpful. The first was to remove the domain from the userid, so that it would be shorter and easier to read. The second was to insert a database query against Blackbaud to show the real name of the currently logged in user. The second edit may not ultimately prove that useful, as the userid seems to be used throughout the script and the real name only sparingly.

Interestingly, DokuWiki tracks the current user by setting the REMOTE_USER environment variable if the server folder does not require authentication and DokuWiki is set to use internal authentication instead. For example, if you turn access control on and then log in, DokuWiki sets the environment variable to the new userid. Most programs use a cookie for this purpose.

I was hoping to give everyone editing privileges to the entire wiki, but it is likely that the diversity club will want to maintain editorial control over the content that it is currently working hard to assemble. Toward this end, I have enabled access control lists, but this stops the script from using REMOTE_USER by default, in order to require login first. Too bad! If I want to use access control lists, I will have to modify this script in the same fashion I have modifed the others, pre-empting the login form by capturing REMOTE_USER and creating the logged in state before the script checks for that.

Look for references to REMOTE_USER in /inc/common.php to find examples of its use in DokuWiki. This is where DokuWiki first determines user identity and invokes an authentication process if necessary.

After two years of periodic discussion with the academic office, we are about to launch a new online course request form. Up to this point, a number of obstacles prevented us from moving forward:

1. This is a critical school function that everyone uses. We had to get the user interface right.
   
2. Only once we moved to Education Edge 7 (last spring) did it become possible to pull the current course list dynamically.
   
3. Our registrar was concerned about losing valuable face-to-face discussions between students and advisors.
   
4. The academic office didn't want to be the guinea pig for student online registration (I am reading between the lines).

Two years ago, it would probably have taken a month to write this script. This time, I wrote about 80% of it in a day! Granted, I did work at home, almost completely free from interruptions that normally make development work nearly impossible during the school day. The result is a tidy little script whose user interface hides much of the complicated work going on behind the scenes.



Data is written to a mySQL database, from which the registrar can produce export files for import into a FileMaker database she uses to review requests before sending them to Blackbaud for scheduling. The MSSQL query that produces the course lists is especially cool. I reproduce it here in case you will find it useful:

$query = "SELECT DISTINCT
EA7COURSES.COURSEID,
EA7COURSES.COURSENAME,
EA7ATTRIBUTETYPES.DESCRIPTION AS ATTRIBUTE,
TABLEENTRIES_2.DESCRIPTION AS DEPARTMENT

FROM
EA7COURSERESTRICTIONS,
EA7ACADEMICYEARS,
EA7COURSERESTRICTIONSSTARTTERMS,
EA7TERMS,
TABLEENTRIES,
EA7COURSEGRADELEVELS,
TABLEENTRIES AS TABLEENTRIES_1,
EA7COURSEFILTERS,
FILTERVALUES7,
TABLEENTRIES AS TABLEENTRIES_2,
EA7COURSES
LEFT OUTER JOIN EA7COURSEATTRIBUTES ON EA7COURSES.EA7COURSESID = EA7COURSEATTRIBUTES.PARENTID
LEFT OUTER JOIN EA7ATTRIBUTETYPES ON EA7COURSEATTRIBUTES.ATTRIBUTETYPESID = EA7ATTRIBUTETYPES.ATTRIBUTETYPESID

WHERE
EA7COURSES.EA7COURSESID = EA7COURSERESTRICTIONS.EA7COURSESID AND
EA7COURSERESTRICTIONS.EA7ACADEMICYEARSID = EA7ACADEMICYEARS.EA7ACADEMICYEARSID AND
EA7ACADEMICYEARS.DESCRIPTION = '$currentacademicyear' AND
EA7COURSERESTRICTIONS.EA7COURSERESTRICTIONSID = EA7COURSERESTRICTIONSSTARTTERMS.EA7COURSERESTRICTIONSID AND
EA7COURSERESTRICTIONSSTARTTERMS.EA7TERMSID = EA7TERMS.EA7TERMSID AND
EA7TERMS.TERMID = TABLEENTRIES.TABLEENTRIESID AND
TABLEENTRIES.DESCRIPTION='$currentsemester' AND
EA7COURSES.EA7COURSESID = EA7COURSEGRADELEVELS.EA7COURSESID AND
EA7COURSEGRADELEVELS.GRADELEVEL = TABLEENTRIES_1.TABLEENTRIESID AND
TABLEENTRIES_1.DESCRIPTION='$student{'GRADELEVEL'}' AND
EA7COURSES.EA7COURSESID = EA7COURSEFILTERS.PARENTID AND
EA7COURSEFILTERS.FILTERSID = FILTERVALUES7.PARENTID AND
FILTERVALUES7.FILTERIDVALUE1 = TABLEENTRIES_2.TABLEENTRIESID AND
FILTERVALUES7.RECORDTYPE = '1098'";

The Departments designation for a course is stored in a strange place with a ton of other attributes, hence the "RECORDTYPE='1098' condition. We include COURSEATTRIBUTES as an optional join because we store the "signature required" course option there.

We will pilot this script with a small number of users next week and then hopefully roll it out to everyone in mid-April.

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.

Here are notes on my successful import of 200 classes into Moodle. This is part of a project to create a major assignments calendar for our upper school that calculates the number of major assignments by student rather than by grade, providing a more accurate view of potential conflicts before they happen.

First, I wrote a Perl script to export classes from Education Edge into a text file. I used the following SQL query and then formatted the results pretty.

SELECT DISTINCT
ea7classes.ea7classesid,
ea7courses.coursename,
deptentries.description as coursedept,
ea7courses.ea7coursesid,
ea7courses.courseid,
ea7classes.classsection,
periodstable.entryid as period,
ea7faculty.userdefinedid as facultyid,
ea7records.nickname,
ea7records.lastname,
ea7records.passportnumber as facultyusername,
ea7rooms.roomid

FROM
ea7courses
inner join schools on (schools.schoolsid = ea7courses.schoolsid)
inner join ea7classes on (ea7classes.ea7coursesid = ea7courses.ea7coursesid)
inner join ea7sessions on (ea7sessions.ea7sessionsid = ea7classes.ea7sessionsid)
inner join ea7academicyears on (ea7academicyears.ea7academicyearsid = ea7sessions.ea7academicyearsid)
inner join ea7classterms on (ea7classterms.ea7classesid = ea7classes.ea7classesid)
inner join ea7terms on (ea7terms.ea7termsid = ea7classterms.ea7termsid)
inner join tableentries as termstable on (termstable.tableentriesid = ea7terms.termid)
left outer join ea7coursefilters on (ea7coursefilters.parentid = ea7courses.ea7coursesid and ea7coursefilters.filtertype=163)
left outer join filtervalues7 on (filtervalues7.parentid = ea7coursefilters.filtersid and filtervalues7.recordtype = 1098)
left outer join tableentries as deptentries on (deptentries.tableentriesid= filtervalues7.filteridvalue1)
left outer join ea7classtermmeetings on (ea7classtermmeetings.ea7classtermsid = ea7classterms.ea7classtermsid)
left outer join ea7timetableentries on (ea7timetableentries.ea7timetableentriesid = ea7classtermmeetings.ea7timetableentriesid)
left outer join tableentries as periodstable on (periodstable.tableentriesid = ea7timetableentries.period)
left outer join ea7facultyclasstermmeetings on (ea7facultyclasstermmeetings.ea7classtermmeetingsid = ea7classtermmeetings.ea7classtermmeetingsid)
left outer join ea7rooms on (ea7rooms.ea7roomsid = ea7classtermmeetings.ea7roomsid)
left outer join ea7faculty on (ea7faculty.ea7facultyid = ea7facultyclasstermmeetings.ea7facultyid)
left outer join ea7records on (ea7records.ea7recordsid = ea7faculty.ea7recordsid)

WHERE
schools.schoolid = 'US'
and ea7academicyears.description = '2008-2009'
and ea7courses.ea7coursesid

ORDER BY
ea7classes.ea7classesid,
ea7classes.classsection

Then I installed and ran Moodle's Upload Courses contributed module. What a nice job the author did with this. Once I cleared all of the ambiguous teacher names, the courses imported in a flash.



I decided to create one Moodle class for each course/teacher combination. In other words, if one teacher teaches one section of ninth grade English, and a second teacher teaches two sections, I would create two Moodle courses. Teachers are pretty autonomous at our school, and this structure parallels what most teachers did with Moodle when I created courses only upon request. In cases where the teachers actually co-teach the course, I will need to remove one course and double up the teachers in the other remaining.

I embedded teacher initials in course IDs to distinguish them and provide a key for subsequent Education Edge searches based on this information, but will have to go back and embed them in the course fullnames as well -- the students can't currently tell the courses apart, because they only see the full names!

Rather than importing enrollments as well, I will allow students to enroll themselves on the first day of classes.

I edited config.php to show a limited set of default blocks for all the new courses. Unfortunately, the major assignments block I am working on did not show up, so I will have to add that subsequently with a script.

I am still working on a major assignments block for our Moodle that borrows concepts and SQL queries from Gary Anderson and pulls course enrollments from Education Edge. More on that later.


tags: moodle, blackbaud, educationedge

I am leading an internal team to move the public-facing web site of our PS-12 independent school to Drupal. In this post, I share accomplishments and decisions made so far in an attempt to spread any knowledge that is useful to other institutions and gain your feedback. I consider myself an intermediate Drupal user, so some of the following may seem trivial to advanced users, whereas beginners may find it helpful as they get started. A test site is available for you to view. I have included module and content type lists at the end if you would like to jump directly to them. Many thanks in advance for any advice or feedback you may provide!


Page and News content types

We recently made the decision to give news more prominent billing than we do in our current site. We realized that news is more important than calendar information, for instance. At the same time, we will still require the ability to post descriptive content about school programs that doesn't change all too often. First-time visitors to our school will still require this, and they are a very important audience. How could we provide for both within Drupal's structure?

We want to have as many school departments and divisions manage their own site content. Therefore, adding pages to the Book hierarchy or adding News items to one or more sections needs to be as easy as possible. In Drupal 6, Book nodes can be of any content type. This will serve us later, though for the time being I am only adding Page nodes to Books. I did modify the Page content type to allow comments and add fields for multimedia content. I configured Book to automatically create menu items, as we don't want to trouble our site editors around campus with navigating Drupal's menu administration pages, which will get quite long in a large site.



I have included News content by embedding a manual PHP-driven database query in each top-level landing page. Users will click on a primary nav link and find a page with the category outline in the left-hand menu and the news for that section in the body of the page. News can be posted to multiple site sections (taxonomy terms), whereas Pages can only be posted to one place in the book hierarchy. Our web site editor was very pleased to see how easily we could move pages around the hierarchy, even from one book to another.

Will we want to post a single page to multiple books? I am not sure that this is desirable, since users may benefit from a predictable hierarchy (where is that page? what section am I in?). However, we may also find a way to configure this in Drupal, or we could write a script to pull content from the desired node when in a specific page. The ability to insert any node into a book is going to be a great boon if we need to create a custom content type with code to pull content from other nodes.

Book Access has been troublesome so far. I at first assumed that we would want to grant editing privileges at the book level, to those people who need to able to edit that book. However, not only has book access not worked as advertised, but I have also come to the realization that more open access may in fact better serve our purposes. We know and trust our web site editors, and time is precious. Why shouldn't all users blessed as editors of our books be able to edit any of them? Some institutions have even wikified their entire site. We can mitigate (unlikely) problems by turning on revisions and setting actions to notify the web site editor when changes are made to the books. Presto.


Classroom and team pages

Organic Groups seems the obvious choice for classroom and team pages. Small communities build up around classrooms and teams at our school. Parents want to know news and upcoming dates, teachers and coaches want to contribute content, there are scores and reports to publish, and we want to expose most of this to the public for people to see our school in action.

Organic groups allows us to mark some content as public and some as private, maintain descriptive and news items, invite others into the group, and allow users to maintain short lists of their favorite groups in the site. I will want to further investigate how to set multiple moderators for a single group, suppress other groups from the audience list, and make subscription management easier.

Organic groups could also permit other affinity groups to spring up within the school around issues, initiatives, or interests. Again, it helps enormously that community features are core to the Drupal ecosystem. These features are well developed, well-documented, and widely used, making it easier for us to make our next web site more capable of community strengthening functions.


Media

On our current site, the home page feature image is randomly selected from a set of photos. Each photo has as caption. I am experimenting with enhancing this feature by: 1) using a Flash-based slideshow to cycle through images on the home page; 2) linking each image to related pages within the site, so that it also serves as a navigational element. The test site currently uses MonoSlideshow, but Slideshow Pro also has a standalone version that pulls image information from a XML file and a Drupal module. I would like to take this one step further by writing an extension to automatically updated the XML file based on the contents of a particular image gallery.

Outside of the home page, I am using the Image module, including galleries. I have not yet seen the need to go to ImageCache and appreciate the simplicity of automatic creation of gallery pages. If Image Gallery Access works as advertised, then I will be able to distinguish albums to which ordinary, authenticated users can post from those that are reserved for web site editors. FUpload appears to provide batch uploading that should work nicely for site editors, parents, and students (should I worry about Flash version compatibility?). Missing at the moment is the ability for an authenticated user to create a new image gallery, which would be great if someone is posting sports photos and wants to create a new sub-gallery for each game. If we decide to limit the number of photos posted on this server, then prolific photographers may be better off using Flickr, anyhow.

Embedded Media Field appears to work great, except that I can't figure out a way to wrap body text around the embedded item. This may be fine for relatively unprivileged, authenticated users but probably not sufficient for web site editors.

I have recently switched from TinyMCE to FCKEditor and am loving it so far. Everything seems to present and work better with FCKEditor, especially embedded images. Do you know of a way to limit file browsing capability to user directories for some roles? I wouldn't want any user to have access to the primary embedded image store for the site. I would also like buttons and filters to elegantly embed files, uploaded media, and third-party media all within the WYSIWYG interface. I wonder how difficult it would be to write those extensions and make them available to some roles and not others.


Roles

Drupal, as community software, offers the exciting opportunity to invite many different constituencies in our community within the site and provide features such as comments, blogs, directories of people, and photo upload privileges specifically to them. I have created the following roles so far:

anonymous user
authenticated user
administrator
alumnus/a
applicant for admission
faculty/staff member
job applicant
parent
student
web site editor


User Profiles

I haven't begun to explore this yet. In some cases, the user profile is a critical function. For example, we want alumni to edit their information through the site: contact details, schools attended, place of employment, interest in the career network, etc. Faculty members will have short biographical passages to help describe themselves. This means that some roles will use different profile fields from others -- I need to learn how to make that happen, and whether to use nodes for user profiles in these cases. On a related note, real names will need to be visible throughout the site -- I have used Authorship for this before and will need to evaluate it and investigate alternatives once more. Authorship does not have a Drupal 6 version at this time.


Commenting

Opening commenting is a really exciting opportunity. We currently do not have this feature in our current site, yet we know that our users have a lot to say, and we want to draw them more tightly into the school community. At the same time, independent schools try to maintain a decent level of control over publicly-available content. Drupal's commenting system seems perfect for this -- allow all authenticated users to see and post comments, allow all web site editors to administer comments, and do not use a moderation queue at all. Done.

One downside I can see at this time is that a blog author may in the future want the ability to moderate their own comments. I'm not sure whether this would require a lot of hoop-jumping-through in Drupal, as compared to a blogging platform such as Wordpress.


Calendaring

I have done a little investigation here, not very much. I have set up Calendar and CCK Date. I am finding setting up calendar Views to be bit involved. We will make extensive use of list views of calendar items by taxonomy terms in blocks throughout the site.

The custom content type for Athletics events looks great -- opponent (node reference), bus departure and return times, result, score, notes will serve their function well. One glaring omission for Drupal 6 is Time. The last I read, developers are testing a Drupal 6 version. We will need this CCK field in order to have additional times for the day of the event (such as bus departure and return times).

We have yet to decide whether Drupal's calendar could meet all of our internal, public calendaring and resource reservation needs, or whether we should install a proper calendar server.


Migrating Custom Functionality

We have built over the years a lot of custom PHP and Perl scripts that we plan to migrate into Drupal over time. Many of these will wait until year two or three of the project. They function fine now, and we have to first roll out the core functionality of the site.

Applicants for admission can complete an inquiry form, sign up to visit the campus, and download admission forms online. All of these functions pull data from our Blackbaud database in order to function. We could migrate these (rather large) scripts into Drupal, gaining additional benefits: applicants for admission would become authenticated users and be able to read and post comments, gaining greater visibility into the site.

Our current volunteer signup and management system keeps track of multiple events, caps signups for specific time slots in order to automatically distribute volunteers to where they are needed, and produces summary lists for volunteer coordinators. If Signup can do all this, then we will move this feature to Drupal in a flash.

Job applicants can, using another site, view and apply for jobs at the school. This seems like a prime candidate to move to Drupal, which has better designed file submission features than our current system. It will be key to leverage Drupal to provide good workflow management functions for the human resources office -- the ability to flag applicants for certain categories, add notes to applicant files, invite supervisors to review applicants online, and send mass emails to those declined for the position.


Social network sites

Connecting with constituents through social network sites is a hot issue right now for independent schools.We want to meet our constituents where they are, in addition to drawing them into our site. At the same time, we want to leverage existing content and processes as much as possible while making this happen. I am happy to find out about Ping.fm, which should allow us to automatically generate Twitter, Facebook, and other status updates from specific News items that we post to our site. With no additional effort, we will broadcast our news items to our users' communities and cultivate follower lists around the web.


Content types

Here is a list of content types in our test site so far.

Athletic event
Blog entry
Calendar event
Classroom (node for organic groups)
Image
News item
Opponent
Page (modified to serve as the main content type for descriptive pages)
Team (node for organic groups)


Modules

(so far)

autosave
book_access
calendar
cck
date
devel
emfield
fckeditor
filefield
image
image_fupload
jquery_media
ldap_integration
lightbox2
messaging
notifications
og
print
scheduler
simplemenu
slideshow
token
views


tags: drupal, school, webdesign, webdevelopment, community, fundraising, nonprofit

I presented this talk today at the BAISNet meeting on open-source software hosted at Lick-Wilmerding School. Some of the links point to internal, password-protected resources. Contact me if you would like a tour.

In the early days of the web, schools designed and developed their own web sites. Then we purchased professional graphic design and content management systems. In recent years, full-service companies have sprung up to deliver schools a complete solution. This is one possible -- but not the only -- modern solution for a course web site. How did we move so far away from managing our own web sites? Is it possible to turn back?

With a little DIY spirit and open-source software, it is possible. Open-source content management systems have matured and arrived. They are now a viable choice for a professional, modern, effective school public-facing web site. In a time of cost consciousness, it is imperative for us to learn this field and build our own web sites once again.

We know how to evaluate and tell other people about commercial software. Read the company's sales materials, ask other schools for references, review a live demo or an existing site, determine the terms of a contract, monitor the site as it is developed/delivered, contract with a trainer. After the site is launched, requests tweaks/new features as desired.

The open-source ecosystem is very different from the familiar landscape of commercial software. Open-source software does not have huge advertising budgets. You won't see The Network or The Trusted Voice or taste tests.

The key to evaluating open-source software is to enter the community and try the software. Take Drupal as an example. You can search the forums for helpful community members, view everyone's support issues, browse hundreds of community-contributed modules, read community-authored user and developer guides, read case studies by real users, join affinity groups, and build a test site to evaluate the suitability of the software for your purposes. Hire a Drupal developer or another if you like.

You may have concerns about running your public-facing web site on open-source software. That's all right. Start with lower-stakes projects and get to know the software and the process well. Build up your own familiarity and comfort level through experience.

Drupal.org has over 350,000 registered users. Version 1.0 was released in 2001. The current version is 6.9.

Peer schools using Drupal include Burke's, Seattle Academy, Billings, and Meridian. College Prep uses Plone. Notable, Amherst College runs its main, public-facing web site on Drupal.

Other options: Plone, Joomla, Wordpress

Once you go through this process and become a convert, how do you convince others? It may help to guide your school colleagues through a "lite" version of the steps you experienced. At Catlin Gabel, we formed a committee of ten major stakeholders in the web site project (admission, development, human resources, technical staff). I added to this group a parent who also works for an organization that provides technology consulting services to environmental organizations. We redesign our web site about once every six years. Drew redesigns organization web sites as his daily work. One of the most compelling answers was, "What about all of the R&D that companies put into their products? Open source communities probably devote a hundred times' R&D into their 'products.'"

Social Features

I pushed Drupal at my school because of its emphasis on social features. The White House has a blog. The Vatican has a YouTube channel. 40-somethings are joining Facebook in droves. Now is the time to introduce social features to your public-facing web site. Drupal is built around social features. Blogs, RSS feeds, tags, media support, and a powerful content management system are all strengths in Drupal.

Education-specific Drupal distributions: Social Media Classroom, DrupalEd, FunnyMonkey Commons. Will we see a distribution specifically for a school's public-facing web site? Shall we create it together?

At Catlin Gabel, I first launched a community intranet portal to facilitate anyone's media publishing. This site is organized around media types. All users may post articles, audio files, movies, and calendar entries, maintain a blog, and build an electronic portfolio. We also host other community content here, such as a carpool map, school archive, and summer jobs bulletin board.

With this experience under our belts, we are now taking on our main, public-facing web site. We plan to fully integrate community features throughout the site, not just in a dedicated section. Our constituents seek to engage with the school through its web site. Middle and upper school students seek authentic representations of student experience at the school. Commenting on all News articles. Making comments visible to all registered users. Providing user accounts to faculty, staff, students, alumni, parents, applicants, board members, and friends of the school. Approved "site editors" will be able to edit all static pages (not restricted to own department). Every user will have a blog, and blog content will be aggregated by topic (tags or content searches). Existing newsletters will fold into the blog and news functions.

Specific Features of Drupal

Drupal has a very small core and then hundreds of contributed modules. Let's take a look at the core.

• River of news


• RSS feeds


• User profiles


• Search


• Blog for each user


• RSS aggregator


• Forums


• Commenting


• Contact forms


• File upload


• Taxonomy (tagging)


• Hierarchical content type (book)


• Menu, theme, and content independent of each other


• Revisions

Now let's take a look at some commonly-used contributed modules:

• Content Construction Kit (CCK) -- page, news item, calendar event, athletic event, trip, curriculum map entry ...


• Views -- define criteria to select content and then a format to display it


• Calendar -- control over categories, display formats


• Image -- image galleries, embedded images


• Organic Groups -- classrooms, clubs, teams, affinity groups


• Notifications -- email subscriptions by content type, keyword, or author


• WYSIWYG editors -- button-level control, link buttons to custom code


• LDAP integration -- same username/password as your other network systems


• JavaScript functions (jQuery) -- usability enhancements (AJAX)


• Wiki -- wiki markup if desired, otherwise revisions + editing rights + diff


• Page functions -- print, email, PDF

Fine-grained control

• Position of administration functions


• Per-item control of menus (user and administration)


• Role definitions and permissions


• Per-field control over content definitions


• Ability to embed PHP code and write custom modules (Blackbaud integration)

Exciting additional features demonstrate the Drupal community's ability to integrate promising new technologies.

• Flash-based media players


• Embedded media fields


• Ping.fm

Getting Started

My journey: insideCatlin, public-facing shadow site, Shasta Mountain Guides, 3D Cell Explorer, San Diego Hat Co, summer school registration, test installation for new site

Lessons learned: start small, grow gradually, distinguish yourself with modern features, don't just talk about open-source, rather provide hands-on demos.

That's it for this session. Will get into specific site design issues in the second session.

Questions

tags: drupal, drupaled, webdesign, webdevelopment