Extract Date and Comment from prepend log entry field
- 2 days ago
Using the wonderful explanation provided by MarkShnier in this post, I was able to answer my own question. Posting full formula in case it helps others.
The formula below is used to return up to the 3 most recent comments in a notes field with log entries turned on. New comments are prepended in the field. If there are no comments, it will return a "No Update" value. I needed the display format to just include the date of the entry and the actual comment. Hope this helps someone else.
Much appreciation to Mark and this forum for assistance in developing this formula.
var text LINEONE = Left(Part([Status Update],2,"["),9) & ":" & Right(Part([Status Update],2,"["),"]");
var text LINETWO = Left(Part([Status Update],3,"["),9) & ":" & Right(Part([Status Update],3,"["),"]");
var text LINETHREE = Left(Part([Status Update],4,"["),9) & ":" & Right(Part([Status Update],4,"["),"]");//starts by pulling out the date of the comment, then adds a delimitator ":", then pulls out the actual comment
If(Length($LINEONE) < 3 and Length($LINETWO) < 3 and Length($LINETHREE) < 3, "- No Update",
If(Length($LINEONE) > 1 and Length($LINETWO) < 3 and Length($LINETHREE) < 3, "- " & $LINEONE,
If(Length($LINEONE) > 1 and Length($LINETWO) > 1 and Length($LINETHREE) < 3, "- " & $LINEONE &"\n"& "- " & $LINETWO,
"- " & $LINEONE &"\n"& "- " & $LINETWO &"\n"& "- " & $LINETHREE)))//this section determines the "number" of comments in the field, then returns either "No Update", or the most recent comment, or the 2 most recent comments, or the 3 most recent comments.