Color coding calc field according to result

[Zerosum]

Is it possible to vary the color of the text value of a calculation field according to what the calculation returns?

I have constructed a database of commodity price movements. One field "Direction" returns "up" or "down". Is there some way to color "up" blue and "down" red? Of course if "up" and "down" had their own fields, I could easily do this in Layout, but that's not what I want. I also have various calculation fields returning the ANSI character ▲ or ▼. I would like to color code these as well.

It might be possible to accomplish this using container fields. However I understand that it is not possible to perform searches, or further calculations using the contents of container fields, so that rules them out.

Please note that I am new to FMP. I have managed to compose all the necessary calculation functions, but formatting problems are less intuitive. I notice a similar question from "The Dog", but I don't understand the answers.

One suggestion involves "stacking" one field on another. How does one do that? This is not necessary in FMP 8?

Another suggests setting up the calculation not as a calculation field but as a text field and choosing "auto-calc with replace." I am using FMP 8, but I can find no "with replace" option in the Define Database, text field set-up options window. Whenever I enter a calculation in a Text field Auto Enter/Calculated Value window, no values are generated in the desired field. What gives?

Any advise will be appreciated.

Thanks

[LaRetta]

Hi Zerosum,

Your auto-enter (Replace) means that, in the Auto-Enter tab - right below Calculated Value checkbox is another checkbox which says 'Do Not Replace Existing Value for Field.' This must be UNCHECKED for the results to happen the moment changes to the data happens.

Calculation to change field to blue for UP and red for DOWN would be something like:

Case (

Position ( Direction ; "Up" ; 1 ; 1 ) ; TextColor ( Direction ; RGB ( 0 ; 0 ; 255 ) ) ;

Position ( Direction ; "Down" ; 1 ; 1 ) ; TextColor ( Direction ; RGB ( 255 ; 0 ; 0 ) ) ;

Direction

)

Position() is more efficient than PatternCount(). And yes, this would be Auto-Enter (Replace). If those ansi characters are fonts in a standard field, they should change color as well. I have changed the color of wingding fonts using this technique so it should work the same way. Field stacking can sometimes STILL come in handy in vs. 8 but you wouldn't need to use it for this purpose. Versions prior to 7 don't have the text formatting functions so stacking was necessary.

LaRetta

[Zerosum]

Thanks LaRetta for your reply.

Your solution works fine - you even wrote the function for me. But now I have two fields yielding the same value: Direction in black and white, and Direction 2 in color. Hopefully, I can compose a function that combines the two steps

Direction = Case(Change > 0; "up";Change = 0;"unch"; Change < 0;"down")

and

Case (Position ( Direction ; "Up" ; 1 ; 1 ) ; TextColor ( Direction ; RGB ( 0 ; 0 ; 255 ) ) ;

Position ( Direction ; "Down" ; 1 ; 1 ) ; TextColor ( Direction ; RGB ( 255 ; 0 ; 0 ) ) ;

Direction)

into one so that the calculation is carried out and the resulting value colored thus eliminating the reference to the intermediate Direction field in your equation. I have ten or so of these fields I would like to color.

Or should I make the referenced Diretion field invisible; probably by just physically reducing the field width to 0? (Actually this is quite interesting as it enables all sorts of complex Data manipulation.)

Thanks again

[Zerosum]

I don't understand why one cannot simply append text color commands to the original calculation field

Case(Change > 0; "up";Change = 0;"unch"; Change < 0;"down")

Something like:

Case(Change > 0; "up";Change = 0;"unch"; Change < 0;"down"; Direction = "down"; TextColor("down";RGB(255;0;0); Direction = "up"; TextColor("up";RGB(0;0;255))

Probably the syntax is incorrect (and, in fact, the added info is ignored by FM) but surely there must be some common sense solution to a situation like this.

[LaRetta]

Well Zerosum, the calculation I gave you should be applied to your text field itself as an auto-enter calculation. I may be missing something here ... I admit I'm a bit perplexed by your explanation - but that's probably just me.

It sounds more like you are creating a straight calculation field (result of text) to hold the formula? Is this correct? Or am I still missing something here?

UPDATE: I should also clarify that the field 'Direction' referenced in the calculation is referencing itself.

Edited May 27, 2006 by Guest
Added update

[comment]

IIUC, the input is a calculation field called Change, whose result is currently a number. If so, the field Direction should also be a calculation field, with result set to text, and it should reference the Change field, rather than itself, e.g.:

Choose ( Sign ( Change ) + 1 ;

TextColor ("down" ; RGB ( 255 ; 0 ; 0 ) ) ;

"unch" ;

TextColor ("up" ; RGB ( 0 ; 200 ; 0 ) )

)

Another option would be to do this in the Change field itself, but then any further numeric calculations based on Change would become impossible (or at least cumbersome).

[LaRetta]

Alright, Michael. I do not see the word Change in this entire thread; nor do I see it referencing a number. Where are the numbers? :confused:

[Lee Smith]

The calculation has Change as the field. The intial post has Commodity Price in the description of the problem.

[LaRetta]

Thanks, Lee! I was re-reading the original post. The second post made no sense to me (obviously). :wink2:

[Zerosum]

Yes, "Direction" is a calculation field that returns a text value: "up" or "down". It is calculated by referencing another field "Change" that is also a calculation field that returns a number calculated by comparing the closing prices of the current and previous trading sessions.

Sorry, in future posts I will take greater care to be more precise .

Thank you comment. Your suggestion worked brilliantly!

I do appreciate all the advice. Great forum!