Sir ,I want to copy 2 in my sql DB

I increased the delay upto 10 sec now check the output for
Case 3:

9/17/2020, 7:55:50 PMnode: dcb68435.c57928
Trade_ID : msg.payload : Object
Fob_Tag_ID: "04222436"
Trade_ID: "2814"

What do you think? This is your application and you need to understand it.

So yes, that is looking good.

The next task is to create a Switch node that handles the three cases.

Have told you to use a Switch node repeatedly, the truth is the logic you need is actually a bit tricky to express in the Switch node. It will be easier to use a Function node.

Replace that Switch node with a Function node. Configure the Function node to have 3 outputs and use this code:

var hasTag = msg.payload.hasOwnProperty("Fob_Tag_ID");
var hasTrade = msg.payload.hasOwnProperty("Trade_ID");

if (hasTag && !hasTrade) {
   return [msg, null, null];
} else if (!hasTag && hasTrade) {
   return [null, msg, null];
} else if (hasTag && hasTrade) {
   return [null, null, msg];

I hope that code is self-explanatory.

Sir i have replaced the switch node with this function you just gave
output of this function node acc to case 1:


9/17/2020, 8:14:37 PMnode: function_debug
Fob_Tag_ID : msg : Object
topic: "Fob_Tag_ID"
_msgid: "45329283.c1930c"
payload: object
Fob_Tag_ID: "04222436"

You don't need to show me the output of the Function because the Function doesn't modify the message at all.

All the Function does is pass the message to one of its three outputs according to which case it is for. That is what you now need to verify: add a Debug node to each output of the Function and make sure that Case 1 appears on the top output of the Function, Case 2 from the middle output and Case 3 from the bottom output.

If that is all working, you can then add whatever logic you want in each of those separate cases.

All 3 output is coming from that function ,now my sql query which i have to write for case 3 especially what it could be?

You need to be clear. Are you seeing the messages get sent on the correct output of the Function to match each case?

You want an INSERT statement with both values in right? You already know how to do an insert with one value - so why don't you try writing the query yourself. If you get stuck, share what you have tried and we can help.

Sir Case 1 and 2 i am getting output in correct mannner

but for case 3 check:

9/17/2020, 8:34:35 PMnode: Fob&tagINSERT INTO NCS_DATABASE2 (Date_and_Time_India,Date_and_Time_UK ,Event_Type ,Event_Description ,Person_ID,Person_Name_Description ,Fob_Tag_ID ,Trade_ID,Door_ID ,Door_Description ,Status) VALUES ((strftime('%Y-%m-%d %H:%M:%S','now','localtime')),(strftime('%Y-%m-%d %H:%M:%S','now','localtime','-4 hour','-30 minutes')),'FOB & TAG','DR OPEN BY FOB & TAG',' ','Shipra Nigam',' ','2814','2814 ',' ','High Risk DR ON') : msg : Object

2814 is getting stored twice.please suggest

How can I suggest anything? You have not shown how you are generating the SQL.

Pardon Sir,

query for 3rd case looks like this ,this i have done previously so modification is required ,kindly suggest

let newMsg2 = {};

newMsg2.topic = `INSERT INTO  NCS_DATABASE2 (Date_and_Time_India,Date_and_Time_UK ,Event_Type ,Event_Description ,Person_ID,Person_Name_Description ,Fob_Tag_ID ,Trade_ID,Door_ID ,Door_Description ,Status)
VALUES ((strftime('%Y-%m-%d %H:%M:%S','now','localtime')),(strftime('%Y-%m-%d %H:%M:%S','now','localtime','-4 hour','-30 minutes')),'FOB & TAG','DR OPEN BY FOB & TAG',' ','Shipra Nigam',' ','${}','${msg.id1} ',' ','High Risk DR ON')`;

return newMsg2;

So your query is using the message properties and msg.id1. But we have spent the whole of today getting to the point where msg.payload.Fob_Tag_ID contains the fob and msg.payload.Trade_ID contains the trade id.

Can you see how to change your code?

thank you and sorry for my silly mistake i removed that and getting the three output correctly.

but sir one query is ,if i am entering for case 3:
Fob id :000000000(wrong id)
Tag id : 2814(correct id)
then why it is updating me a case 2 Sql query????? this is not correct?please suggest

Again, how can I possibly suggest anything as I am not sat in front of your flow?

Can you explain what you mean in terms of all the work we have done?

In the scenario you are asking about, which of the three outputs of the Function node is the message passing? What does that message look like exactly?

At this point, you really should be in a position to debug these things yourself. We've have spent so much time stepping through the logic in your flow and building it up step by step.

Use Debug nodes to understand where messages are going in your flow. Figure it out.

Yes sir , like as i am entering 00000000(wrong id) and 2814 (right id) ,then in output of my 2 function node is coming .it should not come because user has entered first id wrong ,in this case no updation would be there (whether first id or second id wrong then no updation )

this is for when i am in case 3 and entering one wrong id and right id

Ah okay. That makes sense.

At the moment, your flow checks the Tag/Trade are valid values and only passes them to the Join node if they are valid.

The problem with that approach is exactly what you are seeing: if it is scenario 3 (tag & trade provided) but either one of them is invalid, then the Join node will pass on a message with only the valid value - so the Function node thinks it is case 1 or case 2.

There are a few ways you could fix it. One way would be, earlier in the flow, when you identify it is an invalid value, then set it to something like INVALID, but still pass it on to the Join node. That means you will be sure to get "complete" messages in all three cases. You would then add some additional logic in the Function node after the Join node to check to see if the value is valid or not.

Something like:

var hasTag = msg.payload.hasOwnProperty("Fob_Tag_ID") && msg.payload.Fob_Tag_ID !== "INVALID";
var hasTrade = msg.payload.hasOwnProperty("Trade_ID") && msg.payload.Trade_ID !== "INVALID";
1 Like

Sir all you said i got that but this line can u please elaborate please

like this i tried ..

please suggest


like this i have done acc to what you suggest ,litlle bit not getting the correct output,please suggest.

Once again, how can I help if you don't say what is wrong? You say you don't get the correct output, but you don't say what output you get. You have shared a screenshot of some Change nodes, but no information on how you have configured them.

At a glance, I can see you are using msg.payload1 in the Function code. Where has that come from?

Sir ,I want to add a refresh button in ui dashboard for this project ,by this button user can get the recent updated database .and further i want to get this updated data back to mqtt sub node with come string lets say "database successfully updated" on subscriber side .How can i implement this .
I am arising this query here because I dont want to explain my project from any new person ,so that he also dont get in trouble and he can understand my flow easily.