I’m trying to colour code a grid I have built, based on whether the values in each cell are above or below target. I can see that I can colour code based on a static value, but the target changes depending on the user which it relates to so it needs to be dynamic.
Below is an example of a grid I want to colour code:
And this is an example of how we have done it in excel using conditional formatting:
Any help would be much appreciated!
Hi,
I recommend that you go see the KTL feature called Colorize Field by Value. It has the keyword _cfv and you can see the documentation here: Keywords · cortexrd/Knack-Toolkit-Library Wiki (github.com)
It does exactly what you want.
The toolkit is free and open-source.
Here’s a screen grab of what it can do in a grid view:
It also works with list views:
If you have questions, please let me know.
Normand D.
I presume these are numeric fields?
If so you can add criteria to the display rule,
If the sales value is less than $25k it’s red, if it equals $25k it’s green and if it’s greater than $25k it’s green.
I might be misunderstanding something but you can set up each field with similar conditions as you would with conditional formatting in Excel.
Hey Carl!
The _cfv keyword supports both numeric and text values.
If you use this keyword along with Knack’s built-in display rules, the keyword will have precedence because executed after the view’s render, thus overwriting the rules.
In the case of the Stock Qty you see above, yes the field is numeric. The keyword string is this one:
_cfv=[lt,125,yellow, black], [lt,115,orange, black], [lt,75,red, black]
It is placed in the field’s description like this:
The _cfv keyword can be used in grid and list views. In such cases, it will be applied to the current view only, unless a specific selector is used (ktlSel option).
When placed in the field, it is applied all across the app, in all grid and lists, and has precedence of the view/grid usage.
In the example here, I have multiple groups of parameters, defining color ranges based on the “less than value” (lt) operator. Since they are executed from left to right, the latter overwrites the previous and this creates the gradient effect.
There’s also several string operators: equals, not equal, starts with, etc.
And options to propagate the color to the row, etc. Pretty cool stuff!
In short, yes I think it works a lot like Excel.
Norm
Hi again,
I just read your post once more to realize that my solution as it is now does not really address your requirement. I am also comparing to static values.
But it would be possible to add an option to tell the _cfv keyword to fetch its reference value from a selector like #view_1 .field_2. This could be in a detail view, or a summary row for example.
In the end, instead of using hard-coded values like this:
_cfv=[lt,125,yellow, black], [lt,115,orange, black], [lt,75,red, black]
We’d use a dynamic reference value like this:
_cfv=[lt, ktlRefVal, yellow, black], [lt, ktlRefVal-10, orange, black], [lt, ktlRefVal-50, red, black], [ktlRefVal,#view_1 .field_2]
Would that solve your issue?
Norm
P.S. @CarlHolmes Sorry, I thought your question was addressed to me 
That’s it! I’ve got it working now.
See this screen grab:
The green highlight is based on a referenced value that is an average of sales at the bottom of the grid at $28.27.
All values greater than this will be colorized. And it will change dynamically upon the next calculation. It can even change in real-time is you add the auto-refresh keyword _ar
All this is done with a string added to the view’s description, as seen here:
The string goes like this:
_cfv=[Sales, gt,ktlRefVal,yellow,darkgreen], [ktlRefVal,#view_219 .kn-table-totals:last-of-type td:nth-child(2)]
It would also work with another view, like a details view or anything else. Just change the CSS selector following the ktlRefVal option.
I will release this shortly in version 0.13.11 and update the documentation.
Cheers, enjoy!
Norm
Wow…mind officially blown 
Ok guys, time to have fun!
The code has been released in KTL v0.13.11 and the documentation has been updated.
Let me know how it goes.
If you find problems, create an issue on the GitHub page.
Good night,
Norm
Awesome thank you! Good spotting that I needed it to work it out dynamically. I havent tried it yet but I’ll have a go and let you know if I have any options. Thanks again!
Sorry I may have misread what your trying to do but surely the simplest approach is to add Display Rules on the column ? I do this through the main screen of our program which has to determine based on the results of a field value in the record whether a particular soldier is confirmed (green), not qualified (red), unconfirmed (yellow) or unknown based on missing records (purple) in the BACKGROUND COLOUR.
We also evaluated whether there is data in respective fields Edit Death Notes etc and change the icon colour based on certain flags or data found in values in the record.
See screen below
Apologies of this is not appropriate to your needs
Hi guys,
I’ve just uploaded a new video that show the Colorize Field by Value in the KTL.
Keyword: _cfv
See it here in action
Enjoy,
Normand
@NormandDefayette_CortexRD Will this work on Pivot Table Reports?
