none
Custom Text Field used in IIF Formula not working

    Question

  • I feel I ought to know how to do this; but it's not working and my mind elsewhere than debugging Project ....

    I have a Custom Text [Text21] field renamed "Milestone Level" with three lookup values: "MS Level 1", "MS Level 2", and "MS Level 3".  I have a Custom Flag called "Is Milestone Level 1" which is set to be a formula

    IIf([Text21]="MS Level 1","Yes","No")

    All records in the plan compute Is Milestone Level 1 field as "No" even if the text in [Text21]="MS Level 1".  

    I'm stumped. What could be wrong?


    --rms www.rmschneider.com

    Thursday, November 14, 2013 3:59 PM

All replies

  • Rob --

    I just tested this in Microsoft Project 2013 and it works perfectly.  I used the Text1 field and the Flag1 field, BTW.  What happens if you press the F9 function key to recalculate the project?  Does it work after doing that?  Let us know and we will try to help.


    Dale A. Howard [MVP]

    Thursday, November 14, 2013 4:05 PM
    Moderator
  • Dale,

    Thanks... i should have said using Project 2010 SP 2.

    Pressing F9 to recalculate recalculates the project, but the flag field does not budge into "Yes" where it should.


    --rms www.rmschneider.com

    Thursday, November 14, 2013 4:09 PM
  • When I create a new empty project, and a make a new text field and then do the flag field against that text field, it works just fine.  (Sigh).

    When I make a lookup list to that text field, the flag field recomputes just fine. (Double Sigh).

    Must be file corruption or something.  Probably self inflicted. (Triple Sigh).


    --rms www.rmschneider.com

    Thursday, November 14, 2013 4:14 PM
  • Hi Rob,

    I'm sure you have already checked this out, but make sure not to have double space characters between "MS" and "level" for instance. Your formula will not recognize the chain. 

    If the file appears to be corrupted, you could try to save it as a XML file, then save it again as a mpp.

    Hope this helps.

    Thursday, November 14, 2013 4:18 PM
  • I *think* I found the problem. It is is not, i think, file corruption.  The formula for the Flag field was incomplete:

    The formula defined in a macro:

    CustomFieldRename FieldID:=pjCustomTaskFlag8, NewName:="Is Milestone Level 2"

    But I was missing a CustomFieldPropertiesEx statement with Attribute:=pjFieldAttribureFormula, e.g.:

    CustomFieldPropertiesEx FieldID:=pjCustomTaskFlag8, Attribute:=pjFieldAttributeFormula, SummaryCalc:=pjCalcNone, GraphicalIndicators:=False

    Without the above, Project put the formula into postion, but the button to say it was a formula or not was set to "none" and did not turn on the formula.  

    Sorted, I think.


    --rms www.rmschneider.com

    Thursday, November 14, 2013 5:03 PM