Help with Javascript on Function Node

I am Currently using an MQTT Gateway to receive values from a PLC using modbus. Then sending those values to a raspberry pi, and storing them into a SQLite Database. The Values only send to the Raspberry Pi "On Value Change".

I currently have 15 MQTT devices being joined to KEY/Value Pairs, then running through a function to place the values in a table.

The issue I am having is that when some values change and others do not the function node is throwing a Undefined error.
Capture

I am assuming this error is occurring because some values are sending and some are not thus the value is undefined.

My Question is How can I change my Javascript to allow zero to take its place if no value sends through or any suggestions on helping with my problem.

var newMsg = { payload: msg.payload };
newMsg.topic = `INSERT INTO SSreadings (ProgramNumber, NumOfLeaks, FsFlow, MsFlow, Ds1Flow, SpruFlow, Jc1Flow, Jc2Flow, Jc3Flow, Jc4Flow, FacFlow, JcFlow, FacPress, JcPress, Ds2Flow) VALUES (${msg.payload.ProgramNumber.value}, ${msg.payload.NumOfLeaks.value}, ${msg.payload.FsFlow.value}, ${msg.payload.MsFlow.value}, ${msg.payload.Ds1Flow.value}, ${msg.payload.SpruFlow.value}, ${msg.payload.Jc1Flow.value}, ${msg.payload.Jc2Flow.value}, ${msg.payload.Jc3Flow.value}, ${msg.payload.Jc4Flow.value}, ${msg.payload.FacFlow.value}, ${msg.payload.JcFlow.value}, ${msg.payload.FacPress.value}, ${msg.payload.JcPress.value}, ${msg.payload.Ds2Flow.value});`
return newMsg;

I would appreciate any help, I am still very new to Javascript and Nodered

You can test a value for undefined using, for example,
if (typeof msg.payload.whatever === "undefined")
so if you want to set it to zero then
if (typeof msg.payload.whatever === "undefined") msg.payload.whatever = 0

However I am not sure that is what you would want.
If you configure the Join node correctly then values from last time should still be there even if they have not changed.

That may be exactly what I need, I want the undefined values to equal zero. I try and se what happens and update the result

var newMsg = { payload: msg.payload };

if (typeof msg.payload.ProgramNumber === "undefined") msg.payload.ProgramNumber = 0;
if (typeof msg.payload.NumOfLeaks === "undefined") msg.payload.NumOfLeaks = 0;
if (typeof msg.payload.FsFlow === "undefined") msg.payload.FsFlow = 0;
if (typeof msg.payload.MsFlow === "undefined") msg.payload.MsFlow = 0;
if (typeof msg.payload.Ds1Flow === "undefined") msg.payload.Ds1Flow = 0;
if (typeof msg.payload.SpruFlow === "undefined") msg.payload.SpruFlow = 0;
if (typeof msg.payload.Jc1Flow === "undefined") msg.payload.Jc1Flow = 0;
if (typeof msg.payload.Jc2Flow === "undefined") msg.payload.Jc2Flow = 0;
if (typeof msg.payload.Jc3Flow === "undefined") msg.payload.Jc3Flow = 0;
if (typeof msg.payload.Jc4Flow === "undefined") msg.payload.Jc4Flow = 0;
if (typeof msg.payload.FacFlow === "undefined") msg.payload.FacFlow = 0;
if (typeof msg.payload.JcFlow === "undefined") msg.payload.JcFlow = 0;
if (typeof msg.payload.FacPress === "undefined") msg.payload.FacPress = 0;
if (typeof msg.payload.JcPress === "undefined") msg.payload.JcPress = 0;
if (typeof msg.payload.Ds2Flow === "undefined") msg.payload.Ds2Flow = 0;

newMsg.topic = `INSERT INTO SSreadings (ProgramNumber, NumOfLeaks, FsFlow, MsFlow, Ds1Flow, SpruFlow, Jc1Flow, Jc2Flow, Jc3Flow, Jc4Flow, FacFlow, JcFlow, FacPress, JcPress, Ds2Flow) VALUES (${msg.payload.ProgramNumber.value}, ${msg.payload.NumOfLeaks.value}, ${msg.payload.FsFlow.value}, ${msg.payload.MsFlow.value}, ${msg.payload.Ds1Flow.value}, ${msg.payload.SpruFlow.value}, ${msg.payload.Jc1Flow.value}, ${msg.payload.Jc2Flow.value}, ${msg.payload.Jc3Flow.value}, ${msg.payload.Jc4Flow.value}, ${msg.payload.FacFlow.value}, ${msg.payload.JcFlow.value}, ${msg.payload.FacPress.value}, ${msg.payload.JcPress.value}, ${msg.payload.Ds2Flow.value});`
return newMsg;

