none
Task Level Custom Field with Formula Issue RRS feed

  • Question

  • Hi,

    I have created a Task level Custom Field "Cost Indicator" with Text as Type.

    I want to show CPI and SPI indicators in Project Schedule views with the values given below:

    IIf([TaskCPI]>=0.8, "Green",
    IIf([TaskCPI]<0.8, "Red",
    "Yellow"))
    IIf([TaskSPI]>=0.8, "Green",
    IIf([TaskSPI]<0.8, "Red",
    "Yellow"))

    Also set Graphical indicators accordingly.

    When I save Custom field, it shows error:

    "This formula contains references to one or more fields that do not exist in the system. Correct the formula and try again."

    Am I missing something in above formulas OR [TaskCPI] & [TaskSPI] can't be used in custom field formula?

    Thanks,


    Noman Sohail

    Tuesday, January 3, 2017 11:51 AM

Answers

  • I do not believe formula Task fields are supported in Resource Assignment Views.

    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    • Marked as answer by NomanSohail Tuesday, January 10, 2017 9:56 AM
    Friday, January 6, 2017 8:45 PM
    Moderator
  • Hello,

    Unfortunately, if you can't find the field to add to the view in the Manage Views page it will not be possible.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    • Marked as answer by NomanSohail Tuesday, January 10, 2017 9:55 AM
    Saturday, January 7, 2017 10:06 AM
    Moderator
  • Try this:

    IIf([CPI] < 0.8, "Red", IIf([CPI] < 1, "Yellow", IIf([CPI] >= 1, "Green", "No Value")))

    You don't need the else as no value, you could take out the last if and just do:

    IIf([CPI] < 0.8, "Red", IIf([CPI] < 1, "Yellow", "Green"))

    The Project custom field should just use [CPI] too but will be a project level field instead of a task level.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    • Marked as answer by NomanSohail Friday, January 13, 2017 11:12 AM
    Tuesday, January 10, 2017 10:39 AM
    Moderator

All replies

  • Hello, The fields are CPI and SPI:

    IIf([CPI] >= 0.8, "Green", IIf([CPI] < 0.8, "Red", "Yellow"))


    IIf([SPI] >= 0.8, "Green", IIf([SPI] < 0.8, "Red", "Yellow"))

    Do they work?

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Tuesday, January 3, 2017 2:53 PM
    Moderator
  • Thanks Paul, it worked.

    I am facing one more issue:

    I have created Task level ECF with formula and graphical indicator, also selected "Roll down, unless manually specified", and want to show in Resource Assignments view, but I couldn't find that ECF in Resource Assignments view.

    How can I add this new ECF in Resource Assignments views?

    Thanks,


    Noman Sohail


    • Edited by NomanSohail Wednesday, January 4, 2017 6:40 AM Update
    Wednesday, January 4, 2017 6:39 AM
  • I do not believe formula Task fields are supported in Resource Assignment Views.

    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    • Marked as answer by NomanSohail Tuesday, January 10, 2017 9:56 AM
    Friday, January 6, 2017 8:45 PM
    Moderator
  • Hello,

    Unfortunately, if you can't find the field to add to the view in the Manage Views page it will not be possible.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    • Marked as answer by NomanSohail Tuesday, January 10, 2017 9:55 AM
    Saturday, January 7, 2017 10:06 AM
    Moderator
  • Thanks,

    Can you help me in writing Task level correct formula?

    • [CPI] >= 0.8 and < 1, "Yellow"
    • [CPI] < 0.8, "Red"
    • [CPI] >= 1, "Green"
    • Else "No value"

    And for Project Level ECF, formula would contains the same [CPI] & [SPI]? OR [ProjectCPI] & [ProjectSPI]

    Thanks,


    Noman Sohail

    Tuesday, January 10, 2017 9:55 AM
  • Try this:

    IIf([CPI] < 0.8, "Red", IIf([CPI] < 1, "Yellow", IIf([CPI] >= 1, "Green", "No Value")))

    You don't need the else as no value, you could take out the last if and just do:

    IIf([CPI] < 0.8, "Red", IIf([CPI] < 1, "Yellow", "Green"))

    The Project custom field should just use [CPI] too but will be a project level field instead of a task level.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    • Marked as answer by NomanSohail Friday, January 13, 2017 11:12 AM
    Tuesday, January 10, 2017 10:39 AM
    Moderator