Welcome to my online portfolio, the complement/substitute for my resume. The opinions included herein are my own and do not reflect those of any client or employer, past or present. Please check out the new site: http://danieljohnsonjr.com

Thursday, December 27, 2007

Your use of social media may vary - and that's okay

Palindrome Mileage
Palindrome Mileage,
originally uploaded by Matthew Oliphant.
I was chatting with someone yesterday about how encouraged I am about being able to reconnect with family members (aunts, uncles, cousins, second cousins, etc.) - near and far. Of course I would love to connect with them on every social network in which I participate. Yet I have to recognize that there are several that don't even have access to a computer. Or, as one uncle said, "I can't even remember my email address!"

That's true for a lot of people in general. The new media playground consists of numerous tools, websites, and the like. While some people are early adopters of social media tools and new media technologies, others prefer a slower approach. It may take many months or years before someone close to you decides to get on email or start writing a blog, let alone record a podcast or even join a social media network.

Another interesting dynamic in social media is the idea of being connected to many people or just a few. Some people want to be connected to a few people with whom they can build close relationships; others like to be connected to everyone; still others choose somewhere in between.
originally uploaded by localstatic.

I used to be critical of one approach over the other, but I realize that each serves a purpose. I can appreciate that people like Chris Brogan, Christopher Penn, Robert Scoble, and Mitch Joel have decided to be super-connectors. I also can appreciate that Clarence has decided to connect with only a handful of people. I myself have opted for somewhere in-between.

Some folks like to be connected to others all around the globe, while others prefer to remain local. The same can be said among certain user groups, such as podcasters, bloggers, people looking for jobs, others in the same industry, and so forth. You find all kinds in social media.

As we play in the new media playground, I strongly believe it's important to let others play as they like and be ready to help others try out something new - if and when they ask.

Check out my other blogs:
Journey Inside My Mind Blog
Journey Inside My Mind Podcast
Get That Job!

Related tags: , , , , , , , ,

Celebrating the past present and future

Thoughts from the commute

Mobile post sent by danieljohnsonjr using Utterz Replies.  mp3

Friday, December 21, 2007

Mobile post sent by danieljohnsonjr using Utterz Replies.  mp3

New Media Cincinnati Meetup, January 2008

New Media Cincinnati meetup:

When: January 12, 2007 4:30-6:30 PM EST
Where: Panera Bread Company, 405 East Kemper Road, Springdale, OH 45246 (map)
This is an informal meetup for people in the Greater Cincinnati, Northern Kentucky, and Dayton areas involved with or who have an interest in new media (podcasting, video podcasting, blogging, Facebook, Twitter, social networking, etc.).

Everyone is welcome to come.

For more information on the store, please visit their local website: http://www.panera-centralohio.com/

The restaurant management has reserved the meeting room for us and politely requests that the group collectively spend at least $25 while we're there. I don't think this will be a problem.

The store has free wi-fi, so feel free to bring your recording equipment. There is currently no single topic of discussion planned. However, if you have something that you'd like to share with the group, please contact Daniel Johnson, Jr.

If you have any questions about this event, please contact:
Daniel Johnson, Jr.
(513) 373-4442

Facebook: http://www.newmediacincinnati.com
Google Calendar: http://tinyurl.com/2caxxp
Twitter: http://twitter.com/newmediacincy
Jaiku: http://jaiku.com/channel/newmediacincy
Flickr: http://www.flickr.com/groups/newmediacincy/

Facebook Event: http://www.facebook.com/event.php?eid=6323616508

If you have a Facebook account, please RSVP over there; otherwise, you can send an email or RSVP in the comments below.

Check out my other blogs:
Journey Inside My Mind Blog
Journey Inside My Mind Podcast
Get That Job!

Related tags:

Thursday, December 13, 2007

Reorganizing clients in Darwin provides opportunity for SQL Server clean-up

This is a post where I share more of the technical aspects of my job as a 'conscientious programmer/analyst'.

The company I work for is reorganizing clients into new databases in the Darwin business system (a customized version of Microsft Great Plains Dynamics), and the business sees this as a great opportunity to clean up a lot of things. This view is especially shared by us who work in the IT department.

The 12 current databases are, for the most part, the same in their structure; i.e., same tables, views, stored procedures, and so forth. The company has used these multiple SQL Server databases to for specific types of clients, based on their industry classifications, etc.

Street Sweeper
Street Sweeper,
originally uploaded by itsray.
One bit similarity is in the paycodes that are used. Paycodes, for the purposes of what I refer to in this and other posts on this blog, refer to specific codes that are used to signify specific payroll transactions. The company has paycodes set up for regular and overtime hours, commissions, bonuses, mileage reimbursements, and other types of income. Additionally, there are codes for deductions, such as cash advances, uniforms, payments made by the employee for benefits and 401(k). Moreover , there are codes set up for benefits, which include an employer's contributions to health care and 401(k), and the like. Finally, there are a separate set of codes for both state taxes and local taxes.

