ARTICLES

MapPoint: Route Management

A lot of effort, some database wizardry and a vb application make MapPoint 2002 a badly needed management tool

This is the story of one man’s adventure with MapPoint. As you’ll see it really was an adventure because the problem was great and the solution did not come easily. I’d like to share my experience of how I overcame a series of obstacles and how in the process I created something that became a passionate pursuit.

I hope my experience with MapPoint will be of interest to others in the mapping community. Even though my routing problems are specific to my business, I think many will enjoy reading about my ups and downs with this new and exciting technology.

Family business had specific requirements

Way back in June, 1980 my wife Roselyn and I opened up shop in Raleigh, NC operating snack food vending routes servicing small businesses on the honor system. Snackpacker prospered and soon we had divisions in five states. The business was sold in 1997 and the Kachucks moved to Arkansas where David was employed as an Oracle dba.

Roselyn opened up another honor snack route business servicing Little Rock and the surrounding areas. She planned to do it on her own while I pursued my computer career. However, I soon found myself working for Roselyn again after the drastic cutbacks in IT eliminated my job at Acxiom.

It takes a large number of locations to earn a living with honor boxes. One of the most difficult jobs in servicing routes is finding the customers once accounts have been established. Honor box routes are particularly volatile with accounts being added in one area while the route erodes somewhere else. Over the years we learned to handle the nightmare of preparing routes with 50 to 100 stops per day for each driver.

Managers did most of the map searching and route sorting. The drivers would still have to find the locations plus figure out how to get from one customer to the next. But the route driver was the only one who really knew the route. So, periodically the driver would have to take home a pack of “geosort tickets” and put them in order. In addition to management time, route sorting required a significant amount of associate time, even when they knew the route. Training and retaining new drivers was a serious problem. Incomplete service days were just a normal part of business.

Routes grew to overlap with unserviceable numbers of stops alongside of routes that had eroded far below economic viability. Roselyn was spending her evenings and weekends struggling to keep the routes in serviceable order and finally, she asked David for help.

The legacy system

In 1986 I chose Foxbase v2, the DOS database system which had just appeared on the market to challenge Ashton-Tate’s industry standard dBase III as a platform to build an honor box management system. Ashton-Tate quickly countered with dBase IV, but it was too late. Fox’s clone had taken over. This is the original database programming language that I’m talking about. Known colloquially as dBase, it joins the historical archives like Ancient Latin and Greek. It is not the same as FoxPro which still exists today. But in its time it was great. I built features into SnackTracker™ that made the most of 16 years experience running honor boxes.

The system manages shortage, compared routes, allows accounts to be added and deleted easily and moved between drivers’ account folios and so forth. The relational database system is keyed on an account number. A table (dbf file) of account numbers in geographic order define the service sequence (the route order). There was only one way to get the numbers in map order. The “geosort tickets” have to be searched out one by one on paper maps and the tickets put in order. The account numbers are then typed into a tempgeo table from the sorted deck. Error checking for missing numbers, duplicates and a record count check insure that human errors are eliminated. I’m trying to make an impression of how unbearable a hassle this process is. It’s terrible tedious, time consuming and you have to do it over and over again. MapPoint was a tremendous help right from the start.

There’s still a DOS computer at the operating center running data in and out of SnackTracker every day. And, there’s a modern computer in my home office that does everything else.

First encounter with MapPoint

Long before I learned about MapPoint I was importing SnackTracker data into MS Access. MS Access is fast, fun and aesthetically pleasing. Dbase programming is none of these.

Roselyn and I wanted some relief from the constant geosorting problem. Checking the marketplace for computer programs that help with route management, it turned out there were not a lot of choices. Microsoft’s MapPoint 2002 was selected as a possible solution. At less than $300 it compared well with competitive packages that run up to $10,000. I bought it.

I had no intention of writing a program. It seemed that the MP user interface could do everything. Indeed, it does a lot.

I worked my MS Access mdb file to interface between MapPoint and SnackTracker. I used the “Import Data” function to connect. I prepared a query for each route to line up the fields I wanted on the map and to concatonate the account number with the account name. This forced the MP balloon to hold both of these in their name field. Then, the printed strip map with accompanying directions replaced the old “geotickets”. I could type the geographic order back into SnackTracker right from the directions panel.

My MS Access database kernel contains SnackPacker’s route structure with all pertinent geographic lists and reports.

SnackPacker now had route maps. This was so terrific, we were all very excited. Most important, the pinpoints were mapped and drivers would not have to search them out. I didn’t worry much if the route sequence was a bit bizarre. And geotickets were obsolete. The thrill of the accomplishment was intoxicating. I mapped my whole system. The honeymoon lasted until I had to restructure some of my routes.

About 12% of my data entry encountered errors. Some streets are unknown to MP, some streets in MP are without address data and some of my data is substandard. The first time through this was not a formidable problem. I dropped the substandard “no matches” in the zip code or city area and marked each account on a control list. These pushpins then had to be found, researched and placed correctly.

You already know about our volatile routes. What could I do when I need to reassign 600 accounts? The user interface allows reassignment 1 account at a time. Not acceptable! My MapPoint solution mimicked the old paper method. Batches of accounts are dumped into a holding route. The holding route is geographically sorted and cut back to the ideal account load.

Remember the 12% error factor? Each time an account was put back on the map the dual process of find the pin and put it where it belongs had to be repeated. My bulk route and resort process means that each problem account had to be dealt with at least twice each time the routes are restructured. This turned out to be an exhausting task that was just slightly better than the old paper map method. Nothing in the user interface solves this problem.

I’ll have more to say about MapPoint routing and the MapPoint route optimizer later. But for now, suffice it to say that MapPoint optimized our routes into swirling loops that passed by stops like a komikaze bomber coming back around and around to hit its targets on repeating passes.

Processes developed

This is about the time I started to take an interest in the MapPoint forums and the possibility of refreshing my rusty knowledge of Visual Basic. My first success was a module that allowed the geographic order to be imported through MS Access right into SnackTracker without typing. This was very important. Particularly when I had to produce 4 new routes with 400 accounts early in the morning so as to have service tickets printed for that day.

A walk through the menus of the Routeworks application may require its own essay. The foundation of the system is the integrated database kernel where each account coming into the system is stored with its latitude and longitude. My code checks for a record on the geo code table before sending it to MapPoint to find the address. Updates and changes are right at my fingertips.

MapPoint templates are an important device for compensating for weaknesses of the Microsoft product. I use a template to store information that MapPoint itself is missing or shows incorrectly. There are streets unknown to MapPoint, intersections that MapPoint has wrong plus important landmarks such as shopping centers and business complexes.

Enterprise envisioned

I started talking to delivery drivers from other companies about route planning. I learned that most of them don’t plan at all. Some use maps but many depend on asking directions along the way. It occurred to me that there might be a great need for a route planning and mapping service such as I was providing for SnackPacker.

The potential client list for Routeworks is huge and includes couriers, delivery companies, wholesale distributors, vending operators and a host of other businesses.

Routeworks proposes a new way of looking at routing. With the high cost of gasoline, among the problems presented by a difficult and competitive market, a large number of innovative managers will consider mapping and planning as proactive solutions. An application service provider (ASP) that could provide mapping and consultation at reasonable cost may be able to attract clients that lack the skill or resources to master software themselves.

	MP2Kmag Internet