One of the best features of Deltek Vision is the ability to update other fields, usually User Defined Fields (UDF’s) after it’s been saved or through a workflow. In this article you’ll see how to calculate a value based on the other fields in an Opportunity and display it a UDF. For this example we’ll use a Risk Calculator that was described in the Opportunity Risk Calculator. If you’re not familiar with SQL, the intro to SQL with Vision is, well… required reading. So start here, it’s still a work in progress.

The components of Stored procedure are first a name and to create it, for example Create PROCEDURE
Set_GoNoGo_Risk
, so you’ve just got a CREATE Procedure and it’and name(Set_GoNoGo_Risk), note there are no spaces allowed unless you use []… it’s best just to use no spaces though. While we’re on the topic, later when you need to update it later you’ll use Alter NAME… ie Alter PROCEDURE
Set_GoNoGo_Risk
, followed by the rest… read on. Next is the what information you need to send(pass) to the procedure to do it’s thing, it could an project wbs1, client id, or nothing at all. To declare this value you use the @ symbol followed by a name and then a data type, the type of data it is could be any of the following or more: string(varchar(how long/?500)); int(integer); money (basic decimal). For this example let’s use an opportunityid, which is an alphanumeric value no longer than 32 characters, @OpportunityID
varchar(32).
That gives us the following:

 

Create PROCEDURE
Set_GoNoGo_Risk

    @OpportunityID
varchar(32)

 

Only two things left to get work, we need to add an AS and a Begin. Why?, that’s just how it works. So now we have:

Create PROCEDURE
Set_GoNoGo_Risk

    @OpportunityID
varchar(32)

AS

BEGIN

 

Two quick points, if you have more than one value to pass in, for example a clientid, you would just add it on like this, note the coma:

Create PROCEDURE
Set_GoNoGo_Risk

    @OpportunityID
varchar(32),

    @ClientID
varchar(32)

 

Or if there’s no information to pass then it’s just:

Create PROCEDURE
Set_GoNoGo_Risk

AS

BEGIN

 

From this point on it’s just sql, using that value/variable you’re passing along.

The first thing to do in this example is declare a variable to hold our risk score, we’re calling custRiskAssessmentScore, again just use the @symbol for a variable name, except since we’re doing sql we need to declare the variable, so use declare and again followed by the datatype:

Declare
@custRiskAssessmentScore
int

We use Set to give the variable a value, 0 in this case, so that if not of the values add up we get at least 0.

 

set
@custRiskAssessmentScore
= 0

 

Now we’ll look at totaling 3 of the fields from our risk screen.

 

In these first two examples a case statement is being used to evaluated whether the UDF is checked. In the first case it’s looking to see if the AIA contract checkbox udf was checked, so this: “case
when
custOurStandardcontractorAIAstylecontract
=
‘Y’
then
@custRiskAssessmentScore
+ 1″ reads as when the UDF is checked (= Y), then add 1 to our RiskAssessmentScore variable. If not “else @custRiskAssessmentScore
+ 12″ then add 12 to the variable. The final part is say where to get the UDF value/status from, so “From

dbo.OpportunityCustomTabFields where opportunityid = @OpportunityID”
reads as, From the customOpportunityTabFields get the row or rows(there’s only one row for UDFs, unless it’s a grid) where the opportunityid = the opportunity variable we passed in.

 

–custOurStandardcontractorAIAstylecontract

select

@custRiskAssessmentScore
=
case

when
custOurStandardcontractorAIAstylecontract
=
‘Y’
then


@custRiskAssessmentScore
+ 1

else

@custRiskAssessmentScore
+ 12

end

From
dbo.OpportunityCustomTabFields

where
opportunityid
=
@OpportunityID

 

You can use same basic idea to add values based on a Pay When Paid checkbox, except this time more is added to the variable if the UDF is checked rather than unchecked.

 

–custAreweasubconsultantPWP

select

@custRiskAssessmentScore
=
case

when
custAreweasubconsultantPWP
=
‘Y’
then


@custRiskAssessmentScore
+ 12

else

@custRiskAssessmentScore
+ 1

end

From
dbo.OpportunityCustomTabFields

where
opportunityid
=
@OpportunityID

 

 

In the final example the same basic idea exists of using the case statements, but this time there’s multiple cases because the UDF is a dropdown. It’s the same process though, case(if) the UDF’s value is equal to ‘0%’ then add 12 to the risk variable, and so on.

 

–custProjectContingency

select

@custRiskAssessmentScore
=
case

when
custProjectContingency
=
‘0%’
then


@custRiskAssessmentScore
+ 12

when
custProjectContingency
=
‘.5% to 3%’
then

@custRiskAssessmentScore
+ 10

