Creating form then logging entry in SQL

General
1

Hello everyone, I'm trying to create an entry form check below, flow can anyone help on how to establish logging in SQL or is there a better way to do it. Also, my codding skill is equivalent to an infant :baby_bottle: :baby:. Sharing any link of previous similar discussion forum might also help.

[{"id":"684eeca3.6ebfa4","type":"group","z":"1c1bd358.aeecad","style":{"stroke":"#999999","stroke-opacity":"1","fill":"none","fill-opacity":"1","label":true,"label-position":"nw","color":"#a4a4a4"},"nodes":["b8ea7b2e.4395b8","b0f6b89b.8d3728","bd353083.7b433","89ba651b.42d578","bdce8500.698648","5f66099.6c679f8","f66a0f25.cb699","1e86fa71.3dc8a6","6d27b917.f6a648","21609de6.d84ce2","fa11d581.03eec8"],"x":814,"y":39,"w":652,"h":402},{"id":"b8ea7b2e.4395b8","type":"ui_dropdown","z":"1c1bd358.aeecad","g":"684eeca3.6ebfa4","name":"M/C Location","label":"Accident Location","tooltip":"Accident Location","place":"Select Location","group":"685dc7dd.443798","order":4,"width":6,"height":1,"passthru":true,"multiple":false,"options":[{"label":"Shed 1","value":"1","type":"str"},{"label":"Shed 2","value":"2","type":"str"},{"label":"Shed 3","value":"3","type":"str"},{"label":"Shed 4","value":"4","type":"str"}],"payload":"","topic":"topic","topicType":"msg","x":950,"y":200,"wires":[["21609de6.d84ce2"]]},{"id":"b0f6b89b.8d3728","type":"ui_dropdown","z":"1c1bd358.aeecad","g":"684eeca3.6ebfa4","name":"Machine category","label":"","tooltip":"","place":"Machine category","group":"685dc7dd.443798","order":6,"width":6,"height":1,"passthru":true,"multiple":false,"options":[{"label":"CNC","value":"1","type":"str"},{"label":"VMC","value":"2","type":"str"},{"label":"Boltmaker","value":"3","type":"str"},{"label":"Header","value":"4","type":"str"}],"payload":"","topic":"topic","topicType":"msg","x":930,"y":280,"wires":[["21609de6.d84ce2"]]},{"id":"bd353083.7b433","type":"ui_text_input","z":"1c1bd358.aeecad","g":"684eeca3.6ebfa4","name":"machine number","label":"Machine Number","tooltip":"Machine Number","group":"685dc7dd.443798","order":7,"width":6,"height":1,"passthru":true,"mode":"number","delay":300,"topic":"topic","topicType":"msg","x":930,"y":320,"wires":[["21609de6.d84ce2"]]},{"id":"89ba651b.42d578","type":"ui_dropdown","z":"1c1bd358.aeecad","g":"684eeca3.6ebfa4","name":"Affected","label":"Affected","tooltip":"Affected","place":"Select ","group":"685dc7dd.443798","order":5,"width":6,"height":1,"passthru":true,"multiple":true,"options":[{"label":"Man","value":"1","type":"str"},{"label":"Machine","value":"2","type":"str"}],"payload":"","topic":"topic","topicType":"msg","x":960,"y":240,"wires":[["21609de6.d84ce2"]]},{"id":"bdce8500.698648","type":"ui_text_input","z":"1c1bd358.aeecad","g":"684eeca3.6ebfa4","name":"Description","label":"Description (if any)","tooltip":"Description (if any)","group":"685dc7dd.443798","order":9,"width":12,"height":3,"passthru":true,"mode":"text","delay":300,"topic":"topic","topicType":"msg","x":950,"y":360,"wires":[["21609de6.d84ce2"]]},{"id":"5f66099.6c679f8","type":"ui_button","z":"1c1bd358.aeecad","g":"684eeca3.6ebfa4","name":"","group":"685dc7dd.443798","order":10,"width":4,"height":1,"passthru":false,"label":"Submit","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":960,"y":400,"wires":[["21609de6.d84ce2"]]},{"id":"f66a0f25.cb699","type":"ui_multistate_switch","z":"1c1bd358.aeecad","g":"684eeca3.6ebfa4","name":"Accident category","group":"685dc7dd.443798","order":1,"width":12,"height":1,"label":"Accident category","options":[{"label":"Major","value":"option_0","valueType":"str"},{"label":"Minor","value":"option_1","valueType":"str"},{"label":"First Aid","value":"option_2","valueType":"str"}],"x":930,"y":80,"wires":[["21609de6.d84ce2"]]},{"id":"1e86fa71.3dc8a6","type":"ui_date_picker","z":"1c1bd358.aeecad","g":"684eeca3.6ebfa4","name":"Occurance Date","label":"Occurance Date","group":"685dc7dd.443798","order":2,"width":8,"height":1,"passthru":true,"topic":"topic","topicType":"msg","x":940,"y":120,"wires":[["21609de6.d84ce2"]]},{"id":"6d27b917.f6a648","type":"ui_text_input","z":"1c1bd358.aeecad","g":"684eeca3.6ebfa4","name":"Occurance Time","label":"Occurance Time","tooltip":"Occurance Time","group":"685dc7dd.443798","order":3,"width":4,"height":1,"passthru":true,"mode":"time","delay":300,"topic":"topic","topicType":"msg","x":940,"y":160,"wires":[["21609de6.d84ce2"]]},{"id":"21609de6.d84ce2","type":"join","z":"1c1bd358.aeecad","g":"684eeca3.6ebfa4","name":"","mode":"auto","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":"false","timeout":"","count":"","reduceRight":false,"x":1170,"y":240,"wires":[["fa11d581.03eec8"]]},{"id":"fa11d581.03eec8","type":"debug","z":"1c1bd358.aeecad","g":"684eeca3.6ebfa4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1350,"y":240,"wires":[]},{"id":"685dc7dd.443798","type":"ui_group","name":"Accident - Machine / Man","tab":"749760e0.fe809","order":3,"disp":true,"width":"12","collapse":false},{"id":"749760e0.fe809","type":"ui_tab","name":"Accident","icon":"dashboard","order":13,"disabled":false,"hidden":false}]

Screenshot:

screenshot 3
screenshot 3656×562 11.7 KB

2

Hi, you need to join your payloads, I gave each a string topic in the dashboard nodes, these strings should be the names of the columns in your database. I then join node i set to join manually using the msg.topic as the key field.
The function then dynamically creates the sql query using the msg.topic as the column names. I also made the submit button send msg.complete, which then passes on all joined info. The function then makes sure there are 7 fields before continuing.
You will need to make sure the topics are the column names from your db
This is an example and will need more data validation and resetting of form on submission, and some feed back but it hope it moves you forward.

[{"id":"1e86fa71.3dc8a6","type":"ui_date_picker","z":"8e69aac0.f342e8","g":"684eeca3.6ebfa4","name":"Occurance Date","label":"Occurance Date","group":"685dc7dd.443798","order":2,"width":8,"height":1,"passthru":true,"topic":"date","topicType":"str","x":180,"y":220,"wires":[["21609de6.d84ce2"]]},{"id":"21609de6.d84ce2","type":"join","z":"8e69aac0.f342e8","g":"684eeca3.6ebfa4","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":410,"y":340,"wires":[["fa11d581.03eec8","26c1f22b.5b2796"]]},{"id":"b8ea7b2e.4395b8","type":"ui_dropdown","z":"8e69aac0.f342e8","g":"684eeca3.6ebfa4","name":"M/C Location","label":"Accident Location","tooltip":"Accident Location","place":"Select Location","group":"685dc7dd.443798","order":4,"width":6,"height":1,"passthru":true,"multiple":false,"options":[{"label":"Shed 1","value":"1","type":"str"},{"label":"Shed 2","value":"2","type":"str"},{"label":"Shed 3","value":"3","type":"str"},{"label":"Shed 4","value":"4","type":"str"}],"payload":"","topic":"location","topicType":"str","x":190,"y":300,"wires":[["21609de6.d84ce2"]]},{"id":"b0f6b89b.8d3728","type":"ui_dropdown","z":"8e69aac0.f342e8","g":"684eeca3.6ebfa4","name":"Machine category","label":"","tooltip":"","place":"Machine category","group":"685dc7dd.443798","order":6,"width":6,"height":1,"passthru":true,"multiple":false,"options":[{"label":"CNC","value":"1","type":"str"},{"label":"VMC","value":"2","type":"str"},{"label":"Boltmaker","value":"3","type":"str"},{"label":"Header","value":"4","type":"str"}],"payload":"","topic":"category","topicType":"str","x":170,"y":380,"wires":[["21609de6.d84ce2"]]},{"id":"bd353083.7b433","type":"ui_text_input","z":"8e69aac0.f342e8","g":"684eeca3.6ebfa4","name":"machine number","label":"Machine Number","tooltip":"Machine Number","group":"685dc7dd.443798","order":7,"width":6,"height":1,"passthru":true,"mode":"number","delay":300,"topic":"number","topicType":"str","x":170,"y":420,"wires":[["21609de6.d84ce2"]]},{"id":"89ba651b.42d578","type":"ui_dropdown","z":"8e69aac0.f342e8","g":"684eeca3.6ebfa4","name":"Affected","label":"Affected","tooltip":"Affected","place":"Select ","group":"685dc7dd.443798","order":5,"width":6,"height":1,"passthru":true,"multiple":true,"options":[{"label":"Man","value":"1","type":"str"},{"label":"Machine","value":"2","type":"str"}],"payload":"","topic":"affected","topicType":"str","x":200,"y":340,"wires":[["21609de6.d84ce2"]]},{"id":"bdce8500.698648","type":"ui_text_input","z":"8e69aac0.f342e8","g":"684eeca3.6ebfa4","name":"Description","label":"Description (if any)","tooltip":"Description (if any)","group":"685dc7dd.443798","order":9,"width":12,"height":3,"passthru":true,"mode":"text","delay":300,"topic":"description","topicType":"str","x":190,"y":460,"wires":[["21609de6.d84ce2"]]},{"id":"6d27b917.f6a648","type":"ui_text_input","z":"8e69aac0.f342e8","g":"684eeca3.6ebfa4","name":"Occurance Time","label":"Occurance Time","tooltip":"Occurance Time","group":"685dc7dd.443798","order":3,"width":4,"height":1,"passthru":true,"mode":"time","delay":300,"topic":"time","topicType":"str","x":180,"y":260,"wires":[["21609de6.d84ce2"]]},{"id":"fa11d581.03eec8","type":"debug","z":"8e69aac0.f342e8","g":"684eeca3.6ebfa4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":650,"y":260,"wires":[]},{"id":"cdb2605.c4a7fa","type":"change","z":"8e69aac0.f342e8","g":"684eeca3.6ebfa4","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"complete","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":390,"y":500,"wires":[["21609de6.d84ce2"]]},{"id":"26c1f22b.5b2796","type":"function","z":"8e69aac0.f342e8","g":"684eeca3.6ebfa4","name":"","func":"if(Object.values(msg.payload).length === 7){\n    let columns = Object.keys(msg.payload);  // create array of column names\n    let values = Object.values(msg.payload); //create array of values\n    columns = columns.join(\", \"); //join coulmns into string \n    values = \"'\" + values.join(\"', '\") + \"'\"; //join values to string\n    msg.payload = `INSERT INTO tablename ( ${columns}) VALUE (${values});`; //create query\n    return msg;\n}else{\n    return null;\n}","outputs":1,"noerr":0,"initialize":"","finalize":"","x":570,"y":340,"wires":[["fa11d581.03eec8"]]},{"id":"5f66099.6c679f8","type":"ui_button","z":"8e69aac0.f342e8","g":"684eeca3.6ebfa4","name":"","group":"685dc7dd.443798","order":10,"width":4,"height":1,"passthru":false,"label":"Submit","tooltip":"","color":"","bgcolor":"","icon":"","payload":"complete","payloadType":"str","topic":"","topicType":"str","x":200,"y":500,"wires":[["cdb2605.c4a7fa"]]},{"id":"685dc7dd.443798","type":"ui_group","name":"Accident - Machine / Man","tab":"749760e0.fe809","order":3,"disp":true,"width":"12","collapse":false},{"id":"749760e0.fe809","type":"ui_tab","name":"Accident","icon":"dashboard","order":13,"disabled":false,"hidden":false}]
3

@E1cid Thankyou so much, will execute the modified flow.

4

@E1cid , updated flow is perfect. But more help is needed. like after pressing submit button, how to reset all values. Also, with date picker & time node the entered values will be in dd-mm-yyyy & hh:mm format when inserted in SQL?
screenshot 4

5

For date and time use changes node after the date and time pickers, select jsonata expression.
Date expresstion $moment(payload).tz("Europe/London").format("DD-MM-YYYY")
to convert time stamp to string yday month year.
time expression $moment(payload).tz("Europe/London").format("HH:mm")

To reset the values, set the ui nodes to not pass on inputs( its a check box in each ui node). Then after the join node you can feed back into the ui nodes with empty msg.payloads, except the date picker , i think that will require a time stamp other wise it will set to jan 1st 1970.
warning Go with small steps feeding back , make sure pass-through off messages is turned off, or you may end up with endless loops.

6

@E1cid, thanks again.

Updated flow

[{"id":"22c6eefb.5b03b2","type":"ui_dropdown","z":"1c1bd358.aeecad","name":"Accident Category","label":"Accident Category","tooltip":"Accident Category","place":"Select","group":"685dc7dd.443798","order":1,"width":12,"height":1,"passthru":false,"multiple":false,"options":[{"label":"Major","value":"Major","type":"str"},{"label":"Minor","value":"Minor","type":"str"},{"label":"First Aid","value":"First Aid","type":"str"}],"payload":"","topic":"accident category","topicType":"str","x":1210,"y":520,"wires":[["87144c27.b9add"]]},{"id":"e09a2362.2845c","type":"ui_date_picker","z":"1c1bd358.aeecad","name":"Occurance Date","label":"Occurrence Date","group":"685dc7dd.443798","order":2,"width":8,"height":1,"passthru":false,"topic":"Occurrence Date","topicType":"str","x":1220,"y":560,"wires":[["c1363f03.e3a03"]]},{"id":"87144c27.b9add","type":"join","z":"1c1bd358.aeecad","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":1670,"y":680,"wires":[["4cff989c.0d0008","4b858b33.701a94"]]},{"id":"f0a3732e.69bba","type":"ui_dropdown","z":"1c1bd358.aeecad","name":"Accident Location","label":"Accident Location","tooltip":"Accident Location","place":"Select","group":"685dc7dd.443798","order":4,"width":6,"height":1,"passthru":false,"multiple":false,"options":[{"label":"Shed 1","value":"shed 1","type":"str"},{"label":"Shed 2","value":"shed 2","type":"str"},{"label":"Shed 3","value":"shed 3","type":"str"},{"label":"Shed 4","value":"shed 4","type":"str"}],"payload":"","topic":"Machine Location","topicType":"str","x":1210,"y":640,"wires":[["87144c27.b9add"]]},{"id":"a81a5fbf.b29c4","type":"ui_dropdown","z":"1c1bd358.aeecad","name":"Machine category","label":"Machine category","tooltip":"Machine category","place":"Select","group":"685dc7dd.443798","order":6,"width":6,"height":1,"passthru":false,"multiple":false,"options":[{"label":"CNC","value":"CNC","type":"str"},{"label":"VMC","value":"VMC","type":"str"},{"label":"Boltmaker","value":"Boltmaker","type":"str"},{"label":"Header","value":"Header","type":"str"}],"payload":"","topic":"Machine category","topicType":"str","x":1210,"y":720,"wires":[["87144c27.b9add"]]},{"id":"4e47c62e.af45c8","type":"ui_text_input","z":"1c1bd358.aeecad","name":"Machine Number","label":"Machine Number","tooltip":"Machine Number","group":"685dc7dd.443798","order":7,"width":6,"height":1,"passthru":false,"mode":"number","delay":300,"topic":"Machine Number","topicType":"str","x":1210,"y":760,"wires":[["87144c27.b9add"]]},{"id":"e6d8e401.234468","type":"ui_dropdown","z":"1c1bd358.aeecad","name":"Affected","label":"Affected","tooltip":"Affected","place":"Select","group":"685dc7dd.443798","order":5,"width":6,"height":1,"passthru":false,"multiple":true,"options":[{"label":"Man","value":"man","type":"str"},{"label":"Machine","value":"machine","type":"str"}],"payload":"","topic":"affected","topicType":"str","x":1240,"y":680,"wires":[["87144c27.b9add"]]},{"id":"a8fb3401.1f8978","type":"ui_text_input","z":"1c1bd358.aeecad","name":"Description","label":"Description (if any)","tooltip":"Description (if any)","group":"685dc7dd.443798","order":8,"width":12,"height":1,"passthru":false,"mode":"text","delay":300,"topic":"description","topicType":"str","x":1230,"y":800,"wires":[["87144c27.b9add"]]},{"id":"a9a853cf.6da19","type":"ui_text_input","z":"1c1bd358.aeecad","name":"Occurrence Time","label":"Occurrence Time","tooltip":"Occurrence Time","group":"685dc7dd.443798","order":3,"width":4,"height":1,"passthru":false,"mode":"time","delay":300,"topic":"Occurrence Time","topicType":"str","x":1210,"y":600,"wires":[["47a45d10.3d39a4"]]},{"id":"4cff989c.0d0008","type":"debug","z":"1c1bd358.aeecad","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1950,"y":680,"wires":[]},{"id":"9e27aaf7.41fe88","type":"change","z":"1c1bd358.aeecad","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"complete","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1430,"y":840,"wires":[["87144c27.b9add"]]},{"id":"4b858b33.701a94","type":"function","z":"1c1bd358.aeecad","name":"","func":"if(Object.values(msg.payload).length === 8){\n    let columns = Object.keys(msg.payload);  // create array of column names\n    let values = Object.values(msg.payload); //create array of values\n    columns = columns.join(\", \"); //join coulmns into string \n    values = \"'\" + values.join(\"', '\") + \"'\"; //join values to string\n    msg.payload = `INSERT INTO tablename ( ${columns}) VALUE (${values});`; //create query\n    return msg;\n}else{\n    return null;\n}","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1800,"y":700,"wires":[["4cff989c.0d0008"]]},{"id":"91df7889.688798","type":"ui_button","z":"1c1bd358.aeecad","name":"","group":"685dc7dd.443798","order":10,"width":4,"height":1,"passthru":false,"label":"Submit","tooltip":"","color":"","bgcolor":"","icon":"","payload":"complete","payloadType":"str","topic":"","topicType":"str","x":1240,"y":840,"wires":[["9e27aaf7.41fe88","48365d4e.2b62f4"]]},{"id":"48365d4e.2b62f4","type":"change","z":"1c1bd358.aeecad","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"[]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":1420,"y":880,"wires":[["839df5b5.aa4d08"]]},{"id":"839df5b5.aa4d08","type":"delay","z":"1c1bd358.aeecad","name":"","pauseType":"delay","timeout":"2","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":1580,"y":880,"wires":[["58bd3942.265ea8"]]},{"id":"58bd3942.265ea8","type":"link out","z":"1c1bd358.aeecad","name":"","links":["cc3f9b08.808e08"],"x":1675,"y":880,"wires":[]},{"id":"cc3f9b08.808e08","type":"link in","z":"1c1bd358.aeecad","name":"","links":["58bd3942.265ea8"],"x":935,"y":660,"wires":[["22c6eefb.5b03b2","a9a853cf.6da19","f0a3732e.69bba","e6d8e401.234468","a81a5fbf.b29c4","4e47c62e.af45c8","a8fb3401.1f8978","31ad7a06.5d8f86"]]},{"id":"8cb0d4f3.59d548","type":"debug","z":"1c1bd358.aeecad","name":"","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1650,"y":560,"wires":[]},{"id":"c1363f03.e3a03","type":"change","z":"1c1bd358.aeecad","name":"dd-mm-yyyy","rules":[{"t":"set","p":"payload","pt":"msg","to":"$moment(payload).tz(\"Europe/London\").format(\"DD-MM-YYYY\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1410,"y":560,"wires":[["87144c27.b9add","8cb0d4f3.59d548"]]},{"id":"47a45d10.3d39a4","type":"change","z":"1c1bd358.aeecad","name":"hh:mm","rules":[{"t":"set","p":"payload","pt":"msg","to":"$moment(payload).tz(\"Europe/London\").format(\"HH:mm\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1390,"y":600,"wires":[["87144c27.b9add","15030fe0.b2da6"]]},{"id":"15030fe0.b2da6","type":"debug","z":"1c1bd358.aeecad","name":"","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1750,"y":600,"wires":[]},{"id":"31ad7a06.5d8f86","type":"change","z":"1c1bd358.aeecad","name":"timestamp","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"}],"action":"","property":"","from":"","to":"","reg":false,"x":1010,"y":560,"wires":[["e09a2362.2845c"]]},{"id":"685dc7dd.443798","type":"ui_group","name":"Accident - Machine / Man","tab":"749760e0.fe809","order":3,"disp":true,"width":"12","collapse":false},{"id":"749760e0.fe809","type":"ui_tab","name":"Accident","icon":"dashboard","order":13,"disabled":false,"hidden":false}]
7

@E1cid , need help once more. I tried inserting but getting error:

"Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'description' at line 1"
screenshot 8

Flow:

[{"id":"a65faca6.d03ab","type":"group","z":"1c1bd358.aeecad","style":{"stroke":"#999999","stroke-opacity":"1","fill":"none","fill-opacity":"1","label":true,"label-position":"nw","color":"#a4a4a4"},"nodes":["22c6eefb.5b03b2","e09a2362.2845c","87144c27.b9add","f0a3732e.69bba","a81a5fbf.b29c4","4e47c62e.af45c8","e6d8e401.234468","a8fb3401.1f8978","a9a853cf.6da19","4cff989c.0d0008","9e27aaf7.41fe88","4b858b33.701a94","91df7889.688798","48365d4e.2b62f4","839df5b5.aa4d08","58bd3942.265ea8","cc3f9b08.808e08","8cb0d4f3.59d548","c1363f03.e3a03","47a45d10.3d39a4","15030fe0.b2da6","31ad7a06.5d8f86","862b51ab.31225","463cd774.4123e8"],"x":1454,"y":199,"w":1372,"h":442},{"id":"22c6eefb.5b03b2","type":"ui_dropdown","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"Accident Category","label":"Accident Category","tooltip":"Accident Category","place":"Select","group":"685dc7dd.443798","order":1,"width":12,"height":1,"passthru":false,"multiple":false,"options":[{"label":"Major","value":"Major","type":"str"},{"label":"Minor","value":"Minor","type":"str"},{"label":"First Aid","value":"First Aid","type":"str"}],"payload":"","topic":"accident_category","topicType":"str","x":1770,"y":240,"wires":[["87144c27.b9add"]]},{"id":"e09a2362.2845c","type":"ui_date_picker","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"Occurance Date","label":"Occurrence Date","group":"685dc7dd.443798","order":2,"width":8,"height":1,"passthru":false,"topic":"Occurrence_Date","topicType":"str","x":1780,"y":280,"wires":[["c1363f03.e3a03"]]},{"id":"87144c27.b9add","type":"join","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":2230,"y":400,"wires":[["4cff989c.0d0008","4b858b33.701a94"]]},{"id":"f0a3732e.69bba","type":"ui_dropdown","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"Accident Location","label":"Accident Location","tooltip":"Accident Location","place":"Select","group":"685dc7dd.443798","order":4,"width":6,"height":1,"passthru":false,"multiple":false,"options":[{"label":"Shed 1","value":"shed 1","type":"str"},{"label":"Shed 2","value":"shed 2","type":"str"},{"label":"Shed 3","value":"shed 3","type":"str"},{"label":"Shed 4","value":"shed 4","type":"str"}],"payload":"","topic":"Machine_Location","topicType":"str","x":1770,"y":360,"wires":[["87144c27.b9add"]]},{"id":"a81a5fbf.b29c4","type":"ui_dropdown","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"Machine category","label":"Machine category","tooltip":"Machine category","place":"Select","group":"685dc7dd.443798","order":6,"width":6,"height":1,"passthru":false,"multiple":false,"options":[{"label":"CNC","value":"CNC","type":"str"},{"label":"VMC","value":"VMC","type":"str"},{"label":"Boltmaker","value":"Boltmaker","type":"str"},{"label":"Header","value":"Header","type":"str"}],"payload":"","topic":"Machine_category","topicType":"str","x":1770,"y":440,"wires":[["87144c27.b9add"]]},{"id":"4e47c62e.af45c8","type":"ui_text_input","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"Machine Number","label":"Machine Number","tooltip":"Machine Number","group":"685dc7dd.443798","order":7,"width":6,"height":1,"passthru":false,"mode":"number","delay":300,"topic":"Machine_Number","topicType":"str","x":1770,"y":480,"wires":[["87144c27.b9add"]]},{"id":"e6d8e401.234468","type":"ui_dropdown","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"Affected","label":"Affected","tooltip":"Affected","place":"Select","group":"685dc7dd.443798","order":5,"width":6,"height":1,"passthru":false,"multiple":true,"options":[{"label":"Man","value":"man","type":"str"},{"label":"Machine","value":"machine","type":"str"}],"payload":"","topic":"affected","topicType":"str","x":1800,"y":400,"wires":[["87144c27.b9add"]]},{"id":"a8fb3401.1f8978","type":"ui_text_input","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"Description","label":"Description (if any)","tooltip":"Description (if any)","group":"685dc7dd.443798","order":8,"width":12,"height":1,"passthru":false,"mode":"text","delay":300,"topic":"description","topicType":"str","x":1790,"y":520,"wires":[["87144c27.b9add"]]},{"id":"a9a853cf.6da19","type":"ui_text_input","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"Occurrence Time","label":"Occurrence Time","tooltip":"Occurrence Time","group":"685dc7dd.443798","order":3,"width":4,"height":1,"passthru":false,"mode":"time","delay":300,"topic":"Occurrence_Time","topicType":"str","x":1770,"y":320,"wires":[["47a45d10.3d39a4"]]},{"id":"4cff989c.0d0008","type":"debug","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":2510,"y":400,"wires":[]},{"id":"9e27aaf7.41fe88","type":"change","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"complete","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1990,"y":560,"wires":[["87144c27.b9add"]]},{"id":"4b858b33.701a94","type":"function","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"","func":"if(Object.values(msg.payload).length === 8){\n    let columns = Object.keys(msg.payload);  // create array of column names\n    let values = Object.values(msg.payload); //create array of values\n    columns = columns.join(\", \"); //join coulmns into string \n    values = \"'\" + values.join(\"', '\") + \"'\"; //join values to string\n    msg.payload = `INSERT INTO accident_report ( ${columns}) VALUE (${values});`; //create query\n    return msg;\n}else{\n    return null;\n}","outputs":1,"noerr":0,"initialize":"","finalize":"","x":2360,"y":420,"wires":[["4cff989c.0d0008","862b51ab.31225"]]},{"id":"91df7889.688798","type":"ui_button","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"","group":"685dc7dd.443798","order":10,"width":4,"height":1,"passthru":false,"label":"Submit","tooltip":"","color":"","bgcolor":"","icon":"","payload":"complete","payloadType":"str","topic":"","topicType":"str","x":1800,"y":560,"wires":[["9e27aaf7.41fe88","48365d4e.2b62f4"]]},{"id":"48365d4e.2b62f4","type":"change","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"[]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":1980,"y":600,"wires":[["839df5b5.aa4d08"]]},{"id":"839df5b5.aa4d08","type":"delay","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"","pauseType":"delay","timeout":"2","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":2140,"y":600,"wires":[["58bd3942.265ea8"]]},{"id":"58bd3942.265ea8","type":"link out","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"","links":["cc3f9b08.808e08"],"x":2235,"y":600,"wires":[]},{"id":"cc3f9b08.808e08","type":"link in","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"","links":["58bd3942.265ea8"],"x":1495,"y":380,"wires":[["22c6eefb.5b03b2","a9a853cf.6da19","f0a3732e.69bba","e6d8e401.234468","a81a5fbf.b29c4","4e47c62e.af45c8","a8fb3401.1f8978","31ad7a06.5d8f86"]]},{"id":"8cb0d4f3.59d548","type":"debug","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":2210,"y":280,"wires":[]},{"id":"c1363f03.e3a03","type":"change","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"yyyy-mm-dd","rules":[{"t":"set","p":"payload","pt":"msg","to":"$moment(payload).tz(\"Europe/London\").format(\"YYYY-MM-DD\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1970,"y":280,"wires":[["87144c27.b9add","8cb0d4f3.59d548"]]},{"id":"47a45d10.3d39a4","type":"change","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"hh:mm","rules":[{"t":"set","p":"payload","pt":"msg","to":"$moment(payload).tz(\"Europe/London\").format(\"HH:mm\")","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1950,"y":320,"wires":[["87144c27.b9add","15030fe0.b2da6"]]},{"id":"15030fe0.b2da6","type":"debug","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":2310,"y":320,"wires":[]},{"id":"31ad7a06.5d8f86","type":"change","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"timestamp","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"}],"action":"","property":"","from":"","to":"","reg":false,"x":1570,"y":280,"wires":[["e09a2362.2845c"]]},{"id":"862b51ab.31225","type":"mysql","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","mydb":"f0730937.a57188","name":"accident_report","x":2520,"y":480,"wires":[["463cd774.4123e8"]]},{"id":"463cd774.4123e8","type":"debug","z":"1c1bd358.aeecad","g":"a65faca6.d03ab","name":"","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":2710,"y":480,"wires":[]},{"id":"685dc7dd.443798","type":"ui_group","name":"Accident - Machine / Man","tab":"749760e0.fe809","order":3,"disp":true,"width":"12","collapse":false},{"id":"f0730937.a57188","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"mt02","tz":"","charset":"utf8mb4_general_ci"},{"id":"749760e0.fe809","type":"ui_tab","name":"Accident Entry Form","icon":"add_alert","order":10,"disabled":false,"hidden":false}]

but when insert manually, no error occurs

screenshot 9
screenshot 91111×124 8.25 KB

screenshot 10
screenshot 101095×401 35.1 KB

8

The only thing I see is that VALUE could be changed to VALUES, depending on your version.

Everything else looks OK.

9

@E1cid , still showing error

screenshot 11
screenshot 11971×617 41.2 KB

10

I am not seeing anything jump out at me, It says the error is with description, try deleting the the description in the topic and retyping it, maybe there is a invisible carriage return. Also try adding a space after description

11

which node are you using for database - node-red-contrib-mysql?

What does the built in help say?

On node-red-node-mysql it says...

msg.topic must hold the query for the database, and the result is returned in msg.payload .

But I can see you are setting msg.payload

12

@E1cid , I changed "description" to "remark" still error :neutral_face:
screenshot 12

13

@Steve-Mcl , Is it because I haven't updated the node? :thinking:

screenshot 13
screenshot 13711×245 5.69 KB

14

no, node-red-node-mysql has ALWAYS required the query to be in msg.topic

image
image1187×435 49.6 KB

NOTE: update anyway - I would recommend that (however the query must STILL go in msg.topic!)

15

@Steve-Mcl , Its working now. Arigatogozaimashita, Sensei :pray:

16

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