Trivial problem getting INPUTs as UPDATE TO MSSQL

[Aimovoria]

Hi there guys,

I'm completely new to the world of Revolution PI and started to work my way thru and already got stopped on a kinda trivial problem that I can't pass :),

BACKGROUND:
I created database - RevPi with three tables for each woodworking machine:

• dbo.Klipsiarka (Wood clipper) with two columns "Plyta","Klips" (translated to "Board","Clips") - both accepts null values,
• dbo.Klownica (Wood riveter) with two columns "KlowaCzyPlyta","KlowaUderzenie" (tranlated to "RivetOrBoard","RivetHit") - both accepts null values,
• dbo.Wielowrzecionowka (Multi-spindle for wood) with one column "Wiercenie" (translated to "Drilling")

Equipment:

• Revolution RevPi Connect+ 16GB,
• Revolution RevPi DI (digital inputs expansion for 16 inputs)

For:

1. dbo.Klipsiarka has two inputs (I_1 / I_2)
   a) I want to check if there is any board (0,1) if there is then when "I_1" is 1 - update value from "I_1" to dbo.[Klipsiarka].[Plyta] as int (1),
   b) also when "I_1" == 1, and "I_2" occurs as state 1 then - update value from "I_2" to dbo.[Klipsiarka].[Klips] as int (1) -- this one will change states to 0 and 1 (I want to update value with int (1) everytime when "I_1" == 1 and "I_2 == 1)

2. dbo.Klownica (similar to above dbo.Klipsiarka) has two inputs (I_3 / I_4)
   a) same to above (1.a) but update everytime table when "I_3" is 1 - update value to dbo.[Klownica].[KlowaCzyPlyta] as int (1),
   b) similar to above (1.b) but update everytime table when "I_3" == 1 and "I_4" == 1 - update value from "I_4" to dbo.[Klownica].[KlowaUderzenie] as int (1)

3. dbo.Wielowrzecionowka has only one input "I_5"
   a) when "I_5" == 1 then - update value to dbo.[Wielowrzecionowka].[Wiercenie] as int (1).

I tried defining as I_1 (Input1) as var [something], let [something], msg.payload as I_1 and I cannot get it to work to update all values into my table/column because it doesn't know what "I_1" is, same to other inputs.
Please help, I stopped on this probably very trivial for You guys problem which I cannot solve.. I would really appreciate for a solution or maybe getting into this simple project and correcting what I did wrong.

Thank You for every solution or criticism..

CLIPBOARD LINK BELOW:

