Hey guys. I'm hoping this is going to be easy, but I just can't wrap my head around this. I have a database that writes to a field called "log" whenever it runs a script. They just decided that they want to know how long it takes between certain scripts for reporting. I have created script a execute time, script b execute time and time between script executes. This works going forward, but somehow I need to extract the data from the old records. The log looks like this:

VP Level Approval Requested. - 1/5/2010 - 3:11:51 PM

VP Approved. - 1/5/2010 - 6:23:53 PM

I need to somehow put the date on the line with "VP level Approval Requested" into the script a execute time and then VP Approved into script b execute time. Is there a way to do this?

Thanks!

I'm unclear if there is text before or after each of these phrases presented or even if there are carriage returns after each entry but I believe this will protect from undefined parameters:

Let ( [

levelStart = Position ( LogField ; "VP level Approval Requested" ; 1 ; 1 ) + Length ( "VP level Approval Requested" ) ;

levelEnd =LeftWords ( Middle ( LogField ; levelStart ; Length ( LogField ) ) ; 3 ) ;

appStart = Position ( LogField ; "VP Approved" ; 1 ; 1 ) + Length ( "VP Approved" ) ;

appEnd = LeftWords ( Middle ( LogField ; appStart ; Length ( LogField ) ) ; 3 )

] ;

[color:green]GetAsTimeStamp ( Substitute ( appEnd ; "-" ; "" ) ) - GetAsTimeStamp ( Substitute ( levelEnd ; "-" ; "" ) ))

... however, it would work best to have each line of the log, a separate record. All future needs (yet unknown) will be easier to handle.

UPDATE: Ooops, my test data didn't have the dash between the date and time so the calculation needs to be adjusted to remove the dash. I adjusted the portion in green.

Edited April 14, 201015 yr by Guest

I just need the date, so this should work. i'm going to try it now and report back. Thanks!

Hmmm... i get a "?". Here's the entire log field:

_____________________________________________

No Director, sending for Executive Approval. - 1/5/2010 - 3:11:36 PM

Director Approved. - 1/5/2010 - 3:11:48 PM

VP Level Approval Requested. - 1/5/2010 - 3:11:51 PM

VP Approved. - 1/5/2010 - 6:23:53 PM

Externally Approved. - 1/5/2010 - 6:23:53 PM

POR Requested. 1/6/2010 - 9:13:50 AM

All requests handled. Closing Request - 1/6/2010 - 9:14:29 AM

--------------------------------------------------

And here's exactly what i put in the calculation:

__________________________________________________

Let ( [

levelStart = Position ( log ; "VP level Approval Requested" ; 1 ; 1 ) + Length ( "VP level Approval Requested" ) ;

levelEnd =LeftWords ( Middle ( log; levelStart ; Length ( log) ) ; 3 ) ;

appStart = Position ( log; "VP Approved" ; 1 ; 1 ) + Length ( "VP Approved" ) ;

appEnd = LeftWords ( Middle ( log; appStart ; Length ( log) ) ; 3 )

] ;

GetAsTimestamp ( Substitute ( appEnd ; "-" ; "" ) ) - GetAsTimestamp ( Substitute ( levelEnd ; "-" ; "" ) ))

------------------------------------------------

Here's the file. The yellow is what I created. The green is your example above. They both produce 3:12:02 time elapsed which is correct.

You want to only compare dates so the calc must be changed to LeftWords() of 1 in both cases, you must change it to GetAsDate() instead of GetAsTimeStamp() AND ... you must change the result to a number because you are wanting only the number of days difference.

Let me know if you still get stuck but use the last calc I added in this file (which uses GetAsDate() etc ... :smile2:

vptest.zip

Edited April 14, 201015 yr by Guest
replaced file

DOY, i had the calculation set as a number. Set as a timestamp and it works. Thanks!