Taking out the trash.

[straud13]

I have a memo field that contains a bunch of text that has an obnoxious header and footer that vary in length for each entry.

EG

This case was resolved by Darren Louie on 7/22/04 12:15 PM as follows: 1. Disabled Orderdesk. No more ODBC error.
2. Deleted AMGR. Settings now saving.
3. Looks like he created a new address book. Tried pointing to correct folder but got error while converting. Deleted the non .MAX files and now trying to convert. No error was given. He is still going through the conversion process and will call back if still having problems.

Case description:
Getting ODBC error when opening address book. Tries to save settings and not saving. Tried using existing data with Max8 but address book is empty.

-----------------------------------------------------------
Case number: HQ-27535
Case subject: ODBC error, settings not saving, and can't find one of his address books
Company: Southern Marketing Associates
Contact: Bales, Terry
Assigned to: Darren Louie
Case owner: Darren Louie
Case product: Maximizer 8
Case category: General

I want to extract all the text in between

"This case was resolved by Darren Louie on 7/22/04 12:15 PM as follows:"

and

"Case description:
Getting ODBC error when opening address book. Tries to save settings and not saving. Tried using existing data with Max8 but address book is empty.

-----------------------------------------------------------
Case number: HQ-27535
Case subject: ODBC error, settings not saving, and can't find one of his address books
Company: Southern Marketing Associates
Contact: Bales, Terry
Assigned to: Darren Louie
Case owner: Darren Louie
Case product: Maximizer 8
Case category: General"

I suspect from reading some of the other string manipulation posts that I will have to capture the length of the whole field the length of the header and the lenght of the footer then do a mid with those numbers which is fine, but how do i do a reverse count to the start of the first occurence of the word "Case Description:"

Keep in mind that there may be colons in the Case description as well.

Thanks

 

[synapsevampire]

You need something consistent to key off of.

Since you say that they might place :'s anywhere, then perhaps they are consistent with the text preceding it, such as:

whileprintingrecords;
stringvar array TheText := split({table.field,":");
stringvar output:="";
numbervar counter;
booleanvar StartSaving:=False;
for Counter := 1 to ubound(TheText) do(
if right(TheText[Counter],11) = "as follows:" then
StartSaving:=True
else
if left(TheText[Counter],17) = "Case description:" then
StartSaving:=False;
;
(
if StartSaving then
output:=output+TheText[Counter]
);
)
output

Something liek that, I can't test right now.

Also note that you might do this on the database, and you might experience problems with this solution depending upon your Crystal version. Not sharing this type of information wastes time.

-k

 

[straud13]

Sorry Crystal Version 9

 

[synapsevampire]

The above should get you close on CR 9.

Doing so on the databse would require the database programming permissions and skill, and knowing the type of database, so I suggest you try the above.

-k

 

[straud13]

I couldn't get the above example to work, however this one does work

whileprintingrecords;
trim(split((split({AMGR_Notes.TextCol},"as follows:")[2]),"Case description:")[1])

But eventually returns this error:

A subscript must be between 1 and the size of the array.

Ideas?

 

[lbass]

Try:

mid({table.string},instr({table.string},":")+1,
len({table.string})-(len({table.string)-
instr({table.string},"Case description:")+1)-
instr({table.string},":"))

-LB

 

[straud13]

Thanks everyone it works now with the following formula:

trim(split((split({AMGR_Notes.TextCol},"as follows:")[2]),"Case description:")[1])

 

[denverpete]

straud13,

I am doing something similar as what you are working on. I tried your formula but got the first error that you mentioned. Can you provide the formula again in the format that is working for you.

Thanks,

Pete

 

[straud13]

This is the exact Copied formula from the working report

whileprintingrecords;
trim(split((split({AMGR_Notes.TextCol},"as follows:")[2]),"Case description:")[1])

 

[synapsevampire]

Denverpete: Try a new post with technical information:

Crystal version
Database/connectivity used
Example data
Expected output

-k

 

[denverpete]

I am using Crystal 11. I am pulling data from Maximizer SQL CRM with an ODBC connection. I am new to Crystal and trying to come up with reports that management wants is a real killer.

Below is the data in the text field.

Phone Call: Brissette, Pete
Number Called: 1 303 576-6610 [315]
Phone Description: Main
Subject: New Registration Call
Result: Completed
Duration: 00:00:07

What I want to do is pull data based on the subject and on the result information in the text field.

I though creating a formula field for each thing would work. Such as:
if ("New Reg" in {AMGR_Notes.TextCol}) = true then "New Registration Call"

And then view and sort on the results. That seems to work ok, however When I want to do a summary count of "New Registration Call" it counts against the original DB field and I cant get good totals when I have multiples of these formula fields in the report.

Could I possibly use a cross tab report?

I still get the error A subscript must be between 1 and the size of the array on the formula that was posted above.

Pete

 

[lbass]

If you insert a count on a formula, the result will be the count of each instance of the formula, regardless of the formula result. To count a specific formula result, create a second formula:

if {@yourformula} = "New Registration Call" then 1

Then insert a summary (SUM, not count) on this formula.

-LB

 

[denverpete]

Hey LB, thanks!!

That worked just great. In fact I found a little better way to do it so I wouldnt have to create 2 formula fields for each item.

It looks like this.

For "New Registraton Calls"

if ("New Reg" in {AMGR_Notes.TextCol}) = true then 1

For "Quote Follow Up Calls"

if ("Subject: Follow-up on Quote" in {AMGR_Notes.TextCol}) = true then 1

This works great. I just change the text I am looking for in the note and can create all the info that I need.

Thanks again you guys were a great help.

Pete