I’ve been working in corporate America for 10 years.  In this time I have seen business of all shapes and size run off of Excel spreadsheets.  Don’t be me wrong, I use Excel as much as the next guy, but when your entire workflow process is focused around updating and tracking information an Excel spreadsheet then you better take a step back and look at what you are doing.

In this post, I’m going to walk through a small project I took on at work to move a department off of Excel to Drupal.  This particular case was a little different then other workflow process automation project I take on as the department came to me looking for an easier way to track information.

Original State:

The Lab Point of Care (POC) department was tracking training session on an excel spreadsheet.  POC is a distributed department with 4 different offices.  2 of the offices shared a spreadsheet and the other two and an identically formatted spreadsheet with information with their location.   The spreadsheet was formatted with each person having it’s own row, and each training session that the person took in a separate column.  All of the spreadsheets are stored on a network share.

Issues with original state:

• The same person could take the same class at multiple locations.  They were only required to take the class at one location.  Since the spreadsheets are not synced, one office would have to open another office’s spreadsheet to verify that the person has taken the class.
• One of the spreadsheets had over 15 thousand rows.  It was slow to load off of the network share.  Due to the size of the spreadsheet, the users would copy the spreadsheet locally and be working off of old data.
• Several users would attempt to update the spreadsheet at the same time.  This would cause file corruption issues and loss of data.

Requirements:

• Easy to search by employee ID or name.
• Ability to quickly add new training session.
  • One session for a day.
  • Multiple sessions for single day.
• Person Data:
  • Employee ID (Unique)
  • Name
  • Location (multiple)
  • Position
• Training Session Data:
  • Training Session Type
  • Certification Length
  • Date
• Reports for training session by multiple criteria.
• Ability to export reports to excel/pdf.
• Upload the current spreadsheets to pre-populate the data.

Plan of Attack:

My main goal was to keep the application was simple as possible.  Most of the application focuses around the Training Sessions screen.  This is the screen where they look up, add/edit training sessions.  Being that this project had very limited time associated to it, I needed to use as many prebuild Drupal modules as possible.  The core of the application is focused around CCK and views.  I used CCK to create the widgets for the forms.  Ajax, Editable and quicktabs were used to help improve UI.  Custom forms (using Form API) combined the views and content type forms on a single page.  The Acquia Marrina Theme from Top Notch Themes (http://www.topnotchthemes.com)

Content Types:

• Person:  Each person has a person node.
• training session:  Each training session is attached to a person.

Main Modules

• Ajax:  Used for UI imporovments.  Gives the ability to add AJAX (http://drupal.org/project/ajax) to any form very easily.
• CCK
  • Content Taxonomy
  • Mulitselect (http://drupal.org/project/multiselect):  Improved UI for Content Taxonomy when using multiple terms.
• Form block (http://drupal.org/project/formblock):  Easily expose a node form to a block.  This was used in combination with Quick Tabs
• Quick Tabs (http://drupal.org/project/quicktabs):  Allow for improvements to the UI.
• Views
• Custom Code

Primary Pages

• Training Tracking Page (Home Page):  Upon first loading the web app, you are giving a page with simple search box (see screen shot).  The user has the option to either type in a few letters of the name or the Employee ID.  The search box is an auto-complete drop down built as a custom form.     Once a person is selected the user presses “search” and it brings up the details for the employee (see screenshot).  At the top of the page the user has the option to either search for another employee or edit the current one.  Under that there is some basic information about the employee.  These fields are pulled from related taxonomy terms.  Quicktabs were used to allow all of the screen to be on the same page.  The first tab is the current sessions and the second tab gives the user the ability to quickly add a new session.  The Ajax module was used to save the info to the database without refreshing the entire page.  This give the user the ability to add training sessions quicker.

• Add/Edit Person Screen:  This is just the add/edit form for person content type.  CCK was used to give the necessary appearance.
• Reports: Views were made with a good amount exposed filters, giving the user the ability to find any necessary information.
• List Management:  The location, position, training session type and certification length are all vocabularies.  The site administrators manage the lists via the core taxonomy forms.

Importing the Old Data

This was the most challenging part of the project.  It required the most custom coding.  The spreadsheets had to be formatted in a way where they could be easily turned in to a csv file.  Once the spreadsheet was in the correct format, the parsecsv-for-php (http://code.google.com/p/parsecsv-for-php/) library was used.  This allows me to easily parse the csv file and place the csv fields in the correct content types.  A little error and duplicate checking and I had myself a nice template to use for future data imports.

Conclusion

The web application has been live for about a month and I’ve already heard how much it has improved their workflow.  The amount of effort that was needed to evaluate the workflow process and create the application was well worth the benefit.  This was the first Drupal application for this company and we are already working on a few others.