Font color change formula resets to default color

[ceej]

Hi,

I created a custom formula which compares two numbers. If the difference between the two numbers is great enough, than I change the color of a field. If not, the color remains the same.

On the field font property I execute the formula several times. My problem is that Crystal will only remember what happened on the last execution.

For example, the second execution may have changed the color to green. The third formula did not change the font to blue, but instead changed the font back to the default black.

Here is my custom formula:

Function FontTst(Value1 as Number, Value2 as Number, Pct as Number, Clr as Number) as Number

Dim Result as Number
If Value1 = 0 or Value2 = 0 or (Value1-Value2) = 0 then
FontTst = crBlack 'Prevent division by zero
Else

Result = (Value1-Value2)/Value2*100

Select Case Sgn(Pct)

Case -1
If Result < Pct then FontTst = Clr
Case 1
If Result > Pct then FontTst = Clr
End Select

End If

End Function

Here is the Font Color property:

formula = FontTst(NumberA,NumberB,Val({?Red}), crRed)
formula = FontTst(NumberA,NumberB,Val({?Green}), crGreen)
formula = FontTst(NumberA,NumberB,Val({?Blue}), crBlue)

Any thoughts?

Thanks!!!

 

[lbass]

I'm not sure how to do this in Basic, but try adding a default to the select case: default = FontTst. In Crystal syntax, it would be: default : FontTst

-LB

 

[ceej]

LB,

Thanks for the idea.

I realized that Crystal was not updating the font color until the end of the code.

So I added a variable to store the current color. I then passed the current color as a parameter into the formula.

Ceej

Custom Formula logic:

Function FontTst(Value1 as Number, Value2 as Number, Pct as Number, DefClr as Number, Clr as Number) as Number

Dim Result as Number
If Value1 = 0 or Value2 = 0 or (Value1-Value2) = 0 then
FontTst = crBlack 'Prevent division by zero
Else

Result = (Value1-Value2)/Value2*100

Select Case Sgn(Pct)

Case -1
If Result < Pct then
FontTst = Clr
Else
FontTst = DefClr
End If
Case 1
If Result > Pct then
FontTst = Clr
Else
FontTst = DefClr
End If
End Select

End If

End Function

Field Font color code:

Dim CurColor as Number

CurColor = DefaultAttribute

CurColor = FontTst({#Prod_CurQtyShip},{#Prod_PriorQtyShip},Val({?Red}),CurColor , crRed)
CurColor = FontTst({#Prod_CurQtyShip},{#Prod_PriorQtyShip},Val({?Green}), CurColor, crGreen)
CurColor = FontTst({#Prod_CurQtyShip},{#Prod_PriorQtyShip},Val({?Blue}), CurColor, crBlue)

formula = CurColor