Spliting and Categorize a Field 2 times; using a Formula?

[Guest_imported]

I am new to Crystal Reports, I am a Lotus Developer who is using an SQL Driver to pull data from Notes into Crystal Reports so that I can create some fancy reports. The main problem is I have never worked with Crystal Reports, but from what I've seen it is a powerful tool and I'm sure I can split a field 3 times but I'm not sure how to do this.
I am using Crystal Reports 8. I have a field Category_Division.9, which has the following data (this is how it is displayed in Crystal Reports) :
ex. 01 Appliance/02 Needs Repair;02 Carpentry/05 Squeak in floor;10 Brick/01 Broken;21 Drywall/02 Wall Cracks; 01 Appliance/01 Install.

I would like to split and categorize the data 2 times:

1. I would to split after the ";", so that I can show everything after the ";" on a separate line.
ex. 01 Appliance/02 Needs Repair
01 Appliance/01 Install.
02 Carpentry/05 Squeak in floor
10 Brick/01 Broken

2. I would then like to split and Categorize the data above after the "/", so that I can display everything categorized By everything before the "/".
ex. 01 Appliance
01 Install
02 Needs Repair

Really what I am trying to accomplish is Step #2, I'm not sure if I can do this in 1 step versus 2!!


Please Help, I am just getting familiar with this report writing tool and don't know just how to accomplish this task.
Thanks in Advance for your help

 

[synapsevampire]

I'm pressed for time this morning, but look at the split function, it will allow you to create an array based on a delimiter, which might help you cheat this.

split(Category_Division.9,";&quot will give you three elements in your array.

Now you can strip everything beyond the / using an instr function to find it.

Someone might flesh this out for you shortly, if not, I'll do so later today.

Keep in mind that you have a 254 char limit with Crystal formulas, so if you're going to exceed that with this field, you'll have to workaround that.

OR

You can also just use a loop and parse this into a variable (while shortening the string in another variable that you're searching against) by looping until you don't get a > 0 with instr.

whileprintingrecords;
stringvar srchStr := Category_Division.9;
stringvar SrchForStr := ';';
stringvar ReturnStr :='';

for x = 1 to 100 (do // 100 is a bad number because you can exceed 254 chars easily, this was just a quick cheat
if instr(srchStr,SrchForStr) > 0
ReturnStr := left(srchStr,instr(srchStr,SrchForStr)-1) +chr(13);
// You can either strip out everything inluding
// and beyond the '/' here, or do it above, it'll get a
//little hairy to write up there

srchStr := mid(srchStr,instr(srchStr,SrchForStr)+1);
else
x:= 101;

ReturnStr
)
This is close, I didn't test it, I just typed out the theory.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

Ok...I'll take a swing at it.

1. none of the records processed can have a field exceeding 254 characters.

2. are you looking to correlate data from one record to another?...hope not

3. you want the values after the "/" sorted by number.

I would use an array for this and do it all in one formula


@breakout (placed in the detail section )

whileprintingrecords;
//FirstPass is the values between the ";" delimiter
//make the array 50% more than you think necessary
stringvar array FirstPass := ["","","","",......""];

numbervar m; // a "for-next" utility number
numbervar k; // a "for-next" utility number
numberVar icount := 0; //an array pointer
numberVar StartPos := 1;
numberVar EndPos := 0;
stringVar Temp;
stringVar SortTemp;
stringVar result := "";


//pass one breakdown
EndPos := instr(StartPos,{Category_Division.9},";"

while EndPos <> 0 do
(
Temp := mid({Category_Division.9},StartPos, EndPos -1);
icount := icount + 1;
//insert into the array in a sorted manner
for m := 1 to icount do
(
if FirstPass[m] = &quot;&quot; then
(
FirstPass[m] := Temp;
exit for;
);
if Temp <= FirstPass[m] then
(
for k = m to ubound(FirstPass) do
(
SortTemp := FirstPass[k];
FirstPass[k] := Temp;
Temp := SortTemp;
if Temp = &quot;&quot; then exit For;
);
exit For;
);
);

StartPos = EndPos + 1;
EndPos := instr(StartPos,{Category_Division.9},&quot;;&quot;
);

//cleanup from the above
icount := icount + 1;
Temp := mid({Category_Division.9},
StartPos,length({Category_Division.9}));
//insert into the array in a sorted manner
for m := 1 to icount do
(
if FirstPass[m] = &quot;&quot; then
(
FirstPass[m] := Temp;
exit for;
);
if Temp <= FirstPass[m] then
(
for k := m to ubound(FirstPass) do
(
SortTemp := FirstPass[k];
FirstPass[k] := Temp;
Temp := SortTemp;
if Temp = &quot;&quot; then exit For;
);
exit For;
);
);

//now most of the work is done this First Pass array is
//totally sorted so now we just format the result string

temp := &quot;&quot;;
for m := 1 to icount do
(
startPos := instr(FirstPass[m],&quot;/&quot;
if temp <> left(FirstPass[m],startPos -1) then
(
temp := left(FirstPass[m],startPos -1);
Result := Result + temp + Chr(13) + Chr(10)
+ &quot; &quot; +
Right(FirstPass[m],length(FirstPass[m])-
startPos) + Chr(13) + Chr(10);
)
else
Result := Result + &quot; &quot; +
Right(FirstPass[m],length(FirstPass[m])-
startPos) + Chr(13) + Chr(10);
);

Result;

Make sure the field has &quot;Can Grow&quot; enabled.

Hope this works...unfortunately I cannot test this since I only have CR7...but I think it will work Jim Broadbent

 

[synapsevampire]

Try:

whileprintingrecords;
stringvar srchStr := Category_Division.9;
stringvar ReturnStr :='';

// Loop through 100 iterations - too many, just for demo
for x = 1 to 100 (do
if instr(srchStr,';') > 0

// The following rips out everything including and past
// the ;
ReturnStr := ReturnStr + left(srchStr,instr(srchStr,SrchForStr)-1);
// Omitted the concatentation above before, need to
// keep the previous string

// The following rips out everything including and past
// the / character
ReturnStr := left(ReturnStr, instr(srchStr,'/')-1) +chr(13);

// This reduces the variable holding the
// Category_Division.9 field

srchStr := mid(srchStr,instr(srchStr,SrchForStr)+1);
else
x:= 101;
)

ReturnStr

That's fairly close if not it. You can probably work it out from there. Note there's no data validation, so you may have some work yet.

If you're going to address it with arrays, you might consider the join and split functions.

I commute 3-4 hours each Monday morning before I go at it so I'm somewhat worthless at this time of the day, so do your due diligence on error and theory checking here.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com