Part 4: Creating Codes to Measure Productivity

March 2005, Government Fleet - Feature

by Christopher D. Amos, CAFM

An optimized network, top-of-the-line fleet information management system (FIMS) software, and stellar security won't get you much if your fleet information is poorly structured. A well thought out system of codes is necessary to organize your information so it helps you manage your fleet.The first three articles in this series took a technical look at the underlying hardware and database structure of an FIMS. Those concepts were important in their own right and as a basis for implementing concepts in this and the upcoming final article that address better methods for using your FIMS.Code Table Functions
As you learned in Part 1 of this series (see Government Fleet July/Aug. 2004, pgs. 42-47), your fleet database consists of many "tables" that serve as organizational structures for the information stored in them. Some tables' sole function is to store codes and their definitions. These force users inputting data to select from pre-defined codes and thereby ensure that the information can be grouped into meaningful categories later.Your FIMS will typically have these system control tables for finite lists of codes for things like manufacturer, make, model, fuel type (e.g., 2=diesel), vehicle classifications, operating departments (e.g., 514=Street Department), repair visit reasons (e.g., P=preventive maintenance), and vehicle systems and components (e.g., 13-006=brakes, master cylinder). There could be dozens of code lists in a modern FIMS.Typically, controlling the code lists is restricted to one or two trusted managers in a fleet organization. These individuals must be careful to include codes that cover the full range of possibilities without there being any overlap or ambiguity in the definitions. The goal here is to make it clear to users selecting from the list of codes while entering or extracting data which one, and only one, is the appropriate choice.Referential Integrity
This code table function of relational database management systems (RDBMS) is called "referential integrity." It is closely tied to the concept of table relationships and key columns discussed in Part 1. While referential integrity is absolutely necessary in your FIMS, it can sometimes prove frustrating and time consuming when you wish to make changes to your code system within an established, active database.For example, you may have started out using vehicle classification codes from the system published by the American Public Works Association (APWA). In which case the classification "120D8" would signify, "Truck, Compactor, Rear Loading, Diesel, Truck GVW 33,001 lbs. and over." This code and definition would be stored in a table for that purpose within the RDBMS. Links to that code in the main unit table and elsewhere would be established by means of storing a reference or "foreign key" to that "row" in the vehicle classification table.For reasons discussed later, you may wish to switch your database over from using the APWA classification system to that published by the National Association of Fleet Administrators (NAFA). Now the proper code for the unit above would be "8761" signifying, ">33,000 lbs. GVW, Straight Truck, Sanitation, Rear Loader."Depending on the FIMS product used, this switchover may be as simple as opening up the screen form in the program, finding the entry for "120D8" and changing the code and definition to match the new system. However, it could instead require that you make changes in several different places amidst a flurry of obscure error messages until you get the change sequence correct.Referential integrity restraints won't let you simply delete codes either if they are still in use within your database — even if the units that used them are now inactive. You may have to reactivate old units long enough to change their codes and deactivate them again in order to get garbage out of your code lists. Having your software vendor write a custom structured query language (SQL) program to accomplish this may also be an expensive but quicker option. As aggravating as working within referential integrity rules can become, it is a necessary evil to ensure that lookup screens and reports continue to work properly.{+PAGEBREAK+}Selecting/Developing Coding Systems
From the discussion above it should be clear that it is easier to think out coding systems when implementing new FIMS software than to change it afterward. The pain of switching codes mid-stream makes getting it right at the beginning especially important.So what makes a code system a good one beyond it being comprehensive and having each entry distinctive and well defined? Other key characteristics include intuitiveness and commonality.Some FIMS software uses short, cryptic codes to reduce "real estate" needed to display them on screens and reports. Other times, codes can be several characters long including both letter and number sequences. To the extent possible, make your codes intuitive to those who must input and interpret them. Doing so will reduce the training time/learning curve at FIMS fielding and for new personnel later on. Modern FIMS software generally allows codes to be selected from a pull down list that includes definitions so this point is less critical than in years past. However, most users will eventually memorize the codes for tasks they perform often. Having the code intuitively linked to the definition (e.g., P=preventive maintenance) helps improve speed, accuracy, and memorization.Selecting from established code systems rather than developing your own has the distinct advantage of making direct data comparisons with similar fleets easier. Picking the most popular code systems in use within your fleet sector, even if they are imperfect, will facilitate benchmarking efforts. Making "apples-to-apples" comparisons between fleets is difficult and time consuming enough without also having to translate between multiple code systems.So which code system is right for you? Call the peers you trust and ask them what they use and how well they work. Look for systems supported and regularly updated by fleet organizations like NAFA, APWA, and the American Trucking Associations' (ATA) Technology and Maintenance Council (TMC). Even if flawed or incomplete for your purposes, these are the most likely to remain industry standards over the long haul. Find the best match possible and stick with it.Impacts on Productivity Measurements
There are really two paramount purposes for an FIMS. First, you collect data to accurately track where your fleet resources (e.g., labor, parts, commercial repair funds) are consumed. Second, you use summaries of this data for fleet management decision support. Neither of these purposes can be accomplished reliably unless the underlying structure of the data is organized to complement your business practices and meet strategic planning needs.Collecting data about the fixed and operating costs of your fleet is a prerequisite for any cost allocation system (e.g., billing). The FIMS will provide the means to link individual units to organizational elements and/or activities so costs can be rolled up. Using the former, hierarchical relationship is commonly done as part of billing and budget planning. This later co-assignment capability by activity can be very useful when a function (e.g., debris removal) spans multiple operating divisions (e.g., Refuse Division picks up residential trash, Weed Control cleans vacant lots, Street Division sweeps roadways, Parks Division cleans parks). It is also useful for tracking the costs of special projects — especially when they are supported by grant or other special funding but require cost documentation.The FIMS's role in supporting fleet management decisions is, arguably, even more important to the cost-effectiveness of the fleet operation than the financial management function. Data should be used to feed lifecycle cost analyses to determine whether you should own vs. lease vs. rent units, the optimum replacement timing for units, and whether a service or function should be performed in-house or outsourced.Those organizations that operate their own maintenance and repair shops can measure productivity of that operation using several functions of the typical FIMS. These include monitoring the reasons work is performed, establishing standards for common jobs, and measuring downtime.Analyzing Work Reasons. Fleet maintenance operations strive to maximize scheduled work and minimize unscheduled work. Establishing work reason codes that track subcategories of these two lets you produce summary reports that show how shop resources are being allocated. For example, scheduled work may include post-delivery inspections/make-ready, preventive maintenance (PM), predictive maintenance, deferred maintenance, seasonal make-ready, and pre-disposal processing. Unscheduled work may include driver report, abuse/neglect, yard-call, road-call, re-work, act of nature, vandalism, theft, and crash.Knowing how many work orders, downtime, and total expense is attributed to each of these work reasons give you a clearer picture of where you need to focus improvement efforts. {+PAGEBREAK+}Downtime Measurement. Minimizing unit downtime is a primary goal for fleet organizations and is probably even more important than cost control to most fleet customers. Before you can manage the causes of downtime, it is important to accurately determine the severity of the problem and its causes. Work reason codes help you determine the causes, but actually measuring downtime requires several parameters to be set in your FIMS and consistent implementation by shop personnel.A good definition of downtime is the amount of time when a customer needs a unit but it is unavailable due to maintenance or repair. For the FIMS to calculate downtime, it has to know what the operating hours (i.e., shift), days of week, and season(s) of the year it normally is used. Shop personnel must be trained to open work orders on vehicles as soon as they break down or are presented for maintenance so the downtime clock starts ticking at that point. The clock stops when the shop notifies the customer that work is complete and the unit is ready for pickup. The FIMS only accrues downtime for the hours established as its normal operating shift between those two points.Once you know how much downtime you actually have, you can then decide if the added expense of operating a loaner pool, conducting after hours maintenance, authorizing overtime to complete repairs, and other strategies are warranted.Monitoring Labor Performance. Labor estimating guides rarely cover jobs for vehicles other than light-duty vehicles. That fact doesn't absolve fleet maintenance operation supervisors from devising an objective means for technician performance evaluation. Features in most FIMS can assist with this important task.Units are generally grouped into vehicle classifications and further into technical specification groups within those classifications. Jobs are generally broken into system (e.g., 13=brakes), component (e.g., 006=master cylinder), and even specific part codes. Where "tech specs" and "job codes" intersect in shop work order detail, the FIMS tracks actual labor times and can produce an average similar to those in published labor guides.These averaged actual completion times can be used as the performance standard if reasonable. However, if the shop supervisor wishes, he/she usually may instead enter a time estimate for "standard jobs" using times derived from direct observation, personal experience, or some other means to override historical FIMS record averages.Improving Shop Productivity
A properly configured FIMS assists shop supervisors and fleet managers in tracking several other critical business processes and asset tracking. Among these are scheduling preventive and predictive maintenance, flagging warranty claim opportunities, controlling valuable inventories, and tracking prices of parts and supplies.Scheduling PM. By placing units in maintenance classification groupings and establishing PM intervals for those groups, the FIMS can produce reports for scheduling upcoming visits. It can also alert shop personnel when a unit brought in for repair is approaching its PM interval and if it is overdue for service. Both forms of alert can prevent back-to-back visits — something that really irritates fleet customers.When used in conjunction with the standard jobs feature already mentioned, the FIMS can generate work orders in advance for scheduled shop visits and even provide a pick ticket for parts and supplies that will be needed so they can be waiting when the vehicle arrives.Warranty Claims. Failing to collect on vehicle and parts manufacturers' warranties is a major cost opportunity missed by many fleets. A FIMS can alert shop and parts personnel to potential warranty claims when warranty periods are input for them. It compares vehicle in-service/part replacement dates from repair history with the warranty period information. The FIMS records can be printed to support warranty claims.Inventory Control. Keeping track of the thousands of items purchased and issued to vehicles is a daunting task even with FIMS assistance. Add to that the complexity of stocking the proper quantity of high-use items and it is near impossible unless part/material records are properly coded.The FIMS can assist parts personnel in determining what they should stock by revealing usage patterns. Taking this information coupled with the length of time it takes for items to be delivered permits setting reorder quantities and trigger points. The system can then generate an order list by supplier to facilitate keeping the inventory at proper levels to support maintenance and repair operations.This same system also keeps accurate counts of what should be on the shelves by tracking what has been issued to work orders and subtracting it from what was received into the system. Of course, ensuring that items are received into the system is a matter of manually spot checking invoices. Once in the FIMS, there will be a positive control trail of transactions involving the items. Price Checking. Comparing prices charged for parts and materials against contract prices is another labor intensive process. Some FIMS allow contract prices to be entered for price check purposes while others help by displaying the most recent price paid and averaging the value of any stock. Parts specialists receiving items are alerted to possible price errors if the invoice amount is different than that in the FIMS. Any inconsistencies should then trigger a contract price list check to determine if the price has changed or the invoice is in error.How Much Detail Is Needed?
With the many features offered by most FIMS products, the problem is often deciding on and properly implementing those necessary for your operation. Discovering that something you need is not available is rarely an issue. There is no substitute for thorough vendor-led training in this regard.Once you understand all the features and what they will do for you, the next step is to decide how much detail is really needed and whether or not the cost of implementing the feature will pay off. Some features require a lot of up front investment of skilled labor but will not be much trouble to sustain. Hiring outside help may be a good solution for this type of work.Finally, the overriding guide in these decisions must be to what extent the FIMS supports attainment of organizational goals and objectives. In other words, is the "juice worth the squeeze?"

