Conditional Text Colour Calculation

[Arcanoi]

I currently have the calculation field:

Total Balance Remaining Calculation = Case ( Total Balance Remaining > 0 ; TextColor ( Total Balance Remaining ; RGB ( 250 ; 0 ; 0 ) ) ; Total Balance Remaining = 0 ; TextColor( Total Balance Remaining ; RGB ( 0 ; 0 ; 0 ) ) )

Where the Field Total Balance Remaining is a Calculation Field with the formula:

Total Amount Owed - Total Amount Paid

what am I doing wrong to make the text change colour?

[RalphL]

You might try:

Total Balance Remaining Calculation = Case ( Total Balance Remaining > 0 ; TextColor ( Total Balance Remaining ; RGB ( 250 ; 0 ; 0 ) ) ; TextColor( Total Balance Remaining ; RGB ( 0 ; 0 ; 0 ) ) )

Or forget about this calculation and format the number to print red when negative. Go to layout mode select number field then from the Format Menu choose number. Format as decimal then under negative select use color and set the color.

[Arcanoi]

Under normal circumstances I would have it print red when negative but I actually want it to do the reverse and print red when positive be black when zero and have green with (....) when negative to show that we ow them money.

[RalphL]

I did some experimenting. If the calculaed color field is formated as a decimal the color remains black.

[Arcanoi]

well thats annoying

[RalphL]

Was that the problem?

[RalphL]

Try this text calculataion:

Case ( Balance > 0; TextColor ( If ( Position ( Balance ; "." ; 1 ; 1 ) ; Left ( Balance & "00"; Position ( Balance ; "." ; 1 ; 1 ) + 2) ; Left ( Balance & ".00"; Position ( Balance & ".00"; "." ; 1 ; 1 ) + 2 )) ; RGB ( 255 ; 0 ; 0 ) );

Balance < 0 ; TextColor ( If ( Position ( Balance ; "." ; 1 ; 1 ) ; Left ( Balance & "00"; Position ( Balance ; "." ; 1 ; 1 ) + 2) ; Left ( Balance & ".00"; Position ( Balance & ".00"; "." ; 1 ; 1 ) + 2 )) ; RGB ( 0 ; 255 ; 0 ) ) ;

TextColor ( If ( Position ( Balance ; "." ; 1 ; 1 ) ; Left ( Balance & "00"; Position ( Balance ; "." ; 1 ; 1 ) + 2) ; Left ( Balance & ".00"; Position ( Balance & ".00"; "." ; 1 ; 1 ) + 2 )) ; RGB ( 0 ; 0 ; 0 ) ) )

Replace Balance with you field Total Balance Remaining.

[Arcanoi]

If I have cents that doesn't make it show up correctly

[RalphL]

It works in my sample file.