PowerShell Cmdlets for Auto Dealerships

I am getting more and more calls for help with CDK Global, DealerTrack, and InfinitiNet data extraction and I want to post now about how I can help best:

  • I will write you a PowerShell cmdlet that will exactly meet your needs

I find the Microsoft PowerShell platform the best for folks that are not technically adept to extract data when, where and as often as they want. It helps to know how to run a PowerShell cmdlet but that is not critical.


Converting to Outside Payroll

Got a call from a programmer who was helping a company convert a 5-store auto dealership company from Reynolds and Reynolds in-house payroll to an outside payroll service. The payroll company I had never heard of but they were very astute and specialized from what I could tell.

The impetus for the change seemed to be a time and attendance software vendor who appeared to use this payroll service company to to produce the payroll using data from his system. It could have been the other way around, the payroll company offered the Time and Attendance software as an important part of their service. I did not get a demo of the Time and Attendance system but the installer and I shared a remote PC connection and it seemed very comprehensive. Time and Attendance systems, if properly setup and managed (the critical part), saves dealerships money; serious money.

So the programmer who contacted me needed help extracting check history and YTD balance data from the Reynolds system. The programmer, Jim Barbour of Sequencing, Inc. was very professional and managed the project very well. Jim needed me to pull all check history as well as pay, tax and deduction year to date totals for the employees. I gave him a quote that made sense and I went to work. I was successful pulling what I thought was all the data and I forwarded a file for his review. Upon verification by the client it was determined that we did not have it all. In particular there were a few deduction items on a several employees that were unaccounted for. Having experience with Reynolds file structures, I planned for this possibility and I had built time in for the possibility that we might need to do extra digging for the odd item. I found the offending miscellaneous deduction codes and related year-to-date totals buried in a multi-valued field called “I/D”. I extracted that data which was tricky because I wanted my condition statement to filter for only those values I was looking for in the multi-valued field. It didn’t work that way. Depending on the field attributes one may have to grab it all and filter programmatically. All was well although the flat file output was rather ugly. Jim being a good sport was able to select for and import the few codes into his conversion.

The interesting thing about this conversion was that the client needed an export for tech hours flagged to go each payday to the service bureau (old term I know, I did payroll in the early 80’s on IBM 360’s for a service bureau subsequently purchased by ADP called Stat-Tab……I was the payroll manager!)  I wrote the export query and it worked out well however the concern on the client’s side was how to make sure the technician’s flags (time entries) are only paid once in cases where the flags are adjusted or if the RO is re-opened and closed again (as in possibly a warranty ticket). The answer was two-fold; one was to isolate flags by the Work Date field (in the query) rather than the RO date or any other date field. Work Date is the date the time entry is actually written, not the date the work is done or the RO was finished or closed. If we stick with the Work Date condition, during the pay cycle only those flags or adjustments done during the cycle get paid regardless of what goes on with the RO. In addition, the client had Body Shops and they wanted to pay flags (time) on ROs as the job was progressing regardless of whether or not the RO was closed in the pay cycle. Using Work Date allowed them to pay on work in progress as well.

The 2nd step in the process from the dealership’s side (I am an accountant, CPA actually but not current on my license) was to schedule the Work In Process – Labor (WIP) general ledger asset account (usually 247) by RO number. Many dealerships do this to control flagging irregularities and I believe the client had these schedules already in place. However, they became extremely important under the new outside payroll processing system. On the credit side of the of the WIP account will be the ROs total of cost of labor, i.e., all tech flags in total costed out on the RO when it was closed. On the debit side will be all the individual flags paid against that RO’s total costed labor. The RO control subtotal should always zero after closing the RO and the subsequent payroll is applied. The office, payroll clerk or office manager, must monitor the schedule often at the start of outside processing and every pay cycle thereafter for problems or irregularities. Techs are smart and some are inclined to “game” the system. Researching ROs with balances and resolving with either a back flag (a charge-back) or a correction to the RO cost and/or costing procedure is imperative so it doesn’t happen again.

All in all a workable solution for those who want to take time and attendance and payroll to the next step in these automobile dealership systems.

The ADP Data Access Odyssey

I recently undertook a project that is important for all of us in the automobile dealership industry who are seeking better ways to attract and keep customers. In the automobile dealership industry there are only a few key players providing comprehensive computer systems for the automobile dealership. The auto dealership group I work with has 21+ dealerships and has recently converted from the old Saturn EDS AS400 system (purchased by ADP to become ADP ARG) to the ADP Elite (recently rebadged as ADP Drive) system.

