Vision Client Info Center Cleanup

This post has taken a lot longer than I would’ve liked because I started writing it before i had actually begun the project that it’s about. So I was writing from a project plan perspective. A valuable lesson learned – even the best laid plans sometimes change!

Let me start by saying that a good portion of my career in IT, Info Systems, databases and more than ever, working with Deltek Vision, has been spent cleaning up other people’s messes. At times this can be frustrating but not so much because I’m cleaning up a mess….that part can be fun and challenging….what’s frustrating is that time and time again these messes can usually be avoided by having good process and controls in place on the data entry side in the first place.

A case in point is the Client Info Center in Vision. I’m currently in the middle of a massive cleanup effort of a Client Info Center that for the most part could have been avoided if some basic controls and some process and procedure had been put in place early on in the implementation. Another key element that was missing was ownership – as in who owned the data and ultimately who was responsible for maintaining its integrity.

What Exactly Constitutes a Mess Anyway?

Obviously opinions will differ between perhaps a regular end-user of Vision, an accountant and an IT professional or database person as to what constitutes a mess in an ERP system. But I think just about everyone would agree that the Client Info Center in Vision is a critical set of records that we need to be very cautious working with so as to not dilute it with useless records (clients we don’t actually work for) or unnecessary duplicate (or more) records. In the particular instance I’m writing about, a mess is defined mostly by the latter and a lot of incomplete records combined with no controls in place to stop the continued dilution of the data.

What went Wrong?

When this firm was implementing Vision someone in a position of influence decreed that the end-users, and more specifically, the Project Managers, were going to be the caretakers of the data. This was probably a very well-intentioned decision meant to empower the users who, prior to Vision were starving for the vital information needed to effectively manage their projects.

This meant that in addition to setting up their own projects, including billing terms, rates, etc. (possibly another post unto itself!), the users had free rein to add new clients, addresses, contacts, etc. with little oversight, control or training on proper usage of the client info center.

The End Result

The end result after four years live with little or no control over the data entry in the Client Info Center is painfully predictable. Here are some of the issues that have resulted:

• Non-existent nomenclature for client naming, numbering, address naming/numbering or formatting
• Several hundred clients have multiple records
• Many of those clients are replicated as many as 20 times
• Many client records duplicated with only slight variations on the name such as spelling, capitalization, etc.
• Many of the additional client records appear to be entered as a means to add an address to clients already in the system
• Users are creating a single entry client record to represent both sides of a “care of” arrangement rather than creating one record for the primary and one for the billing
• Client categorization for market sector and type are inconsistently entered
• Many of the address records are incomplete
• Most addresses do not have a useful description or simply have the name;
• Many addresses incorrectly flagged as Default, Billing or Accounting Only or all 3
• End-users say they are unsure of the correct client to use, and proceed to add yet another new client record!
• Ability to report WIP, AR, Revenue, etc. by client or market segment, etc. is nearly impossible to do accurately without manual manipulation

Identifying Specific Fixes

Identifying the specifics of what needs to be fixed to clean the data is not as straightforward as it may first appear. I’m sure Key Convert and Merge is the first thing that comes to mind but once you look a little closer there is a lot more at play. Let’s look at a few of the specific issues before we look at solutions:

1. Replicated client records
2. Lack of client numbering system
3. Inconsistent Address descriptions
4. “Care ofs” or c/o clients that hold 2 legal entities in one record
5. Inconsistent data entry/incomplete information

Now I’m a huge proponent of scripted fixes and a key conversion is essentially a scripted fix that will do the heavy lifting for you, however key converting/merging is only one part of the solution. At first glance many of the replicated Clients appear to be simply user-created variations of the names, but upon closer inspection some actually represent multiple legal entities such as partnerships for larger projects, subsidiaries of a parent company, etc. With “Care-ofs” we may see things like a client record called “Management Company 123 c/o Strata Corporation 123”. The intention here is that the Management Company hires us to do work for the Strata Corporation of a housing development. So to satisfy a simple issue like the salutation on an invoice they have created a client record that represents both the Primary Client and Billing Client in one record. So if the Management Company hires them for another Strata they need to be entered again and conversely if the Strata changes management companies they need to be added again!

So as you can see Key Converting will only get us so far and before we can even begin to Key Convert we need to do some research on each situation to make sure we are making the correct changes. This is where a brute force approach becomes the only answer to safely cleaning up this mess.

So let’s address the 5 issues above with some detail:

1. Replicated Client Records– Before we can Key Convert and merge any of these records we first have to:
   1. Identify all Client records which could be a replication of the same Client
   2. From that list, analyze and determine which if any are actually separate legal entities (e.g. 123 Engineering Ltd. vs. 123 Engineering Partnership Ltd.)
   3. Flag each record to be Key Converted by appending the master record Client ID # to the end of the name (e.g. 123 Engineering Ltd.50001). This will aid with the Key Convert process especially when we have numerous records which on the surface appear identical.
   4. Perform our Key Converts
