Formula Problem (works in one but not another??) 1

[AMS100]

I have the following formula, that displays the category if the product does not equal one of 4 Productkeys (354,190,532,430), otherwise it generates a 4 character code by applying a set of rules to the sale, here is the formula :

StringVar Korner := If {DATAPROD.REFPRODKEY} <> [354,190,532,430] then
{@KCat};

StringVar FirstChar := if (Count ({DATAPROD.PRODPKEY}, {DATASALES.SALEPKEY})) >1 then &quot;B&quot; else &quot;A&quot;;

StringVar SecChar := if (Count ({DATAPROD.PRODPKEY}, {DATASALES.SALEPKEY})) =1 then &quot;N&quot; else
if {DATAPROD.REFPRODKEY} = 354 then &quot;C&quot; else
if {DATAPROD.REFPRODKEY} = 190 then &quot;E&quot;
else &quot;B&quot;;

StringVar ThirdChar := if (Count ({DATAPROD.PRODPKEY}, {DATASALES.SALEPKEY})) =1 then &quot;N&quot; else
if {DATAPROD.REFPRODKEY} = 532 then &quot;G&quot; else &quot;N&quot;;

StringVar FourthChar := if (Count ({DATAPROD.PRODPKEY}, {DATASALE.SALEPKEY})) = 1 then &quot;N&quot; else
if {DATAPROD.REFPRODKEY} = 430 then &quot;P&quot; else &quot;N&quot;;

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

As mentioned, this works fine in one report but gives an error 'the summary/running total field could not be created'. I've re-written this formula step by step - the first part works fine (StringVar Korner := If {DATAPROD.REFPRODKEY} <> [354,190,532,430] then
{@KCat} the second part (StringVar FirstChar) is when it falls over with the above error. I'm no programmer (despite what tek-tips thinks) so it’s a small miracle the code works in one report! It’s killing me as to why its not working in another. Thanks

 

[Naith]

Confirm in your failing report that you:

(a) have a group on {DATASALES.SALEPKEY}
(b) you are not incorporating an evaluation time function, like 'WhilePrintingRecords' in a formula which is being used for summary or running total purposes.

Naith

 

[AMS100]

Brilliant!, thanks very much. I'd got to the point where I was just going in circles - needed some outside input. Thanks again

 

[Ngolem]

I don't like the structure of your formula....your variable declarations are far too complex...I will redo it and point out some potential problems

StringVar Korner ;
StringVar FirstChar ;
StringVar SecChar ;
StringVar ThirdChar;
StringVar FourthChar ;

// You don't use the value of &quot;Korner&quot; anywhere else in your formula....also there is no default value if the If-Then is false
If {DATAPROD.REFPRODKEY} <> [354,190,532,430] then
Korner := {@KCat};

if (Count ({DATAPROD.PRODPKEY}, {DATASALES.SALEPKEY})) >1 then
FirstChar := &quot;B&quot;
else
FirstChar := &quot;A&quot;;

if (Count ({DATAPROD.PRODPKEY}, {DATASALES.SALEPKEY})) = 1 then
SecChar := &quot;N&quot;
else if {DATAPROD.REFPRODKEY} = 354 then
SecChar := &quot;C&quot;
else if {DATAPROD.REFPRODKEY} = 190 then
SecChar := &quot;E&quot;
else
SecChar := &quot;B&quot; ;

if (Count ({DATAPROD.PRODPKEY}, {DATASALES.SALEPKEY})) =1 then
ThirdChar := &quot;N&quot;
else if {DATAPROD.REFPRODKEY} = 532 then
ThirdChar := &quot;G&quot;
else
ThirdChar := &quot;N&quot;;

if (Count ({DATAPROD.PRODPKEY}, {DATASALE.SALEPKEY})) = 1 then
FourthChar := &quot;N&quot;
else if {DATAPROD.REFPRODKEY} = 430 then
FourthChar := &quot;P&quot;
else
FourthChar := &quot;N&quot;;

//you will get an error here since all of your variables are defined as strings (I assume that the formula {@KCat} is a string from the first formula)...ToText works on converting numbers to strings.

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

As far as the formula failing in the second report goes....make sure that there is No Whileprintingrecords used since this formula uses summary functions this also goes for {@KCat}.

Make sure that the data is grouped on {DATASALE.SALEPKEY}

Also you can test the formula by running just one section of the formula at a time to find out the bad part.

Good luck Jim Broadbent

 

[AMS100]

Thanks for the re-written code, I've tried it out and it all appears to be fine. As you probably guessed I'm not a programmer (despite what tek-tips thinks!) so any help is much appreciated.