Display table data from sqlite

I am tinkering around with an sqlite query which is working but how can I roll this data up onto a table to view through the ui?

On the template node does this look right?

<table style="width:100%">
  <tr>
    <th>Index</th> 
    <th>Object_Name</th>
    <th>Object_Type</th> 
    <th>Object_ID</th>
    <th>Device_ID</th>
  </tr>
  <tr ng-repeat="x in msg.payload | limitTo:200">
    <td>{{msg.payload[$index].index}}</td>
    <td>{{msg.payload[$index].object_names}}</td>
    <td>{{msg.payload[$index].object_type}}</td> 
    <td>{{msg.payload[$index].object_id}}</td>
    <td>{{msg.payload[$index].device_id}}</td>
  </tr>
</table>

The error that pops up is a group name error:
image

Hello .. if you hover over the root of the array db result (in the Debug tab) you'll get a Copy value icon.
Copy and paste using the < / > icon in a post, to have some sample data to test an example.

You dont need to use msg.payload again in the <td> since each element in the msg.payload array .. you named x. So it could just be

<tr ng-repeat="x in msg.payload | limitTo:200">
    <td>{{x.index}}</td>
    <td>{{x.object_names}}</td>
    <td>{{x.object_type}}</td> 
    <td>{{x.object_id}}</td>
    <td>{{x.device_id}}</td>
  </tr>

Ok I think I understand sorry sort of new to node red. This is the copy paste:

[{"index":10,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":23},{"index":10,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":24},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":25},{"index":34,"object_names":"Space Temperature Setpoint BAS","object_type":"analogValue","object_id":27,"device_id":12026},{"index":10,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":27},{"index":33,"object_names":"Space Temperature Setpoint BAS","object_type":"analogValue","object_id":27,"device_id":12028},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":29},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":30},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":31},{"index":34,"object_names":"Space Temperature Setpoint BAS","object_type":"analogValue","object_id":27,"device_id":12032},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":6},{"index":34,"object_names":"Space Temperature Setpoint BAS","object_type":"analogValue","object_id":27,"device_id":12033},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":7},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":34},{"index":10,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":8},{"index":33,"object_names":"Space Temperature Setpoint BAS","object_type":"analogValue","object_id":27,"device_id":12035},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":9},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":36},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":10},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":11},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":13},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":14},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":15},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":16},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":19},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":20},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":21},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":37},{"index":9,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":38},{"index":10,"object_names":"ZN-SP","object_type":"analogValue","object_id":1103,"device_id":39}]

This message also pops up:

image

Double click on the ui_template node and see what is not configured.
it should have a red outline / border around either the Group or Tab the ui node should belong.

Test Flow ( Ctrl-I to import )

[{"id":"79e9853a5758d7f0","type":"inject","z":"4895ea10b4ee9ead","name":"DB Data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"[{\"index\":10,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":23},{\"index\":10,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":24},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":25},{\"index\":34,\"object_names\":\"Space Temperature Setpoint BAS\",\"object_type\":\"analogValue\",\"object_id\":27,\"device_id\":12026},{\"index\":10,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":27},{\"index\":33,\"object_names\":\"Space Temperature Setpoint BAS\",\"object_type\":\"analogValue\",\"object_id\":27,\"device_id\":12028},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":29},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":30},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":31},{\"index\":34,\"object_names\":\"Space Temperature Setpoint BAS\",\"object_type\":\"analogValue\",\"object_id\":27,\"device_id\":12032},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":6},{\"index\":34,\"object_names\":\"Space Temperature Setpoint BAS\",\"object_type\":\"analogValue\",\"object_id\":27,\"device_id\":12033},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":7},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":34},{\"index\":10,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":8},{\"index\":33,\"object_names\":\"Space Temperature Setpoint BAS\",\"object_type\":\"analogValue\",\"object_id\":27,\"device_id\":12035},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":9},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":36},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":10},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":11},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":13},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":14},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":15},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":16},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":19},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":20},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":21},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":37},{\"index\":9,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":38},{\"index\":10,\"object_names\":\"ZN-SP\",\"object_type\":\"analogValue\",\"object_id\":1103,\"device_id\":39}]","payloadType":"json","x":270,"y":460,"wires":[["771a31ca4db80de2"]]},{"id":"771a31ca4db80de2","type":"ui_template","z":"4895ea10b4ee9ead","group":"f541ab73c138159c","name":"","order":0,"width":0,"height":0,"format":"<style>\n    td, th {\n    text-align: left;\n    vertical-align: middle;\n    }\n</style>\n\n<table style=\"width:100%\">\n    <tr>\n        <th>Index</th>\n        <th>Object_Name</th>\n        <th>Object_Type</th>\n        <th>Object_ID</th>\n        <th>Device_ID</th>\n    </tr>\n    <tr ng-repeat=\"x in msg.payload | limitTo:200\">\n        <td>{{x.index}}</td>\n        <td>{{x.object_names}}</td>\n        <td>{{x.object_type}}</td>\n        <td>{{x.object_id}}</td>\n        <td>{{x.device_id}}</td>\n    </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":470,"y":460,"wires":[[]]},{"id":"f541ab73c138159c","type":"ui_group","name":"Default","tab":"8f97582059cc55ea","order":1,"disp":true,"width":"16","collapse":false},{"id":"8f97582059cc55ea","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

Thank you

1 Like

I suggest you try this node node-red-node-ui-table. It makes a perfect table, with column sorting, resizing, shading.
Simply plug your query output into this node.
image