Formula Example

[JavaJoe]

I want Strategic Alignment to be a calculated field based on the following...


IF (
(Portfolio Approval = "Approved for 06") OR
(Portfolio Approval = "Approved for 07") OR
(Portfolio Approval = "Approved for 08") OR
(Portfolio Approval = "Approved for 09") OR
(Portfolio Approval = "Approved for 10")
)
AND
(
(Project Category = "Required Maintenance") OR
(Project Category = "Continuous Improvement") OR
(Project Category = "Key Initiative")
)
AND
(
(Project Status = "Active") OR
(Project Status = "Backlog") OR
(Project Status = "Onhold")
)
THEN
CASE Portfolio Build
WHEN "2008"
THEN Strategic Alignment = "fy09.01"
WHEN "2009"
THEN
CASE Category
WHEN "Key Initiative"
THEN
CASE Initiative Name
WHEN "LSS GIS Combination"
THEN Strategic Alignment = "fy09.04"
WHEN "Product Line Expansion"
THEN Strategic Alignment = "fy09.05"
WHEN "Network Design"
THEN Strategic Alignment = "fy09.06"
WHEN "eCommerce Expansion"
THEN Strategic Alignment = "fy09.07"
WHEN "Small Business Expansion"
THEN Strategic Alignment = "fy09.08"
WHEN "North America and International Acquisitions"
THEN Strategic Alignment = "fy09.09"
WHEN "Operating Margin Expansion"
THEN Strategic Alignment = "fy09.11"
ELSE
THEN Strategic Alignment = "fy09.99"
END
WHEN "Required Maintenance"
THEN Strategic Alignment = "fy09.02"
WHEN "Continuous Improvement"
THEN Strategic Alignment = "fy09.10"
ELSE
THEN Strategic Alignment = NULL
END
ELSE
THEN Strategic Alignment = NULL
END
ELSE
THEN Strategic Alignment = NULL
ENDIF

 

[Jack Dahlgren]

Sorry, that can not fit in a calculated field. It has too many characters,
but you could write a macro to do thi (in fact you can probably reuse almost
everything you have written. Conceivably you could use several fields to
spread the formula out - each one evaluating part of the field and then one
more to assign the value, but custom fields that depend on the result of
other calculated fields have been a bit unstable in the past.

 

[JavaJoe]

Thanks Jack.. Do you know the number or is there a posted limit to the
number of characters in a calculated field. Could you point me to the
documentation for that statement.

Did I mention these are Enterprise Custom fields?