Employee Recognition Program

One of the first, if not the very first, ASP.NET applications I built was for an employee recognition program that was run by the Corporate Communications Department. I didn’t know it at the time but it was the beginning of a long-lasting relationship with that department and some valued friendships.

The program was designed for employees to reward co-workers who impressed them with their accomplishments and dedication to making the company the best it could be. The only rules were that you could not nominate yourself and the act should truly be noteworthy – you weren’t going to be recognized for giving someone a piece of gum…. The program had already been in place when I was asked to build the web application for them. The requirements were fairly simple – validate the nominator, validate the nominee, get the reason for the nomination, store the nomination, nomination approval and print the certificate. The back-end was Microsoft Access and the certificates were done using Microsoft Word and mail merge. When a nomination was made the Program Administrator was notified and she reviewed the nomination. If it was legitimate, a certificate was printed and delivered through company mail. The employee’s manager was also notified of the award.

I added an “Admin” page so that nominations could easily be reviewed, approved or edited. There was also another component that had to be added – a monthly contest. This was different in that it was only open to managers and they could only choose from employees who were nominated for awards that month. The winner received the “Outstanding Employee of the Month” award. This part required another web page for the nominations, another section of the admin page and the ability to verify that the user was a manager.

The application was in production for about 7 months before the program was discontinued but it was a big success and it gave me the foundation upon which to build everything that came after it. I also got quite a bit of recognition for my efforts (I got a couple awards myself!) and I soon found myself with a lot of requests for applications from many different areas of the company. I even got a request for a project that came from the CEO but that’s for another article.

If you would like to know more about this project or have one you would like to discuss, please write to me at joe@joevalencia.site90.com.

Invoice Approval System – Part III: Multiple Approvers

We have come to the last, and most challenging, installment in the Invoice Approval System saga (if you haven’t already read the other 2, here they are – Invoice Approval System – Part I and Invoice Approval System – Part II: The Web.)

When I build something I like to monitor it for a while just to make sure everything is running smoothly. I did this with the Invoice Approval System and noticed that there were many duplicate invoices being sent around to different people causing all kinds of problems. I was also getting calls from the AP group. I ran a few test invoices through the system and everything was working as expected so I met with AP to discuss the problem. It turns out the very premise the tool was built on was flawed – I designed the system to have one invoice = one approver. In reality, most invoices require multiple approvals and so they were sending the same invoice to more than one person wreaking havoc on the system. It was time for a total re-write of the application!

I started by doing a little analysis of what was happening with the current system. I wanted to see if the same invoice was being sent to more than one person at a time or if they seemed to wait for one approval before sending it out again. There was no clear-cut answer – sometimes they were sent out over the course of a couple days and sometimes it seemed like they waited for an approval. It was time to meet with the team and sort it all out. My first question was whether they were looking for a “shotgun” distribution approach or a sequential one. They thought about it and decided that the shotgun approach was the way to go. I then tried to determine if there was any logic behind the distribution list and the immediate response was “No!” I was hoping to have some logic so that I could lessen the time for the clerk to enter the distribution list and reduce the chances of a person being left out of the process. The biggest question remaining was – what to do when someone rejects an invoice. Obviously the invoice had to be returned to AP and they had to take some action but then what do we do? Was the updated invoice re-distributed to everyone, even to anyone who approved the previous version? Did the update go only to the person who rejected it? Everyone who had yet to act on it? The decision was made to only send the updated invoice to the person who rejected it and then to everyone who had yet to act on it.

