Retrieving the most recent date from a series of multi-line text time stamps

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
I have 5 comment fields:
Comment Field 1 (C1)
Comment Field 2 (C2)
Comment Field 3 (C3)
Comment Field 4 (C4)
Comment Field 5 (C5)
Each comment field is logging edits, so the most current comment is the last date there was a response in the field.

I want to extract the date/time form each comment field (a formula field for each comment field).
Formula TimeStamp Field 1 (TS1)
Formula TimeStamp Field 2 (TS2)
Formula TimeStamp Field 3 (TS3)
Formula TimeStamp Field 4 (TS4)
Formula TimeStamp Field 5 (TS5)

Note: some may be null! Not all comment fields may have entries.

I also want to extract the submitter of the last modified comment for each field:
Submitter Field 1 (S1)
Submitter Field 2 (S2)
Submitter Field 3 (S3)
Submitter Field 4 (S4)
Submitter Field 5 (S5)

Note: some may be null! Not all comment fields may have entries.

Now that all the above is completed, I want to validate the most current submission date of all comment fields,
and once identified, The text in the Field should read: "Last submission: " [(TS1-TS5 - whichever is most recent)] &" by " & [[(TS1-TS5 - whichever is most recent)].

Thanks for your help.
Photo of JOSH

JOSH

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,874 Points 50k badge 2x thumb
These formulas should help you get to your objective.





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)