Can someone help me with a calculated field? I'm trying to add a field that tells me when a task or issue is added to an iteration. I can't seem to get the expression correct. | Community
Skip to main content
MelissaDa5
MelissaDa5
Level 3
April 24, 2020
Solved

Can someone help me with a calculated field? I'm trying to add a field that tells me when a task or issue is added to an iteration. I can't seem to get the expression correct.

  • April 24, 2020
  • 1 reply
  • 1046 views

My plan is to add a calculated field that notes when the iteration ID has a value but I can't determine how to do this so that it adds a timestamp of the first time ANY value is present in that field. Here's where I am and this doesn't work:

IF(NOTBLANK{Iteration ID}),IF(ISBLANK({DE:Iteration Assigned Date}),$$NOW,{DE:Iteration Assigned Date}),{DE:Iteration Assigned Date})

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Doug_Den_Hoed__AtAppStore

    Hi Melissa,

    From this handy Calculated Data Expressions article, I believe the term "ISBLANK" (vs NOTBLANK) is what you're after, and suggest you try this formula instead:

    IF(ISBLANK({Iteration ID})

    ,{DE:Iteration Assigned Date}

    ,IF(ISBLANK({DE:Iteration Assigned Date})

    ,$$NOW

    ,{DE:Iteration Assigned Date}

    )

    )

    Regards,

    Doug

    1 reply

    Doug_Den_Hoed__AtAppStore
    Community Advisor
    Doug_Den_Hoed__AtAppStoreCommunity AdvisorAccepted solution
    Community Advisor
    April 24, 2020

    Hi Melissa,

    From this handy Calculated Data Expressions article, I believe the term "ISBLANK" (vs NOTBLANK) is what you're after, and suggest you try this formula instead:

    IF(ISBLANK({Iteration ID})

    ,{DE:Iteration Assigned Date}

    ,IF(ISBLANK({DE:Iteration Assigned Date})

    ,$$NOW

    ,{DE:Iteration Assigned Date}

    )

    )

    Regards,

    Doug

      MelissaDa5
      MelissaDa5Author
      Level 3
      April 24, 2020

      Thanks Doug! I'm not sure this will work though. I'm looking to calculate the date of the first time that there IS data in the IterationID field. So the first time it goes from blank to not blank.

      Doug_Den_Hoed__AtAppStore
      Community Advisor
      Doug_Den_Hoed__AtAppStoreCommunity Advisor
      Community Advisor
      April 24, 2020

      Yes, that's what I'm intending too, Melissa; here's the concept:

      • Initial State: Iteration ID blank, Iteration Assigned Date blank
      • Time passes
      • An edit is made, causing this formula to recalculate, and since the Iteration ID is still blank, the Iteration Assigned Date is left alone (so, still blank)
      • More time passes, with the previous step repeating (so, still blank)
      • Eventually, an Iteration ID does get assigned
      • The formula kicks in again, but this time, since the Iteration ID is NOT blank, proceeds to the second part, confirms that the Iteration Assigned Date is IS blank, and fills it in with $$NOW, capturing this moment when the Iteration ID was first set
      • Time passes
      • Regardless of what is edited thereafter, both branches of the formula leave the Iteration Assigned Date alone (with that initial $$NOW value)

      Worth testing, of course, but I believe it should work.

      Regards,

      Doug

        Terms & ConditionsAccessibility statement

        Loading footer...