I now had enough information to begin building a prototype. By now, I am beginning to have second thoughts about the “shotgun” approach so I put something together quickly; not wanting to spend too much time developing something I was not sure about. It turns out the shotgun approach was a bad idea – it was not too bad unless an invoice was rejected by someone and then it became a nightmare. There was another problem with this approach that we didn’t anticipate and that had to do with the escalation process. There were many times (if not most) an invoice had to be approved by more than one person reporting to the same manager. If we did the shotgun distribution and three people working for the same manager failed to respond, the manager would get three e-mails. I had a feeling this was the best way to get the e-mails ignored after a while. It was settled – the invoice would be distributed “in series” and would only be passed along when it was approved. This simplified for the logic and, therefore, simplified the code and database. By now we had upgraded SQL Server and I took full advantage of the new “FILESTREAM” capabilities. The previous versions relied upon the PDF being on the server and the user opening the physical file to view. I could now store the PDF in the database! The PDF was built “on demand” when the user wanted to view it; no more wasted space on the server.

I finished to application and put it on the production server. I had to keep the older version active until all outstanding invoices were approved but that wasn’t too bad – there weren’t many outstanding invoices at this point. So ends another successful project; the client is happy, the end-users are happy, everyone is happy.

(cue sound of needle being dragged over a record)

WAIT! That’s not really how the story ends! It was in production and everyone was happy except we had a new problem. It seems that I didn’t get the full story of the distribution process and the new system was causing problems. You see, there was one department in the company that needed a different process; the invoice had to go to one person in the department who then decided who needed to approve the invoice. The process still had to be initiated by AP but it had to go to the department, get the list and then be distributed. This required a re-work of the distribution page, a new page for the non-AP user to set the list and code to set everything up. Fortunately, this did not directly affect the existing application. It only took a few days to add this process and get it tested. NOW I am done! Think again…. The group decided they needed an “URGENT APPROVAL” process. This meant the invoice had to be approved by the end of the business day. This only required a check box on the distribution page and some additional code so it wasn’t a big deal. I needed to add a reminder and escalation process for the urgent invoices and so the notice went out hourly until the invoice was approved.

NOW I was done! Yes, I am done. There was a lot more to the application than I have here – there was an administration page, reporting, etc… but I didn’t want this article to become a book. I was quite happy with the final result and am proud of all three versions.

If you would like to know more about this project or have one you would like to discuss, please write to me at joe@joevalencia.site90.com.

Invoice Approval System – Part II: The Web

You were introduced to the Invoice Approval System project in my recent blog post, Invoice Approval System – Part I. This post is about the second generation of that project where I take it from Microsoft Office-based to web-based. I did this for a number of reasons; it was more efficient, it was easier to use and the reporting was more accessible. I was also able to add a couple features that would have been difficult to do before – escalation and approval by proxy. The application worked as it did before except the clerk would use a web form to send the invoice and the user had their own form for viewing and approving. The AP form was connected to Active Directory (AD) and the recipients e-mail address was taken from there. As a developer I like to remove human error as much as possible and by using AD I took away the possibility of having the invoice sent to someone who no longer worked for the company or mistyping and sending the invoice to the wrong person. The other thing connecting to AD allowed me to do was determine the approver’s manager and send mail to the manager if the approver didn’t act on the invoice in a timely manner.

The first thing to be done was data modeling – what was the database, now SQL Server, going to look like? I needed to track the invoice, the approver, the AP clerk responsible and the status. I needed to know when the invoice was initially sent, when reminders were sent and when escalation notices were sent. The escalation notice was sent to an approver’s manager if the approver had ignored the original approval request and two (2) subsequent reminders. The intention was to have the manager speak with the person and get them to act upon the invoice; the secondary desired effect was to encourage people to act promptly to avoid getting their manager involved. The escalation would continue to move up the org chart until the invoice was approved, stopping short of the CEO (that wasn’t the case initially but one angry e-mail was enough to make the change.) If the escalation process was exhausted and the invoice was left without approval an e-mail was sent to the originating AP clerk to notify them; they would then follow-up with a phone call. To accomplish all of this I needed to build an “audit trail” and stored procedures that would run on timers.

