If map is not a function then your payload is not an array.
So what steps are you going to take to debug this?
I had tried both methods, but still not working. Not sure what do next to make this work.
You need to check what your msg.payload contains. What node lets you examine a message?
1 Like
Hi Nick,
I added a Debug node after the Change node. This is what I can see after I deployed the flow:
How to make the dropdown working?
Thank for helping.
Hello, can anybody give me the export of the current state? It would be very grateful
cheers 
You have a different SQL query there compared to the previous screenshot.
You objects have properties locID and location. Previously you had id and location.
With these new property names, the Change node expression would be:
$.payload.{location:locID}I already did it, but still the error coming up.
@rishanaziz in which case the message you have shown us in Debug is not the message you are passing the Change node.
Can you show us a screenshot of your flow that shows the sql node, debug node and change node?
You will have to narrow it down a lot. I don't see an SQL node there, so where is the query being done? Which are the relevant nodes here? Which Debug message is coming from which Debug node?
Your screenshot of the Debug sidebar now has two different messages - one of which is a number and one is an array. My guess is you are passing the message which is just a number to the Change node - which is why it doesn't work.
But until you explain your flow and highlight the relevant parts to us, its impossible for us to help.
I linked the SQL node to another tab. Here is the tab:
You still need to answer my questions if you want any more help.
Until you can show which part of your flow we should be looking at, which debug message came from which debug node and why you appear to have a debug message which a number payload rather than the expected array, then there's little more we can do.
Hi Nick,
I had updated the flow as shown below:
The following are the results that I'm getting from debug:
I'm able to display dropdown option from database data. However, I'm unable to insert the data because of the ER_WRONG_VALUE_COUNT_ON_ROW error. Kindly help to see what's wrong with it. I'm also having the doubt with the join and switch nodes. Maybe something we need to do there as well.
Here's the flow code:
[{"id":"a181134e.edb82","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"machineCode","label":"Machine Code","group":"8a70bacc.615718","order":1,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"machineCode","x":965,"y":60,"wires":[["f402e4c4.c30a18"]]},{"id":"6af9ae98.a40c8","type":"change","z":"9f52e83b.5b44f8","name":"clear-text-inputs","rules":[{"t":"set","p":"clear","pt":"msg","to":"true","tot":"bool"}],"action":"","property":"","from":"","to":"","reg":false,"x":605,"y":480,"wires":[["a181134e.edb82","415c3e3d.03a44","4af2c428.57646c","63dff417.6b7acc","e194e211.f2034","9bfe9fb9.48b82","8da75106.5596d","437a5349.f6673c","b4bd6e0d.d04eb","ab81ae8d.e9876","ed291fe8.d0053","e50970b9.13ec3","2c63516b.b8ccbe","484de85e.7f3b28","8cfa1f9b.b4f82"]]},{"id":"4ef40af8.594684","type":"change","z":"9f52e83b.5b44f8","name":"clear-dropdown","rules":[{"t":"set","p":"payload","pt":"msg","to":"false","tot":"bool"}],"action":"","property":"","from":"","to":"","reg":false,"x":605,"y":600,"wires":[["71842900.e5f4d8","e734029b.1a5d8","84e9ac7a.e3e39","b196da07.832eb8","8e404bf4.551de8"]]},{"id":"415c3e3d.03a44","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"description","label":"Description","group":"8a70bacc.615718","order":2,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"description","x":955,"y":105,"wires":[["f402e4c4.c30a18"]]},{"id":"71842900.e5f4d8","type":"mui_dropdown","z":"9f52e83b.5b44f8","name":"location","label":"","place":"Select Location","group":"8a70bacc.615718","order":3,"width":0,"height":0,"passthru":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"location","x":945,"y":150,"wires":[["f402e4c4.c30a18"]]},{"id":"e734029b.1a5d8","type":"mui_dropdown","z":"9f52e83b.5b44f8","name":"type","label":"","place":"Select Type","group":"8a70bacc.615718","order":5,"width":0,"height":0,"passthru":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"type","x":935,"y":240,"wires":[["f402e4c4.c30a18"]]},{"id":"4af2c428.57646c","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"oee","label":"OEE Threshold","group":"c0ad49ec.dff698","order":1,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"oee","x":935,"y":285,"wires":[["f402e4c4.c30a18"]]},{"id":"63dff417.6b7acc","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"ip","label":"IP Address","group":"c0ad49ec.dff698","order":2,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"ip","x":935,"y":330,"wires":[["f402e4c4.c30a18"]]},{"id":"e194e211.f2034","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"port","label":"IP Port","group":"c0ad49ec.dff698","order":3,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"port","x":935,"y":375,"wires":[["f402e4c4.c30a18"]]},{"id":"9bfe9fb9.48b82","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"url","label":"Client URL","group":"c0ad49ec.dff698","order":4,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"url","x":935,"y":420,"wires":[["f402e4c4.c30a18"]]},{"id":"b196da07.832eb8","type":"mui_dropdown","z":"9f52e83b.5b44f8","name":"poll","label":"","place":"Select Poll Rate","group":"18a2002a.8e676","order":1,"width":0,"height":0,"passthru":false,"options":[{"label":"5 seconds","value":5,"type":"num"},{"label":"10 seconds","value":10,"type":"num"},{"label":"15 seconds","value":15,"type":"num"},{"label":"20 seconds","value":20,"type":"num"},{"label":"25 seconds","value":25,"type":"num"},{"label":"30 seconds","value":30,"type":"num"},{"label":"35 seconds","value":35,"type":"num"},{"label":"40 seconds","value":40,"type":"num"},{"label":"45 seconds","value":45,"type":"num"},{"label":"50 seconds","value":50,"type":"num"},{"label":"55 seconds","value":55,"type":"num"},{"label":"60 seconds","value":60,"type":"num"}],"payload":"","topic":"poll","x":935,"y":465,"wires":[["f402e4c4.c30a18"]]},{"id":"8da75106.5596d","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"processCode","label":"Process Code","group":"18a2002a.8e676","order":2,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"processCode","x":955,"y":510,"wires":[["f402e4c4.c30a18"]]},{"id":"437a5349.f6673c","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"srcLocPath","label":"Source Local Path","group":"18a2002a.8e676","order":3,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"srcLocPath","x":955,"y":555,"wires":[["f402e4c4.c30a18"]]},{"id":"b4bd6e0d.d04eb","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"dstSrvrPath","label":"Destination Server Path","group":"18a2002a.8e676","order":4,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"dstSrvrPath","x":955,"y":600,"wires":[["f402e4c4.c30a18"]]},{"id":"ab81ae8d.e9876","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"fileExecSrvr","label":"File Execute Server","group":"18a2002a.8e676","order":5,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"fileExecSrvr","x":955,"y":645,"wires":[["f402e4c4.c30a18"]]},{"id":"ed291fe8.d0053","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"fileExecLoc","label":"File Execute Local","group":"7feabf50.affdc","order":1,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"fileExecLoc","x":955,"y":690,"wires":[["f402e4c4.c30a18"]]},{"id":"e50970b9.13ec3","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"dtSrc1","label":"Data Type Source 1","group":"7feabf50.affdc","order":2,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"dtSrc1","x":935,"y":735,"wires":[["f402e4c4.c30a18"]]},{"id":"2c63516b.b8ccbe","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"dtSrc2","label":"Data Type Source 2","group":"7feabf50.affdc","order":3,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"dtSrc2","x":935,"y":780,"wires":[["f402e4c4.c30a18"]]},{"id":"84e9ac7a.e3e39","type":"mui_dropdown","z":"9f52e83b.5b44f8","name":"owner","label":"","place":"Select Owner","group":"c0ad49ec.dff698","order":5,"width":0,"height":0,"passthru":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"owner","x":935,"y":825,"wires":[["f402e4c4.c30a18"]]},{"id":"484de85e.7f3b28","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"to","label":"Alert to","group":"7feabf50.affdc","order":4,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"to","x":935,"y":870,"wires":[["f402e4c4.c30a18"]]},{"id":"8cfa1f9b.b4f82","type":"mui_text_input","z":"9f52e83b.5b44f8","name":"cc","label":"Alert cc","group":"7feabf50.affdc","order":5,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"cc","x":935,"y":915,"wires":[["f402e4c4.c30a18"]]},{"id":"6aae142b.83450c","type":"mui_button","z":"9f52e83b.5b44f8","name":"saveButton","group":"8dbe39c3.7d0808","order":1,"width":0,"height":0,"passthru":false,"label":"Save","color":"","bgcolor":"","icon":"","payload":"{}","payloadType":"global","topic":"saveButton","x":955,"y":960,"wires":[["f402e4c4.c30a18"]]},{"id":"f402e4c4.c30a18","type":"join","z":"9f52e83b.5b44f8","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"20","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":1235,"y":480,"wires":[["f3855113.80ed"]]},{"id":"f3855113.80ed","type":"switch","z":"9f52e83b.5b44f8","name":"","property":"topic","propertyType":"msg","rules":[{"t":"eq","v":"saveButton","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":1370,"y":480,"wires":[["bad8dc6a.c75","a9fef1cd.75b25"]]},{"id":"bad8dc6a.c75","type":"function","z":"9f52e83b.5b44f8","name":"select-query","func":"select = {topic: \"SELECT COUNT(*) AS count FROM machine2 WHERE name='\" + msg.payload.machine_code + \"'\"};\nreturn select;","outputs":1,"noerr":0,"x":1525,"y":480,"wires":[["358d7577.50367a"]]},{"id":"59eafeac.7ebf5","type":"switch","z":"9f52e83b.5b44f8","name":"","property":"payload[0].count","propertyType":"msg","rules":[{"t":"eq","v":"0","vt":"num"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":1370,"y":630,"wires":[["a9fef1cd.75b25"],["6537be1e.b9685"]]},{"id":"a9fef1cd.75b25","type":"join","z":"9f52e83b.5b44f8","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":1505,"y":555,"wires":[["c3cd55f7.ab90b8"]]},{"id":"6537be1e.b9685","type":"function","z":"9f52e83b.5b44f8","name":"failure-msg","func":"actionString = \"Data insertion failed!\";\nmsg.payload = actionString;\nreturn msg;","outputs":1,"noerr":0,"x":1690,"y":660,"wires":[["5cea048d.7a85cc"]]},{"id":"c3cd55f7.ab90b8","type":"function","z":"9f52e83b.5b44f8","name":"insert-query","func":"var machineCode = {payload: (msg.payload.saveButton.machineCode)};\nvar description = {payload: (msg.payload.saveButton.description)};\nvar location = {payload: (msg.payload.saveButton.location)};\nvar bu = {payload: (msg.payload.saveButton.bu)};\nvar oee = {payload: (msg.payload.saveButton.oee)};\nvar type = {payload: (msg.payload.saveButton.type)};\nvar ip = {payload: (msg.payload.saveButton.ip)};\nvar port = {payload: (msg.payload.saveButton.port)};\nvar url = {payload: (msg.payload.saveButton.url)};\nvar poll = {payload: (msg.payload.saveButton.poll)};\nvar processCode = {payload: (msg.payload.saveButton.processCode)};\n\nvar srcLocPath = {payload: (msg.payload.saveButton.srcLocPath)};\nvar dstSrvrPath = {payload: (msg.payload.saveButton.dstSrvrPath)};\nvar fileExecSrvr = {payload: (msg.payload.saveButton.fileExecSrvr)};\nvar fileExecLoc = {payload: (msg.payload.saveButton.fileExecLoc)};\nvar dtSrc1 = {payload: (msg.payload.saveButton.dtSrc1)};\nvar dtSrc2 = {payload: (msg.payload.saveButton.dtSrc2)};\n\nvar owner = {payload: (msg.payload.saveButton.owner)};\nvar to = {payload: (msg.payload.saveButton.to)};\nvar cc = {payload: (msg.payload.saveButton.cc)};\n\ninsert = {topic: \"INSERT INTO machine2 (name,description,location,bu,oee,type,ip_address,clienturl,process_code,source_local_path,dest_server_path,file_execute_server,file_execute_local,data_type_source1,data_type_source2,owner_id,alert_to,created_at) VALUES ('\" + machineCode.payload + \"','\" + description.payload + \"','\" + location.payload + \"','\" + bu.payload + \"','\" + oee.payload + \"','\" + type.payload + \"','\" + ip.payload + \"','\" + port.payload + \"','\" + poll.payload + \"','\" + srcLocPath.payload + \"','\" + dstSrvrPath.payload + \"','\" + fileExecSrvr.payload + \"','\" + dtSrc1.payload + \"','\" + owner.payload + \"','\" + cc.payload + \"',CURRENT_TIMESTAMP)\"};\n\nactionString = {payload: (\"Data inserted successfully!\")};\nreturn [insert,actionString];","outputs":2,"noerr":0,"x":1690,"y":555,"wires":[["92d1bf33.288d4"],["5cea048d.7a85cc"]]},{"id":"8e404bf4.551de8","type":"mui_dropdown","z":"9f52e83b.5b44f8","name":"bu","label":"","place":"Select Business Unit","group":"8a70bacc.615718","order":4,"width":0,"height":0,"passthru":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"bu","x":935,"y":195,"wires":[["f402e4c4.c30a18"]]},{"id":"487b740c.f6ff1c","type":"mui_button","z":"9f52e83b.5b44f8","name":"clearButton","group":"8dbe39c3.7d0808","order":2,"width":0,"height":0,"passthru":false,"label":"Clear","color":"","bgcolor":"","icon":"","payload":"{}","payloadType":"global","topic":"","x":295,"y":540,"wires":[["6af9ae98.a40c8","4ef40af8.594684"]]},{"id":"79b49b22.1ed3e4","type":"function","z":"9f52e83b.5b44f8","name":"select-loc","func":"msg.topic=\"SELECT id AS locID,location FROM location\";\nreturn msg;","outputs":1,"noerr":0,"x":330,"y":150,"wires":[["f34a78d6.98e6d8"]]},{"id":"5c1a76ee.bf6bb8","type":"inject","z":"9f52e83b.5b44f8","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"onceDelay":0.1,"x":145,"y":225,"wires":[["79b49b22.1ed3e4","3bf95fed.e0e85","13a07979.8b45d7","3984a3ca.b8367c"]]},{"id":"91327e7.78e0c8","type":"change","z":"9f52e83b.5b44f8","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"$.payload.{location:locID}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":680,"y":150,"wires":[["71842900.e5f4d8"]]},{"id":"13a07979.8b45d7","type":"function","z":"9f52e83b.5b44f8","name":"select-bu","func":"msg.topic=\"SELECT unit,description FROM business_unit\";\nreturn msg;","outputs":1,"noerr":0,"x":330,"y":195,"wires":[["a190b68f.0153c8"]]},{"id":"9b34957c.708a08","type":"change","z":"9f52e83b.5b44f8","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"$.payload.{description:unit}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":680,"y":195,"wires":[["8e404bf4.551de8"]]},{"id":"3984a3ca.b8367c","type":"function","z":"9f52e83b.5b44f8","name":"select-type","func":"msg.topic=\"SELECT id as typeID, CONCAT(name,' | ',description) AS typeDesc FROM machine_type\";\nreturn msg;","outputs":1,"noerr":0,"x":325,"y":240,"wires":[["13e36aac.6e4785"]]},{"id":"373bee74.9cd492","type":"change","z":"9f52e83b.5b44f8","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"$.payload.{typeDesc:typeID}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":680,"y":240,"wires":[["e734029b.1a5d8"]]},{"id":"3bf95fed.e0e85","type":"function","z":"9f52e83b.5b44f8","name":"select-owner","func":"msg.topic=\"SELECT id AS userID,name as username FROM users\";\nreturn msg;","outputs":1,"noerr":0,"x":325,"y":285,"wires":[["2575ee4a.2c8e22"]]},{"id":"d8f8d333.cb6ff","type":"change","z":"9f52e83b.5b44f8","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"$.payload.{username:userID}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":680,"y":285,"wires":[["84e9ac7a.e3e39"]]},{"id":"5cea048d.7a85cc","type":"debug","z":"9f52e83b.5b44f8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1870,"y":615,"wires":[]},{"id":"f34a78d6.98e6d8","type":"mysql","z":"9f52e83b.5b44f8","mydb":"24d38c58.3fc104","name":"","x":500,"y":150,"wires":[["91327e7.78e0c8","6616b82.d677e48"]]},{"id":"a190b68f.0153c8","type":"mysql","z":"9f52e83b.5b44f8","mydb":"24d38c58.3fc104","name":"","x":500,"y":195,"wires":[["9b34957c.708a08","6616b82.d677e48"]]},{"id":"13e36aac.6e4785","type":"mysql","z":"9f52e83b.5b44f8","mydb":"24d38c58.3fc104","name":"","x":500,"y":240,"wires":[["373bee74.9cd492","6616b82.d677e48"]]},{"id":"2575ee4a.2c8e22","type":"mysql","z":"9f52e83b.5b44f8","mydb":"24d38c58.3fc104","name":"","x":500,"y":285,"wires":[["d8f8d333.cb6ff","6616b82.d677e48"]]},{"id":"6616b82.d677e48","type":"debug","z":"9f52e83b.5b44f8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":670,"y":360,"wires":[]},{"id":"358d7577.50367a","type":"mysql","z":"9f52e83b.5b44f8","mydb":"24d38c58.3fc104","name":"","x":1670,"y":480,"wires":[["59eafeac.7ebf5"]]},{"id":"92d1bf33.288d4","type":"mysql","z":"9f52e83b.5b44f8","mydb":"24d38c58.3fc104","name":"","x":1850,"y":495,"wires":[[]]},{"id":"8a70bacc.615718","type":"mui_group","z":"","name":"mGroup 1","tab":"d4e8d996.700768","order":1,"disp":false,"width":"6","collapse":false},{"id":"c0ad49ec.dff698","type":"mui_group","z":"","name":"mGroup 2","tab":"d4e8d996.700768","order":2,"disp":false,"width":"6","collapse":false},{"id":"18a2002a.8e676","type":"mui_group","z":"","name":"mGroup 3","tab":"d4e8d996.700768","order":3,"disp":false,"width":"6","collapse":false},{"id":"7feabf50.affdc","type":"mui_group","z":"","name":"mGroup 4","tab":"d4e8d996.700768","order":4,"disp":false,"width":"6","collapse":false},{"id":"8dbe39c3.7d0808","type":"mui_group","z":"","name":"mGroup 5","tab":"d4e8d996.700768","order":5,"disp":false,"width":"6","collapse":false},{"id":"24d38c58.3fc104","type":"MySQLdatabase","z":"","host":"10.21.11.15","port":"3306","db":"i4inari","tz":""},{"id":"d4e8d996.700768","type":"mui_tab","z":"","name":"I4Inari : Machine Management ","icon":"dashboard","order":3}]
Hope to hear from you soon.
Thanks for helping.
so you get an the error ER_WRONG_VALUE_COUNT_ON_ROW -
- how many columns do you have in the table
- how many of those columns do you specify in the INSERT?
- how many items do you have in the 'VALUES' clause?
I would suggest that the number of columns does not match the number of values hence the error. I would add a debug node to the output of the node you use to create the INSERT statement and check it out.
- How many columns do you have in the table?: More than 20 columns
- How many of those columns do you specify in the INSERT? 20 columns
- How many items do you have in the 'VALUES' clause?: 20 items
Okay, I'll try it out.
You need to look at a debug node showing the insert command as that is the command that is failing.
Hi Colin,
I already shown them on my previous reply. You need to click on the debug image to see the results.
I don't see an INSERT statement there, all I see is SELECTs, then a message saying an insert succeeded then the error.
To be really clear: to debug this error we need to see the complete insert statement you are passing the SQL node that leads to this error.
The error tells us you have a different number of columns and values in the statement. Armed with that information you should be able to look at your insert statement and fix it.
Ok, I don't eleive this is from an insert. If you look at the debug log you provide, you see the message "Data inserted sucessfully' followed by the ER_WRONG_VALUE_COUNT_ON_ROW. If you look at the node id (end of first line that reads 8/20/2018, 10:51:39 AM node: 92d1bf33:288d4 that is the node displaying this error
Here is what I would do: add a seperate debug node to each function that creates a query and add a seperate debug nodt to the output of each database node. Then run the code again and you should be able to narrow down where the error is actually coming from.
