Report on Elements of an Array

[groasa]

In the Customer table there is a string field called Schedule of length 26. Each of the 26 places is either "N" or "Y".

For example:
{Customer.Schedule} = NNYNNNNYNNNNNYNNNNNNNNNNNN

Each element of the array corresponds with the alphabet, so {Customer.Schedule}[3] would be "C". I know that I can display each letter in this manner.

What I need to do is find out what letters each customer has (indicated in the field by each "Y&quot. Then group the customers by schedule letter.

I'm using ver 8.5.

Any help is greatly appreciated.
Glenda

 

[synapsevampire]

I'd suggest doing this on the database side, but this should help if you want to do it in CR.

Creata a formula with the following:

whileprintingrecords;
numbervar x; //A Counter
stringvar theletters :=""; // The resulting letters
for x := 1 to 26 do( //Start looping
if mid({MyTable.MyField},x) = 'Y' then // Check for a Y
theletters := theletters + chr(x+64) // Add the char
);

theletters // Return the letters found

That should work.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

I don't reall agree with SV's approach here since "whilePrintingRecords" will not allow the formula to be grouped on

Basically his formula is ok though....my version would be the following though

@GroupLetters

StringVar Schedule := {Customer.Schedule};
StringVar result:= "";
numberVar Icounter;

for icounter := 1 to 26 do
(
if Schedule[icounter] = "Y" then
result := result + chr(64 + icounter);
);

result;


Now you can Group on this formula.

Jim Broadbent

 

[synapsevampire]

Thanks, Jim, I skipped over the grouping requirement.

I think that the only difference in yours is that you removed the whileprintingrecords to allow for grouping and added an unrequired variable, right?

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[groasa]

Thanks to both of you for your help.

Jim's version gets me much closer to what I'm aiming for.

Now, I need to figure out how to group by the individual letters, if this is even possible.

Right now the customer in the example above would appear in the group "CHN". What I need is for him to appear in group "C" then in group "H" then again in group "N".

What do you think? Is this something that I won't be able to do in Crystal?

Glenda

 

[lyanch]

You can't get the same detail line to show in more than one place in crystal. You can do this with a union statement, but a stored proc would probably be better.

something along the lines of:

while i < (number of slots)
select *, i
into #temp
from table
where Substring(i,1,stringfield) = 'Y'
next i

select * from #temp

That is really pseudo code off the top of my head so don't copy that and expect it to work

Lisa