Color Coding Fields

Question

I am trying to make a report that color codes the individual fields based on a given specification range. For example, the one I'm starting with I want to highlight the field in red when the number is greater than 1.50. I tried looking through the community tab to see where in the coding I went wrong but I wasn't having any luck. For some reason, the code is functioning properly in terms of the highlighting, but isn't pulling the number over correctly. Here is the code that I have set up in the Formula Rich Field (I've also attached an image):If([Lot Sensory Score:] &gt; 1.5, "&lt;div style=\"background-color:red;\"&gt;[Lot Sensory Score:]
&lt;/div&gt;", ToText([Lot Sensory Score:]))For some reason the second "[Lot Sensory Score]" isn't pulling correctly and isn't turning green in the code window.Eventually, I want to get more complicated with the color coding fields (i.e. if this field is A, color based on 80-100; if this field is B...), but I was trying to start simple.------------------------------Paige M.------------------------------

markshnier__you · Answer

Try thisIf([Lot Sensory Score:] &gt; 1.5, "&lt;div style=background-color:red&gt;" &amp; ToText([Lot Sensory Score:]) &amp; "&lt;/div&gt;", ToText([Lot Sensory Score:]))&nbsp;if you want to have more complex colors you can do this.If([Lot Sensory Score:] &gt; 1.5, "&lt;div style=background-color:red&gt;" &amp; ToText([Lot Sensory Score:]) &amp; "&lt;/div&gt;", [Lot Sensory Score:] &gt; 1.2, "&lt;div style=background-color:Blue&gt;" &amp; ToText([Lot Sensory Score:]) &amp; "&lt;/div&gt;", [Lot Sensory Score:] &gt; 1.0, "&lt;div style=background-color:yellow&gt;" &amp; ToText([Lot Sensory Score:]) &amp; "&lt;/div&gt;", ToText([Lot Sensory Score:]))There is also complete set of hex color codes here https://laurahillier.quickbase.com/db/bhy8pumuk?a=q&amp;qid=-1000003&amp;dr=1 which you can use instead of the color names in words.------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

paigemann · Answer

Thank you Mark. That ended up working to solve my issue. I figured that I had something in the base coding that was throwing it off.

For the complex colors, if I were to tie in a specific field reference and the number range, would that be an additional and statement within the if statement?

For example:

Field 1 = Type (i.e. A, B, C, etc.)
Field 2 = Sensory Score (the measured number)

Type A has a range of 1 - 1.2; Type B has a range of 1.2 - 1.5; etc.

Would the code look something like this:

If(
[Field 1] = C & [Field 2] > 1.5,
"<div style=background-color:Red>" & ToText([Field 2]) & "</div>",

[Field 1] = B & [Field 2] > 1.2,
"<div style=background-color:Blue>" & ToText([Field 2]) & "</div>",

[Field 1] = A & [Field 2] > 1.0,
"<div style=background-color:Yellow>" & ToText([Field 2]) & "</div>",

ToText([Field 2]))

------------------------------
Paige Mann
------------------------------

markshnier__you · Answer

... you were close.&nbsp; &amp; means concatenation, similar to excel and AND means boolean&nbsp;If([Field 1] = C and [Field 2] &gt; 1.5,"&lt;div style=background-color:Red&gt;" &amp; ToText([Field 2]) &amp; "&lt;/div&gt;",[Field 1] = B and [Field 2] &gt; 1.2,"&lt;div style=background-color:Blue&gt;" &amp; ToText([Field 2]) &amp; "&lt;/div&gt;",[Field 1] = A and [Field 2] &gt; 1.0,"&lt;div style=background-color:Yellow&gt;" &amp; ToText([Field 2]) &amp; "&lt;/div&gt;",ToText([Field 2]))------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

Forum Discussion

Color Coding Fields