Set background color of row ui table for different data

Kimo · 26 September 2022 09:41

Hi all.
I am a newbie in using ui table tabulator and ui control. I have data comes from sql (Hand size and Quantity), then based on the hand size I want to set different background color to the respective row. I did read on the tabulator and ui control articles, but haven't fully understand to apply on my flows.
Thank you

[
    {
        "id": "b7566cb0d0928290",
        "type": "tab",
        "label": "Flow 1",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "415203cbeae93ab9",
        "type": "function",
        "z": "b7566cb0d0928290",
        "name": "Select from DB",
        "func": "\nmsg.payload = \"select [Hand Size], [Quantity] from [dbo].[Data]  \";\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 460,
        "y": 420,
        "wires": [
            [
                "ac18db9b37b7f3ac"
            ]
        ]
    },
    {
        "id": "ac18db9b37b7f3ac",
        "type": "MSSQL",
        "z": "b7566cb0d0928290",
        "mssqlCN": "df8c0b88.91b0a8",
        "name": "MSSQL",
        "outField": "payload",
        "returnType": "0",
        "throwErrors": "0",
        "query": "",
        "modeOpt": "",
        "modeOptType": "query",
        "queryOpt": "payload",
        "queryOptType": "msg",
        "paramsOpt": "queryParams",
        "paramsOptType": "msg",
        "rows": "",
        "rowsType": "msg",
        "params": [],
        "x": 680,
        "y": 420,
        "wires": [
            [
                "5eb4d2f4131ebc68"
            ]
        ]
    },
    {
        "id": "5eb4d2f4131ebc68",
        "type": "function",
        "z": "b7566cb0d0928290",
        "name": "",
        "func": "var key = Object.keys(msg.payload[0]);\nvar arr = [];\nvar col = [];\n\nfor(i = 0; i <key.length; i++){\n    arr = {\n        \"title\": key[i],\n        \"field\": key[i],\n        \"headerFilter\": \"input\"\n    }\n    col.push(arr)\n}\n\nmsg.ui_control = \n{\n    \"tabulator\": {\n        \"columns\": col\n    }\n}\nreturn msg;\n\n\n\n\n",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 880,
        "y": 420,
        "wires": [
            [
                "8f5434908f35809c"
            ]
        ]
    },
    {
        "id": "8f5434908f35809c",
        "type": "ui_table",
        "z": "b7566cb0d0928290",
        "group": "13b35473.20565c",
        "name": "Data",
        "order": 1,
        "width": "0",
        "height": "0",
        "columns": [
            {
                "field": "",
                "title": "Hand Size",
                "width": "",
                "align": "center",
                "formatter": "plaintext",
                "formatterParams": {
                    "target": "_blank"
                }
            },
            {
                "field": "",
                "title": "Quantity",
                "width": "",
                "align": "center",
                "formatter": "plaintext",
                "formatterParams": {
                    "target": "_blank"
                }
            }
        ],
        "outputs": 0,
        "cts": false,
        "x": 1070,
        "y": 420,
        "wires": []
    },
    {
        "id": "d4a00da03f7f4cc3",
        "type": "inject",
        "z": "b7566cb0d0928290",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "1",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 190,
        "y": 420,
        "wires": [
            [
                "415203cbeae93ab9"
            ]
        ]
    },
    {
        "id": "df8c0b88.91b0a8",
        "type": "MSSQL-CN",
        "name": "Test",
        "server": "",
        "port": "",
        "encyption": false,
        "trustServerCertificate": false,
        "database": "Test",
        "useUTC": false,
        "connectTimeout": "",
        "requestTimeout": "",
        "cancelTimeout": "",
        "pool": "20",
        "parseJSON": false,
        "enableArithAbort": true,
        "credentials": {}
    },
    {
        "id": "13b35473.20565c",
        "type": "ui_group",
        "name": "Default",
        "tab": "de4df751.046f08",
        "order": 3,
        "disp": false,
        "width": "6",
        "collapse": false
    },
    {
        "id": "de4df751.046f08",
        "type": "ui_tab",
        "name": "Home",
        "icon": "track_changes",
        "order": 1,
        "disabled": false,
        "hidden": false
    }
]

