What people want and how I help them get it is my passion

I think the part of my job that I enjoy the most is finding out what people want and then showing them how to get it. I realize that is a broad statement, but I think it definitely applies to my primary motivation for what I do.

For example, at work we're working on this search tool, which I shared about previously. I've been talking with potential end users of the tool to further define its requirements, while my fellow developer is working to implement these requirements. I like that I can communicate to each group based on their needs; whether it's with a user who is not so savvy about technology or with my coworker about the nitty gritty details of a SQL statement.

It hasn't been just with this project either; I can see other examples with previous projects.

Lesson learned while on a mission critical conversion project

Here's a project I worked on where I learned an important lesson.

The client was a global consumer products company, and the project was to convert an Access 2.0 application to Access 95, which was a conversion from a 16-bit environment to a 32-bit environment. I'm not even going to claim that I understand what all that means, except to say that there were a lot of procedure calls to the Windows Application Programming Interface (API) that needed to be changed over. This application was used to facilitate preparing the company's profit/loss and balance sheets for all of their business units around the world. Hereafter, I'll call it the "financial reporting application".

Where I went wrong was that I didn't test portions of the program as I converted the code. It demonstrated a skill in programming that I needed to learn, and I learned that lesson the hard way because, once the program was "converted", i.e., all the code compiled correctly with no errors, there were other application errors that began popping up all over.

I created a tool to help me keep track of all the errors and the steps taken to resolve them and provided the client with daily progress reports.

As I think back on it, I would have done much better if I had taken more time to understand how the program worked in its prior environment and tested the program incrementally as I revised the code.

Making this blog more reader-friendly

I recognize that I have a challenge in maintaining this particular blog. As I continue to promote myself across the "internets," different people will be visiting. What will they find? I want this to be the default landing place for people when they are looking for me. To that end, I've recently updated danieljohnsonjr.com:

• Stretched layout with more white space for easier reading
• Added content from my other blog feeds to the sidebar, so you can see the other fun stuff I'm doing
• Every post is labeled, so that you can find and read articles on the same topic
• Comments are always open, so please let me know what you think!

Recent blog makeover

I recently gave one of my oldest blogs, Get That Job! a makeover. Here's a link to the specific post where I mention this change, which includes screen shots of how the blog recently looked as well as how it looked in its earliest incarnation.

I like that the new version of Blogger lets you assign labels to posts, and I like how it presents the archives. I think I'm going to explore a way to present items from that blog, as well as my others, over here.

Check it out and let me know what you think!

Deploying a much-needed search tool in the workplace

We have a limited number of licenses to use the payroll management software in our organization. As we have added more staff whose responsibilities involve using that software, those licenses get used up pretty quickly. It's not uncommon to receive 5 emails every day asking if someone would log out of the system so that someone else can get in to search something or run a payroll.

With some knowledge of the database, we've been able to develop a tool to work around the system by querying the server directly. In fact, this is exactly what I've done when developing the 401(k) reporting tool and other custom tools for the organization.

We have created a search tool that members of the organization can use to return some basic information about an employee. I may have mentioned before that we have several databases for different clients, and many times, we don't know the for whom the client works or in which database his or her records are. As a result, the user has to use trial and error to find the employee, which is time-consuming.

Our new Search Tool, which is what I'll refer to it as here, returns an employee's address and company name, along with whether he or she is marked as Active in the system. I have to give a lot of credit to my fellow developer for taking the intial idea I had, and, with some guidance on the SQL, has created the tool to execute the search.

I spent part of the day deploying the tool to different members of the organization who would most likely use it the most: One person from the Risk Management department who coordinates the paperwork related to unemployment claims; one person who is fielding calls from employees about not getting W-2 forms (probably because of an address change); and the receptionist, who helps out with overflow work, especially with respect to returned mail.

We're still building on to the tool while the application is in use, but we are developing in a separate environment. Once again, by having this application on a shared network drive, we can update the file on that one location as needed.

Now on the Podsafe Music Network!

I recorded something on my saxophone for the Journey Inside My Mind Podcast, and, after a little while, I decided others should hear it and be able to play it on their podcasts.

Check out "Sax in the Rain" by Jazzmania Productions.



Related tags: jazzmania+productions jazz instrumental saxophone daniel+johnson+jr

Backing up the bridge applications and drive-independence