BMW Art Car Jeff Koons

I am not a professional programmer however I do enjoy writing code and staying current on the technological improvements in our world. I am a CPA by training and education with a BA in Economics who has worked in the car business since graduating from college many years ago. I have actually been a factory-qualified Dealer in addition to being a General Manager, CFO, Controller, Salesperson and IT Director. One of my current hats is IT Director and so I find myself embarking on this project. I thought it might help to recount my experiences about all things ADP since there is not a lot of transparency on the web in this area. I believe the retail automobile business is relatively unsophisticated in terms of IT. We typically rely on DMS (Dealership Management System) vendors to keep us informed and that leaves us a  bit like a child growing up in a closet; it is hard for us to know what is out there in the IT world. I undertook to change that, at least in my world.

DMS providers capture data, summarize it in various ways and report it back to us. I believe a competitive advantage is available for dealerships that get into that data and mine it for gold. If I can glean some opportunities from my data and my competitor’s are just looking at standard reports, I win.

For the past 10 years I have written IBM SQL data queries using Showcase Strategy for my employer and others. The EDS Saturn system was wide open in terms of Data Access. Showcase Strategy is a very robust front end for querying the DB2 database. However the Saturn EDS system was old when EDS got a hold of it and modified it for Saturn. The redeeming factor though was the ability to extract information using a robust language (DB2 SQL) and a killer front end (Showcase Strategy).

Since Saturn’s demise ADP has decided to drop support for their ADP ARG (EDS AS400) system and we needed to move up to Elite (Drive) or go elsewhere. In the evaluation and negotiation process I was sure to focus on data access as one of our most important requirements.

ADP’s platform is based on the PICK DBMS and this very old system has been built upon for over 25 years I would guess. It is a UNIX box I believe with PICK installed. When James Pick died it seems the DBMS was taken over by IBM. There must be a few installations out there for that to happen eh? Anyway, ADP doesn’t offer much in terms of Data Access out-of-the-Box other than PICK query statements called ENGLISH. There is only access to individual files with no JOIN capability. The selection of functions and operators is not good as well. This was not going to do for us so I kept pushing the ADP reps for a solution.

There is a program that ADP maintains for 3rd party vendors to access a dealerships data for the purposes of building add-on applications. They call it the Data Access Program (DAP). It is a set of APIs to access data objects on the DMS server. ADP was reluctant to offer this because it is a complicated system and most dealerships don’t have the inclination to hire people capable of working with native APIs. I had done a little C# and Windows forms work as well as a lot of VBA so I knew I could get a handle on it.

In the ADP SDK they had a .Net Data Provider so I started my training building Windows Forms apps using ADO.Net. On the sample data they offered for training all went well. They also offered an XML Engine interface which I basically ignored in my training because I figured ADO.Net would serve all our needs. I passed the training and got my certificate. There was also training on how to build custom objects which will be of considerable benefit when I get it to work on my server.

Let me just say that the DAP documentation is so-so. It is all there but you need to look in several places to get the complete picture. The email help desk people are great though so all you have to do is fire off an email if you get lost.

I have approached the DAP implementation for my organization as a slow investigation and learning process. We have been converting dealerships one or two a month all this year and by the time they are all converted in 2011, I will have the DAP program fully under way. I have abandoned the ADO.Net approach because when I went live I kept getting spurious results on my data extractions. When I asked the help desk they said that  the .Net provider is not supported on Vista Win7 platforms and to move to the XML Engine. I then had to get up to speed on the XML Engine. The XML engine accepts soap-like requests, accesses the PICK DBMS, and sends back a response. Simple.

My plan now is to develop a front-end to the XML Engine using PowerShell which will eventually serve as the start of an Excel Application that will help various users in the dealership get the data they need. I envision an Excel Workbook for those job titles that need one with built-in XML requests and the resulting formatting of the data as required. The users can then use PowerPivot to slice and dice as needed. I envision master workbooks on Sharepoint that I maintain and update regularly which might be thought of as a Base Class which all instances of the say “BDC Manager Workbook”  inherit from. I might modify an individuals instance of the workbook on rare occasions. I think this is a good plan because the Excel learning curve is already underway. If the data just appears, they can be trained to use Excel’s powerful Data features to get what they need.

The PowerShell learning curve for me has been steep and since there is little help on the web I wanted to share what I’ve learned. In the next post I will launch into the PowerShell script that has allowed me to explore and understand the ADP DAP program.