Extract Date from Logged Field
I'm trying to extract a date from a logged field. In the example below, I am trying to get the Backlogged Date. I figured I could use a series of 'left', 'right', 'notleft' etc. However, my first try ...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendParsing 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 (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
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 (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
Mark,
I have success pulling from the most recent field. But there will be instances where the date I want is not the most recent selection. That is why I was trying to base it off a word. Ever have success pulling a date from a field that could be 1st, 2nd, or 3rd....?
------------------------------
Mike Tamoush
------------------------------
I have success pulling from the most recent field. But there will be instances where the date I want is not the most recent selection. That is why I was trying to base it off a word. Ever have success pulling a date from a field that could be 1st, 2nd, or 3rd....?
------------------------------
Mike Tamoush
------------------------------
- MarkShnier__YouI don't have a formula handy, but I would use SearchAndReplace to change the word to a pipe | and then do the "left / right" thing based off the location of that Pipe. | There is nothing special about a Pipe other than it is a character rarely used intentionally for data entry.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------