Report showing Project Owner Updates

[TimAb]

‚I am trying to show All or Selected Updates entered by the Project Owner. I can show the Project Owner's Last Update using "Last Condition Note: Note Text" after filtering the report for a limited number of Project Owners (by name). I would like to have another couple of options:

1) Show only the Project Owner's First Update - as this update provides initial details for approvers to consider.

2) Show all Project Owner's Updates - as the project owner may describe additional or revised details missing from the first update.

Here are the Text Mode lines from the Last Condition Note: Note Text column. Just for kicks I tried changing the "lastConditionNote" entries to "firstConditionNote", but it did not work.

displayname=Project Owner's Last Update

linkedname=lastConditionNote

namekey=view.relatedcolumn

namekeyargkey.0=lastConditionNote

namekeyargkey.1=noteText

querysort=lastConditionNote:noteText

valuefield=lastConditionNote:noteText

valueformat=HTML

I will appreciate any advice from the Community.

[Doug_Den_Hoed__AtAppStore]

The Last Update is just that, so I don't think it will take you where you want to go.

However, a few years ago I designed a workaround to highlight a particular comment using custom data.

The concept is to use a calculated parameter attached to the object being commented upon (eg Task) that monitors the human-entered Last Update, and "IF" it matches some certain condition, replace (or append, or prepend) the previous such matching Last Update with this new valid text; otherwise, if the condition is not met, leave the previous such matching contents alone.

Having surely defended my Run On Sentence World Title Belt, consider this simple example:

- create a calculated text parameter called "Last Official Status" (LOS)

- add the LOS to a Task category (and add to all Tasks)

- set the LOS formula to IF(LEFT(Last Update,2)="! ",Last Update,LOS)

As Updates are made on the Task, the formula will examine them and record none them, until one is entered that starts with the unusual but prearranged "! " convention (eg "! DDH 2017-02-01 wow, it is already February!"). Matching the "! ", the formula plucks that Last Update out of the comments and Duly notes it in the LOS. One thousand comments of clever banter later - pushing my original well past the Update tab's historical reach limit and therefore offscreen - provided none included the all powerful "! " prefix, my original pithy remark will remain, protected in the LOS for all to see (and easily report on).

Using this technique, you can then get as clever as you dare:

- Different prefixes for similar designated LOS-like parms

- ignore prefixes from all but the current primary task owner

- append to LOS, vs overwriting, to effectively create a running log

- prepend to LOS to avoid truncating the latest data (space limits)

- inject audit info such as who and when

- track such audit info separately (eg LOSLastUpdateDate)

- embellish Workfront reports by shading LOS "lighter" depending on how old (suggesting "faded") the LOSLUD is relative to $$NOW

And many, many other inventive uses.

Enjoy!

[JohnMa3]

Hi Doug,

I'm trying your LOS recommendation and I finally got it working. Just a quick correction:

• When I entered your formula in the Calculation box, I got an error, which I surmised is because "Last Updated" is not a field being pulled into the Task category. There is one called "Status Update," which is the one I used, so that the formula reads: IF(LEFT(Status Update,2)="! ",Status Update,Last Official Status)

That got the custom form to save and close. In testing it, I applied the new custom form (with the LOS calculated field) to the tasks I want to report the LOS on, and I went into the updates for those tasks and entered in an update that started with "! ". After a little trial and error, I realized that the LOS only shows up on reports/dashboards if the task being updated is owned/assigned to the person leaving the update.

Is there any way to open that reporting up so that the Last Official Status can be calculated from an update left by anyone? Or must that ownership connection be there?

Is Status Update the right field to be using in the calculation? I tried this with projects that were both with Current and On Hold statuses, and neither work. I tried it on projects for which I was the owner and projects on which I wasn't a

[Doug_Den_Hoed__AtAppStore]

Hi John,

When people make updates (whether they are the Task Owner or not), it's stored in the Last Note.Note Text, so if you switch to that one, I believe you'll get the generic behavior you're looking for.

Regards,

Doug

[JohnMa3]