It may be easy to imagine, then, over time, and as clients come and go, that the databases would have lots of various codes. Mirror that across 12 databases, and it becomes more complicated. Furthermore, I've discovered that the code descriptions are not consistent from database to database. That the business has a need to reorganize clients into new databases presents a great opportunity to clean things up, as a result.

Yesterday, the Director of IT and the Director of Special Projects asked for a list of active codes for active employees, across all 12 databases. I am the guy they turn to in order to get this done quickly. Because of my experience with how the databases have been set up, I usually know pretty quickly which tables to use in my SQL scripts.

In this particular case, I was interested in the Transaction History table, since it contains the three most important elements my internal customers needed: check date, transaction type, and paycode.

I initially set up the script to pull all paycodes, but I found close to 10,000 codes in use since the business started using Darwin in 2005. I checked with the Director of Special Projects, and she asked me to limit to just those codes in use since October 2007. Thankfully, that narrowed the list to just under 2000. I also included, at her request, the name of the database in which the codes were used. This proved especially helpful, since not all codes are in use in all the databases.

On my way home last night, I called into Jott to remind myself to set this up as a stored procedure.

Just another way I'm able to help keep the business engine going.

Check out my other blogs:
Journey Inside My Mind Blog
Journey Inside My Mind Podcast
Get That Job!
Utterz by danieljohnsonjr

Related tags: , , , , , , ,

Tuesday, November 6, 2007

05 Nov 2007 - Utterz from the road

Three topics discussed:

  1. Effects of Daylight Savings Time ending one week later this year, especially on the commute home.
  2. New Media Cincinnati: http://www.newmediacincinnati.com or http://danieljohnsonjr.com/2007/11/new-media-cincinnati-meetup-november.html
  3. Dashboard-types of reporting I've been doing to provide a good pulse on the company. I love doing this kind of marketing-type of analysis.

Hear all my Utterz: http://www.utterz.com/~h-danieljohnsonjr/list.php

Check out my other blogs:
Journey Inside My Mind Blog
Journey Inside My Mind Podcast
Get That Job!

Related tags:

Monday, November 5, 2007

New Media Cincinnati Meetup, November 2007

New Media Cincinnati meetup:

When: November 17, 2007 4-6 PM EST
Where: Panera Bread Company, 9444 Civic Centre Blvd, West Chester, OH 45069 (map)
This is an informal meetup for people in the Greater Cincinnati, Northern Kentucky, and Dayton areas involved with or who have an interest in new media (podcasting, video podcasting, blogging, Twitter, social networking, etc.).

Everyone is welcome to come.

For more information on the store, please visit their local website: http://www.panera-centralohio.com/

The restaurant management has reserved the meeting room for us and politely requests that the group collectively spend at least $25 while we're there. I don't think this will be a problem.

The store has free wi-fi, so feel free to bring your recording equipment. There is currently no single topic of discussion planned. However, if you have something that you'd like to share with the group, please contact Daniel Johnson, Jr.

If you have any questions about this event, please contact:
Daniel Johnson, Jr.
(513) 373-4442

Facebook: http://www.newmediacincinnati.com
Google Calendar: http://tinyurl.com/2caxxp
Twitter: http://twitter.com/newmediacincy
Jaiku: http://jaiku.com/channel/newmediacincy
Flickr: http://www.flickr.com/groups/newmediacincy/

Facebook Event: http://www.facebook.com/event.php?eid=6146127822

If you have a Facebook account, please RSVP over there; otherwise, you can send an email or RSVP in the comments below.

Check out my other blogs:
Journey Inside My Mind Blog
Journey Inside My Mind Podcast
Get That Job!

Related tags:

Saturday, October 20, 2007

New Media Cincinnati Meetup, October 2007

New Media Cincinnati Meetup, October 2007
How was your Saturday? I and about 11 others met up today, the first New Media Cincinnati meetup, at the Golden Corral just off exit 32 at I-75.

Some big thanks go out to people for helping inspire and organize and otherwise contribute to making the event possible:

It was so great meeting other people, most of whom I'd only known online, and some that I'd met for the first time. I was so engaged in the conversations that I barely ate anything!
New Media Cincinnati Meetup, October 2007
Here are the people I met, with links to their websites/podcasts/online presence:
I am definitely inspired by the depth of talent and diversity in the group, and I look forward to meeting up next month. More details on that to come soon. Be sure to check out newmediacincinnati.com!

Check out my other blogs:
Journey Inside My Mind Blog
Journey Inside My Mind Podcast
Get That Job!

Related tags:

Monday, October 8, 2007

Daniel Johnson, Jr. Interview on Post It Cast Live on Tuesday

Post It Cast Live
Host: Michael W. Moss
Episode: Interview With Dan Johnson Jr.
Host Of The Journey Inside My Mind Podcast: http://jimmpodcast.blogspot.com/
Also huge promoter of a great cause: http://onevoicewalk.org/
Tuesday, 09 October 2007
6:15 pm Eastern
live podcast
Talkcast ID 32555
Answers Here

