Coloring a range of numbers

[cmack]

I have no idea if this is possible but what I have been asked for is to color code the test scores of our students.

For example students that score between 1-500 would be red, 501-800 blue, 801-1000 green.

I have no idea how to go about this so if it is possible any help would be terrific.

[mr_vodka]

A lot easier to do in FM 7 and up, but still doable in 6.

You would have to create a calculation field for each criteria with "invisible" or clear background. Then put each field on top of one another.

Red calc: (with field font setting red)

Case( score >0 and score <= 500, score)

Blue calc: (with field font setting blue)

Case( score >500 and score <= 800, score )

Green calc: (with field font setting green)

Case( score >800 and score <= 1000, score )

Black calc: (possibly needed to handle anything over 1000 or less than 1, but most likely you would want to have a validation in the original "score" field if you want them to specifically enter a range.

Case( score <1 or score > 1000, score )

[MRH]

cmack,

If you want just a colour/color coding have a look at this example I just knocked up, quick & dirty.

Mark

Score.fp5.zip

[cmack]

I'm sorry I have filemaker 8 adv. I thought that was what I selected. Sorry about that. Is the process different?

I am going to look at the example now.

Thanks for the help.

Edited January 23, 2007 by Guest

[mr_vodka]

For FM 8 its easy. All you need is one calc.

Case( score < 1; score;

      score <= 500; TextColor ( score; RGB ( 255 ; 0 ; 0 ) );

      score <= 800; TextColor ( score; RGB ( 0 ; 0 ; 255 ) );

      score <= 1000; TextColor ( score; RGB ( 0 ; 255 ; 0 ) );

      score

     )

OR 



If you have a field validation setup (range between 1 -1000) on the score field to prevent anything not in that range, you can just have the calc be:

Case( score <= 500; TextColor ( score; RGB ( 255 ; 0 ; 0 ) );

      score <= 800; TextColor ( score; RGB ( 0 ; 0 ; 255 ) );

      score <= 1000; TextColor ( score; RGB ( 0 ; 255 ; 0 ) )

     )

Edited January 23, 2007 by Guest
Added calc if using field validation

[cmack]

Mr. Vodka,

Thank you very much that worked like a charm. I have one other question for you. How do you determine the colors? I saw the 255 and thought that was part of it. Or am I way off?

Just curious in case I have to expand the colors some day.

Thank you for you all your help.

[mr_vodka]

This value is based on R,G,B (Red, Green, blue) if you open your color pallette, when do you things such text color, fill color, line color, etc. You will see the values for whatever color you select in the bottom right corner.

Also you can take a look here for a reference.

RGB Chart

[MRH]

No Problem, re-done it in V8, incorporating both graphic & text options.

Used Global field so you can play around with text colours.

Hope this makes sense.

Mark

Score_in_Colour_Color.fp7.zip

[cmack]

Thank you both. MRH the example file helped me and Mr. Vodka your script did exactly what I needed. I appreciate you both taking the time to help me with this.

I have been using FM for a long time and have a good handle on the basics but when it comes to scripting and calculations I am afraid I can only the simplest of tasks. I guess this may have been simple for you both but it was giving me fits. So I thank you.

[mr_vodka]

BTW Mark,

Case (

InitialScore ≥ 0 and InitialScore ≤ 500 ; Colour_pixel [1] ;

InitialScore ≥ 501 and InitialScore ≤ 800 ; Colour_pixel [2] ;

InitialScore ≥ 801 and InitialScore ≤ 1000 ; Colour_pixel [3] ;

InitialScore )

You can shorten your Case statement to just.

Case (

InitialScore ≥ 1 and InitialScore ≤ 500 ; Colour_pixel [1] ;

InitialScore ≤ 800 ; Colour_pixel [2] ;

InitialScore ≤ 1000 ; Colour_pixel [3] ;

InitialScore )

The Case () Statement evaluates each test expression in the order that it is in.

Other other thing. The range for red should not include zero.

[sbg2]

You can shorten your Case statement to just.

Case (

InitialScore ≥ 1 and InitialScore ≤ 500 ; Colour_pixel [1] ;

InitialScore ≤ 800 ; Colour_pixel [2] ;

InitialScore ≤ 1000 ; Colour_pixel [3] ;

InitialScore )

The Case () Statement evaluates each test expression in the order that it is in.

Not nitpicking but... When I read the statement in bold it leads me to think an InitialScore of 1 would result in Coulour_Pixel [3] not Colour-pixel [1]. I think "The Case () Statement evaluates each test expression in the order it is in and exits the case statement after it hits the first test expression that evaluates as true" might be a better way to express it.

[MRH]

Hi mr_vodka,

I meant to include "0" in the first part of the case statement as I wanted to catch a "0" score incase of really bad students…but your quiet right about the rest of the statement…much better - but as I said:

quick & dirty

Mark.