Jump to content

Color coding calc field according to result


Zerosum
 Share

This topic is 5665 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • Newbies

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

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

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? :wink2:

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

Edited by Guest
Added update
Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

  • Newbies

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!

Link to comment
Share on other sites

This topic is 5665 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.