Borderless & dynamic background colors in Interactive Grid

D

Did you ever wanted to show cells in grid with a background color without default borders? Borderless. And to map values to the colors in a way that they can be changed by the user?

Note: by dynamic I ment easily adjustable by end users.

Backend

First we need to create the LOV table with tresholds and colors. Typically I create more of these tables per project/app and I name them with the real value they represents (like LOV_COLOR_BUDGET_RANGE). Lets create the table and some test data:

CREATE TABLE lov_treshold_colors (
    status_id           VARCHAR2(64),
    treshold_value      NUMBER,
    color_code          VARCHAR2(8),
    --
    CONSTRAINT pk_lov_treshold_colors
    	PRIMARY KEY (status_id)
);
--
INSERT INTO lov_treshold_colors
SELECT 'A',  20, '#00cc00' FROM DUAL UNION ALL
SELECT 'B',  40, '#00bb00' FROM DUAL UNION ALL
SELECT 'C',  60, '#00aa00' FROM DUAL UNION ALL
SELECT 'D',  80, '#009900' FROM DUAL UNION ALL
SELECT 'E', 100, '#008800' FROM DUAL;
--
COMMIT;
--
SELECT * FROM lov_treshold_colors;

To simplify matching values to colors we will use a function (I would append a LOV name in real project/app, like GET_COLOR_BUDGET_RANGE). Also you have to decide in which direction will you be looking for the colors. For example from the LOV table above, you might treat the treshold as anything above value 20 will have color #00cc00 or as anything below 20 will have color #00cc00. To alter direction you can use in_search_below argument in the function.

CREATE OR REPLACE FUNCTION get_color (
    in_value            lov_treshold_colors.treshold_value%TYPE,
    in_search_below     CHAR                                        := NULL
)
RETURN lov_treshold_colors.color_code%TYPE
AS
    out_color           lov_treshold_colors.color_code%TYPE;
BEGIN
    IF in_search_below IS NULL THEN
        SELECT MIN(t.color_code) KEEP (DENSE_RANK FIRST ORDER BY t.treshold_value DESC) INTO out_color
        FROM lov_treshold_colors t
        WHERE t.treshold_value <= in_value;
    ELSE
        SELECT MIN(t.color_code) KEEP (DENSE_RANK FIRST ORDER BY t.treshold_value) INTO out_color
        FROM lov_treshold_colors t
        WHERE t.treshold_value >= in_value;
    END IF;
    --
    RETURN out_color;
END;
/

You can test this function on a random sample:

WITH test_data AS (
    SELECT
        LEVEL AS row_id,
        ROUND(DBMS_RANDOM.VALUE(1, 120), 0) AS value_
    FROM DUAL
    CONNECT BY LEVEL <= 20
)
SELECT
    d.row_id,
    d.value_,
    get_color(d.value_)         AS color_above,
    get_color(d.value_, 'Y')    AS color_below
FROM test_data d;

Frontend

Now when we can map values to colors, we should create a setup page for privileged users, with a simple grid where they can easily change these treshold values and colors without any assistance or request to developers.

I usually create a blank page, assign proper authorization scheme, add page to the navigation, create a Hero region for header, another region for grid mapped to the table above, make the grid editable, check primary key on the grid and change color column to a Color picker type. Done. Also usually I have the report page already present and I am just adding colors to the existing report/grid. But for this purpose lets create a new report page and map colors there. For the region source we will reuse the test query above.

Then we need to adjust CSS styles on the page and add a Javascript function. I would recommend to do this on page zero or even better in application/workspace files so it can be shared through multiple pages and apps. The Javascript function allows to pass value (visible to user), color (background) and title (visible on hover).

<style>

/* this works for Redwood, you might need to adjust it */
.COLOR_STATUS {
    padding:    0 !important;
    margin:     0 !important;
}
.COLOR_STATUS > div {
    width:      100%; 
    height:     2.5rem;
    padding:    0.6rem 0.6rem 0;
}

</style>

<script>

var color_status = function (options, value, color, title) {
    options.defaultGridColumnOptions = {
        cellTemplate: '<div style="background: ' + color + ';" title="' + title + '">' + value + '</div>'
    };
    return options;
}

</script>

Now we need to add a CSS class to the column and adjust the JS init code.

function (options) {
    return color_status(options, '&VALUE_.', '&COLOR_ABOVE.', '');
}

Ta daa.