Trouble with Tables

[sanderson]

Hi all,

I am not quite getting how to use the DB2 table node (or the DB2 template node) to display data. I have two asynchronous msg.payload objects, with identical labels, but different values. I would like to display these as three columns, the first column being the labels, the second being the payload values from msg 1, and the third being the values from msg 2. I have tried a variety of tests, but none of them show any information. I think I need to join the msgs with a Join 2+ and a timeout (one of the msgs doesn't arrive very frequently), but can't quite see how to get these into my table. I include an example object below. Thanks for any insight you can share.

{"RVC/THERMOSTAT_STATUS_1/7":{"data":"070142E125E125FF","dgn":"1FFE2","fan mode":"00","fan mode definition":"auto","fan speed":33,"instance":7,"name":"THERMOSTAT_STATUS_1","operating mode":"0001","operating mode definition":"cool","schedule mode":"00","schedule mode definition":"disabled","setpoint temp cool":30,"setpoint temp cool F":86,"setpoint temp heat":30,"setpoint temp heat F":86,"timestamp":"1726025066.253274"},"RVC/THERMOSTAT_COMMAND_1/7":{"data":"074142C524C524","dgn":"1FEF9","fan mode":"00","fan mode definition":"auto","fan speed":33,"instance":7,"name":"THERMOSTAT_COMMAND_1","operating mode":"0001","operating mode definition":"cool","schedule mode":"01","schedule mode definition":"enabled","setpoint temp cool":21.2,"setpoint temp cool F":70.1,"setpoint temp heat":21.2,"setpoint temp heat F":70.1,"timestamp":"1726025068.333129"}}

[E1cid]

The joined object would need converting to display in 3 columns.
e.g.

[{"id":"9f128cd2f9b80ce1","type":"inject","z":"d1395164b4eec73e","name":"RVC/THERMOSTAT_STATUS_1/7","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"RVC/THERMOSTAT_STATUS_1/7","payload":"{\"data\":\"070142E125E125FF\",\"dgn\":\"1FFE2\",\"fan mode\":\"00\",\"fan mode definition\":\"auto\",\"fan speed\":33,\"instance\":7,\"name\":\"THERMOSTAT_STATUS_1\",\"operating mode\":\"0001\",\"operating mode definition\":\"cool\",\"schedule mode\":\"00\",\"schedule mode definition\":\"disabled\",\"setpoint temp cool\":30,\"setpoint temp cool F\":86,\"setpoint temp heat\":30,\"setpoint temp heat F\":86,\"timestamp\":\"1726025066.253274\"}","payloadType":"json","x":210,"y":2200,"wires":[["075cbb982ad850a7"]]},{"id":"075cbb982ad850a7","type":"change","z":"d1395164b4eec73e","name":"","rules":[{"t":"move","p":"payload.name","pt":"msg","to":"topic","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":250,"y":2240,"wires":[["203ed03520270085"]]},{"id":"3c6d7319ba8fb2b9","type":"inject","z":"d1395164b4eec73e","name":"RVC/THERMOSTAT_COMMAND_1/7","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"RVC/THERMOSTAT_COMMAND_1/7","payload":"{\"data\":\"074142C524C524\",\"dgn\":\"1FEF9\",\"fan mode\":\"00\",\"fan mode definition\":\"auto\",\"fan speed\":33,\"instance\":7,\"name\":\"THERMOSTAT_COMMAND_1\",\"operating mode\":\"0001\",\"operating mode definition\":\"cool\",\"schedule mode\":\"01\",\"schedule mode definition\":\"enabled\",\"setpoint temp cool\":21.2,\"setpoint temp cool F\":70.1,\"setpoint temp heat\":21.2,\"setpoint temp heat F\":70.1,\"timestamp\":\"1726025068.333129\"}","payloadType":"json","x":220,"y":2280,"wires":[["075cbb982ad850a7"]]},{"id":"203ed03520270085","type":"join","z":"d1395164b4eec73e","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":430,"y":2240,"wires":[["9ef592c473b349e8"]]},{"id":"9ef592c473b349e8","type":"change","z":"d1395164b4eec73e","name":"convert object","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t    $machines := $keys($$.payload);\t    $fields := $keys($$.payload.*[0]);\t    $fields#$index.\t        $merge(\t            [$append(\t                [{\t                    \"name\":$\t                }],\t                [$machines#$indexm.{\t                        $machines[$indexm]:$lookup($lookup($$.payload, $machines[$indexm]),$fields[$index])\t                    }]\t            )]\t        )\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":580,"y":2240,"wires":[["7c7c221cfb49f6da","ad1c6238be52e4a3"]]},{"id":"7c7c221cfb49f6da","type":"ui-table","z":"d1395164b4eec73e","group":"9d83cd8b3d5ee33c","name":"","label":"text","order":1,"width":0,"height":0,"maxrows":0,"passthru":false,"autocols":true,"showSearch":true,"selectionType":"none","columns":[],"mobileBreakpoint":"sm","mobileBreakpointType":"defaults","x":770,"y":2240,"wires":[[]]},{"id":"ad1c6238be52e4a3","type":"debug","z":"d1395164b4eec73e","name":"debug 2567","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":670,"y":2320,"wires":[]},{"id":"9d83cd8b3d5ee33c","type":"ui-group","name":"Group1","page":"c694d0ebe0d2b702","width":"6","height":"1","order":1,"showTitle":true,"className":"","visible":"true","disabled":"false"},{"id":"c694d0ebe0d2b702","type":"ui-page","name":"Page1","ui":"1805777f90e92057","path":"/page1","icon":"home","layout":"grid","theme":"a965ccfef139317a","order":2,"className":"","visible":"true","disabled":"false"},{"id":"1805777f90e92057","type":"ui-base","name":"dashboard","path":"/dashboard","includeClientData":true,"acceptsClientConfig":["ui-notification","ui-control"],"showPathInSidebar":false,"showPageTitle":true,"titleBarStyle":"default"},{"id":"a965ccfef139317a","type":"ui-theme","name":"HN Theme","colors":{"surface":"#5c5c5c","primary":"#00fdff","bgPage":"#383838","groupBg":"#4f4f4f","groupOutline":"#858585"},"sizes":{"pagePadding":"12px","groupGap":"12px","groupBorderRadius":"4px","widgetGap":"12px"}}]

[edit]
Or a low code option using context storage.

[{"id":"23c554c61ec26dca","type":"inject","z":"d1395164b4eec73e","name":"RVC/THERMOSTAT_STATUS_1/7","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"RVC/THERMOSTAT_STATUS_1/7","payload":"{\"data\":\"070142E125E125FF\",\"dgn\":\"1FFE2\",\"fan mode\":\"00\",\"fan mode definition\":\"auto\",\"fan speed\":33,\"instance\":7,\"name\":\"THERMOSTAT_STATUS_1\",\"operating mode\":\"0001\",\"operating mode definition\":\"cool\",\"schedule mode\":\"00\",\"schedule mode definition\":\"disabled\",\"setpoint temp cool\":30,\"setpoint temp cool F\":86,\"setpoint temp heat\":30,\"setpoint temp heat F\":86,\"timestamp\":\"1726025066.253274\"}","payloadType":"json","x":170,"y":2740,"wires":[["d851755860b97dab"]]},{"id":"d851755860b97dab","type":"change","z":"d1395164b4eec73e","name":"","rules":[{"t":"move","p":"payload.name","pt":"msg","to":"topic","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":530,"y":2740,"wires":[["ad7abccda36839e8"]]},{"id":"6f41ad06a1ded073","type":"inject","z":"d1395164b4eec73e","name":"RVC/THERMOSTAT_COMMAND_1/7","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"RVC/THERMOSTAT_COMMAND_1/7","payload":"{\"data\":\"074142C524C524\",\"dgn\":\"1FEF9\",\"fan mode\":\"00\",\"fan mode definition\":\"auto\",\"fan speed\":33,\"instance\":7,\"name\":\"THERMOSTAT_COMMAND_1\",\"operating mode\":\"0001\",\"operating mode definition\":\"cool\",\"schedule mode\":\"01\",\"schedule mode definition\":\"enabled\",\"setpoint temp cool\":21.2,\"setpoint temp cool F\":70.1,\"setpoint temp heat\":21.2,\"setpoint temp heat F\":70.1,\"timestamp\":\"1726025068.333129\"}","payloadType":"json","x":180,"y":2820,"wires":[["d851755860b97dab"]]},{"id":"ad7abccda36839e8","type":"split","z":"d1395164b4eec73e","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":470,"y":2800,"wires":[["ffe03c6a3619c069"]]},{"id":"ffe03c6a3619c069","type":"change","z":"d1395164b4eec73e","name":"","rules":[{"t":"set","p":"table_array[msg.parts.index].name","pt":"flow","to":"parts.key","tot":"msg"},{"t":"set","p":"table_array[msg.parts.index][msg.topic]","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":620,"y":2800,"wires":[["62935a99782214bb"]]},{"id":"62935a99782214bb","type":"join","z":"d1395164b4eec73e","name":"","mode":"auto","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":770,"y":2800,"wires":[["d9c35ee4837ff4a4"]]},{"id":"d9c35ee4837ff4a4","type":"change","z":"d1395164b4eec73e","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"table_array","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":460,"y":2860,"wires":[["d061da9f82f521dd","a34ff8ce054ec262"]]},{"id":"d061da9f82f521dd","type":"debug","z":"d1395164b4eec73e","name":"debug 2567","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":630,"y":2940,"wires":[]},{"id":"a34ff8ce054ec262","type":"ui-table","z":"d1395164b4eec73e","group":"9d83cd8b3d5ee33c","name":"","label":"text","order":2,"width":0,"height":0,"maxrows":0,"passthru":false,"autocols":true,"showSearch":true,"selectionType":"none","columns":[],"mobileBreakpoint":"sm","mobileBreakpointType":"defaults","x":650,"y":2860,"wires":[[]]},{"id":"9d83cd8b3d5ee33c","type":"ui-group","name":"Group1","page":"c694d0ebe0d2b702","width":"6","height":"1","order":1,"showTitle":true,"className":"","visible":"true","disabled":"false"},{"id":"c694d0ebe0d2b702","type":"ui-page","name":"Page1","ui":"1805777f90e92057","path":"/page1","icon":"home","layout":"grid","theme":"a965ccfef139317a","order":2,"className":"","visible":"true","disabled":"false"},{"id":"1805777f90e92057","type":"ui-base","name":"dashboard","path":"/dashboard","includeClientData":true,"acceptsClientConfig":["ui-notification","ui-control"],"showPathInSidebar":false,"showPageTitle":true,"titleBarStyle":"default"},{"id":"a965ccfef139317a","type":"ui-theme","name":"HN Theme","colors":{"surface":"#5c5c5c","primary":"#00fdff","bgPage":"#383838","groupBg":"#4f4f4f","groupOutline":"#858585"},"sizes":{"pagePadding":"12px","groupGap":"12px","groupBorderRadius":"4px","widgetGap":"12px"}}]

[sanderson]

Hello again, and thank again!

Both of these methods work great - I was thinking that the parts of the msgs had to be broken apart into an array of objects, one per line.

I think it will take me a moment to understand how version one is doing this. Version 2 I almost get, except what is happening in the "change 2 rules" node. Can you give a brief explanation?

All I can say is this forum is amazing. I try things for a while, get befuddled, and within an overnight, someone like yourself can come along and clarify the magic of either JS or split/join jujitsu.

Thank you!

[E1cid]

Set flow table_array[msg.parts.index].name
To msg parts.key
e.g ( set flow table_array[0].name )

This creates the name property in each element of the flow contecxt table_array.

msg.parts.key is part of the split msg.parts, if you set a debug to show complete message object, you will see key changes for each split message, e.g. data, dgn, etc. name is the object property name.
msg.parts.index is the split elenet index 0 to 15 for each message of the split.

Set flow table_array[msg.parts.index][msg.topic].
To msg payload
e.g( set flow table_array[0].THERMOSTAT_STATUS_1 )

This creates the THERMOSTAT_STATUS_1 and THERMOSTAT_COMMAND_1 property in each element of the flow context table_array, and is set to the split payload for each message of the split. msg.topic has been set to the incoming object property name prior to the split, and becomes the property name.

[edit]
No JS was used in these examples, but it could be done with a function node. The first example uses JSONata

[sanderson]

Thank you! I now have this working well. It is making it much easier to track down what is going on with these components! I also figured out how to reset a table (though not obviously documented, it just like resetting a chart, for anyone seeking this tidbit).