Syncing a Field between two Deltek Vision Info Centers
I did a session at Insight 2014 where attendees voted on what tool would be most helpful for the Deltek Vision community, and the result was a tool to synchronize a field between two Deltek Vision Info Centers.
You can view the source code here:
or download the application from here:
In this example I’m starting with the Client/Company Info Center, where I’ve added a custom Market filed at the bottom and I’ll be syncing it to a custom Market field in the Project info center. There’s a few reasons to do something like this, first it makes market searchable for your projects, and more importantly now you have a market field you can use in Project Reporting to get things like Project Revenue by market.
To create the market field in Client/Companies I just added the custom field as a dropdown as seen below.
Now for the project there’s a twist, in order to get this to work I need a clientid in the project custom table so we can relate the table back to the client custom table (otherwise this tool would be very complex, if not impossible). So for all use cases think about how you’re going to relate the two tables (custom or regular), it could be by EmployeeID, Project WBS, Clientid etc, but that ID needs to be stored in both tables so they can be related/connected. So in the example below you can see I’ve added the clientid and market fields to the projects info center.
Now those fields are visible in the project info center. One thing to consider after you’ve completed testing it to make this fields hidden and/or read only, and they’ll still be available for reporting and searching.
Now that I’ve got the Clientid in the project info center, I’ve got to get it filled in with the clientid, the easiest way to do this would be with sql which would be something like this:
set clientid = pr.clientid
where ProjectCustomTabFields.wbs1 = pr.wbs1
However if you don’t know sql you can use a workflow to get it completed, and you’re going to want to use a workflow to maintain the value for new or changed records. So in this example I created two workflows, one for changes to projects and one for new projects.
Then I chose Column Field Change, and choose the new custom ClientID.
And for the Expression I chose PR.ClientID, so that now anytime a project is changed or added the custom clientid is completed. Note, this will take some time fill in, either you’ll need to make some small change to your projects or just use this on a go forward basis. One other note, after you’re satisfied with the projects being updated you should add a condition on the change workflow to only update if the client field changed.
OK, now I can use the Sync Vision Fields tool to create a stored procedure to actually sync between the info centers. I just select the ClientCustomTabFields as the source table and ProjectCustomTabFields as the destination table. The Field that will be updated is the Market, so I chose those two columns in the Update Field, and lastly I chose the ClientID colums as the Relationship. Also don’t forget to give this a Title (NOTE do not use spaces, I use an _ instead). You’ll need to use this title to in the last workflow.
Now you can click Preview, to see what will be created. This is also a great way to learn about Stored Procedures. If it looks ok just click the Create Procedure to finish.
Now go back to the workflows and choose clients/companies and again I create a change workflow to trigger the market update to projects, this time though I select a Stored Procedure workflow.
I just enter the Title I gave the stored procedure.
And for the expression I choose ClientID.
Now as long as the project has been updated, so it has the ClientID field completed, the Market will be updated after a change to the Client/Company.
Now you can see the Market has been updated (which means I updated the Red Sox company record).
The entire source code is available here: