Beginner - array and declaring variables

General
1

Hi Everyone,

I am new to this.

Just after some advice or direction on how to do this.

What I'm trying to achieve - I have a flow that runs on a time trigger -> extracts data from a database -> formats that data from the database -> emails out a report to users of that data. So to email out a daily report of automated doors battery levels, so that i know when they need to be replaced.

trigger (daily time trigger) -> SQL Database extraction -> function to format data from SQL Extraction-> email out a report

The sql database pumps out an array object to the function, I didn't want to hard code anything because the size of the array can change daily due to the database SQL query results so I need to somehow declare the variables for formatting from the sql query (array object).

I am trying to declare variables from each array object every time the flow runs.

i decided i could try and achieve it through a forloop to go through each element but not sure on how to do this.

im not a coder so please be gentle :stuck_out_tongue:

see image and code example below. (EDITED, added the email format function)

flow email report
flow email report1294Ă—154 14.9 KB

SQL Extraction query is simple:

SELECT TOP (20) [id_lock]
      ,[name]
      ,[Description]
      ,[Battery]
  FROM [BATTERY_RW].[dbo].[BP_Locks]  where Battery < 11 order by Battery asc

Here is an example of data extraction from SQL query that I'm working with.

[{"id_lock":1271,"name":"Front Door 1","Description":"Front Door Battery Lock","Battery":10},{"id_lock":1249,"name":"Side Door 2","Description":"Side Door Battery Lock","Battery":3},{"id_lock":1177,"name":"Back Door 3","Description":"Back Door Battery Lock","Battery":5}]

then this is the function that formats the data:


var counter = msg.payload.length;
msg.topic = "CRITICAL - Low Door Battery Report";
msg.from = "Node-Red-Door-Batteries@Reports.com";

for (var i=0; i < counter; i++)
{
    // don't need this anymore msg.SD[i] = ("Battery Lock Doors Door - " + msg.payload[i]["name"] + " - Battery Level: " + msg.payload[i]["Battery"] + "%");
    msg.SDB[i] = ("CRITICAL - Battery Level: " + msg.payload[i]["Battery"] + "%");
    msg.SDO[i] = (msg.payload[i]["name"]);
    return msg; //testing whats returned
}

I pass variables -> msg.SD1,msg.SD2,msg.SDB1, msg.SDB2, msg.SDO1, msg.SDO2 etc into the email part but i don't know how to declare SD1, SD2 etc based on the array element

sorry forgot to add the html email formatting function -


html email
<html>
	<head>
	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><title>Salto Doors 20 List</title> 
	</head>
	        <body>
		         <p> 
		 	       Battery Door - {{{SDO1}}}
		         </p>
		         <p>
			       Battery Level - {{{SDB1}}}
		        </p>

                         <p> 
		 	       Battery Door 2 - {{{SDO2}}}
		         </p>
		         <p>
			       Battery Level - {{{SDB2}}}
		        </p>
	      </body>
</html>

thank you for your help in advance!

hope that makes sense!
cheers
Ed

2

What should the output msg look like ?

3

I forgot to add the email format function. the way i did it when i had it hard coded was then pass the declared variables to a html email function then email that out.

not sure if this is the best way to do this.

i have edited the original post with a quick simple html example of the output. let me know if that makes sense! cheers

4

Try this function:

m = msg.payload
msg.topic = "CRITICAL - Low Door Battery Report";
msg.from = "Node-Red-Door-Batteries@Reports.com";

body=''

for(x=0;x<m.length;x++){
    battery = m[x].Battery
    name = m[x]['name']
    
    severity = (battery<10)? 'CRITICAL' : 'NORMAL';
    
    body += "<p>Door:" + name + " - Battery Level: " + battery + "% ("+ severity +")</p>"

}
msg.payload = body

return msg;

It will output:

<p>Door:Front Door 1 - Battery Level: 10% (NORMAL)</p>
<p>Door:Side Door 2 - Battery Level: 3% (CRITICAL)</p>
<p>Door:Back Door 3 - Battery Level: 5% (CRITICAL)</p>

You can change the "critical" battery level, in this example less then 10.

Or if you want a fancy table in your email;

m = msg.payload
msg.topic = "CRITICAL - Low Door Battery Report";
msg.from = "Node-Red-Door-Batteries@Reports.com";

body='<html><body><table><tr><th>Door</th><th>Battery Level</th><th>Severity</th></tr>'

for(x=0;x<m.length;x++){
    battery = m[x].Battery
    name = m[x]['name']
    
    severity = (battery<10)? 'CRITICAL' : 'WARNING';
    
    body += "<tr><td>" + name + "</td><td>" + battery + "%</td><td>"+ severity +"</td></tr>"

}
body +="</table></body></html>"

msg.payload = body

return msg;
5

Awesome, thank you so much! that worked perfectly. i was able to then do a few changes and pass it into my html template without any problems.

And now i understand how it works and the way you did it! really good to know for the future.

cheers
Edward

1 Like
6

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