Once the database was sorted out I could start building the user interfaces and build the logic. I needed to restrict access to certain pages and restrict the invoice approval to the intended approver. This is another time that AD played a role, as well as Windows. When a user opened a page in the application their Windows UserID was used to identify them and grant permissions accordingly. The only people allowed to open the distribution page were the AP clerks assigned to the task. There were also a couple of “Super Users” or administrators who had access to different parts of the application, such as assigning access rights and reporting. The user was only allowed to view/approve invoices that were sent to them and so when they opened the approval page they only saw their invoices. They could view the PDF by clicking a link and then approve or reject the invoice by pushing a button onscreen. The responsible clerk was then notified.

This system worked well until someone was on vacation and wasn’t available to approve an invoice – oops! we didn’t think about that…. The answer was to allow a user to assign a “proxy” to approve invoices on their behalf. The proxy could be permanent – like a secretary or assistant – or you could assign someone like a co-worker on a temporary basis by inputting a start and end date.

And there you have it – the new and improved Invoice Approval System! There was now a higher level of accountability, an audit trail to allow AP to know the exact status of an invoice at any time, security and reporting. What more could you ask for? It turns out, quite a bit…. I will save that for the third and final installment of this series.

Until then, I bid you farewell.

If you would like to know more about this project or have one you would like to discuss, please write to me at joe@joevalencia.site90.com.

Invoice Approval System – Part I

I have built a lot of applications during my career, some of which I have already shared with you, but the Invoice Approval System is one of my favorites. I actually created three (3) different systems, each one increasingly more sophisticated, and so I will write this article in three (3) parts.

I was approached by my manager one morning and was asked if I had some time to help the Accounting Department with a problem they were having. I agreed to meet with them, thinking they might have an Excel spreadsheet that was giving them some trouble and it would be a quick fix. I can’t remember the last time I was more wrong! The group that needed my help was Accounts Payable and it wasn’t a problem with a spreadsheet. They needed a system built in which they could track invoices that were out for approval. The system in place was as-follows:

1. Invoice is received by AP
2. Invoice scanned to PDF
3. PDF sent via e-mail to person responsible for approval
4. Approval is received
5. Invoice is paid

Pretty simple, right? It was but it was also riddled with problems. The main problem was that there was absolutely no record keeping; they didn’t know who had an invoice sitting in their Inbox awaiting approval or how long it was sitting there. I will get to the other problem later, which is when I discovered it too. I asked a few questions, laid out my vision to the group and then got to work. I figured they were already using e-mail so that would be my starting point – the application would be Microsoft Outlook based with an Access database on the back-end for reporting purposes. I was comfortable writing VBA for Excel and Access but had never done much with Outlook before, in fact, the only Outlook automation I had done was through Excel VBA code to automate form letter mailing. It is important to remember that this project was done in a post-Melissa world (for those unfamiliar with Melissa you can read all about it here) and automating Outlook was much trickier due to new security features. My first challenge was how to get around the Outlook security and I discovered that you could do so by writing code that was executed by a “rule” set up in Outlook. This meant that the AP clerk sending the invoice would send it to Outlook for processing – it was exactly what they were used to doing anyway! They just needed to send it to the Accounts Payable e-mail address with the approvers name embedded in the message along with a specific subject text. The new process looked like this:

1. Invoice is received by AP
2. Invoice scanned to PDF
3. E-mail is sent to “Accounts Payable”
4. Invoice number and approver is stored in Access database
5. PDF sent via e-mail to person responsible for approval
6. Approval is received
7. Access database records approval
8. AP clerk is notified of approval
9. Invoice is paid

You can clearly see that there is more going on but no one is doing any more work than before; the computer is working hard and a record is being kept. I was able to get this system up and running within a few days but I just couldn’t leave it alone. I decided to add a feature that I thought would be helpful – an automated reminder. Everyone gets busy and chores not directly related to your job tend to get put aside so I decided to send gentle reminders to anyone with an outstanding invoice in their inbox. The interval we decided upon was 3 days and I accomplished this by creating “tasks” within Outlook that triggered code when they became due.

