Recently in Staff Database Category

How to print the GPS Phone List

On the Staff tab...

  • Go to the List view by clicking the "View As" buttons beneath the button bar.
  • Click the "GPS Phone List" button at the bottom of the list.

About the Phone List

  • The GPS Phone List is programmed to show active staff members in the major GPS units. For ISSS, active students are also included.
  • ICGC is not included on the Phone List. If, in the future, they ought to be included, ask IT to update the script.
  • The order and position of the units on the Phone List is determined by two fields in the Unit table. Go to the list view in Units and manipulate the two fields: Phone List Column and Phone List Order. Keep a preview of the Phone List open in another window so you can see the effect of your changes in real time. Make sure each unit has a unique value in the Phone List Order field.

Close-Out: Staff Database


75 hours since June 7, 2010.

Keys and SWGs

I added the ability to indicate the chair of a SWG. Notice a checkbox next to their name in the form view of the SWGs tab. Chairs, or if more than one, Co-chairs, will show on reports and in the Keys and SWGs sub-tab in the form view of the Staff tab.

I uploaded Key information from the old GC Records database. You can print a list of who has keys in two places:

  1. bottom of the list view in the Staff tab (prints key information for all staff, regardless of unit)
  2. bottom of the form view in the Unit tab (prints key information for staff in single unit)

Notes about Key Information

  • The "Checked Out" date was empty for most of the historical records. I populated the missing dates with yesterday's date.
  • It appeared in the historical data that keys weren't marked returned, instead fields storing key information were cleared out. It's better to fill in a "Returned" date - that way we preserve the history of who had what key when.

Peoplesoft remains the definitive record for absence reporting. The Absence Card Reporting feature in the Staff database is meant to easily track who hasn't turned their absence card, and for this it works well enough. It succeeds primarily in reducing clerical time and generating friendly reminders to those who have fallen slightly behind. But it has shortcomings. It's important to understand what those are and how to work around them.

Long Absences

The system doesn't handle long, excused absences. Some people are not required to turn in absence cards because of illness or FMLA. The system does not provide a way to exempt a range of dates from someone's list of missing cards. The person will continue to appear on the Missing Absence Cards report and will receive Missing Absence Cards emails. There are two ways around this:

  • Reset the "Starting with Date" value. This tells the database to disregard missing absence cards before that date. Be cautious however; if the person is legitimately missing an absence card before their prolonged, excused absence, it will no longer be captured in the Missing report and email.
  • Mark the absence cards as received.

Built for LAC

Only the Learning Abroad Center tracks absence cards in this fashion. If other units began using the system I'd want to modify it so the user can isolate staff by unit.

Wipes History When No Longer Needed

If you no longer need to track someone's absence cards, you flip the "Track Absence Card?" field to No. This deletes (with no undo) the history of turned-in absence cards. There is a dialog box warning you.

Due Dates We Care About

This is a list of dates the user maintains. The dates are Sundays and represent the last day of the pay period. You must enter dates with leading zeros and 4-digit years, eg. 03/09/2010. This list of dates is used in the database three ways:

  1. It builds the pop-up list at the top of the "Record Absence Cards" window.
  2. It builds the drop-down list for the start date in the Absence Cards section of the Need-To-Know Basis tab.
  3. It serves as the standard against which each person's list of missing cards is judged.

How "Due Dates We Care About" is Quirky

  1. While it's a list of dates, I had to store it as a text field, resulting in it sorts in alphabetical order rather than chronologically. Some of this is solved by entering dates with leading zeros but it doesn't help across years. Dates in January 2011 are mixed in with dates from January 2010.

  2. If you remove dates for which people are missing absence cards, the database will no longer report them as missing. Meaning, this is not a great tool for tracking severely delinquent absence cards.

  3. If you enter a future date, the system will report that people are missing absence cards for that date.

Absence Reporting: How It Works

The Staff database has the ability to track those who turn in absence cards. Not all units track absence cards so the database is selective about whom to track.

Set Up

To tell the database to track someone:

  1. Navigate to his or her record and click the "Need-To-Know Basis" tab.
  2. In the Absence Cards section, answer "Yes" to "Track Absence Cards?"
  3. Select a Starting Date. The starting date represents the first date the person should provide an absence card. If for example, someone starts in July, they're not obligated to turn in an absence card for June.

Recording Cards

As people turn in absence cards, record them in the database:

  1. Select "Record Absence Cards..." from the Scripts menu.
  2. Select a due date from the pop-up list at the top of the window. If the due date you need is not a value in the list, add it to the bottom of the "Due Dates We Care About" field on the right.
  3. Click the names of people who have turned in their absence card for that due date. When you click their name, it disappears from the "Missing" list and appears in the "Received" list. If you make a mistake, click their name in the "Received" list to undo.
  4. When you're done, close the window.


The database generates a report of who is missing absence cards. To print this report:

  1. Go to the list view in the Staff Database
  2. Click "Missing Absence Cards" at the bottom of the screen

Automatic Emails

In addition to tracking, the database kicks off two emails:

  1. Every two weeks on the Friday before they're due, a reminder to turn in absence cards
  2. Every six weeks a notice to those who are missing absence cards

Staff Collection

Before I was able to finish the Statement of Work (we were on our third round of revisions), the Staff Database project was done. Credit goes not to lightning-fast work but rather shrinking the scope down to a pea. Gone are the ambitious goals of storing emergency contact information, alternate phone numbers and home addresses. We shed these features not because it's difficult to program but because we haven't spelled out OIP-wide guidelines for collecting, updating and accessing this sensitive information. I kicked that over to Molly P and the HR people. Once they describe the policy and access requirements I'll modify the database to support them. All the fields, reports and scripts are done and waiting.

Even stripped down, the Staff Database delivers a few wins:

  • A common spot to store names and phone numbers so we can easily print a phone list.
  • Decentralizes data entry. Units will maintain their own data rather than forwarding changes to the Dean's Office. Localized data entry often translates into more accurate and timely data.
  • Brings efficiencies to how LAC manages absence forms and small working groups (SWGs)

Next Steps

Provide a list to Molly of the admins I should train prior to her directors' meeting on Friday.

A Case for K.I.S.S.

The GPS Staff Database project is prompting a discussion of access privileges. Users commonly suggest elaborate rules for viewing, editing and creating data. While I take privacy and privilege very seriously, I often argue in favor of a mallet rather than a scalpel approach to security.

  • Building a complex security layer requires more work and more development time. While this is never justification on its own, it's part of the equation.
  • In my experience many degrees of access that seem important in the beginning, eventually collapse into fewer in practice. You're left with a system that makes distinctions, the logic of which is forgotten and the business process no longer supports.
  • In my freelance career, I often felt access privileges were determined by personality and politics rather than job function. When someone quit or advanced, the access layer no longer "fit" the organization.
  • Quis custodiet ipsos custodes? Who guards the guards? A system has yet to be build that prevents malicious use. Trust, Training, and Tracking. We have to trust people. We have to train them not only on the system but the business process around it. We track who does what, when. Not in secret, but in a log where everyone can see.
  • IT should never be part of the access plan. Sure, we have the master password, but we're outside the business process. We have to rely on what others tell us. In which case, why not make it so those in-the-know can do for themselves?
  • IT can't be integral to the systems we build. Think of us as freelancers who disappear once the work is done. You can call us back for further development but day-to-day operation has to be owned by users.