I’ve had several clients who have run into a problem with their Baseline Change Log because multiple changes get clumped into a single change log reference number. The scenario has happened on projects that are processing multiple changes on a program within a given month.
The change process will be completed in the schedule to reflect all of the changes and then all the changes are loaded into Deltek Cobra at the same time. The problem comes when they go to enter the change number and add the comment; there is only one number and comment for each transaction.
The transaction, from Deltek Cobra’s perspective, is the pulling in the data from the schedule. So even if there are three changes identified in the schedule, if they are brought into Cobra during the same integration they all are coded with the same change number. The log will correctly reflect the different control accounts that the changes were applied to, but if your customer wants to see each change identified separately in the log, they’ll have a problem.
One way to avoid this issue is to use the filter function on the integration wizard and pull in each change separately, so you can have a unique change number and comment for each one. But if you have already pulled them in, you’ll have to make a change to the log itself. Here’s how you can do that.
Like all data within Deltek Cobra, your change log transactions are stored in a table in the database. You can use the SQL Command Utility in Cobra to retrieve and update records in these tables. First, by refining your SQL statement, you make sure that you only impact the records you want. Once you have made sure youa are only going to impact the records you want, you can change the SQL statement to an UPDATE script and update the information in the log with the correct change number and description. This takes two queries that we’ll walk through below.
The first query is the SELECT query that pulls the information that is in the log currently. The best way to do this is pull all the fields in the table by using the * variable after the SELECT statement so you can see everything that is in the table. You are going to want to pull the data from the BASELOG table and then you will filter the results by including specific values for the different fields in the table.
In the example below I’m using the PROGRAM, REFNO and CA1 fields from the table. The field REFNO is the reference number for the transaction and is the value you see in the Log report under the column heading “Transaction Number”. I know which one to use in my query because I pulled it off of the Log report.
SELECT * FROM BASELOG WHERE PROGRAM =’XYZ’ AND REFNO = ‘1234’ AND (CA1 = ‘1.01.01’ OR CA1 = ‘1.01.02’)
For the Control Accounts I’m using the CA1 field and you see that I have included an “OR” in the SQL statement so I can tell it to look for the records associated with more than one Control Account if I need to. You can update the CA1 values to narrow the records returned to other control accounts. It is important that the CA1 entries are within the parenthesis. You will run this query to make sure only the records you want to affect will be included. Once the results of the query are what you want, you can create the UPDATE query.
The UPDATE query below will update the specific fields (CCN and Log Comment fields) identified in the query. By using the same “WHERE” clause as the select statement above you know which records will be updated.
With an UPDATE query you tell the system what fields you want to SET a value for and then the value to SET those fields to. For example, if I want to set the change number field (CCN) with “9876” you use SET CCN = ‘9876’. You can set the values for multiple fields in one query, just separate the fields and their values with a comma. The example below sets the values for records we identified with the SQL statement above:
UPDATE BASELOG SET CCN = ‘BCR-16-001’, LOGCOMMENT = ‘Detail planning for 2016 material.’ WHERE PROGRAM =’XYZ’ AND REFNO = ‘1234’ AND (CA1 = ‘1.01.01’ OR CA1 = ‘1.01.02’)
With this SQL statement applied, the Log will correctly reflect the BCR number and comment for the 2016 material charges.
Final Thoughts
A couple of thoughts before trying this. You need to have access to the SQL Command Utility in Deltek Cobra and access to the Project. If you don’t see it on the menu, your login doesn’t have access to the tool.
This is an advanced way to manage the Cobra data. You should only try this if you are comfortable with SQL commands and have the rights to the data. Prior to running the UPDATE query you should contact the Cobra Administrator to let them know you are running the update so they are aware of it. And finally, always backup your project before applying this kind of change.