Discussions

 View Only
  • 1.  Trying to retrieve last comment from Audit History

    Posted 03-04-2023 13:17

    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
    ------------------------------


  • 2.  RE: Trying to retrieve last comment from Audit History

    Posted 03-04-2023 14:45
    Edited by Mark Shnier (Your Quickbase Coach) 03-04-2023 17:22

    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
    ------------------------------



  • 3.  RE: Trying to retrieve last comment from Audit History

    Posted 03-04-2023 22:26

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



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