Check out my other blogs:
Journey Inside My Mind Blog
Journey Inside My Mind Podcast
Get That Job!

Related tags:

Monday, September 24, 2007

Keeping upper management knowledgeable and salespeople paid

This is another post where I share technical details about a project I have been working on.


Sales executives within the company receive monthly commission checks based on active client employee counts and gross payroll, for clients that they have brought on. In addition, upper management needs to see high-level numbers such as active clients, active employee counts, and gross payrolls - dashboard-type information.

A easy-to-use tool to generate this information did not exist at a user level. Previously, upper management relied on IT or the Controller to generate this information and send it to them.

Through some personnel reorganization, the process for generating this report fell through the cracks. Salespeople were waiting for their commission checks for the previous month, so the project was both urgent and important.

As usual, this information needs to come from the multiple SQL Server databases the company uses to manage client information through the Darwin PEO System, a customized, version of Microsoft Great Plains for the Professional Employer Organization (PEO) industry.


I was asked to develop a tool that upper management can use to generate information themselves. Some of the application requirements and thoughts that guided the development:

  • Let users pick the date range, click a button, and have the system produce a report.
  • Develop the application quickly to meet the immediate needs of the organization, yet with the ability to be reused whenever upper management so desires.
  • Since upper management is most comfortable with Microsoft Excel and will want the data in a workbook anyway, use Excel Visual Basic for Applications (VBA)) and ActiveX Data Objects (ADO) within a single Excel workbook to produce the results.
  • Choose Excel over Access because the application overhead is low (i.e., no need for tables, forms, reports, etc.).
  • Since the company doesn't mark employees and clients as inactive in the system immediately when they are terminated, define an active employee during a date range as a paid employee.
  • In addition to a paid employee count, obtain a total check count and gross payroll amount for each client during the date range.
  • If an employee received a check and it wasn't voided, it counts.
  • Take advantage of server-side processing to achieve the best performance.

I first developed the SQL statement to unite data across the twelve SQL databases, based on prior knowledge of where to find information. Then I wrapped the SQL statement up in a stored procedure, with start and end dates as parameters.

After testing the procedure with different date ranges to make sure the information was accurate and made sense, I moved on to the Excel piece. I wrote code in Excel VBA and ADO to execute the stored procedure and output the results to a worksheet in the workbook.

Once I had tweaked the completed application to make sure everything ran smoothly, I e-mailed it to the director who requested it.


Within a few minutes I received a phone call from her, telling me how awesome I am. She also sent the application to the owner of the company so that he can run the report as often as he wants.

Now they are able to generate the information in a matter of seconds themselves, versus waiting for the Controller or someone else in IT to generate it for them; or, even worse, spend hours compiling the information themselves.

Check out my other blogs:
Journey Inside My Mind Blog
Journey Inside My Mind Podcast
Get That Job!

Related tags:

Friday, September 21, 2007

Reaching the point of diminishing returns on a project

Over the past month, I have been working on a project to automate payroll entry for a client that has close to 300 employees and is growing. Naturally, it is the company's best interest to get this payroll automated as much as possible.

To do this I've been developing an application to bridge the payroll info to Microsoft Great Plains Integration Manager.

One of the criteria I've used to evaluate a client's payroll is a consistent layout in a good format, so that I can tell the program where to map regular hours, overtime, cash reimbursements, etc. In some cases I'm able to tweak the payroll to make it into a layout that is consistent enough for me to automate.

This particular client is a construction company, and the only electronic version of the payroll is a text file that is not delimited. The file is basically a report from the client's system, and it has page headers and department footers, as well has breakdowns of withholding, etc. Most of that I can ignore in the program.

My colleague was able to import it into Microsoft Excel, using spaces as the delimiter. From there I saved the file as tab-delimited text, the format used in the other bridge applications I've developed.

The big problem is the inconsistent layout, which has come from using spaces to delimit the text, depending largely on how much detail is on a particular line of text. Look at the following 3 lines of text:

  • E 22 Per Diem - C AZ 3 99
  • E 22 Per Diem - C AZ 3 XYZ111 99
  • E 22 Per Diem - C 99
In each of these lines, the payroll item is Per Deim, and the amount is 99. When you space-delimit the lines the amounts are in different columns.

This is just one major aspect of complexity that has come from my attempt to automate the payroll. After talking it over with my boss, we realize that we've come to the point of diminishing returns.

I'm off this project until there is another way to parse the payroll information and am able to move on to another project that's in the queue.

Related tags:

Sunday, September 9, 2007

One Voice Walk 2007

crossposted to Journey Inside My Mind Blog

