# Beginner - array and declaring variables
**URL:** https://discourse.nodered.org/t/beginner-array-and-declaring-variables/26964
**Category:** General
**Created:** [20 May 2020 03:17 UTC](https://discourse.nodered.org/t/beginner-array-and-declaring-variables/26964 "2020-05-20T03:17:05Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@lenvirthus](https://discourse.nodered.org/u/lenvirthus)
#### Post date: [20 May 2020 03:17 UTC](https://discourse.nodered.org/t/beginner-array-and-declaring-variables/26964/1 "2020-05-20T03:17:05Z")
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 😛
see image and code example below. (EDITED, added the email format function)
SQL Extraction query is simple:
```auto
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.
```auto
[{"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:
```auto
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 -
```auto
html email
Salto Doors 20 List
Battery Door - {{{SDO1}}}
Battery Level - {{{SDB1}}}
Battery Door 2 - {{{SDO2}}}
Battery Level - {{{SDB2}}}
```
thank you for your help in advance!
hope that makes sense!
cheers
Ed
---
### Author:  [@bakman2](https://discourse.nodered.org/u/bakman2)
#### Post date: [20 May 2020 05:01 UTC](https://discourse.nodered.org/t/beginner-array-and-declaring-variables/26964/2 "2020-05-20T05:01:34Z")
What should the output `msg` look like ?
---
### Author:  [@lenvirthus](https://discourse.nodered.org/u/lenvirthus)
#### Post date: [20 May 2020 05:21 UTC](https://discourse.nodered.org/t/beginner-array-and-declaring-variables/26964/3 "2020-05-20T05:21:55Z")
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
---
### Author:  [@bakman2](https://discourse.nodered.org/u/bakman2)
#### Post date: [20 May 2020 05:23 UTC](https://discourse.nodered.org/t/beginner-array-and-declaring-variables/26964/4 "2020-05-20T05:23:52Z")
Try this function:
```auto
m = msg.payload
msg.topic = "CRITICAL - Low Door Battery Report";
msg.from = "Node-Red-Door-Batteries@Reports.com";
body=''
for(x=0;x"
}
msg.payload = body
return msg;
```
It will output:
```auto
Door:Front Door 1 - Battery Level: 10% (NORMAL)
Door:Side Door 2 - Battery Level: 3% (CRITICAL)
Door:Back Door 3 - Battery Level: 5% (CRITICAL)
```
You can change the "critical" battery level, in this example less then 10.
Or if you want a fancy table in your email;
```auto
m = msg.payload
msg.topic = "CRITICAL - Low Door Battery Report";
msg.from = "Node-Red-Door-Batteries@Reports.com";
body='| Door | Battery Level | Severity |
|---|
'
for(x=0;x" + battery + "% | "+ severity +" | "
}
body +="
"
msg.payload = body
return msg;
```
---
### Author:  [@lenvirthus](https://discourse.nodered.org/u/lenvirthus)
#### Post date: [22 May 2020 02:44 UTC](https://discourse.nodered.org/t/beginner-array-and-declaring-variables/26964/5 "2020-05-22T02:44:47Z")
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
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [5 June 2020 02:44 UTC](https://discourse.nodered.org/t/beginner-array-and-declaring-variables/26964/6 "2020-06-05T02:44:49Z")
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.