I was happy with the resulting application, but more importantly so was my client. There was now some accountability and invoices were being approved and paid in a more timely manner. It took some creativity to make this work (you remember Creativity, right?) but I was up to it. Little did I know that I was nowhere near done with this project – not by a long shot! One of the downsides of this solution was that Outlook had to be running 24×7 and to do that I had to get a virtual machine set up. This was okay but not ideal and there was an interest in having this application made into something web-based.

Check back for “Invoice Approval System – Part II” when we go web with this application….

If you would like to know more about this project or have one you would like to discuss, please write to me at joe@joevalencia.site90.com.

We Need a Website

My last post was about building an Excel-based cost estimation tool (see Building the Impossible: The Cost Estimator) and this is somewhat of a follow-up to it. If you recall, I was working for the International Services group of a large company back in the mid-1990′s where I was initially tasked to help with their proposals. While I was nearing the end of that project I was approached by my manager and asked how much I knew about building websites. I looked him right in the eye and said “Absolutely nothing.” “Would you like to learn?” he asked. I replied “Sure, why not?” “Good! Jim (not his real name) promised John that we would have one up next week.” “Oh….” My first thought was – to paraphrase Oliver Hardy – here’s another fine mess you’ve gotten yourself into! Why can’t I ever seem to say “NO!”?

The conversation above happened on a Tuesday afternoon. I spent a good part of Wednesday morning looking for a website where I could learn how to build a website; that is, when I wasn’t working on the proposal project. I found one at Case Western Reserve University and ran through the tutorial that afternoon. The next day I went through the intermediate HTML course and on Friday to an HTML class at CompUSA. I was now ready to start building the site – well, almost. I needed some software to work with and found HomeSite (at that time it was free), downloaded it and was on my way. I got an initial layout and realized that I had nowhere to put it! There were no intranet servers set up yet. You see, this was going to be the very first intranet site and no one thought about how it was going to be hosted. I tracked down “the guy” and got him to build a server for me and on Wednesday afternoon we had the basic site up. It was only a single page but it was a start.

We slowly added pages and it soon became my only job, which was fine because I was enjoying it. After a couple of weeks I was presented with the Cost Estimator project which made the website a part-time job.

If we fast-forward to the dwindling weeks of of the cost estimator we get back to building the web presence in earnest. By this time our site was getting a lot of notice and it was decided that the whole organization needed to be represented, not just our group. Guess who was in charge of that project…. Once again I found myself in a conference room with a lot of people trying to get them enthusiastic about a project. I got them to agree to supply me with information but no one wanted to help with design or coding. There were eight (8) groups that I had to support with this site and I knew that it would be easy for the user to get lost so I decided to use a different color-scheme for each group and a slightly different background graphic. Everything else stayed the same – font, font size, layout, etc…. This way you knew you were on the organization’s website but also had visual clues as to which groups pages you were looking at. I ended up teaching someone from another group how to do the work and he took over the site when my contract ended.

I don’t know how long the site lasted after I was gone or if Bob is still involved with website design but I would like to think the site still survives although I am sure it is now far more sophisticated and probably a .NET site. I would also like to think that Bob is still going strong somewhere, building his own applications and teaching others to take charge when he moves on.

If you would like to know more about this project or have one you would like to discuss, please write to me at joe@joevalencia.site90.com.

Building the Impossible: The Cost Estimator

Doing the impossible – sometimes I think that is the actual job description for consultants. I have been asked to do the impossible many times over the years, both as a consultant and as an employee. There are times when I expect the project specifications to self-destruct after I read them. This article is about one of my earlier forays into the impossible; a service contract cost estimator for an international company. I was brought in as a consultant to help re-write the proposal templates they were using and to build an application to automate their proposal generation using Microsoft Word. This was not unfamiliar territory for me because I had just spent a few years working in sales and had co-authored software designed for building proposals (see My Biggest Failure (or How I Went From Draftsman to Programmer)), the most difficult part of the job was deciphering all of the acronyms they were using. When that was done I was given another project which is the subject of this article.

