Skip to main content
Planview Customer Success Center

How to set the value of a configurable field based on the value of another configurable field?

For example, you have a request configurable field called “Vendor” that is based on the Customer entity, and a configurable text field called “Vendor address”. When a customer is selected in the “Vendor” field, you want the customer’s address to automatically be displayed in the “Vendor address” field.

You can do this by creating a calculated field that includes a stored procedure which, based on the customer that is selected in the "Vendor" field, determines the address and then inserts the address into the "Vendor address" field. 

  1. Create a calculated field, which is a configurable text field for which you change the field type to numeric.
  2. Set the calculated field as hidden so that it is not displayed in the user interface.
  3. Add a custom stored procedure to the Formula field of the calculated field. 

For more information about creating calculated fields, and using custom stored procedures in a calculated field formula, see the Changepoint Administration Guide or online help.

The documentation includes the following sample stored procedure, which calculates the number of requests associated with a project: 

CREATE PROCEDURE [dbo].[CPCALC_project1]
@PROJECTID UNIQUEIDENTIFIER,
@PARA_VAR INTEGER output
AS
select @PARA_VAR =count(1) FROM RequestEntity Where EntityId=@PROJECTID
GO
GRANT EXECUTE ON [CPCALC_project1] TO [CPACCESS]
GO

Tips for creating the stored procedure for this scenario:

  • The output is not relevant, so you can set @PARA_VAR to 0 (or any value). 
  • RequestId is the ID of the request that is being saved. Use RequestId to find the customer that is stored for the request in the UDFCode table.
  • Find the address of the customer in the Customer table.
  • Insert or update the customer address field in the UDFtext table for the same request.
  • Insert or update the “Vendor address” field in the UDFtext table for the same request.

NOTE: SQL statements in formulas for calculated fields are not supported within the terms of your Changepoint support agreement. If you require assistance, contact your customer success manager to arrange for chargeable consulting services