Is this correct when adding to my Original Script?

No neeed to define all payload options, just loop through them

for(const K of msg.payload){
    if (typeof msg.payload[k] === "undefined"){
         msg.payload[k] = 0;
    }
}

If you do not mind me asking what does the K stand for?

for(const K of msg.payload){
    if (typeof msg.payload[k] === "undefined"){
         msg.payload[k] = 0;

K is just a variable named K, i could have used any name i choose K for key

Your test and assignments to zero are changing the old msg.payload rather than the one in newMsg. However, since the line
var newMsg = { payload: msg.payload };
actually just makes the new payload be a reference to the original one then it should in fact work. Why are you creating a new message though? I would delete the first line, change the line
newMsg.topic = ...
to
msg.topic = ...
and change the last line to
return msg

Will that work? if msg.payload.something does not exist then I don't think the for .. of loop will find it.

I just created a new message through some tutorials of javascript, trying to learn Javascript on the fly here, Im open to any suggestions for better and proper code.

if (typeof msg.payload.ProgramNumber === "undefined") msg.payload.ProgramNumber = 0;
if (typeof msg.payload.NumOfLeaks === "undefined") msg.payload.NumOfLeaks = 0;
if (typeof msg.payload.FsFlow === "undefined") msg.payload.FsFlow = 0;
if (typeof msg.payload.MsFlow === "undefined") msg.payload.MsFlow = 0;
if (typeof msg.payload.Ds1Flow === "undefined") msg.payload.Ds1Flow = 0;
if (typeof msg.payload.SpruFlow === "undefined") msg.payload.SpruFlow = 0;
if (typeof msg.payload.Jc1Flow === "undefined") msg.payload.Jc1Flow = 0;
if (typeof msg.payload.Jc2Flow === "undefined") msg.payload.Jc2Flow = 0;
if (typeof msg.payload.Jc3Flow === "undefined") msg.payload.Jc3Flow = 0;
if (typeof msg.payload.Jc4Flow === "undefined") msg.payload.Jc4Flow = 0;
if (typeof msg.payload.FacFlow === "undefined") msg.payload.FacFlow = 0;
if (typeof msg.payload.JcFlow === "undefined") msg.payload.JcFlow = 0;
if (typeof msg.payload.FacPress === "undefined") msg.payload.FacPress = 0;
if (typeof msg.payload.JcPress === "undefined") msg.payload.JcPress = 0;
if (typeof msg.payload.Ds2Flow === "undefined") msg.payload.Ds2Flow = 0;

msg.topic = `INSERT INTO SSreadings (ProgramNumber, NumOfLeaks, FsFlow, MsFlow, Ds1Flow, SpruFlow, Jc1Flow, Jc2Flow, Jc3Flow, Jc4Flow, FacFlow, JcFlow, FacPress, JcPress, Ds2Flow) VALUES (${msg.payload.ProgramNumber.value}, ${msg.payload.NumOfLeaks.value}, ${msg.payload.FsFlow.value}, ${msg.payload.MsFlow.value}, ${msg.payload.Ds1Flow.value}, ${msg.payload.SpruFlow.value}, ${msg.payload.Jc1Flow.value}, ${msg.payload.Jc2Flow.value}, ${msg.payload.Jc3Flow.value}, ${msg.payload.Jc4Flow.value}, ${msg.payload.FacFlow.value}, ${msg.payload.JcFlow.value}, ${msg.payload.FacPress.value}, ${msg.payload.JcPress.value}, ${msg.payload.Ds2Flow.value});`
return msg;

Does this look correct now?

Put a debug node on the output and feed it some data and see if it works.

I will and then send the results back, Thanks

You are correct, was not thinking clearly.
then i would define an array of keys and check them

const keys = ["ProgramNumber",
    "NumOfLeaks",
    "FsFlow",
    "MsFlow",
    "Ds1Flow",
    "SpruFlow",
    "Jc1Flow",
    "Jc2Flow",
    "Jc3Flow",
    "Jc4Flow",
    "FacFlow",
    "JcFlow",
    "FacPress",
    "JcPress",
    "Ds2Flow"]
for(const V of keys){
    if (typeof msg.payload[V] === "undefined"){
         msg.payload[V] = {"value": 0};
    }
}
return msg;

If you are using nodejs 14 you can take advantage of optional chaining.

image

e.g..
msg.topic = `INSERT INTO SSreadings (ProgramNumber, NumOfLeaks, FsFlow, MsFlow, Ds1Flow, SpruFlow, Jc1Flow, Jc2Flow, Jc3Flow, Jc4Flow, FacFlow, JcFlow, FacPress, JcPress, Ds2Flow) VALUES (${msg.payload?.ProgramNumber?.value || 0}, ${msg.payload?.NumOfLeaks?.value || 0}, etc etc`

The Zeros are taking the place of Undefined now, but im getting this new error as shown in the pic above, any Ideas

is not setting the value property

if (typeof msg.payload.Ds2Flow === "undefined") msg.payload.Ds2Flow  = {"value": 0};
if (typeof msg.payload.ProgramNumber === "undefined") msg.payload.ProgramNumber.value = {"value":0};
if (typeof msg.payload.NumOfLeaks === "undefined") msg.payload.NumOfLeaks.value = {"value":0};
if (typeof msg.payload.FsFlow === "undefined") msg.payload.FsFlow.value = {"value":0};
if (typeof msg.payload.MsFlow === "undefined") msg.payload.MsFlow.value = {"value":0};
if (typeof msg.payload.Ds1Flow === "undefined") msg.payload.Ds1Flow.vaue = {"value":0};
if (typeof msg.payload.SpruFlow === "undefined") msg.payload.SpruFlow.value = {"value":0};
if (typeof msg.payload.Jc1Flow === "undefined") msg.payload.Jc1Flow.value = {"value":0};
if (typeof msg.payload.Jc2Flow === "undefined") msg.payload.Jc2Flow.value = {"value":0};
if (typeof msg.payload.Jc3Flow === "undefined") msg.payload.Jc3Flow.value = {"value":0};
if (typeof msg.payload.Jc4Flow === "undefined") msg.payload.Jc4Flow.value = {"value":0};
if (typeof msg.payload.FacFlow === "undefined") msg.payload.FacFlow.value = {"value":0};
if (typeof msg.payload.JcFlow === "undefined") msg.payload.JcFlow.value = {"value":0};
if (typeof msg.payload.FacPress === "undefined") msg.payload.FacPress.value = {"value":0};
if (typeof msg.payload.JcPress === "undefined") msg.payload.JcPress.value = {"value":0};
if (typeof msg.payload.Ds2Flow === "undefined") msg.payload.Ds2Flow.value = {"value":0};

msg.topic = `INSERT INTO SSreadings (ProgramNumber, NumOfLeaks, FsFlow, MsFlow, Ds1Flow, SpruFlow, Jc1Flow, Jc2Flow, Jc3Flow, Jc4Flow, FacFlow, JcFlow, FacPress, JcPress, Ds2Flow) VALUES (${msg.payload.ProgramNumber.value}, ${msg.payload.NumOfLeaks.value}, ${msg.payload.FsFlow.value}, ${msg.payload.MsFlow.value}, ${msg.payload.Ds1Flow.value}, ${msg.payload.SpruFlow.value}, ${msg.payload.Jc1Flow.value}, ${msg.payload.Jc2Flow.value}, ${msg.payload.Jc3Flow.value}, ${msg.payload.Jc4Flow.value}, ${msg.payload.FacFlow.value}, ${msg.payload.JcFlow.value}, ${msg.payload.FacPress.value}, ${msg.payload.JcPress.value}, ${msg.payload.Ds2Flow.value});`
return msg;

This Look Correct? When Doing this I receive the original error when doing this

That is because you are testing, for example, msg.payload.NumOfLeaks and setting that to zero, but the value in the sql comes from msg.payload.NumOfLeaks.value which is still undefined.

remove the .value from each line

if (typeof msg.payload.Ds2Flow === "undefined") msg.payload.Ds2Flow = {"value":0};

That rather depends on whether the number is in msg.payload.NumOfLeaks or msg.payload.NumOfLeaks.value