[{"id":"8a05e5c5.7e79d8","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"15237651.20281a","type":"debug","z":"8a05e5c5.7e79d8","name":"","active":true,"tosidebar":true,"console":false,"complete":"payload","statusVal":"","statusType":"auto","x":650,"y":80,"wires":[]},{"id":"eac3e9b1.b2f0e8","type":"MSSQL","z":"8a05e5c5.7e79d8","mssqlCN":"df8c0b88.91b0a8","name":"MSSQL","query":"","outField":"payload","x":460,"y":80,"wires":[["15237651.20281a"]]},{"id":"f426b3b3.6a382","type":"revpi-single-input","z":"8a05e5c5.7e79d8","server":"36b43edb.a540f2","inputpin":"I_1","x":110,"y":40,"wires":[["f25c5439.2b7208"]]},{"id":"ca767bc4.7061f8","type":"revpi-single-input","z":"8a05e5c5.7e79d8","server":"36b43edb.a540f2","inputpin":"I_2","x":110,"y":120,"wires":[["f25c5439.2b7208"]]},{"id":"f25c5439.2b7208","type":"function","z":"8a05e5c5.7e79d8","name":"Function","func":"if (rpi.io.InputValue_1 == \"1\" && rpi.io.InputValue_1 == \"2\") {\n    msg.payload = \"INSERT INTO [RevPi].[dbo].[Klipsiarka] (Klips, Plyta) VALUES ('1','1')\";\n} else if (rpi.io.InputValue_1 == \"1\" && rpi.io.InputValue_2 == \"0\") {\n    msg.payload = \"INSERT INTO [RevPi].[dbo].[Klipsiarka] (Plyta) VALUES ('1')\";\n} else {\n    msg.payload = \"BRAK DANYCH\";\n}","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":280,"y":80,"wires":[["eac3e9b1.b2f0e8"]]},{"id":"ea4cfb84.583028","type":"debug","z":"8a05e5c5.7e79d8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":650,"y":240,"wires":[]},{"id":"36ee34b.f1a01cc","type":"MSSQL","z":"8a05e5c5.7e79d8","mssqlCN":"df8c0b88.91b0a8","name":"MSSQL","query":"","outField":"payload","x":460,"y":240,"wires":[["ea4cfb84.583028"]]},{"id":"9061def2.f7037","type":"revpi-single-input","z":"8a05e5c5.7e79d8","server":"36b43edb.a540f2","inputpin":"I_3","x":110,"y":200,"wires":[["485aca8d.6403b4"]]},{"id":"9e6d3c1b.2622c","type":"revpi-single-input","z":"8a05e5c5.7e79d8","server":"36b43edb.a540f2","inputpin":"I_4","x":110,"y":280,"wires":[["485aca8d.6403b4"]]},{"id":"485aca8d.6403b4","type":"function","z":"8a05e5c5.7e79d8","name":"Function","func":"let KlowaCzyPlyta = I_3\nlet KlowaUderzenie = I_4\n\nif (KlowaCzyPlyta == \"1\" && KlowaUderzenie == \"1\") {\n    msg.payload = \"PLYTA 1 - UDERZENIE 1\";\n    payload = \"INSERT INTO [RevPi].[dbo].[Klownica] (KlowaCzyPlyta, KlowaUderzenie) VALUES ('1','1')\";\n} else if (KlowaCzyPlyta == \"1\" && KlowaUderzenie == \"0\") {\n    msg.payload = \"PLYTA 1 - UDERZENIE 0\";\n    payload = \"INSERT INTO [RevPi].[dbo].[Klownica] (KlowaCzyPlyta) VALUES ('1')\";\n} else {\n    msg.payload = \"BRAK DANYCH\";\n}","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":280,"y":240,"wires":[["36ee34b.f1a01cc"]]},{"id":"d6074208.2e918","type":"revpi-single-input","z":"8a05e5c5.7e79d8","server":"36b43edb.a540f2","inputpin":"I_5","x":110,"y":360,"wires":[["8c56dc4.7dda62"]]},{"id":"6e633630.3e51d8","type":"debug","z":"8a05e5c5.7e79d8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"informacja","targetType":"msg","statusVal":"","statusType":"auto","x":660,"y":360,"wires":[]},{"id":"876560a5.3480a","type":"MSSQL","z":"8a05e5c5.7e79d8","mssqlCN":"df8c0b88.91b0a8","name":"MSSQL","query":"","outField":"payload","x":460,"y":360,"wires":[["6e633630.3e51d8"]]},{"id":"8c56dc4.7dda62","type":"function","z":"8a05e5c5.7e79d8","name":"Function","func":"let informacja = msg.informacja;\nlet Input5 = msg.payload;\n\nif ( Input5 === 1) {\n    informacja = \"Input5 = 1\";\n    informacja = \"INSERT INTO [RevPi].[dbo].[Wielowrzecionowka] (Wiercenie) VALUES ('1')\"; //set payload to a string of \"1\" as you requested\n} else {\n    informacja = \"Input5 = 0\";\n}\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":280,"y":360,"wires":[["876560a5.3480a"]]},{"id":"df8c0b88.91b0a8","type":"MSSQL-CN","name":"RevPi","server":"192.168.0.240\\EX12","encyption":false,"database":"RevPi"},{"id":"36b43edb.a540f2","type":"revpi-server","host":"localhost","port":"8000","user":"","password":"","rejectUnauthorized":false,"ca":""}]

Just to point anything, I see when states for every input (from I_1 to I_5) changes so connection works, cause NODE-RED shows it is online.

[Steve-Mcl]

Hi, without trying to fully understand what you are trying to achieve, let my point you in the right direction regarding issues I see...

1. You have errors in Function 1 and 2...
   function 1
   

   
   
   function 2
   
   

2. A fundamental of node-red is understanding that a msg from 2 separate wires will NEVER reach the next node at the same time.
   

   
   
   for this to work, you would need a join node to put both values into the msg at the same time BEFORE the function node. See this article in the cookbook for an example of how to join messages into one object.

3. Use debug nodes everywhere and NAME THEM
   
   This helps people assisting you know which debug message is which
   NOTE: Sometimes there is more (and important) info in other parts of the msg - set the debug node to show complete message to check this.
   

---

If all of this seems overwhelming - I suspect you would benefit from a little tuition. I recommend watching this playlist: Node-RED Essentials. The videos are done by the developers of node-red. They're nice & short and to the point. You will understand a whole lot more in about 1 hour. A small investment for a lot of gain.