none
Custom Field not working RRS feed

  • Question

  • Hi,

    I've made a project level custom field in PWA that takes "Total Slack" (in-built duration field) and based on a switch formula gives out 3 values viz. High, Medium, Low The field formula is as mentioned below:

    Switch([Total Slack] <= -7, "High", [Total Slack] >= 0, "Low", [Total Slack] > -7 And [Total Slack] <= -1, "Medium")

    The formula works very well and displays value as High for projects with Total slack less than -7. However, the formula is not working for Total Slack between -7 and -1 i.e. greater than - 7 and less than -1. The formula returns the value as "High" only.

    I've tried editing and publishing the project both from PWA as well as Proj Professional after updating task duration to calculate Total Slack.

    This field is critical and used in many views. I am looking for a solution to resolve this issue. Any help in this regard would be appreciated.

    Friday, December 5, 2014 2:46 PM

All replies

  • Hi,

    The formula is Switch( expr-1value-1[, expr-2value-2, ... [, expr-n,value-n]] ).

    Try the following adding the [ symbols :

    Switch([Total Slack] <= -7, "High"[, [Total Slack] >= 0, "Low"[, [Total Slack] > -7 And [Total Slack] <= -1, "Medium"]])


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |


    Friday, December 5, 2014 3:03 PM
    Moderator
  • I am unable to save the formula shared. Ms Project Server 2010 does displays error when saving the formula. Is "[", the correct symbol?? Please confirm.
    Friday, December 5, 2014 3:22 PM
  • First I'd like to understand a bit more. The total slack is always positive thus why having -7. Then all duration are calculated in minutes, thus 7 days means 7*480=3360 minutes.

    What are your conditions:

    If total slack is hight or equal to 7 days, then return high,

    ...?


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Friday, December 5, 2014 4:16 PM
    Moderator
  • The condition is as follows:

    a. if Total slack is less than -7 days, it is High

    b. if total slack is greater than - 7 days and less than 0 days, it is medium

    c. If total slack is greater than 0 days, it is low

    Friday, December 5, 2014 4:41 PM
  • Anyway, I'd advice to use an IIF statement instead of a SWITCH.

    Here is an example for the following conditions:

    • If total slack is more than 7d, then return HIGH,
    • Then If total slack is less than 1d, return LOW,
    • Otherwise return MEDIUM.

    The formula will be (based on 480 minutes per working day):

    IIf([Total Slack]>=3360;"High";IIf([Total Slack]<=480;"Low";"Medium"))


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Friday, December 5, 2014 4:42 PM
    Moderator
  • As I said below, the total slack is always positive since it is a duration. Thus you have to review your conditions.

    Please review the conditions I suggested below with the corresponding formula and tell us if it works for you.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Friday, December 5, 2014 5:47 PM
    Moderator
  • Hi,

    Sorry to butt in, but total slack can be negative when a constraint is applied on a project which then applies a scheduling constraint in a plan (typically this is one of the occasions when the planning wizard pops up), or when the task finish date extends beyond a deadline. 

    Also, instead of using 480 minutes (assuming an 8 hour per day), you can use the field [minutes per day] which works this value out for you.


    Ben Howard [MVP] | web | blog | book | P2O

    Friday, December 5, 2014 8:38 PM
    Moderator
  • this is how I would write the formula.  Note that SWITCH statements are calculated left to right, so this works...

    Switch([Total Slack]/[Minutes Per Day]<=-7,"High",[Total Slack]/[Minutes Per Day]<=-1,"Medium",True,"Low")


    Ben Howard [MVP] | web | blog | book | P2O



    Friday, December 5, 2014 8:52 PM
    Moderator
  • Thanks for correcting me Ben! I didn't think about constraints! Your experience is always valuable!

    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Friday, December 5, 2014 9:25 PM
    Moderator
  • I tried both the formula. However, the correct value is not displayed based on Total Slack value of a Project. The field formula is never refreshed till the Project is checked out and Published.

    I further observed a strange behavior. On checking out the Project, the forumla of the field is not getting refreshed at all.

    Saturday, December 6, 2014 7:21 PM
  • Hi Abhijit,

    There are some differences between formulas in MS Project and Project Web App; I will always create formulas in MS Project to test them and then ensure that they work in Project Web App - this formula was created in MS Project and it works fine, I have not tested it in Project Web App.  Formulas are not evaluated unless the project is edited, therefore, the value will only be updated when the project is modified, and not necessarily on check-out.  Pressing F9 in MS Project will force a recalculation if this is necessary. 

    From what you have written above, Project is behaving as designed (other than your issue with the correct value in the formula, which could be my mistake or yours).


    Ben Howard [MVP] | web | blog | book | P2O

    Sunday, December 7, 2014 7:36 PM
    Moderator
  • The custom field till behaved strangely. Risk level is calculted as medium even though Total Slack is -199. the field reflects correct value in Ms Project but not in Ms Project server.

    Wednesday, December 10, 2014 1:59 PM
  • Once you checked the value in MS Project, did you publish the project so that the changes are reflected in the published database when you view the information in PWA?

    Ben Howard [MVP] | web | blog | book | P2O

    Wednesday, December 10, 2014 2:29 PM
    Moderator
  • Yes, the project has been published.
    Wednesday, December 10, 2014 3:38 PM
  • The next step then would be to move away from the SWITCH statement and use a nested if statement, along the lines of....

    iif ([Total Slack]/[Minutes Per Day]<=-7,"High",iif([Total Slack]/[Minutes Per Day]<=-1,"Medium","Low"))

    Please note that I haven't validated this formula as I don't have MSProject on this PC, but it should be good enough to take you forward.


    Ben Howard [MVP] | web | blog | book | P2O

    Wednesday, December 10, 2014 3:44 PM
    Moderator