Forum Discussion

StephanieB's avatar
StephanieB
Qrew Member
2 years ago

Trying to retrieve last comment from Audit History

Help! I'm trying to retrieve the last comment on a table, but I am ending up with the one above the last one.

Any help would be greatly appreciated!

Example of field (comments):

[MAR-03-23 11:21 AM  Crystal] Updated Screener 
[MAR-03-23 11:22 AM  Crystal] .
[MAR-03-23  1:35 PM  Nicole ] confirmed approval, MILITARY LEASE - used LES for POI
[MAR-04-23 12:45 PM  Megan Botti] Added MID + Concession

Expected: [MAR-04-23 12:45 PM  Megan Botti] Added MID + Concession) but it is returning the one aove it

Returned: [MAR-03-23  1:35 PM  Nicole ] confirmed approval, MILITARY LEASE - used LES for POI

FORMULA

If(Trim([Comments])<>"", 
"[" & Right(NotRight([Comments],"["),"["))



------------------------------
Stephanie Bouldin
------------------------------
  • Try your IF statement and then this

    "[" &Trim(Right([my update field],"["))

    Here are some other related formulas

    Parsing Append Only Fields

    so... if you have a text field set to Log Changes where the new entries act in the default manner which is to be at the bottom of the updates, here are some formuals to use to parse out the values from the most recent entry.  I am also assuming that you are logging the date and not the date and time.

    Most recent value (formula text field type)

    Trim(Right([my update field],"]"))

    Date of most recent update (this needs to be a formula date field)

    ToDate(Left(Right([my update field],"["),9))

    Who did the most recent update (formula text field type)

    Trim(NotLeft(Left(Right([my update field],"["),"]"),9))

    Parse out the date and time of the update

    var text DateAndTime = Left(Right([append only with date /time],"["),18);
    var date TheDate = ToDate(Left($DateAndTime," "));
    var timeofday TheTime = ToTimeOfDay(NotLeft($DateAndTime,9));


    ToTimestamp ($TheDate, $TheTime)



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------

    • StephanieB's avatar
      StephanieB
      Qrew Member

      Mark - You are a God amongst mere mortals. I appreciate your help!!! 



      ------------------------------
      Stephanie B
      ------------------------------