Comment On This Story

Comment: (Maximum 10000 characters)  
Leave this field empty:
* Please note that every comment is moderated.


Fleet Management And Leasing

Jack Firriolo from Merchants will answer your questions and challenges

View All


Public Fleet Tracking And Telematics

Amin Amini from Verizon will answer your questions and challenges

View All


Fuel Management

Bernie Kanavagh from WEX will answer your questions and challenges

View All


Recent Topics

I am requesting feedback on how often you replace street sweepers? Also, when you surplus the old sweeper, what has brought you the...

View Topic

would anyone be willing to share there salary ranges for equip tech I thru III, and a welder salary range also

View Topic

Fleet Documents

1106 Fleet Documents (and counting) to Download!

Sponsored by

A 6- or 9-pin port in most heavy-duty vehicles that allows diagnostic information to be obtained with an appropriate scanner.

Read more



Thi Dao
What Your Vehicles Say About Your Fleet

By Thi Dao
Your vehicles may paint a certain picture of the fleet and even the public agency. Is the picture a positive or a negative one?

Are Your Drivers Safe?

By Thi Dao

Managing a Police Fleet

How Chevrolet's Tahoe PPV Differs From its Retail Relative

By Michaela Kwoka-Coleman
For the Chevrolet Tahoe PPV, tires are added to the vehicles that are capable of handling speeds of up to 134 mph and the brakes are adjusted to handle frequent stopping at high speeds.

Police Vehicles Pushed to the Limit in California

By Paul Clinton

Next-Gen Fleet

Facundo Tassara
Streets of the Future Could Take Automatic Tire Readings

By Facundo Tassara
WheelRight allows vehicles to drive through a specialized lane and captures tire pressure readings on all four tires while also providing tire tread depth within a few seconds and printed receipt.

Improved Communication (There's an App for That)

By Facundo Tassara

Driving Notes

Chris Brown
Hyundai Ioniq Plug-in Hybrid

By Chris Brown
The Hyundai Ioniq Plug-in Hybrid is the least expensive model on the market to offer electric-only range, while offering spirited driving performance and plenty of standard features.

2018 Range Rover Velar First Edition

By Mike Antich

Nobody Asked Me, But...

Sherb Brown
Keep Realistic Fleet Expectations

By Sherb Brown
If you don’t stay on top of the latest developments in mobility, battery technology, autonomous vehicles, and telematics, you are subjecting yourself to the whims of senior management who may be making decisions based on the latest trendy news.

Fleet Management 2.0

By Sherb Brown