One Voice Walk 2007
Originally uploaded by danieljohnsonjr
Have you've been wondering where I've been this past month? I've been doing some online publicity as a New Media Consultant for the 4th Annual One Voice Domestic Violence Community Awareness Walk, which was held in Cincinnati, Ohio this morning. I took lots of pictures and recorded lots of audio from the event.

Hearing personal stories really moved me. I honestly felt a flood of emotions ranging from sadness, outrage, hurt, compassion, and more. It was really a lot to take in. And rightfully so. Domestic violence is an anathema to our society and must be stopped.

I really enjoyed interviewing people from the various local organizations and finding out what they are doing to help raise awareness for and to prevent domestic violence.

During the walk, I talked with other participants. In nearly all the interviews, I asked folks why they came. Really great discussions.

Do a Google search (aka 'google') "onevoicewalk", and you'll find this content as it all becomes available. For now, I encourage you to check out these specific websites:

http://onevoicewalk.org - the official website, with information about how to donate
http://flickr.com/groups/onevoicewalk - pictures taken during the event, with more to come
http://twitter.com/onevoicewalk - microblog of updates from before, during, and after the event
http://myspace.com/onevoicewalk - MySpace page
http://jimmpodcast.com - Journey Inside My Mind Podcast, where audio content from the walk will be posted

Speaking of the Journey Inside My Mind Podcast, head over there for the recent episode, Music for the One Voice Walk.

Related tags: , , , , , , , , , ,

Wednesday, August 22, 2007

Server-side processing rocks the house!

I've spent the last couple of weeks learning how to optimize the search tool we built. The short story is that we're now using a pass-through query in Access to pass parameters to stored procedures on SQL Server. Processing time has gone from about 2 minutes to less than 2 seconds.

Now that I know how to work with stored procedures, I'm thinking of how else to implement them with other types of applications.