Kimo · 26 September 2022 16:13

I tried to apply line of codes below as what I read on Examples for node-red-node-ui-table - #85 by deme

var key = Object.keys(msg.payload[0]);
var arr = [];
var col = [];

for(i = 0; i <key.length; i++){
    arr = {
        "title": key[i],
        "field": key[i],
        "headerFilter": "input"
    }
    col.push(arr)
}

msg.ui_control = 
{
    "tabulator": {
        "columns": col,
        
        "rowFormatter": "function(row){     var data = row.getData();    switch (data.$Hand Size) {         case \"7\":             row.getElement().style.backgroundColor = \"#B48439\";             row.getElement().style.color = \"#B48439\";             break;}}",
        
    }
}
return msg;

Basically I just add the last line of rowFormatter to catch data from sql and if the Hand Size column is 7, it will have background color as mentioned. However, the data on table no longer appear.
Any help will greatly appreciated. Thank you

madhouse · 26 September 2022 18:04

You won't add the code into the table itself or any functions that control the table. What you're looking for is to add the information you want to a "Template" node. You'll find the node in the dashboard section of your nodes. Simply add it to your page (it doesn't need to connect to anything), set it to "Add to site section" and add your CSS code to control your how your table is rendered on the page. You'll find documentation here: Tabulator Styling

For instance, code that changes the highlight color of a row to bright green looks like this:

<style>
.tabulator-row.tabulator-selected {
	background: #00cc00 !important;
}
</style>

Hopefully that helps.

Kimo · 30 September 2022 01:52

Thanks madhouse for the reply. Actually what I want is every data that comes into the ui table from sql (let say hand size is 7), then that row containing hand size 7 will have background color let say green automatically. The instance you gave is good, but coloring the row being pointed/highlighted/selected is not I desired.
Thank you

madhouse · 30 September 2022 14:13

No problem. But I think you misunderstood what I was saying. Regardless, I took a second look at your coding and saw something that stands out as something that seems off.

var data = row.getData();    switch (data.$Hand Size)

I have to wonder about this. It may be correct syntax, but I think it would be more correct for Angular than Node-Red. Plus, it seems to be more complicated than necessary, and you don't have something to handle the space in "Hand SIze" like you normally would if you used quotes. That's what stands out. You could accomplish the same thing by using the statement:

switch(row.getCell("Hand Size"))
{
...
}

This eliminates the need to declare a variable and also allows you to use the title of the column instead of the reference, which has potential to introduce problems. You have the right idea, but I think there's just some simple syntax/logic errors that may be lurking.

Good luck.

Kimo · 13 October 2022 09:08

I got tried your suggestion, then the code shows me error like below

Kimo · 13 October 2022 09:39

I have read through some articles regarding what I want like below. And since the framework is in jQuery, and I'm not that into it, I got stuck with I think this issue should be so simple

https://social.msdn.microsoft.com/Forums/en-US/a888c543-1d28-45b2-a923-ad08eeedfca5/change-colour-of-table-row-if-it-contains-a-value?forum=asphtmlcssjavascript

madhouse · 13 October 2022 14:23

According to your picture, it looks like you've included your function in quotes. Your function shouldn't be included in quotes. It's part of the script. Also, since you're putting everything in actual Javascript instead of Node-Red interpretation, your key values won't be in quotes. For instance, here's what you have in your picture:

"columns": col,
"rowFormatter": "function(row){ switch(row.getCell("Hand Size")) {...

When it should be this:

