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

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