Code generation per 'Sale' group?

[AMS100]

I have the following code in one of my formula fields

StringVar KeyValue ;
StringVar FirstChar ;
StringVar SecChar ;
StringVar ThirdChar;
StringVar FourthChar ;

If {Product.Itemkey} <> [354,190,532,430] then
KeyValue := {@KeyValue};

The idea of this code is to generate a code dependant on the items (identified by PRODUCT.ITEMKEY) sold within in one 'Sale'(identified by SALE.SALEKEY) to show a pattern of what is bought with what. If the customer didn't buy one of the following product keys (354, 190, 532, 430) then the report displays the result of another formula field (@KeyValue) otherwise I need to build up a 4 character code using the following IF statements dependant on the product key :-

if (Count ({Product.productkey}, {Sale.Salekey})) >1 then
FirstChar := &quot;B&quot;
else
FirstChar := &quot;A&quot;;
if (Count ({Product.productkey}, {Sale.Salekey})) = 1 then
SecChar := &quot;N&quot;
else if {Product.ItemKEY} = 354 then
SecChar := &quot;C&quot;
else if {Product.ItemKEY} = 190 then
SecChar := &quot;E&quot;
else
SecChar := &quot;B&quot; ;
if (Count ({Product.productkey}, {Sale.Salekey})) =1 then
ThirdChar := &quot;N&quot;
else if {Product.ItemKEY} = 532 then
ThirdChar := &quot;G&quot;
else
ThirdChar := &quot;N&quot;;
if (Count ({Product.productkey}, {Sale.Salekey})) = 1 then
FourthChar := &quot;N&quot;
else if {Product.itemKEY} = 430 then
FourthChar := &quot;P&quot;
else
FourthChar := &quot;N&quot;;

Then displayed using the following :-

If {Product.itemKEY} <> [354,190,532,430] then
ToText ({@KeyValue})
else
'&quot;' + ToText (FirstChar)+totext(SecChar)+totext(ThirdChar)+totext(FourthChar) + '&quot;' ;

This only works per item sold it needs to calculate the code when all items sold within that one sale is taken into account. I'm guessing I need to somehow tell crystal to read in all product keys (for each Sale (I'm already grouping by Sale.Salekey)) then generate the code.
I've been playing with this for a while and need some guidance. As always any help is much appreciated.

 

[synapsevampire]

Not much for data structures offered here, but here's some theory to thunk on:

Create an array in the details of each group to capture the product keys.

whileprintingrecords;
stringvar array MyArray;

Now loop through the array and check for it's existence within your lookup array.

Place something like this in the group footer, a slight tweak should get you there:

whileprintingrecords;
stringvar array MyArray;
booleanvar UseKeyValue;
numbervar x;
for x := 1 to ubound(MyArray) do(
if ltrim(rtrim(MyArray[x])) in [354,190,532,430] then
UseKeyValue := True;
)

Insert a group footer below (right click the group footer and select insert section below) and place something like the following in a formula:

whileprintingrecords;
booleanvar UseKeyValue;
If UseKeyValue then
ToText ({@KeyValue})
else
'&quot;' + ToText (FirstChar)+totext(SecChar)+totext(ThirdChar)+totext(FourthChar) + '&quot;'

Something like that...

-k

 

[AMS100]

Sorry i'm really not to hot on arrays... I think i've got the theory but am having trouble translating it to practice. how do I create the array?
Is it a seperate formula field to be placed within the details section? I'm guessing it is,so im entering
Whileprintingrecords;
stringvar array MyArray;
into a formula field (Array), when i test this I get an error saying the result cannot be an array. I need to tell the array what to 'array' i suppose, in this case it needs to read in all Product.itemKEY per group. i tried the obvious - 3rd line read
Myarray := Product.itemKEY but this errors too.

A bit of back ground :-
i have a SALE table which records each 'sale' and links to the PRODUCT table (one to many). each SALE will have one PRODUCT link/data set per item sold. The PRODUCT table records the ITEMKEY which can be resolved against another table with the item details.

 

[AMS100]

Ok, I've done a bit of research and found where I was going wrong with my array (added &quot;&quot;; to the end of the code) so my Array initiation formula reads
WhilePrintingrecords;
NumberVar array MyArray := [{Product.itemKEY}];
&quot;&quot;;
(this is placed in the details section)
I'm now on the second part of synapsevampire reply, this reads
whileprintingrecords;
numbervar array MyArray;
booleanvar UseKeyValue;
numbervar x;
for x := 1 to ubound(MyArray) do(
(MyArray[x]) in [354,190,532,430] then
UseKeyValue := True;
)
(this is placed in the group footer) This returns 'True' for every group, regardless of the appearance of the 4 item keys ([354,190,532,430])
Thanks