Parsing Memo Field/CR 9 1

[mrichey]

Help! At wits end trying to parse this without using code. I've tried LEFT, INSTR, INSTRREV, etc. but am getting nowwhere.

Using Crystal 9
Need to break out all data between {[ and ]} and then
break out the all between the next {[ and ]} and so on.
In the following example that would return 2 groups of data and could stop before the end -- , {[, , , {}, {}, ]}, {[, , , {}, {}, ]}}

Any help will be greatly appreciated as I've spend the better part of today trying to figure this one out. Also, what would be a good book to learn crystal/basic coding? Thanks!

{{["rca", "open", "PROD REVIEW", {}, {"06/29/05 15:38 DEAN SKINNER", "Shannon - SA is tracking this ticket for ", "completion of RCA cause type and dowtime end. ", " Thanks! Dean Skinner", "06/30/05 10:51 CHRIS ELMORE", "Shannon, John, please update the RCA cause ", "type and downtime end in this ticket.", "Thanks,", "Chris Elmore"}, "SHANNON SANDVEN"]}, {["downtime end", "open", "PROD REVIEW", {}, {}, "SHANNON SANDVEN"]}, {["validate product", "closed", "PROD REVIEW", {}, {"06/23/05 09:39 CHRIS ELMORE", "Chris Elmore spoke to Steve Fernandez and the ", " Apropo executable is on the jaxcorp2 server."}, "SHANNON SANDVEN"]}, {[, , , {}, {}, ]}, {[, , , {}, {}, ]}}

 

[synapsevampire]

Most use Crystal syntax, not Crystl Basic, so I'd suggest going with it as most code uses that as examples.

It's interesting that you show what needs to be broken out, but not wht you want it to look like afterwards.

I would also suggest finding something with a very porous coating on it, with agoodly amount of weight, and dropping it out the window, after the dba, so as to assure that she/he never comes back and allows for such assinine structures in the database. It's just plain lazy to store meaningful data in this fashion.

Anyway, it's probably not *that* difficult.

Here's an example using your string, you may need some other replace statements based on your rules, for instance a chr(13) when you want a carriage return, and there may be other changes that you want.

The resulting array can be used within other formulas to identify each section, or you can display all of the text, again you gave noi indication of what you wanted out of there so I tried to cover the bases.

whileprintingrecords;
numbervar Counter:=1;
Stringvar Array OutPut1:= split('{{["rca", "open", "PROD REVIEW", {}, {"06/29/05 15:38 DEAN SKINNER", "Shannon - SA is tracking this ticket for ", "completion of RCA cause type and dowtime end. ", " Thanks! Dean Skinner", "06/30/05 10:51 CHRIS ELMORE", "Shannon, John, please update the RCA cause ", "type and downtime end in this ticket.", "Thanks,", "Chris Elmore"}, "SHANNON SANDVEN"]}, {["downtime end", "open", "PROD REVIEW", {}, {}, "SHANNON SANDVEN"]}, {["validate product", "closed", "PROD REVIEW", {}, {"06/23/05 09:39 CHRIS ELMORE", "Chris Elmore spoke to Steve Fernandez and the ", " Apropo executable is on the jaxcorp2 server."}, "SHANNON SANDVEN"]}, {[, , , {}, {}, ]}, {[, , , {}, {}, ]}}',"]");
Stringvar OutputString:="";
For counter := 1 to ubound(Output1) do(
OutPut1[counter]:=replace(replace(replace(replace(replace(replace(OutPut1[counter],","," "),'"'," "),"["," "),"{"," "),"}"," ")," "," ");
OutputString:=OutputString+Output1[counter]+" ";
);
Outputstring

To display the first section of the text you might use:

whileprintingrecords;
Stringvar Array OutPut1;
Output1[1]

Where 1 would indicate the first section ending in a ] bracket.

Hoepfully this gives you some ideas, and you can replace the text with your table.field

-k

 

[mrichey]

Sheez, like the PGA adverttisement says --- you guys are good!

That works beautifully. It's like magic. Now how do output the other subsequent sections? You're right on with what I'm trying to do. I'll use the chr(13) later to enter the hard returns.

We were just talking about what a terrible database this is. It's Perigrine Service Center P4. Not good. The only db that gives us fits, probably because it isn't a true relational database.

Seriously, any books that are crystal coding specific?

Thanks so much!!!

 

[synapsevampire]

The output should be doing all of the sections in the first formula I posted, so I'm not sure what you mean by the following sections.

In essence it parses the text into an array, and then replaces it in the array with the "replaced" version, and then displays everything.

The second example shows hot to output just the first element in the array, if in fact you need to just show one part of the text.

-k

 

[synapsevampire]

As for training, I would suggest the Crystal forums here as the best training available, book or otherwise.

There are interesting FAqs, etc.

For personal training or classes I tend to suggest

http://www.kenhamady.com,

he also has some very modestly priced training materials.

-k

 

[mrichey]

