none
Project Server 2010: Custom Field formula expression will not yield true when it should and returns #error RRS feed

  • Question

  • Thank you in advance for those willing to help:

    I have a switch statement, where the purposes is to return a simple value of 0 when a particular cost field = 0. 

    [STATUS] =

    Switch(

    [Budget] = 0, 0,

    expr2, value2

    )

    The custom field [STATUS] is a text type field.  the [Budget] field is a cost field.

    When [Budget] = 0 this [STATUS]will not recognize it and returns "#error" everytime.  When [Budget] is great than 0 it always meets one of the expression criteria and works great. 

    I have tried IFF statements and it won't work.

    I have tried "[Budget]<.01" and it won't work.

    I have tried leaving [Budget]=0,0 as the only expression in the switch statement it will not work.

    I have tried making my formula field a cost field to match the [Budget] field.

    I am testing in MS Professional, publish and saving, existing projects new projects...nothing works!

    Friday, March 15, 2013 9:13 PM

All replies

  • Garett, I tried your formula and couldn't replicate the problem. Apart from the fact that [Status] is a predefined field name (so I used [St] as a local text field) everything worked well with the formula:

    Switch([Cost1]=0,0,[Cost1]>1,17,[Cost1]<-1,30) - and a variation:

    Switch([Cost1]>0,17,[Cost1]=0,0,[Cost1]<0,30)

    Just for completeness I tried it with Enterprise fields and it worked fine again.

    Do your other expressions in the Switch statement involve [Budget] as well? I would be inclined to experiment by setting up a new pair of fields and build up the formula slowly - an expression at a time.

    Graham

    Saturday, March 16, 2013 2:20 PM
  • Graham - your time is much appreciated.

    I was able to make this work through two steps:

    1) I created a new custom project cost field called [B5].  A dummy field that would never be touched or exposed to the users (we do not use professional to connect to server either).

    2)  I then used an IIF statement to see if  [Budget] = [B5] to get my results.  Using a switch statement still returned #error, the IIF was the only way it would work.

    The whole point was that if [Budget] was left untouched, then I wanted to return a specific value that runs an indicator.  If budget was not 0, then I ran a switch statement to figure out budget variance and ultimately run an indicator.  I suppose that project cost fields default to $0, as the ISNULL() function was useless against the cost field as well.

    What a mess !

    Final Formula:

     

    iif(
    [IM CAPEX BUDGET CURRENT FY] = [B5],0,
    Switch(
    [Funding Source] = "None", 0,
    [Funding Source] = "Internal", 1,
    (([IM CAPEX Forecast Final Current FY] - [IM CAPEX Budget Current FY]) / ([IM CAPEX Budget Current FY])) <= -0.101, 2,
    (([IM CAPEX Forecast Final Current FY] - [IM CAPEX Budget Current FY]) / ([IM CAPEX Budget Current FY])) <= 0.05, 1,
    (([IM CAPEX Forecast Final Current FY] - [IM CAPEX Budget Current FY]) / ([IM CAPEX Budget Current FY])) <= 0.1, 2,
    (([IM CAPEX Forecast Final Current FY] - [IM CAPEX Budget Current FY]) / ([IM CAPEX Budget Current FY])) > 0.1, 3
    )
    )

    Saturday, March 16, 2013 4:29 PM
  • Garett, Glad you got an answer. Congratulations on your tenacity.         Graham

    Sunday, March 17, 2013 3:32 AM