Introducing a Better Rapid Timecard Entry Process for AssetWorks AiM

Within our working group that utilizes AssetWorks Action Information Management (AiM) System we have a saying, "There is nothing rapid about Rapid Timecard Entry." And it's true, Rapid Timecard Entry is just specialized enough that our shop workers have a hard time using it while being just generic enough that it's become overwhelmingly cumbersome for our data entry specialist to enter 200+ worker's timecard data.

There had to be an easier way, and after working in HR Information Systems for over 5 years, I knew that there certainly was.

The Planning Phase

There were a few ways that we could go about a solution for this problem:

1. Retrain our shop employees to enter their own timecard data during downtime. This solution could have worked, but due to the varying levels of computer skills and the lack of quality control and oversight that this would present, we felt that this was not the best strategy at this time.
2. Utilize more workers for data entry. This would also work, but at a higher cost to the organization for mundane, repetitive work.
3. Have managers enter all time on a standardized excel timesheet and the load that timesheet in the back end through SQL. This also had the potential to work and it would work fast, but to make it work effectively, you would have to include all of the parameters and integrity checks that the system already has built in. Why rewrite a system that is already built for you?
4. Have managers enter all time on a standardized excel timesheet and the load that timesheet in the front end through an automated process. 

Of course, we chose process 4 as our solution. It maintains data integrity because it forces use of the existing system's data integrity checks. It actually reduces the time spent in data processing - making technology work for the user, not the other way around. Now our data entry specialist has more time for audit and quality control, instead of performing a mundane and repetitive task that was prone to keying errors and had the potential for physical discomfort and/or injury.

So I went about looking for an automated platform. At first, I went with Macro Toolwork's ClickyMouse program, which has a handy automation tool with an easy-to-use recording interface. I've used it a few times for mouseclick automation. Unfortunately, however, the free version does not have the functionality that I required for this project - including loops and conditional statements. The scripting version was $80, but the code still did not seem clean enough for me to go through the process of purchasing it immediately, so I mulled over that decision.

And I'm glad I had pause, because not but a week later, there was a post on Reddit, of all places, about Windows automation, and a whole bunch of users recommended AutoIT and their text editor SciTE. AutoIt was not as easy to use as ClickyMouse at first, because I had to learn the basics of AutoIt's code and the underlying Lua code syntax as well, but all of the functionality was there. It could work with excel by using it's excel library and the same for Internet Explorer through it's IE library.

Now I had all of the tools that I needed to build the automation.

Development: Building a Better Rapid Timecard Entry

Excel Timecard Entry Form

Here is the Excel File (converted to Google Docs) that I developed as the template for Building Services Shop Supervisors to enter their time. The main form is basically a standard timesheet, but it's much faster to use than AiM Rapid Timecard Entry Screen.

Note 1: All sheets, except for the time entry sheet are locked in the original. The data entry specialist has the key to unlock the "Automated Source File", "Source File Backup" and "Lists" sheets.

Note 2: the True/False CapEx row is a way we change the Time Type field from it's default to the CapEx Time type. It's actually a simple checkbox in the Excel version. 

Excel doesn't transfer directly to Google Docs, so some of the content will need to be rebuilt - especially the sorting sheets. All sorting sheets have the content that you will need to rebuild the file. Alternatively, you can try to download the original excel file from GDocs (converted verison), but I cannot guarantee that it will work correctly on your computer.

• Sorting Sheet 1 - Captures the data from the timecard and generates a table. Each row is an employee. The next 5 cells are all the information for a single time entry on the rapid timecard. The next 5 cells are for the employee's second time entry, and so on. 
• Note 1: You can use the sorting rows (row 2 & 3) to sort by header. It is much easier to change the formulas by cells of the same type. You can then put it it back into it's original state (by time record entry) by sorting on row two and sub-sorting by row 3.
• Note 2:  This timecard is for a specific shop with blanket orders, all blanket orders are coded as ("BLDG", bldgNum, year) so a blanket order for building 101 would look like "BLDG 101 2013".  If this isn't how your work orders are named, so you will need to correct each formula in the "Work Order" column to your specifications.
• Note 3: We here at Appalachian State Physical Plant do not utilize Leave Codes (we use blanket orders by Shop), so this field is not in our automation build. 
• Sorting Sheet 2 - Essentially, all Sorting Sheet 2 does is changes the data output in Sorting Sheet 1 from rows to columns. It captures all of the data from Sorting Sheet 1 and breaks each employee row into 30 individual time records (grouped by employee). Someone excel-savvy coul have does this in one step, but it was easier for me to wrap my head around it by splitting the sorting and grouping into 2 steps.
• Note: You'll notice on Sorting Sheet 2 that there are a lot of blank entries for each employee, these are where employees do not have any time for the building on that line.
• Sorting Sheet 3 - Removes all of those blank entries from sorting sheet 3 through some ranking and vlookups. 
• Removing blank rows through formulas can be an incredibly valuable but complicated process. I have used this on many automated files, but have never seen anyone else use it. Some coders use macros that do similar operations, but for downstream users that cannot or will not use macros, it can make forms, processes, and automation in excel much easier. I'll eventually be writing a tutorial on how to do it.

Macros

These Macros are for the data entry specialist. It helps her build the records that will be put into AiM. It isn't essential to use these macros, the specialist could simply copy the valid time records on "Sorting Sheet 3" and paste them into the "Automated Source File" sheet and then clear them for the next entry.

I created buttons on the "Automated Source File" sheet where the colored cells are on the GDocs File.

AutoIt Script

Here is the script. Copy and paste it into AutoIT SciTE.

• This script only works with Windows Internet Explorer, but I've tested it thoroughly and it should work by copying the "Automated Source File" sheet on your excel file. You will need to change the path directory of both the excel file and you AiM URL - find the places where it says "your ... here."
• Note: Our capEx logic is in that file. You may need to change it.
• A huge thanks to Rarst for paving the way to build an array from an excel file.
• Also a huge thanks to the AutoIt help forums and Dale Hohm for building and explaining the AutoIT IE funcitons library.
• Other helpful links:
• AutoIT documentation, dictionary, and function library.
• Lua code reference library.
• More Lua help