Oh to Canada…

It’s funny how the things that should be the easiest to solve can turn in a total nightmare. We offer this great feature called “Where to Buy”. When the customer clicks on the part to get more details there will be an extra button in the header called “Where to Buy.” It will then take the user to a separate window that will allow a look up for store locations in their area where they can purchase the part at a location convenient for them. This should increase the local stores visibility to the internet buyer.

To accomplish this we need to tackle three problems. First, is to secure a good source of zip code information that includes latitude and longitude coordinates. Not a problem for the United States as the internet is loaded with this information for free. We struggled to find a good source of free Canadian zip codes, so that got shelved for the time being.

Next was to find a good SQL command for calculating the distance between zip codes. We wanted to give the user that ability select a range miles to search. Again lots of good examples on the internet, we settled on:

SELECT * FROM ZIPCODE WHERE SQRT(POWER((69.1*(latitude-';
sqlline := sqlline + floattostr(myLat)+')),2)+POWER((69.1*(longitude-('+floattostr(myLong)+'))*cos(latitude/57.3)),2)) < '+myRange;

myLat = latitude of the zip code supplied by user.
myLong = Longitude of zip code supplies by user.
myRange = distance to search with in.

This query supplied a list of all the zip codes within the supplied parameters. The next operation was to build a query of all zip codes and search our store database to find all matches. We perform the query and check to make sure store carries that brand. If the list is empty, we check the list of internet suppliers. Then we build a XML result set and pass it the UI. Give it a try: http://www.showmetheparts.com/wheretobuydemo/

Now things get harder with Canada. We were able to find a list of zip codes for Canada, and swear every person has their own zip code. The database was over 800,000 records and their zip codes are 7 characters long. Our well tested routine took a serious database performance hit because the long/lat result set returned over 4000 records. The whole calculation took over 5 seconds which in a browser is a lifetime.

After closer inspection of the Canadian zip code file we noticed that the first 6 characters were the ones that were significant. So we reloaded the zip code table with unique records based on the first 6 character. We had to change our store query to use Like instead of =. Bing, bang, boom. Our “Where to Buy” option now supports Canada. Seems like a simple enough solution, but it took me about 4 days to figure out the correct solution. (that was not only thing I was working on at the time, lots of projects going on at Vertical Development).

Elevator Speech.

My business coach tells me I need an elevator speech for ShowMeTheParts.com. You know a 20 second pitch to tell someone everything about us.

How's this sound?

ShowMeTheParts.com is a web based parts catalog featuring the latest and greatest parts information from leading parts suppliers covering everything from Cars and Trucks to Power Sport to Ag Equipment, Medium Duty trucks and everything in between. ShowMeTheParts.com user experience can be completely customized or combine our content with your program to build unique solution”

The Database

When it came time to pick the database for ShowMeTheParts the choice was easy. We are basically an anti-Microsoft shop. We don’t use any Microsoft development tools when building applications for our Automotive Aftermarket catalog customers. Our catalog application is entirely native and only requires 3 DLL’s to run, so our application runs on all version of Windows. We never have to worry about some other application installing some goofy Microsoft DLL that throws all other well behaved programs under the bus. I started working with Extended Systems Advantage database server over 15 years ago as a DBF replacement for Clipper and have never looked back. Sometimes we get crap from IT departments from prospective clients because we don’t use SQL, but one of the great advantages is our application/database doesn’t require a Database Administrator. So our customers are not depended on IT resources, which is huge. It is so simple to install, our director of Operations (who has no IT experience) is able to walk most customer thru the installation. In fact a large customer’s IT shop insisted on me coming out and installing an upgrade. It took me about 2 minutes to install (45 minutes to drive there) and when I said I was done; the IT manager fell off his chair. “Are you done already” were his exact words as he picked himself off the floor. Those 3 DLL I spoke if earlier are for Advantage, so once it’s installed it runs quietly on any Windows Server, just doing its job. No IT resources required.

So we love Advantage here and with 15 years of experience we can really make it go.

Next week I’ll get a little deeper into the structure of database and how we related tables together.

I’m off to get a 9 mile run around Waterfall Glen.

Web Application

Ok now for the sizzle, the web interface that everyone sees and is wowed over.

After a little html/java script programming I decided very quickly I had very little patience for learning a new learning a new programming language. When I made the change from a DOS (Clipper) programmer to a Windows programmer I just about quit programming. Most programmers egos are way to fragile and mine is no different. So using the excuse of staying true to our business model and focusing on the database and the catalog I started looking for partners who would be willing to handle just the presentation layer, no database access. Now I’m the first to admit, we struck gold the first time out with Mark Lincoln from www.CycleConsulting.com. Mark has forgotten more about programming than most people will ever know. Mark wrote an interesting article that was to be published by EXT-JS, his development tool of choice. So instead of me writing something up, here is a link to the article: http://www.showmetheparts.com/Html/SmtpImplementationSpotlight.pdf

ShowMeConnect - Data Access

ShowMeConnect is the most important piece of software in our application tool set. Our goal was to separate the database from the presentation layer because we really only wanted to focus on the catalog and not development of another shop management or warehouse system. There already were lots of companies doing that and our mission was to help suppliers get data into the market place faster than the other options available. We wanted to make it as easy as possible for others to access our catalog and add their own business logic. ShowMeConnect is a rich set of API (application programming interface) for accessing any information in the database and return a well structured XML document. The beauty of this strategy is that others don’t have to use all of the data in our catalog, they can cherry pick what they need. For example if a web site needed a group of images for a part number, instead of waiting for the supplier to send him images, they could make a call with a API and we would return a XML file with the URL’s, Height and Width of all the images we have for that part number. This helps everyone in the supply change. Instead of the supplier having to spend time ($) trying to make sure everyone had their current images, they could tell their customers to found them in one place. The customer benefits because they will always have access to the latest images. One of the really cool things we built in was to take advantage of PIES digital assets. The supplier doesn’t even need to send us the actual images; they can send us the URL where to find the image on their servers, saving the industry the time and money spent trying to send images to everyone.

With this strategy of decoupling the data and the presentation it should make it easy for some smart mobile application developers to create iPhone or Droid applications without having to build the database. They can focus on what they do best.

Next up: ShowMeTheParts.com.

Lets Get Started

I came across an interesting web site http://www.hubspot.com/ that will evaluate your web site. You just need to supply your email address and your web address. They will run a free evaluation of your web site and provide some really cool results. Here are our results. http://websitegrader/com/site/www.showmetheparts.com. We found a couple very simple things that we could do to improve our site right away. One thing missing was a blog. Now we already have a Facebook page and Tweet all the time, what could we possibility blog about. I thought maybe people would be interested in the some of the inner workings of how ShowMeTheParts is being developed and extended. So this blog was born. We have done a lot of really cool things with our technology over the last 3 years and not a lot of great communication on what we have done. So this blog will serve as a log of ShowMeTheParts.

Let’s provide a little history first. ShowMeTheParts was created out of frustration we would hear from our catalog customers about how long it would take for their catalog content to make it into the hands (or eyes) of their customers. Vertical Development’s main business has always been providing software solution to aftermarket suppliers that help them maintain their catalogs and produce the printed books and electronic files. www.VerticalDev.com So their pain was our pain. Since we know a little about automotive catalog database, we thought we could build something that could be of value to their customers and so ShowMeTheParts was created.

Our technology actually consists of 3 pieces of software besides the database.

ShowMeBuilder is our content management tool that our product managers use every day to load data we receive from our data providers. One of our goals was to turn data around in 24 hours, so we needed a tool that could easily replace a data set with a new version. Depending on the source file, it takes 3 steps to replace data set. Read the file, Remove old file and load new one. It’s really that simple. We also needed to follow industry standards, so we adopted the VCDB as our application catalog. One of the biggest road blocks other aftermarket catalogs encounter is the conversion from the industry standard tables into their own propriety vehicle table. This process takes time and could cause mistakes if not handled properly. So the ShowMeBuilder takes data from our data providers and quickly loads it with little conversion, that how we achieve that 24 hour turn around.

Ok that is enough for now. I need to get back to programming. Next time I'll take about ShowMeTheParts.com