Formula Driven Field - Retain a Set Value RRS feed

  • Question

  • Hi,

    In a PWA Formula Driven Field, if a formula sets a value the first time it is run, how do you retain that value, if that formula runs again and all of the tests are false?

    You can't set the field name as the default value (e.g. the final "else" attribute of an IIF statement), because you will get a circular reference error.

    I want to define that if nothing's changed, then leave the same value in the field, but can't seem to work out how to do so.

    Any suggestions appreciated.



    Friday, March 29, 2013 8:19 PM

All replies

  • Hey Wayne:

    You can't make a field reference itself, but you can use a pair of fields, and use one to evaluate if the other one is correct.  What specifically are you trying to track in terms of changes?

    Andrew Lavinsky [MVP] Blog: Twitter: @alavinsky

    Saturday, March 30, 2013 2:25 AM
  • Hi Andrew,

    Thanks for taking the time to respond to my post.

    What I am trying to accomplish is to capture the [Start] and [Finish] dates of a Task as it is created, to an Enterprise Custom Field ([Planned Start] and [Planned Finish]).

    But, when the formula runs again, the next time the Project is published, my formula logic (an IIf statement), includes two tests that evaluate to false, so that the final "else" condition of the IIf statement comes into play.

    I basically need the value in the field not to be touched, if the Iff statement values equate to false, but can't seem to identify a way of doing that. As you say, a field can't reference itself.

    If I use a separate field, I still get a circular reference error when attempting to leave the value alone (referencing the target field).

    I have worked out a way so that the dates that are capture are only valid the first time a Project is published, after the Tasks are created. So if I could capture those that one time in a sparate field, that would be great. Then I can just reference that field from my [Planned Start] and [Planned Finish] fields.

    But the IIf statement needs a final "else" condition and when it runs again, it clears out that separate field. If there another separate function that will just capture a value the single time, based on some logic? I tried the Switch sataement, but that equates to Null, if the tests are false.

    Maybe I've been messing with this too long and am blinded to something obvious. But I'm stuck on this one. And now this explanation probably makes it as clear as mud.

    But, any other suggestions most welcomed!



    Saturday, March 30, 2013 5:07 AM
  • ...and you don't want to use a Interim Plan and want to do this in PWA (vs. MS Project)?

    There may be some roundabout ways to do this:

    1) Reference the (Task) Created date...not sure how, but if Created <> Today(), then don't change the field.

    2) Reference the Unique ID, i.e. capture the Unique ID when the task was created, and if it isn't the same as the maximum UID for all of the tasks in the project, then don't change the field.

    The tricky part there is defining "when" you want to take a snapshot of the task. It sounds like on initial publish, i.e. not really when it was created.

    VBA would be easiest, but that won't run in PWA.

    Andrew Lavinsky [MVP] Blog: Twitter: @alavinsky

    Sunday, March 31, 2013 3:26 AM
  • Hi Andrew,

    Yes, the whole purpose of my effort is to work solely within PWA. Project Pro/VBA is not an option.

    Both your options would probably work in terms of capturing the target date when the Task is created.

    My logic and syntax for defining when I want to capture the date works perfectly, so I have that tricky piece working. I have the dates being captured correctly when the Task is created.

    But, my major issue is when the formula runs again, even though I don't want the correctly captured value to change, it does. An that's because I can't set the final 'else' value of my IIf statement to remain the same (the formula driven field can't reference itself).

    Is there some way to have a test in a formula that is just run once (e.g. IIF([Created] = Now(), [Start], ""), then never evaluated/run again?

    That would do the trick for me.



    Monday, April 1, 2013 4:52 AM