When I was developing the bridge applications, I had set the code up to recognize a "test" environment and a "production" environment through hard-coded filenames. It became cumbersome to change the filenames in code every time I moved the application to each environment. We also realized we needed to set up a "standalone" environment for the applications.

...hence, the need for the application to be made "drive-independent".

I wound up setting a property in one of the main VBA classes to "auto-discover" its environment as the application launched. That made it possible to move copies of the application easily among the test/development, production, and standalone environments.

While we're on that topic, every week we need to backup the copies of the bridge applications from the production environment to the other two. The reason for this is because there are certain changes made to some self-correcting features while payroll specialists run the applications each week. (I'll have to discuss these features in a separate post.)

For a few weeks I would manually navigate to each folder that contains a bridge application and copy the application file over to the other environments. That's okay for a couple of files, but as we started implementing this for several clients, it became very cumbersome.

The solution: batch files. I created batch files that copy all the files from the production environment to the development/test and standalone environments. What used to take 45 minutes now takes 1 minute. Way cool.

On the need for conditional formatting

I just realized that these posts are getting a bit lengthy, so I'm considering setting up some conditional formatting to only display a preview of the posts on the main page. I'll have a link to the post pages so that you can read further.

Stay subscribed.

Mining data from 3,000 separate Excel files

One of the consulting projects I worked on was for the finished product exports department at a global consumer products company. The company produces products that get shipped all around the world, but this particular group concentrated on North America.

Because of the North American Free Trade Agreement (NAFTA), the company was able to save millions of dollars each year by taking advantage of tariff exemptions for products shipped among the U.S., Canada, and Mexico. Prior to my joining the project, the company had discovered from their legal department that they needed to provide documentation that these products were indeed exempt.

The regulations describe raw materials that compose the finished products, so we needed to look at the constituent level for every product the company manufactures. I was informed that the company had close to 3,000 Excel spreadsheets for various products that listed the composition of each. Additional information would be forthcoming in the near future after I had started on the project.

I looked at the Excel files and discovered that the composition was not in a flat layout (headings and rows), and thus, I wouldn't readily be able to import the information into a database table. I would need to manipulate the data into such a layout and then import it into a database.

Instead of going brute force into all 3000 of these files, I opted to automate the process. I decided to build a conversion tool in Excel that would search for the relevant contents of each file and copy and paste them into a new spreadsheet in a table layout. I wrote VBA code in a standalone Excel workbook to do the job. In addition, I also wrote code to record errors and other anomalies in a log.

I also wrote some VBA code in Microsoft Access to launch the Data Conversion tool in Excel, along with some use of the Microsoft Office object interface. I put all the directories where the files were located into a Lookup table and had the program go through each directory, performing the conversion on each Excel file, importing each Excel spreadsheet's information to a master database table.

I wound up being able to click a button and watch as the program performed the conversion. I had set up a flag in the Lookup table to indicate whether files were imported successfully. Then I would check the log in Excel for anomalies, handle them appropriately and retry.

I'll share more about this project in future updates.

Fixing the client's 401(k) reporting application to make it run better

The company didn't have a process in place to make sure that this one client receives their 401(k) information on a regular basis. I had developed an application last fall for this specific client to produce the information, but there was no one set up specifically to run the application on a regular basis.

I was contacted earlier today to set up this application on several people's computers. Interestingly enough, everyone could get to the application, since it exists on a networked drive, but, to make things as easily accessible as possible, I prefer to put a shortcut to the application on users' desktops.

After I finished that, I sent an email to everyone affected, letting them know about the application, and letting them know how to get the report.

About 15 minutes later, I was summoned to a user's computer because there was a problem with the report details. She was running the report for last week, but the Last Pay Date for several employees was for this week.

As I looked into the program, I realized that all the other details were correct except for this Last Pay Date, which gets updated whenever a payroll is run. I began brainstorming other ways to get the correct date.

One option was to look at the last pay check date for the date range specified, which is from the first of the month to the date the user selects on a form. That would make sure that date for employees who are getting paid this week is the date the user selected.

As I thought about that, I realized I was not finished. What if we run this report a month from now, and an employee had been terminated this month? The client would still want that employee to show up on the list.

I continued brainstorming, and wrote some mock VBA code on my marker board. Satisfied with that, I created a function, tested it out with various scenarios, and eventually came to a solution that will either use the last pay date for the reporting range OR the Last Pay Date field from the other data table.

I generated the report and sent it off to the client service representative, who will then forward it on to the client.