The company had always provided service for their equipment and associated software free-of-charge but had decided to start charging for this service. The biggest hurdle? No one knew how much it cost and so they couldn’t determine how much to charge. The primary cost was labor which presented a big problem. You see, I was working for the International Services group so we had to cost each contract based upon local pay rates as-well-as the rates for expatriots and employees in the field. We also had to take into account the skill levels of the workers. Add this to a list of more than a half dozen types of equipment and you can see the daunting task at hand. The development tool was Excel and they wanted the ability to save the estimates to a text file so that it could be loaded at a later date and compared to actual costs. I was given the names of the experts in each product line who could help me and I got to work. The experts were located throughout the country so much of the work was done via telephone and e-mail. I was able to get everyone together in one room for the first meeting and I should have run for the hills afterward but I stuck around. There were a lot of reasons why we couldn’t build this tool – “too many variables”, “not enough data”, and “because” lead the way. I was told that it was a doomed project but I asked everyone to humor me and let me worry about how to do it, I just needed them to provide me with the data. I was lucky to have someone on the team that was well versed in probability and statistics because I had no idea how to calculate headcount. The answer was poisson and I was given a lookup table to use.

Now the fun part was set to begin. I needed to take hundreds of questions and whittle them down to something that an agent in the field would be willing to work with. This was then put into a data entry user-form in Excel with thousands of lines of VBA code backing it up. The idea was to have the main workbook that contained all of the user-forms, formulas, constants, variables and code and then a second workbook that contained the finished quote. This second workbook was also to have the ability to store all of the data in a text file that could then be loaded and analyzed. My first thought was to create a random-access file but decided to go with a separate file for each quote thereby making sharing easier.

I was able to get each product down to less than a dozen questions, in some cases only a couple, and it was time to populate the main workbook with constants, calculations and code. It didn’t take long before I had thousands of cells across multiple worksheets making it difficult to remember what I was doing where. I needed to document everything – named ranges, formulas, code, etc…. – so I wrote code. This turned out to be my very first Excel Add-in and something that I have used many times since. I could document any single aspect of the workbook – formulas, named ranges, constants, etc… – or the entire workbook. The result was a Word document, nicely formatted with a table of contents. Now if I needed to know the formula for calculating something or what the value of a particular constant was I could open the document, not search through the workbook.

Each of the experts gave me some sample estimates that they worked up so that I could test the application and, after some tweaking, I felt confident that I had delivered on my promise. As good as I felt about it I knew the “proof is in the pudding” and I needed to get it into the hands of the experts and face the music. The reaction was overwhelmingly positive and my biggest critic told me that I had truly done the impossible. There were a few things that had to be changed slightly and I re-worked the interface a bit but it was ready for its rollout the following week. This meant training users from around the world. Since it would be very expensive to send me around the world or to bring them to me, we decided to do it with conference calls and a web-based application like WebEx that allowed me to give them a live demonstration. Not bad, huh? The downside was that a 2:00 PM conference call with a group in Tokyo meant that I was on the phone at 1:00 AM!

The success of this project lead to another big project (and personal challenge) for this client, stay tuned!

If you would like to know more about this project or have one you would like to discuss, please write to me at joe@joevalencia.site90.com.

My Biggest Failure (or How I Went From Draftsman to Programmer)

