How do we use the log entry on a status field that is used in report filters?

  • 1
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I'm using a "Text - multi-line" field as a status field on a record. I also have log entries turned on for my status field. The status field is also used as a filter in reports. The problem I'm having is that everytime someone changes the status the field is date & time stamped (as expected). However, the date and time stamp makes the field impossible to use as a filter. 

For example, if a user changes the status field to "In Process," then the field is stamped with "[OCT-04-17 <User Name>] In Process." Unfortunately, this messes up the filter for the report.  

How can I fix this so that we have a status field that is used for report filtering and log entries?
Photo of John

John

  • 122 Points 100 badge 2x thumb

Posted 1 year ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,316 Points 50k badge 2x thumb
No problem,  Just create another field which parses out just the latest update status.

Here are some formulas.  I have highlighted the one you need.

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)
Photo of John

John

  • 122 Points 100 badge 2x thumb
Thank you!  This works.