[Event Follow-Up] Ask Workfront Customer Support – Troubleshooting Calculated Expressions and Columns

[NicholeVargas]

 First and foremost, thank you to Quinn Cullison @QuinnCullison from the Adobe Workfront Support team for sharing his expertise and troubleshooting tips on calculated fields and expressions. And to everyone who attended today's session, thanks for being so engaged and asking great questions! If you have any follow-up questions, favorite expressions, tips, etc., reply to this post so everyone can take advantage. 

If you weren't able to attend, watch the on-demand recording and download a copy of the slide deck below:  

• Slide deck PDF 
• Workshop recording 

Additional resources share in the chat: 

• If you run into ANY issues, contact Customer Support – they are free and available 24/7!  
• To help troubleshoot your calculated expressions, count parenthesis, check curly braces, break calculations into smaller pieces, and if all else fails, reach out for help!  
• Want to track the timestamp an object moved from one status to another (applicable to objects moving forward, will not populate historical data)? Learn how to do that HERE 
• The documentation for Calculated Data Expressions provides you with a full list of available expressions to be used in calculated columns or fields  
• Learn more about popular expressions like IF and CONCAT, along with date-related expressions like DATEDIFF, WEEKDAYDIFF and WORKMINUTESDIFF.  
• Recalculating expressions – can be done manually or via an action to trigger it automatically. See what actions trigger a recalculation here.  
• Want some more ideas for sample calculated expressions? Download the slide deck (example #10) from the 10 Text Mode Tips workshop  
• If you watch the recording or review the first few slides, you’ll learn about how to navigate the API Explorer – your go-to for all things text mode  

Be sure to check out the calendar of events for August – there are a handful of exciting sessions coming up, along with Skill Exchange taking place on August 15. Register through the Events page on Experience League. Hope to see you there! 

[KatherineLa]

Thanks to everyone for a great session in the chat as well. I learned a ton from the presenter and everyone else!

[RandyRoberts]

Here are a few of my favourites that I have used in the past:

This formula extracts the leftmost characters from the "DE:Client" field up to the first space character, or more simply, extract the first word from a text field.

LEFT({DE:Client}, NUMBER(SEARCH(" ", {DE:Client},0)))

Here's how it works:
SEARCH(" ", {DE:Client}, 0) - This function finds the position of the first space character in the "DE:Client" field. The third argument of 0 specifies the starting position for the search, which in this case is the beginning of the field.
NUMBER(SEARCH(" ", {DE:Client},0)) - This function converts the result of the SEARCH function to a number.
LEFT({DE:Client}, NUMBER(SEARCH(" ", {DE:Client},0))) - This function returns the leftmost characters of the "DE:Client" field up to the position of the first space character. The number of characters to extract is determined by the result of the NUMBER function.

Calc field formatted as partialAtDate

Calc date fields are short form dates, not good for international. This calc puts it in partialAtDate format using the CASE expression. Cleartime prevents the day name and month name from advancing due to an after hours Planned Completion Date.

Report column

valueexpression=CONCAT(CASE(DAYOFWEEK(CLEARTIME({plannedCompletionDate})),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"),", ",DAYOFMONTH(CLEARTIME({plannedCompletionDate})),", ",CASE(MONTH(CLEARTIME({plannedCompletionDate})),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"),", ",YEAR(CLEARTIME({plannedCompletionDate})))

CALC Field

CONCAT(CASE(DAYOFWEEK(CLEARTIME(Planned Completion Date)),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"),", ",DAYOFMONTH(CLEARTIME(Planned Completion Date)),", ",CASE(MONTH(CLEARTIME(Planned Completion Date)),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"),", ",YEAR(CLEARTIME(Planned Completion Date)))

This isn't very useful by itself but it's really handy if dealing with international teams that format their dates differently and you need to calc a date they both can understand. (7/1/2024 vs 1/7/2024 - Is it July 1 or January 7?)

RESULT: Thursday, 09, Nov, 1989

Replace character(s) in a field

Replacing the spaces in a text string field with underscore.

REPLACE({name}, " ", "_")

This one could actually be used to replace character(s) with any other character(s). You can use it for an entire string as well. In an IF statement, it can be even more useful:

IF field {name} contains "this", replace "this" for "that", else leave it alone

[RandyRoberts]

Thanks all for the "likes" it's nice to be appreciated.

Here's another quick tip:

If you see a piece of text mode code and really want to learn how it works, Go to ChatGPT and prompt "Please explain" and then paste in the code. I did that in the first example and paraphrased to give you an example. ChatGPT is really good at interpreting code, better than Bing or Bard. it will not write good text mode code though (for the most part). It just makes stuff up like it knows what it's talkin' about!

[KatherineLa]

To be fair, most of my text mode starts as '....I think this does what I want??...' when it starts out too!