I spent many years as a draftsman before I got into programming full-time and this project ended up being a springboard. If you are wondering “How do you get from drafting to programming?”, I will tell you. I was hired by a small manufacturing company as a draftsman, by now CAD (computer-aided drafting) was becoming more popular and they had a very expensive package. I had been working with AutoCAD for a year or so and liked it much better. After a few months of being hired I discovered that the software we were using had it’s own scripting language and I could easily customize it to work the way I wanted it to. You see, I had taken some programming classes in high school so it was reasonably easy to teach myself the language. This lead to a drawing automation project that I undertook with one of the engineers. We had begun offering Process & Instrumentation Diagrams (P&ID) to our customers and it was really nothing more than putting together a flowchart of sorts. We built the symbols and then wrote some code to put it all together. It made the process much quicker and thus, cheaper, to produce the drawings. This caught the eye of the company president who then approached us about authoring some software. The machinery – industrial blenders, dryers, formulators and valves – was quite complex and it took a long time (typically several hours or more) for sales representatives to generate proposals and price equipment for customers. The biggest problem was that customers were being sold equipment that couldn’t be made! We were asked if we could create software that would only allow buildable equipment to be configured AND streamline the process so that even an inexperienced sales rep could produce a proposal, with pricing, in 30 minutes. Sure! Why not? We were an engineer and a draftsman, the company had a single PC in accounting (an IBM XT, no less) and we had no idea what to use as a development tool. What were we thinking?!

The first thing we had to do was buy a couple computers to develop on. Well, first we had to decide on a platform. The engineer was an Apple fan and I was Microsoft. We came close to using HyperCard but decided on a Windows-based system and Asymetrix ToolBook was our platform. Our proposals were output to Microsoft Word and were built using MailMerge and hundreds of documents containing everything from paragraphs to just a couple words. I can honestly say that creating those documents is something you have to experience, words cannot describe it – definitely the worst part of the project.

We decided that the best way to build this software was to create a separate application for each but use a front-end that allowed the user to choose the equipment to build. This made development easier while still presenting a single interface for the user. We worked full-time for months collecting all of the information, designing the user-interface, writing code and testing but we finally had something to show the president and sales team. When the demonstration was over the president told us that we failed to produce what he asked for and fired us. I don’t know about the other guy but I couldn’t believe it! The president explained that it was our task to reduce the proposal generation time from hours to 30 minutes. He claims we failed miserably because it only took about 5 minutes using our software. He then laughed and congratulated us on a job well done. We still had a few things that needed work but we were just about ready to roll it out to the masses.

We wrote a manual and created the installation package. This was before the days of CD-ROM and so the installation was put on 3.25″ diskettes for distribution. We also had to have some security so that the installation was one-time only and the software “expired” after a certain time. It was then my job to travel to the offices of some of the local reps to install the software and teach them how to use it. Some of the more distant reps came to us. The software was a great success and ran without a hitch until Microsoft released the next version of Word. Due to the way we were communicating with Word we had to re-write some of the code to accommodate the update. This happened again with the next release of Word but by then we had hired an outside programmer to take over updates. I had moved from the Engineering Department to the Sales Department where I did inside sales but also wrote software, built spreadsheets and databases and served as technical support for our proposal software. My days as a draftsman were gone forever.

I have long since moved on from that company but stayed in touch with a few people. I was surprised last year to get an e-mail from one of the sales team. They were still using the software but started having some trouble – would I mind taking a look and fixing it? It turns out that some of the supporting documents were somehow changed and it was just a matter of fixing the merge. It was fun to re-visit an old friend but I was amazed to see that it was the same application that I had last seen years earlier. We discussed migrating the application to a web-based one but I didn’t have the time to devote to the project in order to get it done in a timely matter so it went to another developer. I understand it is close to being rolled out and I hope to get a chance to see it someday. I would be like watching your child grow to maturity.

So, my greatest “failure” lead to my career as a programmer. I guess it wasn’t really a failure. After all, we exceeded expectations and 20 years later the software is still going strong but whenever we get together and talk about it we refer to it as our greatest failure and laugh.

If you would like to know more about this project or have one you would like to discuss, please write to me at joe@joevalencia.site90.com.