Hitting a payroll home run

Contrary to popular belief, programmers do have a life outside of writing code and doing arcane magic to make computers work. Mine happens to include managing the umpires for the local little league baseball organization. As such, I am responsible for assigning umpires to games, dealing with angry coaches, teaching parents why the umpire’s call was actually the right thing to do and producing the pay sheets and checks.

Payroll v.1.0The long way

When I started, everything was done manually. Every day, I had to collect the umpire names from the scoresheets and make sure the names matched my Excel schedule. This is the crucial part because, being teenagers, the umps sometimes “forgot” that they had a Pee-Wee game to officiate that Friday night.  At 7h30 PM. When all their friends are going out. Then once a month,  when I had to do payroll, I took the Excel sheet, picked out every game for every person, assigned a dollar amount to every game because the pay changes based on seniority, position (at plate or on bases) and age category, add it all up, produce a pay sheet with the list of games and write a check. Then, three days later, go through the process 2-3 more times because I messed up. And the next month… Rinse, lather repeat.

Payroll v2.0 – VBScript

One sunny day I thought, I’m a programmer, dammit! I don’t have to go through all of this! That was the day that the VBScript support in Excel became my new best friend. Gone were the days of painstakingly going through a list of 156 names. My trusty “ExtractNames” function went through the schedule to produce a list of games for every umpire. It even calculated the salaries. All that was left to do now was write these down on a sheet of paper for every umpire and write the check. Well done! But with 32 men-in-blue on my pay list, that is still quite a few pay sheets, with as many checks, to write. If only I had a solution that could take a data sheet and print it…

Payroll v3.0PlanetPress

Wait a minute. I’m a PlanetPress programmer, dammit! I don’t have to go through all of this! As it turns out, my crafty little VBScript function could eaily be converted to write out a CSV file that contains all the data. So I whipped out PlanetPress Design and my graphics design talents and came up with a PlanetPress template that took that CSV file and produced a pay sheet for every person that happened to call strikes and balls and deal with coaches that month. Nice. With that, the process of taking the schedule and producing the pay sheets went from several hours to just a few minutes. Talk about eating your own dog food. No wonder PlanetPress is sometimes seen as a job killer. It almost replaced me!

Now off to write the checks… Wait a minute. I’m still a programmer, dammit! And one armed with PlanetPress. I don’t have to go through this! Checks are, by definition, pre-printed paper, but that doesn’t mean PlanetPress can’t help me there as well. I already have the name and numeric amount. If I place text fields at just the right place on custom-sized paper, I could probably get away with it… and I did! Of course, a check being a check, there is the issue of writing the check amount in words. Being a good programmer means recognizing that sometimes you should rely on other’s work. As it turns out, a PressTalk function that converts numbers to text already exists, conveniently located in the PlanetPress Resource center on Objectif Lune’s web site. Works like a charm (thanks Marty!).

All right. So now my time-consuming process of producing pay sheets and checks has gone from a couple of hours to mere minutes, all thanks to the product on which I worked for the last 15 years. So there actually is a use for it! 🙂

Payroll v4.0 – Automation

Now I wonder… What if all of that could be automated? I mean, loading the Excel spreadsheet, doing Ctrl-Alt-Space-Shift-F4-Escape-Wave-a-Dead-Chicken to start my magic macro and loading the data file in two PlanetPress documents isn’t nearly as daunting as the hoops I had to jump through before, but still, that’s a whole lot of manual steps. Surely I can do better than this. (have I mentioned that programmers are never happy with their solution?)

I’m a programmer, dammit! I… you get the idea.

The nice thing with PlanetPress Workflow is that it gives you all the flexibility you need to achieve your goals. The inside joke is that, not only does it gives you way more rope than needed to hang yourself, but it even comes with a nifty plugin that can do the knot for you. In my case, the rope was its scripting capabilities. Using the Excel object model, I was able to write a script that makes Excel load the schedule and run my VBScript function. With just a few lines of code, I automated the production of my data file! It was easy after that to take that file and print it using the two documents I had created. I just had to insert a pause to allow me to switch from plain to check paper.

At last, the complete solution. As I enjoy the marvelous wonders of modern technology, watching everything being done without intervention while enjoing a cold, alcoholic beverage, I reminisce how tedious this task used to be and think, wow. So this what using PlanetPress feels like. It is a good feeling.

P.S. The more astute amongst you may have noticed that there is still the manual process of collecting the names from the scoresheets. Some of you may even may have started thinking how PlanetPress Workflow could be configured to have a web page that shows the schedule on-line and allow the umps to type in their finished game assignments themselves. Yeah, I too thought about that. I’m a programmer, dammit, but this programmer knows that the pursuit of perfection is a beer-less race. Or something like that.