Need to get summaries on variable data.

[meryls]

I am processing work order records that have the following fields:

name, address, deliveryDate...Unit1Type, Unit1Count, Unit2Type, Unit2Count, Unit3Type, Unit3Count, Unit4Type, Unit4Count...

The UnitType fields can contain any text string and represent the types of units delivered to the customer. The database designers have created this setup so that a given customer can have up-to 4 unit types delivered in a single work order. The UnitCount fields show how many of each type was delivered.

In printing the work order report, I have been asked to create a summary across all the work orders that sums the count of each type of unit in the report. The only way I have of knowing the types of units is by keeping counts as I process the work order records.

Can someone give me help on the code I would need for the formulas? I haven't used arrays in Crystal and suspect that is the only solution. I would need to create the array and check if the unit type was already in it, and either increment or create a new element.

Thanks very much!
Meryl

 

[lbass]

The following worked when I tested it. Create three formulas:

//{@reset} to be placed in the report header:
whileprintingrecords;
numbervar array y:= 0;
redim preserve y[1000]; //change the 1000 to some number greater
//than the maximum number of y values
numbervar counter := 0;

//{@accum} to be placed in the details section:
whileprintingrecords;
stringvar x;
numbervar array y;
numbervar counter;
stringvar results := "";

if instr(x,{table.Unit1Type}) = 0 then
x := x + {table.Unit1Type}+", ";
if instr(x,{table.Unit2Type}) = 0 then
x := x + {table.Unit2Type}+", ";
if instr(x,{table.Unit3Type}) = 0 then
x := x + {table.Unit3Type}+", ";
if instr(x,{table.Unit4Type}) = 0 then
x := x + {table.Unit4Type}+", ";

for counter := 1 to ubound(split(x,", "))-1 do(

if split(x,", ")[counter] = {table.Unit1Type} then
y[counter] := y[counter] + {table.Unit1Count} else
if split(x,", ")[counter] = {table.Unit2Type} then
y[counter] := y[counter] + {table.Unit2Count} else
if split(x,", ")[counter] = {table.Unit3Type} then
y[counter] := y[counter] + {table.Unit3Count} else
if split(x,", ")[counter] = {table.Unit4Type} then
y[counter] := y[counter] + {table.Unit4Count} else
y[counter] := y[counter];
results := results + split(x,", ")[counter] +
": " +totext(y[counter],0,"") + chr(13));

//{@display} to be placed in the report footer:
whileprintingrecords;
stringvar results;

Right click on {@display}->format field->common->and check "Can Grow" so that you can see the entire results.

-LB

 

[meryls]

Thanks, lbass,
Thank you for (1) Helping me with this tricky problem
and (2) Making the formulas so complete and easy to use.

I have this working. I may try to create an array of strings to store the unit type strings. I will probably need more formatting flexibility. e.g., left or right justify the strings and counts separately.

Thanks again,
Meryl

 

[lbass]

Meryl--

To get the results aligned, change the last line of {@accum} to:

results := results + split(x,", ")[counter] + ":" +
space(25-(len(split(x,", ")[counter] +
totext(y[counter],0,"")))) +totext(y[counter],0,"") + chr(13));

And then select {@display}->format field->font->font and select "Courier"--a non-proportional font that allows the alignment to work.

You can change the "25" in the formula as long as it is a number higher than the maximum length of the type plus the count in your report.

-LB