Need to extract specific entries from open text field in crystal 1

[robg82]

I need to pull specific user entries from a 'work log' field. This field is updated anytime a user adds a comment to the designated ticket, and the entry is tagged with the username and a timestamp. The entries are only available through the work log.

Below is an example of a typical work log:

1/1/2011 15:25:00 user1

this is an example work log entry

1/1/2011 17:25:00 user2

this is example2 work log entry

1/1/2011 15:25:00 user1

this is example3 work log entry

1/1/2011 15:25:00 user13

this is example4 work log entry






I've had problems getting a formula to extract only 'user1' work log entries from this table's field. I've tried using split and desiganted users or time stamps but it returns everything from my target down. There is no max or min text for the field in the DB so the entries are dynamic in their length.

Below is an example of a formula that's returning everything from 'user1' most recent entry down:

stringvar array x := split({HIS_MOD_TRB_Trouble.Work_Log,'user1');
x[ubound(x)]


I can supply more details if necessary.

 

[robg82]

Results:

instr worklog test1
0.00
instr worklog test2
56.00

 

[robg82]

Reference for the formualas:

instr worklog test1 = instr({HIS_MOD_TRB_Trouble.Work_Log},chr(13))

instr worklog test2 = instr({HIS_MOD_TRB_Trouble.Work_Log},chr(10))

 

[lbass]

Try this:

stringvar x := {HIS_MOD_TRB_Trouble.Work_Log};
stringvar array y := split(x,chr(10));
numbervar i;
numbervar j := ubound;
stringvar z := "";
numbervar k;
stringvar array r;
numbervar b;
for i := 1 to j do(
redim preserve y[j];
if len(y)>1 and
y startswith
["Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"] then (
if split(y," ")[ubound(split(y," "))]='jdoe' then (
k := k + 1
)
;
if split(y," ")[ubound(split(y," "))]<>'jdoe' then (
k := 0
)
);
if k > 0 then (
b := b + 1;
redim preserve r;
r := y;
z := z + r+chr(10)
)
);
z;

You could replace 'jdoe' with a string parameter if you wish.

If you want to get rid of extra lines, you can change the last line of the formula to:

replace(replace(z,chr(10)+chr(10)+chr(10),chr(10)),chr(10)+chr(10),chr(10))

Be sure to format to "can grow".

-LB

 

[robg82]

Thank you much for the reply.

The formula is not returning anything. In the previous responses to this thread i grabbed example worklog data from our ticketing app, instead of out of the DB. Looks like there is a discrepency in how the date is formatted between the two (Tuesday, January 10, 2012 12:28:54 PM jdoe; 1/10/2012 12:28:54 PM jdoe).

The user's group (NOC - IP - Group, NOC - IP TIER 3 - Group) is also tagged on their entry. Would breaking the entries by the group work?

Below is exactly what comes out of the DB's worklog field:

"1/10/2012 12:25:55 PM nms
Process ID: E1089-1
Node: xxx
Summary: Alert: OM Device Interface cxxx
- GigabitEthernet9/1 ?? chn000xxx,-,chtrcb,l2trnk,185260,gbe,Wick Building Systems--17 Receive Utilization is 21% and Transmit Utilization is 141% - DeviceIP: xxx

Outage Discovered: 01/10/12 12:21:32

Outage Occurred: 01/10/12 12:18:21
1/10/2012 12:28:54 PM enovotny
NOC - IP - Group

enovotny xxx xxx Alert: OM Device Interface xxx - GigabitEthernetx/x ?? chnxx,-,chtrcb,l2trnk,185260,gbe, Building Systems--17 Receive Utilization is 21% and Transmit Utilization is 141% - DeviceIP: xxx 1 Investigating 1/10/12 12:18:21 PM 1/10/12 12:18:21 PM E1089-1 Normal



This alarm comes in constantly.

1/10/2012 12:39:15 PM enovotny
NOC - IP - Group

here it is again

xxx xxx Alert: OM Device Interface csw01mazowi - GigabitEthernet9/1 ?? chn000xx,-,chtrcb,l2trnk,185260,gbe, Building Systems--17 Receive Utilization is 15% and Transmit Utilization is 137% - DeviceIP: xxx 1 1/10/12 12:36:14 PM 1/10/12 12:36:14 PM E1089-1 Normal

1/10/2012 1:30:44 PM jmurphy
NOC - IP TIER 3 - Group
CPM showed the interface rx/tx bandwidth at 2meg. Changed it to 1gig. will monitor to see if alarm returns.
1/13/2012 7:59:25 AM enovotny
NOC - IP - Group
Alarm cleared."

 

[lbass]

Okay, try this:

stringvar x := {HIS_MOD_TRB_Trouble.Work_Log};
stringvar array y := split(x,chr(10));
numbervar i;
numbervar j := ubound;
stringvar z := "";
numbervar k;
stringvar array r;
numbervar b;
for i := 1 to j do(
redim preserve y[j];
if len(y)>1 and
isdate(split(y," ")[1])then (
if split(y," ")[ubound(split(y," "))]='enovotny' then (
k := k + 1
)
;
if split(y," ")[ubound(split(y," "))]<>'enovotny' then (
k := 0
)
);
if k > 0 then (
b := b + 1;
redim preserve r;
r := y;
z := z + r+chr(10)
)
);
z;// or replace(replace(z,chr(10)+chr(10)+chr(10),chr(10)),chr(10)+chr(10),chr(10))

I used "enovotny" but you could replace this with a parameter.

-LB

 

[robg82]

Perfect!

Thank you for working through this with me even though I may have wasted some of your time. This works exactly how I envisioned and will save me a ton of time going forward. Great!

Thanks again!