The company has clients spread across multiple databases for various reasons; namely specific business rules (e.g., worker's comp rate, state tax rate, unemployment insurance, etc.) apply across an entire database.

Quite often someone in the company receives paperwork from an agency or client with nothing but the client employee's name or their SSN. Searching for this employee has been trial and error, going through each database at a time. Also, in many cases the person in our company just needs to see some basic information about the employee. For example, during tax season we just needed to verify a client employee's address and phone number. Hence the reason for such a tool.

We had initially designed the tool to have individual pass-through SELECT queries, uniting and applying criteria to them in Access. It was the uniting and applying criteria that has been slowing things down.


So, I created two stored procedures with parameters: one that searches for an SSN one one that searches on a name. Each stored procedure unites the individual SELECT queries on a database separate from the other 12 that were being searched.

In VBA code, based on which criteria the user enters, we determine which stored procedure to run. We create a pass-through query to EXECUTE the stored procedure. This pass-through query then contains the search results, which is what we had with the earlier version of the tool.


While reading the literature, I came across the concept of SQL injection and the need to prevent it. Since users are passing text to a SQL statment, we need to prevent the intentional or unintentional insertion of code that would prematurely terminate a text string and append a new command.

I wrote one procedure that scans what users enter in the first and last name fields for "--", "CREATE", and other such items. My coworker gave me some code to prevent users from entering other illegal characters. This double-barreled approach will prevent any SQL injection from occurring.


I enjoyed sharing the news of the updated tool with the rest of the company. I knew many had not even heard of it. I constructed the email the way I would a friendly blog post, including pictures.

One user immediately replied: "Awesome! Works very fast. Good job." I was contacted by two people in Accounting and Payroll who asked that I put the tool on their machines as well.

Much joy all around, I tell you. And IT looks good.

Now playing: Tim Hawkins - KidsRock
via FoxyTunes

Monday, August 13, 2007

How do you draw the line between effective promotion and spam?

This is a question I've asked my LinkedIn network, and I also ask here:

How do you draw the line between effective promotion and spam?

When marketing a brand or doing some other type of publicity for someone or something, there are effective ways to promote, and there are not so effective ways. How do identify, especially in the digital age in which we live, where the line between effective promotion (self- or otherwise) is and where you've crossed it?
Comments are open and genuinely appreciated!

Related tags:

crossposted to http://getthatjob.blogspot.com

Thursday, August 9, 2007

JIMM 90 - Crazy Busy Sticky Hot

Listener feedback about past episodes and such. Police and ER visits, Twitter tools, AMP, the upcoming One Voice Walk. Music with a country feel. 2-year anniversary in podcasting coming up this month. Other warm, juicy goodness.

Head over to the Journey Inside My Mind Podcast to listen and read more.

Related tags:

Monday, July 30, 2007

Notes from a Performance Review

I've been going through some old paperwork this morning, and I found a copy of the only formal performance review I've received in the past 2-3 years. Some comments are particularly notable, and I want to emphasize and remember them here.


  • I received an Above average potential rating, which indicates "demonstrated ability to perform a higher number of tasks at a higher level of difficulty"
  • "Dan has been an invaluable asset to the IT department and the company. His development and implementation of various software solutions has allowed numerous payroll and risk management functions to be streamlined, allowing for more efficient company operations. He has the potential to handle even greater responsibility."
Strong areas of present performance

My manager had these comments:
Focused. Expert in area. Responds well to suggestions. Able to grasp organizational needs. Possesses a humility and thoughtfulness that most often attracts good will from others. Able to learn new techniques on own.

Monday, July 9, 2007

Thoughts on social media etiquette

Chris Brogan has many of us thinking about social media etiquette today, especially with respect to Twitter. I definitely recommend reading his post so that my comments here make sense:

As an early adopter of Twitter, I used to get uptight when I saw the proliferation of '@' messages. Now I get it.

My purpose in using Twitter has changed over the months since I started using it. I now use it to participate in conversation, let others know what's going on with me, point to content too large on Twitter, and make/keep in touch with friends.

I've especially enjoyed finding people in my own geographic area through TwitDir.com, and I hope that I can connect through other online means and even meet up with more friends locally in person.

I've decided to use Twitter and other social networks to for relationship-building and personal branding, so knowing how to "take the pulse" of the current climate of a social network is something of value.

Knowing where to fix a problem pays off in quick response

I was on the phone with a Payroll Specialist about 30 minutes ago. One of the company's biggest clients is a trucking company. We set them up back in January with a bridge application to facilitate payroll entry. The client emails their payroll in a spreadsheet with a consistent layout that I and my coworker mapped with the program, and their payroll needs to be done early Monday morning for a Tuesday check date.

Last week, the client left the first column blank for one of their employees, and the payroll specialist didn't find out he wasn't paid until the day after the payroll went out. She called me this morning to have me check to see if the program would do that again. In less than 10 minutes, I was able to diagnose and fix the problem so that it won't happen again.

The code looked for something in that first column before mapping the columns into specific payroll items. In this case, the data in the first column isn't as important as that in, say, the third column. I changed the code that does the test to check the third column instead.

I'm pleased that my knowledge of how this program works lets me respond so quickly and adeptly to a problem like this.

Sunday, July 1, 2007

Yet Another Social Media Network?

It looks like the buzz on Twitter this weekend is about Pownce.com. Chris Brogan put up a post a short while ago entitled, "Why Join Another Social Network?"

Here are comments to what he said:

In the early days of blogging, before Blogger had its own commenting system, there was a commenting system called YAACS. YAACS stood for Yet Another Commenting System. Probably still exists today - I wouldn’t know.

I keep waiting for someone to start YASMN - Yet Another Social Media Network. All of this talk of more places to put oneself online is frustrating. I can definitely understand how it would feel cliquish.

I haven’t yet decided what to do with my Pownce invite yet, nor how to develop my Facebook profile. I feel okay about holding off for a bit and using the current tools I’m using.
I think it's interesting that, in an attempt to reach more outward and gain a wider audience, it can also come across as being exclusive. What I mean by that is the same situation that many of us in the blogging, podcasting, twittering community find ourselves in - only associating with each other. The fishbowl. The echo chamber. We're only talking to ourselves.

So I guess I don't see social media networks as a bad thing in and of themselves if it widens one's circle of influence. For me, I have a rough time of continually fragmenting myself over and over. Something I'm still trying to manage.

Monday, June 18, 2007

Microsoft Great Plains - Integration Manager in action

The company uses a version of Microsoft Great Plains (GP) that's been customized for the PEO industry. One piece of that is Integration Manager (IM). I've talked about the bridge application a number of times before. With an Excel file from a client and, with some sophisticated programming, we create a tab-delimited text transaction file for Integration Manager. We've set up some profiles in Integration Manager to upload payroll transactions into a batch in the Great Plains system, saving a lot of time and money. In some cases we've seen a 91% drop in the amount of time it takes to run a payroll.

Over the next few weeks or so, I'll be running Integration Manager for the Payroll department. Here are some numbers for today:


1002 transactions, for 530 employees of 10 clients.

Sunday, June 10, 2007

Every programmer should be on a testing project

I think every programmer should get a chance to be on a testing project because it will make him or her a better programmer. On one project I supported the complete software development lifecycle process to develop a multi-tiered, Intranet-based, Product Data Management System from a software quality assurance perspective. The company is a global consumer products company, and they were creating a system to manage information about their products.

To do this the testing team, of which I was a part, analyzed business requirements and functional specifications, and designed test cases. Then we built test data and executed the test cases. For the most part, each of us would concentrate on a specific aspect of the system.

Since my time on this and other testing projects, I've found myself approaching development from the perspective as a tester, and it's helped me be a better programmer. I wish the same for every programmer.

Wednesday, June 6, 2007

At a Conference in Downtown Cincinnati

Over the next few days I'll be attending a conference in downtown Cincinnati. I'm really glad that I'll be able to catch the bus instead of driving the hour commute to Dayton.

The conference is being conducted by Thinkware, a vendor that has -- and I'm probably oversimplifying this -- customized Microsoft Great Plains for Professional Employer Organizations (PEOs). I look forward to learning more about the latest versions and seeing how I might be able to use it on my job.

I may or may not provide updates here.

June 7th Update: I found out it's not until today. More to come later perhaps!

Sunday, June 3, 2007

Helping company take advantage of a tax credit

I found out last year from the Accounting department at my current company that employers with tipped employees can get a tax credit based on how employees have reported their tips. I guess it's the IRS's way to encourage employers to get their employees to report tips so that they can get the tax from it.

The PEO model is one of co-employment: the client employs someone to do the work while the PEO employs the worker from an HR perspective. If the client doesn't take the tipped employee tax credit (or whatever it's actually called), then the PEO can take it.

