Retreiving the last update from a Remedy Worklog 1

[69AceCool]

CRXI
ODBC connection to Remedy ARS 5.1

Hi All,

I am trying to figure out a way to retrieve the last update from a a memo field.

e.g:
"
01/12/2005 13:25:50 Name2
Reassignment , this Incident was Previously Assigned to
Name1

02/12/2005 13:35:22 Name2
Blah, blah, problem.

02/12/2005 14:02:55 Name2
Resolved problem - user error.
"

Some worklogs are massive (as in 5 - 10 pages for one incident) but we only require the last update applied to the incident worklog. (Each entry always follows the format as above "Datetime then Analyst username") This way we can look up all outstanding incidents and provide a much smaller report with more relevant info.

Many thanks all.

 

[synapsevampire]

Yeah, that's a bad way to store meaningful data of course.

You may be able to use a known character to split up the data and automatically pull the last element, such as a carriage return:

stringvar array MyData:=split({table.field},chr(13));
MyData[ubound(MyArray)]

If there isn't a standard character, you'll have to read through the data and take the last valid date, which is slightly more complex, let's hear if that's the case.

I'd create a View to allow for future use of this brain dead field (from everything I see, Remedy databases are designed by coders, not qualified dbas), or if you can't create database objects in their database, link to the data in another datbases, such as Access.

-k

 

[69AceCool]

Hi Synapsevampire,

Yeah I hear you!

The system is very locked down, there isn't a standard character as you can cut and paste all sorts of data in to the worklog. A lot of times emails from analysts to users are also pasted in (these can have dates in them as well). If there is a way of reading through the data and taking the last valid date I would love to hear it?

Many thanks!

 

[lbass]

Something like this should work if the dates are presented in a somewhat consistent manner:

whileprintingrecords;
stringvar x := {table.field};
stringvar array y := split({table.field}," ");
numbervar i;
numbervar j := ubound;
datevar z := date(0,0,0);

for i := 1 to j do(
if isdate(y) then
z := cdate(y));
z

This should pick up the last date per record.

-LB

 

[69AceCool]

Hi lbass,

I get an error "An array's dimension must be an integer between 1 and 1000".

 

[lbass]

Is this field sometimes null? Try:

whileprintingrecords;
stringvar x;
if isnull({table.field}) or
len(trim({table.field})) = 0 then
x := "" else
x := {table.field};
stringvar array y;
if x <> "" then
y := split({table.field}," ");
numbervar i;
numbervar j := ubound;
datevar z := date(0,0,0);

for i := 1 to j do(
if isdate(y) then
z := cdate(y));
z

-LB

 

[69AceCool]

Thanks lbass! Worked a treat!