Asked by:
Custom Field not working
Question

Hi,
I've made a project level custom field in PWA that takes "Total Slack" (inbuilt 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.
All replies

Hi,
The formula is Switch( expr1, value1[, expr2, value2, ... [, exprn,valuen]] ).
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,
 Edited by Guillaume Rouyre [MBA, MVP, MCC]MVP, Moderator Friday, December 5, 2014 3:03 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,


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,


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.

 Edited by Ben Howard [MVP]MVP, Moderator Friday, December 5, 2014 8:58 PM corrected formula to include [minutes per day]


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.

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 checkout. 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).



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.