For me it has meant developing yet another report from the SQL Server database. The business, of course, wants the report as soon as they can get it, and I and my boss help them understand that it cannot always happen overnight or even in a week.

The report, as many of these I develop lately, involves looking at the transaction history on employees' paychecks to identify reported tips. It sounds simple, and it really is, as long as you know where to look.

Over the past year or so, I've gotten better as I've come to understand where the types of information are stored - the database and the front-end are a modified version of Microsoft Great Plains, and the tables are not always intuitively-named.

I probably should push this application out to the Accounting department, since all I did was run it for last year and the current year. The upper management was certainly surprised at fast I'd turned their request around.

Monday, May 14, 2007

JIMM 85 - Super THRILL Mix

Memorial Day is just around the corner, and we're getting ready for a vacation to Fort Worth, Texas. I created this Super Thrill Mix of independent music for the occasion. Head on over to the shownotes page for the "warm, juicy goodness". Feel free to burn to a CD so you can enjoy it, too. Please support the artists who make their music available to be shared on shows like this one.

Wednesday, May 9, 2007

Programming for Good to Reduce Necessary Evil

A few weeks ago I mentioned a project I'm working on to streamline the benefits auditing reports. This process involves verifying premium payments for health insurance with what is taken out of employees' paychecks. As I mentioned in the previous post (via the Twitter photo, no doubt), the application I'm working on will save 2-3 weeks' worth of work and many trees' lives.

I just met with the HR department to discuss the status, and the HR Specialist who does this work mentioned that the current process wastes a lot of time. But needed by the business, I said.

"It's a necessary evil," the HR Manager replied.

"Well, hopefully we can make it for something good," I said.

Programming for Good to Reduce Necessary Evil. That's what I'm about.

Saturday, April 28, 2007

JIMM 084 -Emotional Couple of Weeks

The latest episode of the Journey Inside My Mind is now live! You might want to keep some tissue handy because it's an emotional one.


Talk about the emotional couple of weeks right after the last episode, how to help me get some gas money, with music from Laura Clapp, Matthew Ebel, The Reverse Engineers, Black Lab, along with Rob Costlow providing piano accompaniment. Be sure to check out the full shownotes at http://jimmpodcast.com. Direct link to file (mp3, 44:28, 40.7 MB)

Related tags:

Wednesday, April 25, 2007

Migrating Existing Access Applications to Access 2007

This post is one of the more esoteric ones where I delve into the geeky details of some of my programming work. I know - it's really sexy, isn't it?

I have mentioned the bridge application I developed that helps make payrolls run faster, helping client employees get paid faster, and so forth, using Microsoft Access 2003 with VBA, ADO, Excel, Office, etc.

Some members of the company are starting to migrate to Office 2007, and we can see the entire organization moving there soon. A few weeks ago, I tried opening and running one of the bridge applications in Access 2007, and it bombed horribly, specifically in how I've written it to use the Office 11 FileSearch object.

I just found a couple of few documents on MSDN and TechNet that I hope will help understand what is involved in the migration:

Okay, to be honest, I picked that last item because it sounds interesting.

Where Do You Get Your Ideas?

I'm quite proud of what I just wrote at the Journey Inside My Mind Blog that I feel it's necessary to also share with you over here, a bit of cross-pollination, perhaps.

Join the conversation and let me know where you get your ideas!

Tuesday, April 24, 2007

More Benefits Auditing and Follow me on Twitter

I'm going to put up a Twitter badge in the sidebar so you can know about other stuff I'm doing. For now, feel free to follow me on Twitter, and let me know you found me via this website.


Quick Turnaround on a Benefits Audit

I can tell I've become branded as the "go-to" guy for certain types of projects. Last week the HR manager came to me and said that she didn't know who else to talk with about a benefits audit the company is doing for one of its clients.

The company and the client are trying to resolve a $20,000 discrepancy that appears to have arisen from November 2003 through November 2006, when the client stopped using the company's health and dental plan. Oh, and since this all needed to be finalized by the end of this month, which was about two weeks away, she needed whatever I could do in a day or so.

The HR manager sent me an email that contained about six Excel workbooks. Each workbook contained several worksheets. These worksheets were benefit summaries for each payroll that had been run during that three-year period, from the client's perspective.

