Expand my Community achievements bar.

Latest Community Ideas Review is Out: Discover What’s New and What to Expect!

How to add timestamp before update

Avatar

Level 3

I found this calculated custom field on Workfront that display a field's edit history. This calculation adds the date after the update. I've been struggling for hours to try to move the timestamp before the update but with no success. I cannot seem to figure out where my error is at. Can someone please help.

 

Original calculation code: 

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

 

Output: <update> <date> | <previous update history>

 

I want to move the date first then update, and here is my modification of the code but the keep getting an error that this is invalid. 

 

LEFT(IF({DE:Instructions Edit History},LEN(IF(ISBLANK({DE:Instructions}," ",$$NOW," ",{DE:Instructions})))={DE:GCX_Instructions},{DE:Instructions Edit History},CONCAT(IF(ISBLANK({DE:Instructions})," ",$$NOW," ",{DE:Instructions})," ",{DE:Instructions Edit History})),2000)
 
Does anyone have any idea where the error may be?
Topics

Topics help categorize Community content and increase your ability to discover relevant content.

5 Replies

Avatar

Community Advisor

Apologies if I state the obvious -- I think it's really better if in general people understand the code before modifying it. (maybe you already did this part? I wasn't sure from your post what you had considered)

 

The "LEFT" expression is supposed to display a specific number of characters from the left side of the string, and the syntax is LEFT(string, length)

 

The "LEN" expression is supposed to give you the length of a string and the syntax is LEN(string)

 

With this in mind, you should be striving to understand the original calculation, i.e. how does it get to the output of "<update> <date> | <previous update history>".

 

Step 1: reduce your calculation to the start and finish in order to match the "left" expression's syntax.

LEFT(LEAVE THIS PART OUT,2000)

Whatever it is you're doing, has a limit of 2000 characters.

 

Step 2: Breaking down the string to understand it better. 

 

IF(LEFT({DE:Instructions Edit History},LEN(IF(ISBLANK({DE:Instructions}),"-",{DE:Instructions})))={DE:Instructions},{DE:Instructions Edit History},CONCAT(IF(ISBLANK({DE:Instructions}),"-",{DE:Instructions})," (",$$NOW,") | ",{DE:Instructions Edit History}))

 

Step 2.A) The whole string is an IF statement -- you are looking for the following syntax: 

IF(condition, trueExpression, falseExpression)

 Broken down, your original statement looks like this:

 

IF(

HERE'S YOUR CONDITION

LEFT({DE:Instructions Edit History},LEN(IF(ISBLANK({DE:Instructions}),"-",{DE:Instructions})))={DE:Instructions},

HERE'S YOUR TRUE EXPRESSION

{DE:Instructions Edit History},

HERE'S YOUR FALSE EXPRESSION

CONCAT(IF(ISBLANK({DE:Instructions}),"-",{DE:Instructions})," (",$$NOW,") | ",{DE:Instructions Edit History}))

 

Please note your condition contains another IF statement, so you could choose to break this down in the same way.

 

Step 2.B) Understanding the specific condition:

LEFT({DE:Instructions Edit History},LEN(IF(ISBLANK({DE:Instructions}),"-",{DE:Instructions})))

Display this much of the calculated field: if the instructions field is blank, it's a dash, otherwise it's the length of whatever is in the instructions field 

={DE:Instructions},

 The contents of the field you're tracking.

 

In other words, the first part of the calculated field should exactly match whatever field it is that you're tracking.

 

Step 2.C) Understanding the true and false expressions.

If it's true, if they are both equal, just leave it alone -- it is what it is. 

{DE:Instructions Edit History}

If it's not true, tack the contents of the instructions field and a date ("now") in front of the existing calculated field.

 

With all that out of the way, I have a couple of initial observations which led me to write up this obvious-sounding response.

 

1) Your second IF statement -- inside your LEN expression -- appears to have some problems. If you consider the initial syntax I offered (if condition, trueExpression, falseExpression) -- you can see your IF statement is more like "if this is blank," followed by 4 different expressions, none of which are bracketed in any way. Work on what it is you're trying to say here, understanding that the eventual answer needs to be able to be expressed as a LEN.

2) You have included a third field, "GCX_Instructions" -- not sure if this is a typo or if the third field has some bearing on the rest of the calculation. Only you can say.

 

I hope this is of some small help.

Avatar

Community Advisor

editing to add I would probably try for a new LEFT length that is a series of SUMs. e.g. the sum of the LEN in the original calculation, a LEN of the $$NOW, and add 2 for the spaces. Maybe something like

 

SUM(LEN(IF(ISBLANK({DE:Instructions}),"-",{DE:Instructions})),SUM(LEN($$NOW),2))

 

Avatar

Level 3

Hi @skyehansen - Thank you for the suggestion. I just have not been able to get it to work and have taken a break from it. I may revisit this again in a couple weeks. If I still cannot get this to work, do you have any other suggestion on how to pull a collection of updates for an object? Example: a user would like a report to show all updates with timestamp for a Task. This way, they do not have to search through the project history updates.

Avatar

Community Advisor

I'd recommend either a journal entry or notes report. (or failing that, just have the user look through the task updates rather than scrolling through the project updates)

 

Good luck!

Avatar

Level 2

Hi, We have the same issue here, and I was never able to get the timestamp before the update either.   What we ended up doing was showing the timestamp of the *last* update next to the field, serving as a visual trigger for the user to know that something changed.  Then, we send them to a journal report like @skyehansen suggested.  (We also put the link to the journal report into the custom form next to the field so they have a reminder of where to find the data.)   Maybe that can help you in the interim?