2. Lack of a Client numbering system – This may be a bit of a non-issue in that Vision doesn’t actually require a Client record to have a unique ID field. Instead it uses a Primary Key field in the database which is auto-generated. That being said I am still a huge proponent of having a unique ID as it can be used for distinguishing between seemingly identical records. Also many users still like to memorize a client by an ID number. We can easily set up Vision to autonumber the client ID field and even give it a number to start at. In this case we ran into a couple of issues. Firstly, some records DO have a unique client ID, presumably because they were imported from another system or perhaps they were being numbered in the early days after implementation and had subsequently been stopped. Secondly, we can’t go back and autonumber old records without manual database manipulation. So while the clean up is underway it was decided that ClientIDs would be manually assigned starting at a specific number higher than any existing numbers. In this way we can carry out step 1.1 above by manually tracking our numbers. Autonumbering will be enabled but beginning at a number high enough to allow us to manipulate the cleanup records and number them manually.

Inconsistent Address Descriptions – Surprisingly this issue creates the most challenging obstacle in the cleanup! Why? Well to start with let’s think about what happens to addresses on individual client records when we Key Convert and merge them…..you guessed it our new combined record now has all of those addresses from the old Client records listed under it. Now think about what had been happening over 4 years: A client was added and an address was entered and not given a name in many instances or if there were more than one at least one would have; for its description. Then when someone didn’t know how to add an address, they added a new client and entered the address again! Now when we combine them together we have all of the combined addresses together in one record. Fortunately, Vision is smart enough that when it Key Convert/Combines Client records, if it finds an address with the same name it appends a numeric value to it. So now we have 1 Client record with 10 or more addresses and many of them are named1,2, etc. We can’t leave things in this state because our end users will never know which address to use! The solution requires a few steps:

1. First we need to establish a naming convention for our addresses. This will keep them consistent within a Client record as well as across the system. In this case we came up with something quite simple: 1.City.Street.Number where we simply give them a numeric value as we add them, use the City, then Street, and in those rare instances where we have 2 addresses on the same street in the same city, we use the address number. e.g. 1.Anytown.Anystreet.1234 2.Anytown.Anystreet.4567. No address will be left with the; value and shall always be quickly identifiable this way.
2. Secondly we need to determine which address or addresses are correct and since Vision has no Key Convert for addresses we will need to establish which ones will stay and be renamed and which will be removed. This is the BRUTE FORCE portion! Since we do not want to lose our addresses on Project records, especially on the Billing Address, we will rename the most heavily used addresses and reformat them to our new naming scheme. The quickest way to do this is to look up all Project records where the Primary or Billing Client is = to our newly key converted Client name. Then we can switch to Grid view, and make sure we have the Primary and Billing Address fields displayed. Now we can quickly sort and see which address(es) are the most used and reformat them. If it’s a crap shoot then we will simply pick one and start with that.
3. Once we have our correct address(es) reformatted, we need to go back to the Project Info Center lookup and correct any outliers. Again this can partially be done in Grid View but for some unknown reason Vision only allows you to change the Billing Address in Grid View. Primary Company Address must be changed in Tab View so that could be a lengthy process depending on how many records we’re dealing with.
4. Once all of our project records have been updated to reflect our correct addresses we’re almost ready, but not quite to delete the old redundant ones. Guess what? We have to check the addresses on all of the contacts associated to the company as well!
5. Following the same basic steps for updating project records we must now locate and update the addresses for all contacts. Once again this can only be done in Tab View.
6. Now finally we’re ready to start deleting the unused addresses with one final caveat. Vision will allow you to delete an address even if its associated to a project or contact – but it will warn you – one record at a time. In our case what we’ve discovered after thinking we had all our bases covered was that addresses were still tied to projects. But how? Through the Associations Tab! Something I had even realized myself was that Vision automatically adds your Primary and Billing Company to the Associations Tab – actually I did know that part – what I didn’t realize was that when you change the Primary or Billing it doesn’t update but rather adds the new ones! Better still if you copy a project repeatedly you can quickly accumulate a lot of associations that are irrelevant! In our case we knew these were irrelevant and proceeded to delete the redundant addresses and ignore any warnings.

Conclusion

At the time of posting this we’re still in the middle of this massive cleanup and a lot of little surprises have turned up. Since this post got a little longer than I anticipated I will follow up later with some lessons learned and a bit more information on the steps taken to get better control over the data entry.

Kevin Coles
[email protected]