Sysvampire, hope I'm not pushing my luck with you. One last question. Using the formula you provided me, this is the total output for one of the records:

rca open PROD REVIEW 06/29/05 15:38 DEAN SKINNER Shannon - SA is tracking this ticket for completion of RCA cause type and dowtime end. Thanks! Dean Skinner 06/30/05 10:51 CHRIS ELMORE Shannon John please update the RCA cause type and downtime end in this ticket. Thanks Chris Elmore SHANNON SANDVEN downtime end open PROD REVIEW SHANNON SANDVEN validate product closed PROD REVIEW 06/23/05 09:39 CHRIS ELMORE Chris Elmore spoke to Steve Fernandez and the Apropo executable is on the jaxcorp2 server. SHANNON SANDVEN

Below is the output from output1(1)
1. How would I get these vertical?
2. Also, when I create an output1(4) I get an error on refresh -- a subscript must be between 1 and the size of the array.

rca open PROD REVIEW 06/29/05 15:38 DEAN SKINNER Shannon - SA is tracking this ticket for completion of RCA cause type and dowtime end. Thanks! Dean Skinner 06/30/05 10:51 CHRIS ELMORE Shannon John please update the RCA cause type and downtime end in this ticket. Thanks Chris Elmore SHANNON SANDVEN

 

[synapsevampire]

You didn't use my example, the output for each section would be:

output1[1]
not
output1(1)

To create a carriage return bewteen and output all of them, try:

whileprintingrecords;
numbervar Counter:=1;
Stringvar Array OutPut1:= split('{{["rca", "open", "PROD REVIEW", {}, {"06/29/05 15:38 DEAN SKINNER", "Shannon - SA is tracking this ticket for ", "completion of RCA cause type and dowtime end. ", " Thanks! Dean Skinner", "06/30/05 10:51 CHRIS ELMORE", "Shannon, John, please update the RCA cause ", "type and downtime end in this ticket.", "Thanks,", "Chris Elmore"}, "SHANNON SANDVEN"]}, {["downtime end", "open", "PROD REVIEW", {}, {}, "SHANNON SANDVEN"]}, {["validate product", "closed", "PROD REVIEW", {}, {"06/23/05 09:39 CHRIS ELMORE", "Chris Elmore spoke to Steve Fernandez and the ", " Apropo executable is on the jaxcorp2 server."}, "SHANNON SANDVEN"]}, {[, , , {}, {}, ]}, {[, , , {}, {}, ]}}',"]");
Stringvar OutputString:="";
For counter := 1 to ubound(Output1) do(
OutPut1[counter]:=replace(replace(replace(replace(replace(replace(OutPut1[counter],","," "),'"'," "),"["," "),"{"," "),"}"," ")," "," ");
OutputString:=OutputString+Output1[counter]+chr(13);
);
Outputstring

-k

 

[mrichey]

Typo, my formula was brackets, but put parantheses in the email. My bad. Anyway,

The new split formula with the chr(13) added gets this:
{{["component", "open", "PROD REVIEW", {}, {}, "CHASON GRANT"]}, {["downtime start/downtime end", "closed", "PROD REVIEW", {}, {}, "CHASON GRANT"]}, {["additional client numbers", "open", "PROD REVIEW", {}, {}, "CHASON GRANT"]}, {["RCA cause type", "open", "PROD REVIEW", {}, {}, "CHASON GRANT"]}, {[, , , {}, {}, ]}, {[, , , {}, {}, ]}}

to this:
component open PROD REVIEW CHASON GRANT
downtime start/downtime end closed PROD REVIEW CHASON GRANT
additional client numbers open PROD REVIEW CHASON GRANT
RCA cause type open PROD REVIEW CHASON GRANT

Would like to see this but can live without it for now:
component
open
PROD REVIEW
CHASON GRANT

next group

My output1[1] for 1,2,&3 are shown below. Obviously Chason Grant is on the same line, this box is just small.

component open PROD REVIEW CHASON GRANT
downtime start/downtime end closed PROD REVIEW CHASON GRANT
additional client numbers open PROD REVIEW CHASON GRANT

When I add the output1[4] that should pick up the last line of RCA cause type open etc. I get the 'subscript must be between 1 and the size of the array' error.

Can't tell you how much I appreciate your help on this.

MR

 

[synapsevampire]

A shame I didn't understand that in the beginning.

Hence showing example data and expected output is the best way to relate requirements.

As part of the replace formula, use an inner replace of replace({table.field},'",',chr(13))

This will replace the end quotes and comma with a carriage return.

You'll want to have the other replaces as well, but on the outside of this one.

-k

 

[mrichey]

Thanks synapsevampire!!
Apologies for not getting my requirements to you at the outset. I'll be more clear in future posts. Thanks for your patience and help. It has made a huge difference.

Sincerely,

MR

 

[synapsevampire]

I should have pushed for example data and the expected output, I generally do.

Glad that it worked out.

-k