The company needed to compare this information with what existed in the system, but the HR manager recognized that it was very cumbersome to go through the information in its existing format.

With the task at hand, I imported each worksheet into an Access database and compiled the information into a single table that I exported and sent back to the HR manager.

It sounds like I'm making it simpler than it was at the time. I had to write a couple of Excel macros to get the data in the proper format for importing into Access. That and a VBA function to import each worksheet from each workbook did the trick, for the most part.

Thursday, April 12, 2007

In the UML Playground

A fellow developer and I are learning UML and VB.NET. We've decided to look at some of the existing applications the company is using from a UML perspective. We found a free version of Objecteering/UML Modeler and it looks like it will meet our needs so far.

I know there are lots of room for learning and lots of room for improving our existing applications. This stuff excites me.

Tuesday, April 10, 2007

The output is only as good as the input

Part of what I do in my current work is produce ad hoc reports. I often am asked about the information in them because it doesn't appear to match what decision makers had assumed.

The information on the reports is only as good as the raw data that exists in the system, I tell them. If the company no longer do business with a client, for example, and yet the client is still marked active in the system, then the client still gets counted as an active client. The same goes for employees. Active employee count may appear high, but when it comes down to the actual numbers of employees getting paid, the number is much smaller in some cases.

Helping managers and other decision makers understand these truths is one thing that I appreciate doing in my work.


Thursday, March 29, 2007

Competitive intelligence and internet visibility

You might call it competitive intelligence.

Yesterday the COO of the company asked me to search the internet far and wide for any information about a certain company. I spent about two hours searching across as many search engines as I could find, and had a friend with a paid subscription to ZoomInfo do a search for me. I also searched the Thomas Register, Jigsaw.com, the Yellow Pages, Reuters, US Newswire, and in social networking sites like LinkedIn, MySpace, Flickr, Digg and the like.

I found a link on the NewPR Wiki about What Companies Should Monitor and used that as a resource for places to search.

While I was somewhat fruitful, I found I really enjoyed the opportunity to play detective for a little while. The company could have instructed me to sign up for some paid services, but they were looking for some quick information.

It also taught me a lesson about making oneself visible. This company has apparently done a fantastic job of making itself invisible. Contrast that to a Google search done on "Daniel Johnson, Jr." Aside from a Canadian politician of the same name, most of the top results are related to me, which excites me a lot.

Tuesday, March 27, 2007

Music, branding, and new media

In the world of new media, C.C. Chapman is a bellwether for me. I have listened to his Accident Hash music podcast for about one year, and for the past six months, I have caught up with Managing the Gray.

As a podcaster and new media enthusiast myself, I've been drawn into the discussion. Most recently it was about how brands are using familiar music to market themselves. Applebee's comes readily to mind with their versions of "It Takes Two" spots, but there are many, many more.

I, like C.C., am torn as a music zealot between seeing a familiar song's lyrics being twisted to sell a brand, and seeing how the actual irritation of it helps me remember the message. I would like to see more original TV theme songs and original jingles on commercials, and I know I'm not alone.

Read more and join the conversation at Managing the Gray.


Saturday, March 17, 2007

Welcome JibberJobber blog readers!

This week I was following a discussion called "The Resume Experiment" at the JibberJobber blog. I sent a comment in, and Jason mentioned me on the last article of the series. So, welcome any readers that have found out about me from that article.

This blog is mainly an online portfolio of application development projects I've worked on. The discussion gets rather technical at times. In addition to these projects, I'm an avid blogger and podcaster, a new media enthusiast, and somewhat of a 21st-century Renaissance Man.

Please check out the items in the sidebar, and feel free to contact me at danieljohnsonjr at gmail dot com.

Friday, March 16, 2007

Handling errors in the payroll bridge application

Clients email the company their payroll files, and the columns on the payroll indicate Regular Hours, Overtime, Vacation, and so forth. To make the bridge application work, I and the other developer map the program that assigns payroll items based on this layout. It only works, however, when the layout is consistent. Whenever a client changes it by adding or deleting a column, the program generates an error.

One thing I have found so helpful in resolving errors is the Error Handler function. I use the Err object in Visual Basic, but I also add additional information such as the module name and procedure name where the error occurred. The user gets a message box, which is helpful for them, but I also wrote the program to create a log file with the same information so that I can go back and see errors that have occurred.

Wednesday, March 14, 2007

Rapid response to payroll layout change

One hour ago I got an e-mail from a payroll specialist, telling me that a client with 107 active employees had added a column to their payroll file and asking if I could fix their bridge application to allow for the new payroll item.

I opened the code, and after I made the changes needed in a few class modules, we were good to go. I tested it out on my machine and then put it out on the network for the payroll specialist to use.

I was able to get this done so quickly because there were only a few places to add some more code. Having the system set up like this was very efficient.

Monday, March 12, 2007

Expanding the 401(k) reporting tool