columns: col,
rowFormatter: function(row){ switch(row.getCell("Hand Size")) {...

What's happening is that your quote at the beginning of function is quoting the function instead of Hand Size. When writing for Tabulator, keep keys out of quotes and only quote string values (not variables, numbers or boolean expressions).

You may want to take a look at this post by the maker of Tabulator. It deals with basically what you're looking for, though at a cellular level instead of a row level. Very easy to adjust for row though...

Kimo · 14 October 2022 04:06

Thank you. I refine the code like this

msg.ui_control = 
{
    "tabulator": {
        columns: col,
        rowFormatter: function(row){ switch(row.getCell("Hand Size")){ case "7": row.getElement().style.backgroundColor = "#B48439"; row.getElement().style.color = "#B48439";break;  case "8R": row.getElement().style.backgroundColor = "#B48439"; row.getElement().style.color = "#B48439";break;  }}
       
    }
}
return msg;

And also not working.

madhouse · 14 October 2022 17:23

Yes, this won't work. I didn't have more of the context you were trying to do this in before, so now I see multiple problems we're working with.

First off, msg.ui_control is something that you pass in from an outside node to a ui node to change attributes of the ui.node. The ui-template node really doesn't have anything to configure, so you're not going to get any results having that inside your template code. What you're doing inside the template node is actually making the code that does the stuff you want the template node to do. So where you have msg.ui_control, you will actually have your variable name associated with a new Tabulator table. Here is a complete setup from the Tabulator examples page.

var table = new Tabulator("#example-table", {
    height:311,
    layout:"fitColumns",
    autoColumns:true
});

This is what would go inside your template node inside a script section. So your template node will look like this as a starting point:

<div id="example-table"></div>
<script>
var table = new Tabulator("#example-table", {
    height:311,
    layout:"fitColumns",
    autoColumns:true
});
</script>

This will draw a Tabulator table on the dashboard page in the group it belongs to. Now with this setup, there won't be an actual table visible because there's nothing defined to render. There's no columns.
There's no data. There's nothing. You have to define something. So, that takes us back to your example and the issues that need to get resolved.

msg.ui_control = needs to be a variable you're declaring to be the access to your table and its functions.
rowFormatter is part of your column definition and needs to be part of the object that defines that particular column. Placing it as it's own variable will cause an error because Tabulator doesn't have a general rowFormatter function and it doesn't know where to place the format.
columns: col, needs to have col be a variable with the actual JSON column definition inside of it, or the actual column definitions themselves in object format. Like this:

columns:[
        {title:"Name", field:"name", sorter:"string", width:200},
        {title:"Progress", field:"progress", sorter:"number", formatter:"progress"},
        {title:"Gender", field:"gender", sorter:"string"},
        {title:"Rating", field:"rating", formatter:"star", hozAlign:"center", width:100},
        {title:"Favourite Color", field:"col", sorter:"string"},
        {title:"Date Of Birth", field:"dob", sorter:"date", hozAlign:"center"},
        {title:"Driver", field:"car", hozAlign:"center", formatter:"tickCross", sorter:"boolean"},
    ],

Inside that definition you would have your rowFormatter placed. For instance, if you wanted the column Progress to have the highlighting, you would insert the rowFormatter like this:

{title:"Progress", field:"progress", sorter:"number", formatter:"progress", rowFormatter: function(row){ switch(row.getCell("Hand Size")){ case "7": row.getElement().style.backgroundColor = "#B48439"; row.getElement().style.color = "#B48439";break;  case "8R": row.getElement().style.backgroundColor = "#B48439"; row.getElement().style.color = "#B48439";break;  }}},

That defines the Progress column to have a specific row format based on the value of the cell. That would be integrated as an object in the column format as declared in your new Tabulator table definition assigned to the variable table that will manage that newly defined table. Make sense?

Just be sure to follow the examples on the Tabulator examples page. Whatever it is you're trying to do, there's an example or code snippet in the document section for it.

system · 13 November 2022 17:23

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
How can I change ui-table's row of color? Dashboard	6	1173	7 August 2022
Where is the row background color set in the following code? General	5	739	28 May 2022
Ui_controll_tabe Dashboard	3	365	9 October 2020
Ui-table supports ui_control Dashboard	85	23569	26 July 2021
The colorization of ui_table is kicking me in the head! General	20	3563	15 September 2020

Set background color of row ui table for different data

Related topics