Combine 2 different payload into function

[alikhan]

@Steve-Mcl

eventtime, pos, pod, 
    op, eta, [weight], rdy,[length], cut, 
    all_cut, dd, detex, number_ply, posk

done

    [eventtime] [datetime] NOT NULL,
	[POS] [nvarchar](256) NULL,
	[POD] [nvarchar](256) NULL,
	[op] [real] NULL,
	[ETA] [int] NULL,
	[Weight] [real] NULL,
	[rdy] [varchar](256) NULL,
	[Length] [real] NULL,
	[cut] [varchar](256) NULL,
	[All_cut] [varchar](256) NULL,
	[DD] [varchar](256) NULL,
	[Detex] [int] NULL,
	[Number_Ply] [int] NULL,
	[POSK] [nvarchar](256) NULL

[Steve-Mcl]

try this...

[{"id":"ce8063fd.fd102","type":"mqtt in","z":"4b3f21a3.ba434","name":"","topic":"L16/#","qos":"0","datatype":"auto","broker":"b611955e.b00bf8","x":70,"y":80,"wires":[["f0325971.9c3e08","869fa08d.3ba1"]]},{"id":"f0325971.9c3e08","type":"json","z":"4b3f21a3.ba434","name":"","property":"payload","action":"","pretty":true,"x":210,"y":80,"wires":[["8c518462.0d7dc8"]]},{"id":"8c518462.0d7dc8","type":"function","z":"4b3f21a3.ba434","name":"Generate 68 payloads","func":"var d = new Date();\nvar dformat1 = [\n        d.getMonth() + 1, \n        d.getDate(), \n        d.getFullYear()\n    ].join('') + '' +\n    [\n        d.getHours(), \n        d.getMinutes(), \n        d.getSeconds()\n    ].join('');\n\nvar data = msg.payload;\nfor (let pos = 1; pos <= 68; pos++) {\n    \n    let prefix = \"Pos\" + pos + \"_\";\n    let payload = {\n        eventtime: d,\n        pos: pos,\n        pod: data.POD,\n        detex: data.Detex,\n        number_ply: data.Number_Ply,\n        op: data[prefix + \"op\"],\n        eta: data[prefix + \"ETA\"],\n        weight: data[prefix + \"Weight\"],\n        rdy: data[prefix + \"Rdy\"],\n        length: data[prefix + \"Length\"],\n        cut: data[prefix + \"1_2_cut\"],\n        all_cut: data[prefix + \"All_Cut\"],\n        dd: data[prefix + \"DD\"],\n        posk: \"L16POS\" + pos + dformat1\n    }\n\n    if (payload.op){\n        node.send({ payload: payload });\n    }\n    \n}\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":340,"y":160,"wires":[["542238be.840fe8","6b46e20e.869bbc"]]},{"id":"869fa08d.3ba1","type":"debug","z":"4b3f21a3.ba434","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":370,"y":80,"wires":[]},{"id":"542238be.840fe8","type":"debug","z":"4b3f21a3.ba434","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":390,"y":220,"wires":[]},{"id":"edd22648.9fe2d8","type":"MSSQL","z":"4b3f21a3.ba434","mssqlCN":"867426f7.2f6538","name":"","outField":"payload","returnType":"1","throwErrors":1,"query":"INSERT INTO [RedlionMQTT].[dbo].[L16] (\r\n    [DateTime], pos, pod, \r\n    op, eta, [weight], [rdy],[length],[cut], \r\n    all_cut, dd, detex, number_ply, posk\r\n)\r\nVALUES ( \r\n    @eventtime,@pos, @pod,\r\n    @op, @eta, @weight, @rdy,@length,@cut, \r\n    @all_cut, @dd, @detex, @number_ply, @posk\r\n)\r\n","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","params":[{"output":false,"name":"eventtime","type":"DateTime","valueType":"msg","value":"eventtime"},{"output":false,"name":"pos","type":"NvarChar(20)","valueType":"msg","value":"payload.pos"},{"output":false,"name":"pod","type":"NvarChar(20)","valueType":"msg","value":"payload.pod"},{"output":false,"name":"op","type":"Real","valueType":"msg","value":"payload.op"},{"output":false,"name":"eta","type":"Int","valueType":"msg","value":"payload.eta"},{"output":false,"name":"weight","type":"Real","valueType":"msg","value":"payload.weight"},{"output":false,"name":"rdy","type":"NvarChar(20)","valueType":"msg","value":"payload.rdy"},{"output":false,"name":"length","type":"Float","valueType":"msg","value":"payload.length"},{"output":false,"name":"cut","type":"NvarChar(20)","valueType":"msg","value":"payload.cut"},{"output":false,"name":"all_cut","type":"NvarChar(20)","valueType":"msg","value":"payload.all_cut"},{"output":false,"name":"dd","type":"NvarChar(20)","valueType":"msg","value":"payload.dd"},{"output":false,"name":"detex","type":"Int","valueType":"msg","value":"payload.detex"},{"output":false,"name":"number_ply","type":"Int","valueType":"msg","value":"payload.number_ply"},{"output":false,"name":"posk","type":"NvarChar(20)","valueType":"msg","value":"payload.posk"}],"x":760,"y":160,"wires":[[]]},{"id":"6b46e20e.869bbc","type":"delay","z":"4b3f21a3.ba434","name":"","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"5","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":570,"y":160,"wires":[["edd22648.9fe2d8"]]},{"id":"e7f81db8.e7cff","type":"inject","z":"4b3f21a3.ba434","name":"Simulated data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"connected\":1,\"Detex\":0,\"Number_Ply\":0,\"POD\":\"EXT037897\",\"Pos10_1_2_cut\":\"OFF\",\"Pos10_All_Cut\":\"OFF\",\"Pos10_DD\":\"OFF\",\"Pos10_ETA\":2,\"Pos10_Length\":16.45928,\"Pos10_op\":101.112,\"Pos10_Rdy\":\"ON\",\"Pos10_Weight\":15.66313,\"Pos11_1_2_cut\":\"OFF\",\"Pos11_All_Cut\":\"OFF\",\"Pos11_DD\":\"OFF\",\"Pos11_ETA\":1,\"Pos11_Length\":16.23375,\"Pos11_op\":99.522,\"Pos11_Rdy\":\"ON\",\"Pos11_Weight\":15.51428,\"Pos12_1_2_cut\":\"OFF\",\"Pos12_All_Cut\":\"OFF\",\"Pos12_DD\":\"OFF\",\"Pos12_ETA\":1,\"Pos12_Length\":16.18431,\"Pos12_op\":99.202,\"Pos12_Rdy\":\"ON\",\"Pos12_Weight\":15.49117,\"Pos13_1_2_cut\":\"OFF\",\"Pos13_All_Cut\":\"OFF\",\"Pos13_DD\":\"OFF\",\"Pos13_ETA\":1,\"Pos13_Length\":16.43628,\"Pos13_op\":100.657,\"Pos13_Rdy\":\"ON\",\"Pos13_Weight\":15.64794,\"Pos14_1_2_cut\":\"OFF\",\"Pos14_All_Cut\":\"OFF\",\"Pos14_DD\":\"OFF\",\"Pos14_ETA\":1,\"Pos14_Length\":16.40729,\"Pos14_op\":100.479,\"Pos14_Rdy\":\"ON\",\"Pos14_Weight\":15.62881,\"Pos15_1_2_cut\":\"OFF\",\"Pos15_All_Cut\":\"OFF\",\"Pos15_DD\":\"OFF\",\"Pos15_ETA\":1,\"Pos15_Length\":16.48587,\"Pos15_op\":100.96,\"Pos15_Rdy\":\"ON\"}","payloadType":"json","x":120,"y":160,"wires":[["8c518462.0d7dc8"]]},{"id":"b611955e.b00bf8","type":"mqtt-broker","z":"","name":"mosca","broker":"10.104.32.105","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"867426f7.2f6538","type":"MSSQL-CN","z":"","name":"TCTME-TST","server":"10.104.32.107","encyption":true,"database":"RedlionMQTT"}]

This solution uses SQL parameters, where the payload properties are mapped through to parameters...

NOTES

for this to work,

• all properties in the MQTT payload must have same case (JavaScript is case sensitive)

EDIT...

• I have updated the flow (re-import it if necessary)
• This is untested as I dont have your data or database

[alikhan]

some time it getting this error on SQL node.

[Steve-Mcl]

it might be one of the fields are null.

check the messages that come out of here...

[alikhan]

[alikhan]

[Nodi.Rubrum]

@Steve-Mcl,

Nice work by the way. Any time you can be kind to a SQL server, it is a good thing.

[alikhan]

Thanks Steve