As the company grows, the need to be able to report on client's using the 401(k) plan does as well. We are adding clients to additional databases, and that has meant that the 401(k) reporting tool needed to be updated so that the user can identify which clients are participating in the company plan.

Using Microsoft SQL Server Management Studio, I created the additional tables: one for each additional database. Then I added ODBC links to these tables in the 401(k) reporting tool, the desktop application created using Microsoft Access 2003. I have to remind myself that, for each computer using this tool, I have to create the ODBC connection to each database and table. Then I updated the VBA code so that it can recognize and use the new tables as it compiles the information for the reports.

After testing out the changes, everything appeared to be working fine. I contacted the user and updated her version of the tool, refreshing the ODBC links where needed.

The company is now more ready to begin managing the 401(k) program for new clients.

Helping 145 employees get paid

Not my normal function, but the payroll specialist was in a pinch, so she called and asked me to run the program that loads transactions into the business system, helping 145 client employees get paid on time.

Thursday, March 8, 2007

Learning .NET

I'm a little jazzed to say that I'm finally starting to learn .NET, since that's what recruiters tell me all the cool kids know. I'm going to focus on VB.NET, since that's the most easily transferable skill from what I already know.

It looks like I'm not alone: LinkedIn: Answers: What Microsoft Visual Basic and SQL Query Language Resources would you recommend?

Wednesday, March 7, 2007

When the feature became a problem

When I was initially developing the bridge application a couple years ago, I had set up the Close button on several forms to be the Cancel button. That way a user can just hit the Esc button to close the form, if they prefer to use the keyboard.

As the applications for various clients have been used, this feature had become more of a bane than a benefit. It turns out that users hit the Esc key to Undo what they have typed, but the application would close the file. This results in some records being incomplete, causing errors when the application is processing.

While you can try to anticipate the user's experience as much as possible beforehand, some bugs still show up.

No big deal. I just removed that functionality from every bridge application we have in use so that program behaves in a way that users expect it to behave.

Wednesday, February 21, 2007

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.

Monday, February 19, 2007

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.

Friday, February 16, 2007

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!

Monday, February 12, 2007

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.

Saturday, February 3, 2007

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.

Jazzmania Productions on the Podsafe Music Network

Related tags:

Friday, February 2, 2007

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.

Tuesday, January 30, 2007

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.

Helping 525 employees get paid

As you may have read before, I've written the bridge applications that prepare clients' payroll for a program we use to batch load into our payroll management system. Whenever I and now my coworker, whom I'm mentoring, finish one of these bridge applications, we update a master list of all the clients we've set up and email it to the other people in the organization that need it.

The person who normally runs the batch-loading program wasn't available today, because she's printing W-2's for all of our client employees. So, the payroll specialist contacted me, and I ran it for seven of our clients. I estimate that it pulled payroll transactions for about 525 employees.

I love being able to help out in a pinch!

Monday, January 29, 2007

Welcome 2000 Bloggers Readers!

I just found out that I'm 948th among a list that Tino is compiling of 2000 bloggers. It's a bit mind-boggling to look through all the pictures of bloggers and instantly recognize everyone's individuality even though we each blog. It's a crazy concept. The collage of photos is even being considered as a potential book cover. And, as of this writing, there's still time to add yourself to the group.

As for me, you can check out the blurbs about me in the profile section where I have delineated some of my super powers. All that and more is available on the sidebar.

Related tags:

Revised payroll layout forces changes to bridge application

In an earlier post I described some changes to a client's payroll journal. In this one I'll share how I worked those changes into the corresponding bridge application we use to automate the payroll entry.

You will recall that I made two significant changes:

  1. Added a new payroll item and
  2. Removed totals columns
I will discuss each of these in turn, along with some other things I've discovered while working on this application.

New Payroll Item

The client wanted to be able to track Training, so I added it to their payroll journals. In the bridge application, I added the payroll item in the respective classes to recognize it.

Totals Columns

The client tracks both weeks of their pay period individually, and the payroll journal was originally set up to provide totals for each week and for both weeks. The totals columns for Week 1 were set up immediately after Week 1's payroll items. The totals columns for Week 2 and for both weeks were set up after Week 2's payroll items.

When I removed the totals columns, I moved all of Week 2's payroll items. The bridge application contains a VBA class where I've mapped the payroll item columns on the spreadsheet to elements of a tab-delimited array. I revised the code to make sure the each element is mapped appropriately.

Pitfalls while working off a network

Early Friday afternoon the network where I develop the applications became frozen, and I had to shut down the application from the Task Manager. Knowing this corrupts the application file, when I came in this morning, I started over again, but this time off a local copy, in case something would happen to the network again. Since I'd made the changes one time already, it didn't take long to do.

Testing it like the user would

When I tested the application to make sure the changes worked, the output looked odd. I contacted the payroll specialist and found out that she runs the bridge application for one location at a time, and after testing the application that way, everything worked out fine.