when
custProjectContingency
=
‘3.1% to 7.5%’
then

@custRiskAssessmentScore
+ 7

when
custProjectContingency
=
‘7.6% to 9.9%’
then

@custRiskAssessmentScore
+ 3

when
custProjectContingency
=
‘Greater than 10%’
then

@custRiskAssessmentScore
+ 1

else

@custRiskAssessmentScore
+ 12

end

From
dbo.OpportunityCustomTabFields

where
opportunityid
=
@OpportunityID

 

The last thing to demonstrate is updating UDF fields in this case the RiskLevel field (shown below) to display the risk level that’s being calculated.

 


To do this a risklevel variable of type string is declared, then the case statements are used to determine where the Risk Level falls. So this “case
when
@custRiskAssessmentScore
< 35 then”
reads when the risk variable is less than 35 then return ‘Low’, followed then by “when
@custRiskAssessmentScore
>=35 and
@custRiskAssessmentScore
< 50 then
which reads the score is greater than or equal to 35 and less than 50 then return “Guarded”, and so on. Notice that the risklevel variable is set before all of the case statements and it’s simply being set by what the case statements return. This is different than in the prior examples where the RiskScore variable was being updated inside the case statements.

 

 

declare
@risklevel
varchar(50)

 

select

@risklevel
=
case

when
@custRiskAssessmentScore
< 35 then


‘LOW’

when
@custRiskAssessmentScore
>=35 and
@custRiskAssessmentScore
< 50 then

‘GUARDED’

when
@custRiskAssessmentScore
>=50 and
@custRiskAssessmentScore
< 75 then

‘ELEVATED’

when
@custRiskAssessmentScore
>=75 and
@custRiskAssessmentScore
< 110 then

‘HIGH’

when
@custRiskAssessmentScore
>110 then

‘SEVERE’

else

‘SEVERE’

end

From
dbo.OpportunityCustomTabFields

where
opportunityid
=
@OpportunityID

 

–score

update
OpportunityCustomTabFields

set
custRiskLevel
=
@risklevel

where
opportunityid
=
@OpportunityID

 

The last piece is to get Vision to call this stored procedure when an opportunity is saved and thereby update the risk score and level fields. To do that go to Configuration > Workflow > User Intiated Workfllow and choose Opportunities from the Application drop down. Either add a new Change Workflow to the top section first, or choose and existing change workflow and click Insert in the Actions and choose “Stored Procedure”. Enter the name of your stored procedure in the top blank. After moment the Arguments with get filed in with the parameters you declared. Notice at the bottom you can set “Reload Info Center Record” to have your infocenter record reloaded after this stored procedure, in this example it means the Risk Level will be updated and displayed afterwards. If it’s not checked then it won’t be refreshed until someone reopens the opportunity. Below that is the Invoke after all changes are saved, this should also be checked, if it’s not then when the procedure is running it might find that values you checked haven’t been updated so the score won’t be correct.

The last piece is to send the Vision value to your stored procedure to run, so click on the Value field to open the Expression Builder. In this case you’re just trying to pass the opportunity into the procedure so it know what record to update, so simply choose Opportunity.OpporutnityID.

 

That’s it, hopefully you have some good ideas on how to create and use stored procedures in vision to update fields and perhaps calculate values.

 

 

Tagged on:     

4 thoughts on “A Calculated Stored Procedure Workflow in Deltek Vision

  • May 5, 2016 at 12:29 pm
    Permalink

    Is it possible to run the stored procedure before, as I want to validate the fields after they have been updated. My stored procedure is comparing the dates when equal it will update a field. That field will be validated to enable fields to be updated.

    Reply
    • May 6, 2016 at 2:12 pm
      Permalink

      There’s a checkbox in the Stored Procedure Configuration at the bottom, it’s “Invoke after all changes are saved”, this effectively allows you to run the validation before they’re saved.

      Reply
      • May 19, 2016 at 8:21 am
        Permalink

        Just to make my point clearer – I have a stored procedure that compares the dates from the workflow to current date. Changes on the workflow can only be made when the dates are equal to the current date otherwise changes should not be allowed. I want to invoke the stored procedure to compare the dates before changes are saved, when the dates are equal save the changes otherwise give an error message. The situation I have now is that the changes are saved and the stored procedure is invoked last and I can only have the comparison done later which can results in changes being allowed when not supposed to.

        Reply
        • May 26, 2016 at 8:03 pm
          Permalink

          You could do a validation error first on a user initiated workflow. I think there might be a way to validate a save in a stored procedure and return a -1, or 0 or something to prevent an update, but I’m not finding it anywhere in documentation.

          Reply

Leave a Reply

Your email address will not be published. Required fields are marked *