Function to dynamically change the MQTT topic based on user input

General
1

I have a flow where user inputs data through dropdown and form menus. I save this data in flow variable as such:
flow.DBdata[0] = Device
flow.DBdata[1] = Item
flow.DBdata[2] = Serial

What I need to do is to join DBdata[1] and DBdata[2] to a single message and then send mqtt message to whatever Device described in DBdata[0]

So the MQTT topic should look like:
Device/topic_name
MQTT payload:
Item,Serial

I know how to build a single message out of 2 individual messages, but I am not too sure how can I change MQTT topic since I have to manually input topic when I am using MQTT publish node.

flow:

[{"id":"95b7e688.b0a538","type":"change","z":"df177cb8.27882","name":"set flow DBdata.device","rules":[{"t":"set","p":"DBdata[0]","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":520,"y":2700,"wires":[[]]},{"id":"32a52bea.d3a564","type":"change","z":"df177cb8.27882","name":"set flow DBdata.item","rules":[{"t":"set","p":"DBdata[1]","pt":"flow","to":"payload.Item","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":520,"y":2800,"wires":[[]]},{"id":"715089ad.12de08","type":"change","z":"df177cb8.27882","name":"set flow DBdata.serial","rules":[{"t":"set","p":"DBdata[2]","pt":"flow","to":"payload.Serial","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":520,"y":2880,"wires":[[]]},{"id":"9188896.d0fdd78","type":"ui_dropdown","z":"df177cb8.27882","name":"","label":"Select device test","tooltip":"","place":"Select option","group":"3521c1e4.63bd6e","order":3,"width":0,"height":0,"passthru":true,"multiple":false,"options":[{"label":"Device1","value":"Device1","type":"str"},{"label":"Device2","value":"Device2","type":"str"},{"label":"Device3","value":"Device3","type":"str"}],"payload":"","topic":"","x":250,"y":2700,"wires":[["95b7e688.b0a538"]]},{"id":"a133b7b3.55e198","type":"ui_form","z":"df177cb8.27882","name":"","label":"Add data to mysql table test","group":"3521c1e4.63bd6e","order":4,"width":0,"height":0,"options":[{"label":"Item","value":"Item","type":"text","required":true,"rows":null},{"label":"Serial","value":"Serial","type":"text","required":true,"rows":null}],"formValue":{"Item":"","Serial":""},"payload":"","submit":"submit","cancel":"cancel","topic":"","x":220,"y":2840,"wires":[["32a52bea.d3a564","715089ad.12de08"]]},{"id":"c8fc5ac1.e56758","type":"ui_button","z":"df177cb8.27882","name":"","group":"3521c1e4.63bd6e","order":5,"width":0,"height":0,"passthru":false,"label":"button","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":190,"y":2980,"wires":[["8641e264.f6037"]]},{"id":"8641e264.f6037","type":"change","z":"df177cb8.27882","name":"combine 3 flows to msg payload","rules":[{"t":"set","p":"payload","pt":"msg","to":"DBdata","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":490,"y":2980,"wires":[["41d59152.53a51","922fd2dc.d6f3e","f34040a3.1fb81"]]},{"id":"41d59152.53a51","type":"function","z":"df177cb8.27882","name":"","func":"\ndevice = msg.payload[0]\nitem = msg.payload[1]\nserial = msg.payload[2]\n\nmsg.topic=`REPLACE INTO pack_to_light (Device,Item,Serial) VALUES ('${device}','${item}',${serial})`;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":720,"y":2980,"wires":[["4ecc06d0.50b9b8"]]},{"id":"4ecc06d0.50b9b8","type":"mysql","z":"df177cb8.27882","mydb":"97be0e96.67231","name":"Add item to database","x":920,"y":2980,"wires":[[]]},{"id":"922fd2dc.d6f3e","type":"change","z":"df177cb8.27882","name":"Extract item information","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[1]","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":770,"y":2880,"wires":[["98551b72.f10e88"]]},{"id":"f34040a3.1fb81","type":"change","z":"df177cb8.27882","name":"Extract serial information","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[2]","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":770,"y":3060,"wires":[["98551b72.f10e88"]]},{"id":"98551b72.f10e88","type":"join","z":"df177cb8.27882","name":"","mode":"custom","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":1170,"y":2980,"wires":[["6fecb1dd.d6a7d"]]},{"id":"6fecb1dd.d6a7d","type":"mqtt out","z":"df177cb8.27882","name":"","topic":"device3/item_inside","qos":"1","retain":"true","broker":"2727c5a5.a4fb6a","x":1350,"y":2980,"wires":[]},{"id":"3521c1e4.63bd6e","type":"ui_group","z":"","name":"MYSQL","tab":"cc84c991.418318","order":4,"disp":true,"width":"6","collapse":false},{"id":"97be0e96.67231","type":"MySQLdatabase","z":"","name":"","host":"localhost","port":"3306","db":"test","tz":""},{"id":"2727c5a5.a4fb6a","type":"mqtt-broker","z":"","name":"","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"workshop/status","birthQos":"1","birthRetain":"true","birthPayload":"online","closeTopic":"workshop/status","closeQos":"1","closeRetain":"true","closePayload":"offline","willTopic":"workshop/status","willQos":"1","willRetain":"true","willPayload":"disconnected"},{"id":"cc84c991.418318","type":"ui_tab","z":"","name":"Control","icon":"dashboard","disabled":false,"hidden":false}]

2020-07-21-083554_1920x1080_scrot
2020-07-21-083554_1920x1080_scrot1920Ă—1080 238 KB

As you can see, my MQTT topic is device3/item_inside and the payload is joined message from DB_data[1] and DB_data[2].

How can I make the MQTT topic change based on user input DB_data[0].
If user inputs device20 I want my topic to automatiaclly change to device20/item_inside

2

Why do you spit after "combine 3" node then re join? Is unnecessary.
Just put the 2 extracts in series, followed by a function node to compute the topic and place it into msg.topic then to the mqtt node.

If you're stuck, export a demo flow with demo fake data (using inject nodes) to a reply and I will show you)

3

I am using 2 switch nodes after combine to extract information from DBData[1] and DBdata[2] that I use for payload. I did not know any other way to do that. Could you show me what is a better way to do that? I have tried putting 2 extracts in series but doesnt seem to work as expected. I have mode some slight changes and added inject nodes. If you could have a look! :

[{"id":"1583f34e.c8146d","type":"change","z":"df177cb8.27882","name":"set flow DBdata[0] to device","rules":[{"t":"set","p":"DBdata[0]","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":520,"y":3620,"wires":[["a7604536.d7d0d8"]]},{"id":"e93e64fd.8ac098","type":"change","z":"df177cb8.27882","name":"set flow DBdata[1] to item","rules":[{"t":"set","p":"DBdata[1]","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":510,"y":3720,"wires":[["dfc90860.59c1d8"]]},{"id":"4c63fa.e2c7ec08","type":"change","z":"df177cb8.27882","name":"set flow DBdata[2] to serial","rules":[{"t":"set","p":"DBdata[2]","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":520,"y":3800,"wires":[["3459c09a.ad099"]]},{"id":"68f987f0.232ad8","type":"change","z":"df177cb8.27882","name":"combine 3 flows to msg payload","rules":[{"t":"set","p":"payload","pt":"msg","to":"DBdata","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":470,"y":3980,"wires":[["425a981d.dc7638","71929940.285fb8","c7f6ee34.f936b","f1718379.7bfbd","3c686fd3.598cf"]]},{"id":"425a981d.dc7638","type":"function","z":"df177cb8.27882","name":"","func":"\ndevice = msg.payload[0]\nitem = msg.payload[1]\nserial = msg.payload[2]\n\nmsg.topic=`REPLACE INTO pack_to_light (Device,Item,Serial) VALUES ('${device}','${item}',${serial})`;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":820,"y":4120,"wires":[["ac048f0d.a306e"]]},{"id":"ac048f0d.a306e","type":"mysql","z":"df177cb8.27882","mydb":"97be0e96.67231","name":"Add item to database","x":1040,"y":4120,"wires":[[]]},{"id":"71929940.285fb8","type":"change","z":"df177cb8.27882","name":"Extract item information","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[1]","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":830,"y":3940,"wires":[["ba469c18.8b198"]]},{"id":"c7f6ee34.f936b","type":"change","z":"df177cb8.27882","name":"Extract serial information","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[2]","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":830,"y":4000,"wires":[["ba469c18.8b198"]]},{"id":"ba469c18.8b198","type":"join","z":"df177cb8.27882","name":"join item and serial to build payload","mode":"custom","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":1140,"y":3980,"wires":[["f7818f14.6c06","f88fd6d5.f24538"]]},{"id":"f7818f14.6c06","type":"mqtt out","z":"df177cb8.27882","name":"","topic":"msg.mqtt_topic","qos":"1","retain":"true","broker":"2727c5a5.a4fb6a","x":1180,"y":3860,"wires":[]},{"id":"f1718379.7bfbd","type":"function","z":"df177cb8.27882","name":"function to build topic","func":"msg.mqtt_topic = msg.payload[0]+'/item_inside'\n\nreturn msg","outputs":1,"noerr":0,"initialize":"","finalize":"","x":840,"y":3860,"wires":[["3ddf0077.203fb","f7818f14.6c06"]]},{"id":"3ddf0077.203fb","type":"debug","z":"df177cb8.27882","name":"mqtt topic","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"mqtt_topic","targetType":"msg","statusVal":"","statusType":"auto","x":1080,"y":3780,"wires":[]},{"id":"f88fd6d5.f24538","type":"debug","z":"df177cb8.27882","name":"mqtt payload","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1410,"y":3980,"wires":[]},{"id":"3c686fd3.598cf","type":"change","z":"df177cb8.27882","name":"Extract item information","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[1]","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":550,"y":4200,"wires":[["5c36b94.52ecb48"]]},{"id":"5c36b94.52ecb48","type":"change","z":"df177cb8.27882","name":"Extract serial information","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[2]","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":830,"y":4200,"wires":[["e1594c88.a792f"]]},{"id":"e1594c88.a792f","type":"debug","z":"df177cb8.27882","name":"mqtt payload2","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1080,"y":4200,"wires":[]},{"id":"71d05dfc.5df164","type":"inject","z":"df177cb8.27882","name":"inject item","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"item1","payloadType":"str","x":320,"y":3720,"wires":[["e93e64fd.8ac098"]]},{"id":"c7922bda.049bc8","type":"inject","z":"df177cb8.27882","name":"inject serial","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"1234","payloadType":"str","x":320,"y":3800,"wires":[["4c63fa.e2c7ec08"]]},{"id":"c1ebd2ef.9213e","type":"inject","z":"df177cb8.27882","name":"inject device","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"device1","payloadType":"str","x":310,"y":3620,"wires":[["1583f34e.c8146d"]]},{"id":"a7604536.d7d0d8","type":"debug","z":"df177cb8.27882","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":750,"y":3620,"wires":[]},{"id":"dfc90860.59c1d8","type":"debug","z":"df177cb8.27882","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":750,"y":3720,"wires":[]},{"id":"3459c09a.ad099","type":"debug","z":"df177cb8.27882","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":750,"y":3800,"wires":[]},{"id":"70050f26.e0f0e","type":"inject","z":"df177cb8.27882","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":220,"y":3980,"wires":[["68f987f0.232ad8"]]},{"id":"97be0e96.67231","type":"MySQLdatabase","z":"","name":"","host":"localhost","port":"3306","db":"test","tz":""},{"id":"2727c5a5.a4fb6a","type":"mqtt-broker","z":"","name":"","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"workshop/status","birthQos":"1","birthRetain":"true","birthPayload":"online","closeTopic":"workshop/status","closeQos":"1","closeRetain":"true","closePayload":"offline","willTopic":"workshop/status","willQos":"1","willRetain":"true","willPayload":"disconnected"}]

2020-07-21-093028_1920x1080_scrot
2020-07-21-093028_1920x1080_scrot1920Ă—1080 244 KB

I have managed to build topic for mqtt as required, but not sure how to pass it to mqtt publish node

1 Like
4

Note: I have edit and put a new flow in post above to change to button to inject node

5

Few things...

  1. This is NOT how you create a dynamic topic
    image

The help on the mqtt node says...
image

  1. Here you overwrite the payload with payload[1] then try to do it again with payload[2] in the 2nd node.
    At this point, the original payload array has been replaced with element 1 and thus payload[2] is non existent...

    image
    image769Ă—74 9.33 KB

  2. You didnt put the generation of the topic in series with the change nodes (so the topic and data are separate entities)

  3. Your final output looks to be a string of Item name, new line, serial data...

item1
1234

This is not an ideal payload? Why dont you make a standard format payload like a JSON string?

Here are some examples of how you might handle this better...

image
image1015Ă—632 113 KB

[{"id":"47c5c37b.bf406c","type":"function","z":"d1c281e9.1a622","name":"Generate MQTT","func":"msg.topic = msg.device + '/item_inside'\nmsg.payload = msg.item + '\\n' + msg.serial\nreturn msg","outputs":1,"noerr":0,"x":820,"y":220,"wires":[["88acd1b6.38813"]]},{"id":"214fa04b.3b8ea","type":"change","z":"d1c281e9.1a622","name":"set flow DBdata[0] to device","rules":[{"t":"set","p":"DBdata[0]","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":320,"y":40,"wires":[["c4cdf377.692e4"]]},{"id":"c4765746.339818","type":"change","z":"d1c281e9.1a622","name":"set flow DBdata[1] to item","rules":[{"t":"set","p":"DBdata[1]","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":310,"y":80,"wires":[["da7c5ebb.2ee44"]]},{"id":"3b6cfce.1ade004","type":"change","z":"d1c281e9.1a622","name":"set flow DBdata[2] to serial","rules":[{"t":"set","p":"DBdata[2]","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":320,"y":120,"wires":[["cca7cfa4.60323"]]},{"id":"2aa775db.a475ea","type":"change","z":"d1c281e9.1a622","name":"Copy flow.DBdata to msg payload","rules":[{"t":"set","p":"payload","pt":"msg","to":"DBdata","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":320,"y":220,"wires":[["9a443611.8bf878"]]},{"id":"88acd1b6.38813","type":"debug","z":"d1c281e9.1a622","name":"mqtt msg","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":920,"y":260,"wires":[]},{"id":"56ec0df9.edb014","type":"inject","z":"d1c281e9.1a622","name":"inject item","topic":"","payload":"item1","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":80,"wires":[["c4765746.339818"]]},{"id":"3c6e6c81.5a99c4","type":"inject","z":"d1c281e9.1a622","name":"inject serial","topic":"","payload":"1234","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":120,"wires":[["3b6cfce.1ade004"]]},{"id":"43355e54.2adf6","type":"inject","z":"d1c281e9.1a622","name":"inject device","topic":"","payload":"device1","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":110,"y":40,"wires":[["214fa04b.3b8ea"]]},{"id":"c4cdf377.692e4","type":"debug","z":"d1c281e9.1a622","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":570,"y":40,"wires":[]},{"id":"da7c5ebb.2ee44","type":"debug","z":"d1c281e9.1a622","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":570,"y":80,"wires":[]},{"id":"cca7cfa4.60323","type":"debug","z":"d1c281e9.1a622","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":570,"y":120,"wires":[]},{"id":"5e70ae91.f7f8f","type":"inject","z":"d1c281e9.1a622","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":220,"wires":[["2aa775db.a475ea"]]},{"id":"9a443611.8bf878","type":"function","z":"d1c281e9.1a622","name":"Generate SQL","func":"//add the values to the msg so they can be debugged and used later\nmsg.device = msg.payload[0]\nmsg.item = msg.payload[1]\nmsg.serial = msg.payload[2]\n\nmsg.topic=`REPLACE INTO pack_to_light (Device,Item,Serial) VALUES ('${msg.device}','${msg.item}',${msg.serial})`;\nreturn msg;","outputs":1,"noerr":0,"x":580,"y":220,"wires":[["1502e333.f0339d","47c5c37b.bf406c"]]},{"id":"1502e333.f0339d","type":"debug","z":"d1c281e9.1a622","name":"to MySQL","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":600,"y":260,"wires":[]},{"id":"ddf06b7d.bda9f8","type":"function","z":"d1c281e9.1a622","name":"Generate MQTT topic+payload","func":"msg.topic = msg.device + '/item_inside'\nmsg.payload = {\n    \"item\": msg.item,\n    \"serial\": msg.serial\n}\nreturn msg","outputs":1,"noerr":0,"x":830,"y":400,"wires":[["c56a9151.9378c"]]},{"id":"b036bd49.42f4a","type":"change","z":"d1c281e9.1a622","name":"Copy flow.DBdata to msg payload","rules":[{"t":"set","p":"payload","pt":"msg","to":"DBdata","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":320,"y":400,"wires":[["791250fa.ae45c"]]},{"id":"c56a9151.9378c","type":"debug","z":"d1c281e9.1a622","name":"mqtt msg","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":920,"y":440,"wires":[]},{"id":"f221010f.080e1","type":"inject","z":"d1c281e9.1a622","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":400,"wires":[["b036bd49.42f4a"]]},{"id":"4bfe3904.bbbd48","type":"comment","z":"d1c281e9.1a622","name":"Better payload for MQTT","info":"","x":130,"y":360,"wires":[]},{"id":"265bfc79.fefbd4","type":"change","z":"d1c281e9.1a622","name":"Copy flow.DBdata to msg payload","rules":[{"t":"set","p":"payload","pt":"msg","to":"DBdata","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":320,"y":580,"wires":[["c809aea8.0bcad"]]},{"id":"75f6b254.81bc3c","type":"debug","z":"d1c281e9.1a622","name":"mqtt msg","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":920,"y":620,"wires":[]},{"id":"ee16bb7b.89a278","type":"inject","z":"d1c281e9.1a622","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":580,"wires":[["265bfc79.fefbd4"]]},{"id":"c809aea8.0bcad","type":"function","z":"d1c281e9.1a622","name":"Generate SQL","func":"//add the values to the msg so they can be debugged and used later\nmsg.device = msg.payload[0]\nmsg.item = msg.payload[1]\nmsg.serial = msg.payload[2]\n\nmsg.topic=`REPLACE INTO pack_to_light (Device,Item,Serial) VALUES ('${msg.device}','${msg.item}',${msg.serial})`;\nreturn msg;","outputs":1,"noerr":0,"x":580,"y":580,"wires":[["f711fda2.26e48","9d1b4b91.b6dc68"]]},{"id":"f711fda2.26e48","type":"debug","z":"d1c281e9.1a622","name":"to MySQL","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":600,"y":620,"wires":[]},{"id":"b9490f0c.e93e6","type":"comment","z":"d1c281e9.1a622","name":"Better payload for MQTT (alt version)","info":"","x":170,"y":540,"wires":[]},{"id":"8db7f44a.6abd18","type":"comment","z":"d1c281e9.1a622","name":"Original version - generates a string payload with item+newline+serialdata (exept it now works)","info":"","x":340,"y":180,"wires":[]},{"id":"791250fa.ae45c","type":"function","z":"d1c281e9.1a622","name":"Generate SQL","func":"//add the values to the msg so they can be debugged and used later\nmsg.device = msg.payload[0]\nmsg.item = msg.payload[1]\nmsg.serial = msg.payload[2]\n\nmsg.topic=`REPLACE INTO pack_to_light (Device,Item,Serial) VALUES ('${msg.device}','${msg.item}',${msg.serial})`;\nreturn msg;","outputs":1,"noerr":0,"x":580,"y":400,"wires":[["ddf06b7d.bda9f8","14b0bafb.9fbb75"]]},{"id":"14b0bafb.9fbb75","type":"debug","z":"d1c281e9.1a622","name":"to MySQL","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":600,"y":440,"wires":[]},{"id":"9d1b4b91.b6dc68","type":"template","z":"d1c281e9.1a622","name":"generate topic+payload","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"{\n \"topic\": \"{{device}}/item_inside\",\n \"item\": \"{{item}}\",\n \"serial\": \"{{serial}}\"\n}","output":"json","x":810,"y":580,"wires":[["75f6b254.81bc3c"]]}]
6

First of all, thank you very much for all that usefull information! I have impplemented the code using 2nd method that you suggested and there is one problem:

[{"id":"53737866.f6d2f8","type":"change","z":"df177cb8.27882","name":"set flow DBdata[0] to device","rules":[{"t":"set","p":"DBdata[0]","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":440,"y":4200,"wires":[["7b37c86f.595188"]]},{"id":"c9bae84f.bdc658","type":"change","z":"df177cb8.27882","name":"set flow DBdata[1] to item","rules":[{"t":"set","p":"DBdata[1]","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":430,"y":4240,"wires":[["ba2db9fe.7b4078"]]},{"id":"871f3c3c.26a34","type":"change","z":"df177cb8.27882","name":"set flow DBdata[2] to serial","rules":[{"t":"set","p":"DBdata[2]","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":440,"y":4280,"wires":[["73bfd965.d7d0c8"]]},{"id":"493605a7.0abb8c","type":"inject","z":"df177cb8.27882","name":"inject item","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"item1","payloadType":"str","x":220,"y":4240,"wires":[["c9bae84f.bdc658"]]},{"id":"725d4eeb.e0589","type":"inject","z":"df177cb8.27882","name":"inject serial","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"1234","payloadType":"str","x":220,"y":4280,"wires":[["871f3c3c.26a34"]]},{"id":"3e00e40b.9964ec","type":"inject","z":"df177cb8.27882","name":"inject device","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"device1","payloadType":"str","x":230,"y":4200,"wires":[["53737866.f6d2f8"]]},{"id":"7b37c86f.595188","type":"debug","z":"df177cb8.27882","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":690,"y":4200,"wires":[]},{"id":"ba2db9fe.7b4078","type":"debug","z":"df177cb8.27882","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":690,"y":4240,"wires":[]},{"id":"73bfd965.d7d0c8","type":"debug","z":"df177cb8.27882","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":690,"y":4280,"wires":[]},{"id":"f6e5d3ab.33ca8","type":"change","z":"df177cb8.27882","name":"Copy flow.DBdata to msg payload","rules":[{"t":"set","p":"payload","pt":"msg","to":"DBdata","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":4580,"wires":[["4da6f609.805868"]]},{"id":"9fba4215.77f5","type":"debug","z":"df177cb8.27882","name":"mqtt msg","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":1060,"y":4640,"wires":[]},{"id":"d67a6f7e.751a2","type":"inject","z":"df177cb8.27882","name":"","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":260,"y":4580,"wires":[["f6e5d3ab.33ca8"]]},{"id":"4da6f609.805868","type":"function","z":"df177cb8.27882","name":"Generate SQL","func":"//add the values to the msg so they can be debugged and used later\nmsg.device = msg.payload[0]\nmsg.item = msg.payload[1]\nmsg.serial = msg.payload[2]\n\nmsg.topic=`REPLACE INTO pack_to_light (Device,Item,Serial) VALUES ('${msg.device}','${msg.item}',${msg.serial})`;\nreturn msg;","outputs":1,"noerr":0,"x":740,"y":4580,"wires":[["31001d56.889392","1ad51e10.441682","bf0dcfc7.b93f6"]]},{"id":"5714a058.0dd6","type":"comment","z":"df177cb8.27882","name":"Better payload for MQTT (alt version)","info":"","x":330,"y":4540,"wires":[]},{"id":"31001d56.889392","type":"debug","z":"df177cb8.27882","name":"to MySQL","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":740,"y":4640,"wires":[]},{"id":"1ad51e10.441682","type":"mysql","z":"df177cb8.27882","mydb":"97be0e96.67231","name":"Add item to database","x":740,"y":4500,"wires":[[]]},{"id":"c54728e8.ed8888","type":"mqtt out","z":"df177cb8.27882","name":"","topic":"","qos":"1","retain":"true","broker":"2727c5a5.a4fb6a","x":1110,"y":4520,"wires":[]},{"id":"bf0dcfc7.b93f6","type":"function","z":"df177cb8.27882","name":"Generate MQTT topic+payload","func":"msg.topic = msg.device + '/item_inside'\nmsg.payload = {\n    \"item\": msg.item,\n    \"serial\": msg.serial\n}\nreturn msg","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1010,"y":4580,"wires":[["c54728e8.ed8888","9fba4215.77f5"]]},{"id":"4d5a0219.2d805c","type":"mqtt in","z":"df177cb8.27882","name":"","topic":"device1/item_inside","qos":"2","datatype":"auto","broker":"2727c5a5.a4fb6a","x":870,"y":4740,"wires":[["754ddddf.a08974"]]},{"id":"754ddddf.a08974","type":"ui_text","z":"df177cb8.27882","group":"1184af58.d2f001","order":7,"width":0,"height":0,"name":"","label":"Item inside","format":"{{msg.payload}}","layout":"row-spread","x":1070,"y":4740,"wires":[]},{"id":"97be0e96.67231","type":"MySQLdatabase","z":"","name":"","host":"localhost","port":"3306","db":"test","tz":""},{"id":"2727c5a5.a4fb6a","type":"mqtt-broker","z":"","name":"","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"workshop/status","birthQos":"1","birthRetain":"true","birthPayload":"online","closeTopic":"workshop/status","closeQos":"1","closeRetain":"true","closePayload":"offline","willTopic":"workshop/status","willQos":"1","willRetain":"true","willPayload":"disconnected"},{"id":"1184af58.d2f001","type":"ui_group","z":"","name":"Box_1","tab":"cc84c991.418318","order":1,"disp":true,"width":"8","collapse":false},{"id":"cc84c991.418318","type":"ui_tab","z":"","name":"Control","icon":"dashboard","disabled":false,"hidden":false}]

Ideally, I would like to display on UI what is the current Item and Serial assigned to a certain device. I just subscribe to mqtt topic and dispaly it with text node and it seems to work fine, however, the format at which it displays the data is not quite right:

2020-07-21-113835_1920x1080_scrot
2020-07-21-113835_1920x1080_scrot1920Ă—1080 114 KB
2020-07-21-113825_1920x1080_scrot
2020-07-21-113825_1920x1080_scrot1920Ă—1080 216 KB

Would it be possible to display the data like that:
Item: Item1
Serial: 1234

7

you can format it in the text node
image

or in a template node...

image
image776Ă—332 20 KB

8

None of these work. It does not print anything, just returns blank space. Probably because it is just subscribing to mqtt topic therefore it prints only exactly what i publish.

Maybe I need to format mqtt publish node instead?

9

Did you verify (by putting a debug before the template) that the msg going into the template actually has payload.item and payload.serial properties?

10

2020-07-21-123535_1920x1080_scrot
2020-07-21-123535_1920x1080_scrot1920Ă—1080 263 KB

As you can see from debug messages, it returns empty Serial: and Item:

11

Also, you might need to set the MQTT in node to ...
image
so that you can access msg.payload.item etc

It seems you are missing some of the more basic knowledge of node-red (no offence intended)

I recommend watching this playlist: Node-RED Essentials. It is by the developers of node-red. They're nice & short and to the point. You will understand a whole lot more in less than an hour. A small investment for a lot of gain.

12

Yes, from the picute above, you can see that ITEM INSIDE node has payload data item and serial

13

Oh that makes sense.. I will watch that now. Thanks for the help anyways!

14

image

so set the MQTT to parse JSON or use a JSON node before the template

15

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