Forum Discussion

EmberKrumwied's avatar
EmberKrumwied
Qrew Captain
16 days ago

Extract Date and Comment from prepend log entry field

I have created a formula field by piecing together answers from similar posts, but now need to take things a step further and can't seem to find the answer. I have a notes field (Status Update) that...
formula query
  • EmberKrumwied's avatar
    EmberKrumwied
    15 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.

EmberKrumwied's avatar
EmberKrumwied
Qrew Captain
15 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.