Parsing Data from Memo

[cuttyfunk81]

Hello Guys -

New to the forums and new to Crystal Reports 2008. I have been asked to generate a report from our support database that only shows the most recent note added to the field. The problem that I am running into is that the entries are not seperated into seperate fields by the day they were entered, and instead are a "block" of text seperated by the date they were entered "Month/Day".

I have googled and looked over my Crystal book but cannot find how to parse this data out. Any help would be great! For reference I have copied in a sample of what I am needing.

10/28 (*****)
Problems with IP address were captured, and will be resolved in the next firmware release.
The expectation has been set with the customer that drives are exposed to not coming online after a firmware update, and that power cycling the unit (which may be needed up to two times) will likely be required. This is resolved in 12.0.1, and should not occur again during future upgrades.
10/25 (*****)
The unit is currently up and running.
In a meeting with **** ******, we determined that drives going offline was an expected result of the FW update, and thus the reason that engineering requested power cycling the unit twice to ensure the drives come up again.
This problem is fixed in 12.0.1, but each of the units are exposed to this.
We are attempting to reproduce the IP failure in-house to determine the failure mode.

I only need the data from between "10/28 (*****)" and "10/25 (*****)"

Thanks!!!!

 

[lbass]

Try using a formula like this:

stringvar array x := split({table.memo},"(*****)");
if ubound(x)>= 2 then
left(x[2],len(x[2])-6);

-LB

 

[cuttyfunk81]

LB -

Thank you for your response. I entered the formula you suggested and while I do not get any errors, the report is not displaying any data for that formula.

Any other ideas or suggestions on what I might be doing wrong?

 

[lbass]

Does the memo field always contain (*****) after the date? Can the memo field be null?

Please show the actual formula that you used.

-LB

 

[cuttyfunk81]

An update for you if it helps...

I revised the formula you gave me to only show:

stringvar array x := split({table.memo},"(*****)");
ubound(x)

This displayed a number, which from my understanding is the count of the defined array in the memo. So I know the first part of the formula you provided is correct, but the 3rd line of code you provided isn't displaying the most recent note.

Thanks!

 

[cuttyfunk81]

LB-

Here is the code I used:

stringvar array x:= split({Incident.Incident Resolution},"(Briana)");
if ubound(x)>=2 then
left(x[2], len(x[2])-6);

I had originally put ***** in place of the person's name who made the note.

 

[lbass]

You need to format the formula to "can grow" (right click on it->format field->common tab). Also change it slightly to add the date back in:

stringvar array x := split({table.memo},"(*****)");
if ubound(x)>=2 then
x[1]+left(x[2],len(x[2])-6);

-LB

 

[lbass]

I didn't see your post. Try this instead:

stringvar x := extractstring({table.memo},chr(13),"/");
left({table.memo}, instr({table.memo},chr(13))+
left(x,len(x)-2)

-LB

 

[cuttyfunk81]

LB -

Thanks again for your help so far. I tried your most recent formula this morning and got the following error:

"A number, currency amount, date, time, or date-time is required here"

It is highlighting the

"left(x,len(x)-2))" portion of the formula.

Any advice?

Thanks!!!

 

[cuttyfunk81]

An update:

Revised the formula provided to have a close ) at the second line of code. This removed the error described above. Now, the formula does not have an error, but when I run the report, I get the following message pop up:

"String length is less than 0 or not an integer."

Below is my formula:

stringvar x:= extractstring({Incident.Incident Resolution},chr(13),"/");
left({Incident.Incident Resolution}, instr({Incident.Incident Resolution},chr(13)))+
left(x,len(x)-2)

 

[lbass]

stringvar x:= extractstring({Incident.Incident Resolution},chr(13),"/");
left({Incident.Incident Resolution}, instr({Incident.Incident Resolution},chr(13)))+
(
if len(x)> 2 then
left(x,len(x)-2) else
x
)

This will show you cases wehrer there is no chr(13) or "/". It's possible that chr(10) is being used instead of chr(13) in the memo field, also.

-LB

 

[cuttyfunk81]

Thanks LB -

Its really close now but for some reason is cutting off before the start of the next note. Example below:

10/28 (Briana)
Problems with IP address were captured, and will be resolved in the next firmware release.
The expectation has been set with the customer that drives are exposed to not coming online after a firmware update, and that power cycling the unit (which may

It should be:

10/28 (Briana)
Problems with IP address were captured, and will be resolved in the next firmware release.
The expectation has been set with the customer that drives are exposed to not coming online after a firmware update, and that power cycling the unit (which may be needed up to two times) will likely be required. This is resolved in 12.0.1, and should not occur again during future upgrades.


I've messing around with this for a while now and haven't had any luck. If you have any ideas on how to resolve this, that would be great, if not its ok. You've been so much help already that I can't thank you enough!

 

[lbass]

Did you format the formula to "can grow"? Are you doing this within a subreport which NOT set to grow?

-LB

 

[cuttyfunk81]

LB -

Yes, I did select "can grow" and this is not in a subreport.

 

[cuttyfunk81]

Here is another example from a different record in the report that shows the data being cuttoff:

What the formula is displaying:

11/1 (Briana)
I have e-mailed Oleksiy and Travis Hobbs (OK site) to collect the motion traces and CAN of the LS. The sense data 5:3b:0e indicates that the library does not believe that there is a tape in the slot which the host is attempting to move from. The motio

What the actual field in the database shows:

11/1 (Briana)
I have e-mailed Oleksiy and Travis Hobbs (OK site) to collect the motion traces and CAN of the LS. The sense data 5:3b:0e indicates that the library does not believe that there is a tape in the slot which the host is attempting to move from. The motion trace will indicate the last time a tape was moved from that slot, and if there was ever a tape returned. I will work to verify that the physical slot is empty and work with Oleksiy to see how his system is not updated with the correct inventory.
If there is a tape physically in the source where the move is being issued from, the motion and CAN traces will help us to identify why the library is reporting that slot as empty.

11/1 (BH) - 1:11 PM - Requested ASL, consulted with Bri and transfering to her.

 

[lbass]

In the database tab, uncheck "select distinct records" if checked.

Sometimes printer drivers can cause truncation of memo fields.

You could also try using a native driver instead of your current driver.

I've seen each of these suggestions mentioned as solutions to this type of problem, but don't have a way of testing this myself.

-LB

 

[cuttyfunk81]

-LB

Wanted to give you an update here.

It seems the maximujm character limit allowed when using the extractstring() function is 254. No matter parameter I use for the second argument in the extractstring() function, the len(x) is always 254.

I'm going to try and get creative by splitting into sections that are <= 254 and then rejoining them.

Thanks again for all your help and patience!

 

[lbass]

I just tested this, and found a len of 508 for extractstring in CR 2008 with the Xtreme database.

You could also use substrings in SQL expressions to return multiple segments of the memo field.

-LB

 

[cuttyfunk81]

LB -

Thanks for testing it. I just did it again and I am still getting the same thing: 254 characters. I'm on Crystal 2008 and connecting through ODBC to a Microsoft SQL server.