Thanks so much, Doug. That did the trick! Could you be so kind as to point me in the direction of how I can find the commands to prepend and inject some audit info (who made the update and when)? I know I can get those fields from the API explorer, but I'm not too well versed in the syntax of the commands I need to enter into the Calculation field. John Martins OUC

[NRYNR]

Hi John,

Here's one of my favorites, which appends the note owner and date to the end. It also shortens the length of the update to the first 139 characters. Of course, you can override this by simply increasing the numbers in the text mode.

https://wf-pro.com/textmode/text-mode-views/#latest-update

--Narayan

[JohnMa3]

Thank you, Narayan. This is very helpful in understanding how to apply expressions in text mode. I think I'm running into an issue with syntax, however. On the calculated text parameter, the expression I'm using is:

IF(LEFT(Last Note.Note Text,2)="! ",Last Note.Note Text,Last Official Status)

That works great, but I don't think I'm skilled enough to interpret the expression in the PDF you shared- which is meant for text mode in a report, and I'm not quite sure if it should be different in a calculated field. (There are curly brackets in the one you shared, and I'm making a best guess attempt at interweaving them both.) I'll repost it here to aid in the discussion:

valueexpression=IF(LEN({lastNote}.{noteText})>140, CONCAT(SUBSTR({lastNote}.{noteText},0,139),"...(open for more) -- ", {lastNote}.{owner}.{name}," on ", {lastNote}.{entryDate}), IF(LEN({lastNote}.{noteText})>0, CONCAT({lastNote}.{noteText}," -- ", {lastNote}.{owner}.{name}," on ", {lastNote}.{entryDate})))

Ideally, I would have an expression in the calculated text field that both filters for updates made with the "! " before it and then prepend and add owner date info. Hoping that's possible!

[NRYNR]

Indeed, when you are building a calculation on a custom form, the syntax changes. In my example, the calculation would be as follows:

IF(LEN(Last Note.Note Text)>140, CONCAT(SUBSTR(Last Note.Note Text,0,139),"...(open for more) -- ", Last Note.Owner.Name," on ", Last Note.Entry Date), IF(LEN(Last Note.Note Text)>0, CONCAT(Last Note.Note Text," -- ", Last Note.Owner.Name," on ", Last Note.Entry Date)))

I personally wouldn't want this to be a calculation on a custom form though because I'd have to recalculate custom expressions on the project/task/issue record to ensure that I had "calculated" the last note. That's why it's best in a view/report, to ensure it is fully dynamic and updated.

Here's what I interpret your requirements as:

If the last note contains ! in the first two characters, then show me the owner of the note, the entry date of the note, and the note itself.

Is this accurate? Also, I'm not sure what you're looking for from "Last Official Status". Is that a custom field?

--Narayan

[JohnMa3]

Thanks so much, Narayan. I see- I thought the custom form would automatically calculate whenever a report was being generated, but I see why it would be best to include it in the view/report.

And yes, you got the requirements right. We would want to add the "! " so that the view/report knows to only show the updates that are marked as important, and not ones that are related to other more granular aspects of the project. Yes, "Last Official Status" was a custom parameter that I created through a custom form from an idea that Doug shared in Response #2 on this thread. ‚

[NRYNR]

After going back through the thread and seeing Doug's original message, it occurred to me that I hadn't yet understood that updates would actually trigger a custom form recalculation. I learned something new today! That said, using this formula will give you the latest note containing the !, along with the person who entered it and the date.

IF(LEFT(Last Note.Note Text,2)="! ",CONCAT(SUBSTR(Last Note.Note Text,1,LEN(Last Note.Note Text))," --- ",Last Note.Owner.Name," on ",Last Note.Entry Date),Last Official Status)

If the last note contains !, show me the last note without the first character but with all subsequent characters, followed by the owner of the note and when it was entered. Otherwise, keep the last official status value intact.‚

[Doug_Den_Hoed__AtAppStore]

Very nice work Narayan! Thanks for co-piloting this one.

John, with your new skills and Narayan's initial example in place, you're now primed to stretch your text mode wings and try some of the Clever As You Dare augmentations I'd mentioned in my first post.

Regards,

Doug

[JohnMa3]

Thanks so much, Doug and Narayan. We got it working! John Martins OUC