Overview

Most seasoned Deltek Vision users have experience using the Key Conversion utilities. Key Converting is basically a way to modify Key fields in your database like Project No. (WBS1), Org, etc. without breaking important relationships within the database. Key Converting is also handy for combining records such as duplicate Companies, etc.

Key Convert Project’s Org

Key Converting a Project’s Org is the process of reassigning a Project, Phase or Task from one Org to another without breaking any existing data relationships such as time/expense entries, invoicing, etc. Perhaps a project was mistakenly created under the wrong Org so to correctly assign revenue, costs, etc. to the correct Org it has to be moved. But Vision will not allow you to simply change the Org in the Project Info Center once there has been activity because there are background relationships that would be affected. This is where Key Converting of the project’s Org is required.

What if I Need to Convert a Lot of Projects

So what if you need to convert more than a handful of records at one time? Recently I ran into a situation where a client was doing a complete Vision reorganization to better align their system to their actual organizational structure. This meant that their Org structure was going to expand from Company:Region to a Company:Region:Discipline model. This would mean that every project would need to have it’s Org updated…..thousands of Projects! Anyone familiar with the Key Convert interface knows that manually entering thousands of projects for Key Conversion would be an insurmountable task.

Leveraging SQL

So how do we Key Convert the Org for 1000’s of projects? Well it’s actually pretty easy – we populate the Key Convert table at the database level using a basic SQL script. Now obviously every situation is different but the basic concept remains the same and isn’t limited to any single Key Convert utility – all of them store the records to be converted in a database table – which can be pre-populated via a SQL script. Then once the update is done you can run the Key Convert from within Vision.

In the particular instance I was working on we needed to move the Project Org from Company: Region to Company:Region:Discipline. We quickly determined that the best way to correlate the correct new Org for each project was to use the Project Manager’s Org. Since the overall reorganization also required every employee’s Org to be updated, it would be easy to match the PM’s Employee Org to the Project Org.

Here is a basic SQL example for how the Key Convert Project’s Org table was populated:

INSERT INTO KeyConvertWorkPROrg (WBS1, WBS2, WBS3, NewOrg, Timeframe, StartPeriod, Account)

SELECT PR.WBS1, PR.WBS2, PR.WBS3, EM.Org, ‘1’ AS TimeFrame, ‘0’ AS StartPeriod,
‘116.00’ AS Account
FROM PR
INNER JOIN EM
ON PR.ProjMgr = EM.Employee
WHERE PR.Org like ‘CompanyA:%’ and PR.Status <> ‘D’

So basically what this simple script does is “Insert” into the Key Convert table, a selection set of projects and the Employee Org of each Project’s PM. You can use the WHERE clause to filter out the projects you want to update. In this case we only update projects from “CompanyA” that were not of a Status of “Dormant”.

Once the script was run we simply went into Vision and ran the Key Convert Process, hit Run and waited……about 1/2 hour to update 14,000 records!

If you have any questions feel free to comment or drop me a line: [email protected]