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

Mark Status Timestamp In Field

  • October 16, 2024
  • 1 reply
  • 1042 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
Sven-iXAccepted solution
New Participant
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
    New Participant
    October 16, 2024

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

    Sven-iX
    Sven-iX
    New Participant
    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...