If you read my blog about Cascading Attributes and found that useful, here’s another way to do something similar, but a bit more complicated.
Recently I was asked, “How do I create a SELECT statement to do this?”
I have two Project Properties: @"PROJECT_Project_Number" and @"PROJECT_Billing_Type"
Here is the logic I want to use: If @"PROJECT_Project_Number" is a NULL then @"PROJECT_Billing_Type" = Non-billable (pre-contract) Else If the first character of @"PROJECT_Project_Number" is a 'P' then @"PROJECT_Billing_Type" = Non-billable (pre-W) Else If the first character of @"PROJECT_Project_Number" is a 'W' then @"PROJECT_Billing_Type" = Non-billable (pre-contract) Else @"PROJECT_Billing_Type" = Billable (Contracted) End If
Well, I’m no SQL guru, but I do know that it would be tough to do this with just plain SQL, but pretty easy if you create and use a stored SQL Server procedure instead! (And of course, you can do something similar with Oracle.)
First setup the Billing_Type Project PropertyUsing the ProjectWise Administrator Client, create the necessary Project Type and add your Project Properties as usual, but you will need to provide a default value for Billing_Type as well as adding a “select” statement that actually calls a stored procedure. Here’s an example Project Type (Billing Example) with some Project Properties.
Take a good look at “Project_Number” and “Billing_Type”.
Now create the stored procedure in SQL ServerI used the tools inside of Microsoft SQL Server Management Studio, but no matter what tool you use, you want a stored procedure that looks like this:
Now test the stored procedureCreate a new Project in ProjectWise Explore and then go to the Project Properties page. Your “Billing_Type” should update based on what you enter for the “Project_Number”. Here’s how it should look like with some sample data.