Mark Status Timestamp In Field | Community
Skip to main content
Lawson02
Lawson02
Level 6
October 16, 2024
Solved

Mark Status Timestamp In Field

  • October 16, 2024
  • 1 reply
  • 1045 views

How would I modify this code from @doug_den_hoed__atappstore to reflect a single status change. For instance when a project goes into status "On Hold". Everytime I try and do it myself it updates using $$NOW time when another field is being updated, am banging my head against my desk (metaphorically) trying to figure this out, please help. I modified $$NOW to show UTC time to create a scenario in Fusion using this field.

 

Target:

{status}

 

Target History:
LEFT(IF(LEFT(Target History,LEN(IF(ISBLANK({Target}),"-",{Target})))={Target},Target History,CONCAT(IF(ISBLANK({Target}),"-",{Target})," (",$$NOW,") | ",{Target History})),2000)

 

Modified $$NOW time UTC:

CONCAT(
YEAR($$NOW),"-",
IF(MONTH($$NOW)<10,CONCAT("0",MONTH($$NOW)),SUB(MONTH($$NOW),7)),"-",
IF(DAYOFMONTH($$NOW)<10,CONCAT("0",DAYOFMONTH($$NOW)),DAYOFMONTH($$NOW)),"T",
IF(HOUR($$NOW)+7>24,CONCAT("0",SUB(HOUR($$NOW),24)),IF(HOUR($$NOW)+7<10,CONCAT("0",HOUR($$NOW)+7),HOUR($$NOW)+7)),":",
IF(MINUTE($$NOW)<10,CONCAT("0",MINUTE($$NOW)),MINUTE($$NOW)),":",
IF(SECOND($$NOW)<10,CONCAT("0",SECOND($$NOW)),SECOND($$NOW)),".000Z")
)

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 Sven-iX

@lawson02 

 

here is an example of a one-time-set calculated field

 

IF( ISBLANK({DE:status set to NEW})&&{status}="NEW", $$now, {DE:status set to NEW} )

 

The logic is simple: If the field is blank and the current status is NEW - set the field to $$now, otherwise, keep the value (literally, re-set it to its current value)

If you want to get more fancy, format your own date/time string (pain in the neck but i needed to see seconds for testing)

 

IF(ISBLANK({DE:status set to NEW})&&{status}="NEW", IF(MONTH($$NOW)<10,"0","")+MONTH($$NOW)+"/"+IF(DAYOFMONTH($$NOW)<10,"0","")+DAYOFMONTH($$NOW)+YEAR($$NOW)+" "+IF(HOUR($$NOW)<10,"0","")+HOUR($$NOW)+":"+IF(MINUTE($$NOW)<10,"0","")+MINUTE($$NOW)+":"+IF(SECOND($$NOW)<10,"0","")+SECOND($$NOW),{DE:status set to NEW})

 

I had 3 fields and timestamped on NEW, INP and CLS - these stay the same even when I press recalc, or otherwise edit the issue.

 

1 reply

Sven-iX
Community Advisor
Sven-iXCommunity AdvisorAccepted solution
Community Advisor
October 16, 2024

@lawson02 

 

here is an example of a one-time-set calculated field

 

IF( ISBLANK({DE:status set to NEW})&&{status}="NEW", $$now, {DE:status set to NEW} )

 

The logic is simple: If the field is blank and the current status is NEW - set the field to $$now, otherwise, keep the value (literally, re-set it to its current value)

If you want to get more fancy, format your own date/time string (pain in the neck but i needed to see seconds for testing)

 

IF(ISBLANK({DE:status set to NEW})&&{status}="NEW", IF(MONTH($$NOW)<10,"0","")+MONTH($$NOW)+"/"+IF(DAYOFMONTH($$NOW)<10,"0","")+DAYOFMONTH($$NOW)+YEAR($$NOW)+" "+IF(HOUR($$NOW)<10,"0","")+HOUR($$NOW)+":"+IF(MINUTE($$NOW)<10,"0","")+MINUTE($$NOW)+":"+IF(SECOND($$NOW)<10,"0","")+SECOND($$NOW),{DE:status set to NEW})

 

I had 3 fields and timestamped on NEW, INP and CLS - these stay the same even when I press recalc, or otherwise edit the issue.

 

    Lawson02
    Lawson02Author
    Level 6
    October 16, 2024

    Thanks for the quick response. Is it possible to add, if status changes from NEW, set back to blank?

    Sven-iX
    Community Advisor
    Sven-iXCommunity Advisor
    Community Advisor
    October 17, 2024

    Not sure i understand the rationale - but this formula will set the field to a dash if the status is not NEW

    ( setting a calculated field to emptystring doesn't work )

    IF( {status}!="NEW", "-", IF( (ISBLANK({DE:status set to NEW reset}) || {DE:status set to NEW reset}="-")&&{status}="NEW", IF(MONTH($$NOW)<10,"0","")+MONTH($$NOW)+"/"+IF(DAYOFMONTH($$NOW)<10,"0","")+DAYOFMONTH($$NOW)+"/"+YEAR($$NOW)+" "+IF(HOUR($$NOW)<10,"0","")+HOUR($$NOW)+":"+IF(MINUTE($$NOW)<10,"0","")+MINUTE($$NOW)+":"+IF(SECOND($$NOW)<10,"0","")+SECOND($$NOW), {DE:status set to NEW reset} ) )

     

    Terms & ConditionsAccessibility statement

    Loading footer...