Using LOCALTIMESTAMP in item details computed field

[rmm359]

Hello,

Previously, someone had created a countdown field in a workspace that would countdown the days until something was due. They also created another field that had flags for when the countdown was approaching. These fields at the time were saved just fine in plm, but I am trying to create additional fields similar to it by cloning the field, and it is telling me that localtimestamp is undefined. Is there a way to work around this? I want it to countdown from today's date to the next update due date. I pasted an example below

Example: Guage R&R (the row already has the next update due gauge RR field)

field name: countdown gauge R&R

(DATEDIFF('DAY', LOCALTIMESTAMP, NEXT_UPDATE_DUE_GAUGE_RR))

field name: flag gauge R&R

(CASE WHEN NEXT_UPDATE_DUE_GAUGE_RR IS NULL THEN '<span style="text-align:center; line-height:30px; color:black; background:black; width:100px; float:left; height:25px;">BLACK</span>' WHEN DATEDIFF('DAY', LOCALTIMESTAMP, NEXT_UPDATE_DUE_GAUGE_RR) > 30 THEN '<span style="text-align:center; line-height:30px; color:green; background:green; width:100px; float:left; height:25px;">GREEN</span>' WHEN DATEDIFF('DAY', LOCALTIMESTAMP, NEXT_UPDATE_DUE_GAUGE_RR) > 0 THEN '<span style="text-align:center; line-height:30px; color:yellow; background:yellow; width:100px; float:left; height:25px;">YELLOW</span>' ELSE '<span style="background:red; color:red; width:100px; float:left; height:25px;">RED</span>' END)

Again, these fields were functioning properly when created and saved at the time, but now, the system is not recognizing LOCALTIMESTAMP. Is there another name for it or another way I can get the system to take the difference between the current date (which would obviously change every day) and my next update due field?

Thanks 

[Andrew.Shaw · Accepted solution]

Hello Rania,

You could use something like this:

to_number(to_char((DATE1 - now()),'dd'),'999')

and, as for negative figures, you could add this in front of the calculation:

(CASE WHEN DATE1 > now() THEN 1 ELSE -1 END) *

Best regards,

[rmm359]

It worked. Thank you!