# Define msg.topic in function node

**URL:** <https://discourse.nodered.org/t/define-msg-topic-in-function-node/45008>\
**Category:** General\
**Created:** [1 May 2021 07:03 UTC](https://discourse.nodered.org/t/define-msg-topic-in-function-node/45008 "2021-05-01T07:03:25Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![sahsha](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sahsha/32/43845_2.png) [@sahsha](https://discourse.nodered.org/u/sahsha)\
**Post date:** [1 May 2021 07:03 UTC](https://discourse.nodered.org/t/define-msg-topic-in-function-node/45008/1 "2021-05-01T07:03:25Z")

</div>

Hello everyone, need help to create one function node  
below is the given flow

```auto
[{"id":"4476fe10.e2765","type":"simpletime","z":"1f149e22.43a482","name":"","mydate":true,"myymd":true,"myyear":true,"mymonth":true,"mymonthn":true,"mydom":true,"mydoy":true,"myday":true,"myhourpm":true,"myhour":true,"mytime":true,"mytimes":true,"myminute":true,"myminutes":true,"mysecond":true,"mymillis":true,"myepoch":true,"myrawdate":true,"mypm":true,"x":450,"y":960,"wires":[["ed99652.1402f98"]]},{"id":"ed99652.1402f98","type":"template","z":"1f149e22.43a482","name":"date","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"from 01-{{mymonthn}}-{{myyear}} to {{mydom}}-{{mymonthn}}-{{myyear}}","output":"str","x":610,"y":960,"wires":[["e352cec1.fb968"]]},{"id":"e352cec1.fb968","type":"function","z":"1f149e22.43a482","name":"date, time","func":"msg.payload = msg.payload;\nmsg.topic='date';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":760,"y":960,"wires":[["1d1920d3.2a95ef"]]},{"id":"e2a56fda.b415","type":"MSSQL","z":"1f149e22.43a482","mssqlCN":"898d2a6f.9fb7b8","name":"A, P, Q, OEE (all machines)","query":"SELECT \n(((SUM (CAST([MACHINE SHIFT TIME (X)(Min.)] AS FLOAT))- SUM (CAST([AVAILABILITY LOSS TIME(Min.)] AS FLOAT)))/SUM (CAST([MACHINE SHIFT TIME (X)(Min.)] AS FLOAT)))*100) \nAS availability,\n((SUM(CAST([OK PRODUCTION] AS FLOAT)) / SUM(COALESCE((((CAST([MACHINE SHIFT TIME (X)(Min.)] AS FLOAT)-CAST([AVAILABILITY LOSS TIME(Min.)] AS FLOAT))/(NULLIF(CAST([TOTAL STANDARD CYCLE TIME(Sec.)] AS FLOAT),0)))*60*CAST([PARTS PER CYCLE (U)] AS FLOAT)),0)))*100)\nAS performance,\n((SUM(CAST([OK PRODUCTION] AS FLOAT)) / SUM(CAST([TOTAL PRODUCTION] AS FLOAT)))*100) \nAS quality,\n(((((SUM (CAST([MACHINE SHIFT TIME (X)(Min.)] AS FLOAT))- SUM (CAST([AVAILABILITY LOSS TIME(Min.)] AS FLOAT)))/SUM (CAST([MACHINE SHIFT TIME (X)(Min.)] AS FLOAT)))*100) \n*((SUM(CAST([OK PRODUCTION] AS FLOAT)) / SUM(COALESCE((((CAST([MACHINE SHIFT TIME (X)(Min.)] AS FLOAT)-CAST([AVAILABILITY LOSS TIME(Min.)] AS FLOAT))/(NULLIF(CAST([TOTAL STANDARD CYCLE TIME(Sec.)] AS FLOAT),0)))*60*CAST([PARTS PER CYCLE (U)] AS FLOAT)),0)))*100)\n*((SUM(CAST([OK PRODUCTION] AS FLOAT)) / SUM(CAST([TOTAL PRODUCTION] AS FLOAT)))*100))/10000)\nAS oee\nFROM dbo.SHIFT_DATA \nWHERE \ndatepart(mm,DATE) =month(getdate()) \nAND datepart(yyyy,DATE) =year(getdate()) \n AND [MACHINE CODE]='M02CN001'\n OR [MACHINE CODE]='M02CN002'\n OR [MACHINE CODE]='M02CN003'\n OR [MACHINE CODE]='M02CN004'\n OR [MACHINE CODE]='M02CN005'\n OR [MACHINE CODE]='M02CN006'\n OR [MACHINE CODE]='M02CN007'\n OR [MACHINE CODE]='M02CN008'\n OR [MACHINE CODE]='M02CN009'\n OR [MACHINE CODE]='M02CN010'\n OR [MACHINE CODE]='M02CN011'\n OR [MACHINE CODE]='M02CN012'\n OR [MACHINE CODE]='M02CN013'\n OR [MACHINE CODE]='M02HB001'\n OR [MACHINE CODE]='M02HB002'\n OR [MACHINE CODE]='M02HB004'\n OR [MACHINE CODE]='M02HB006'","outField":"payload","x":500,"y":860,"wires":[["78e7f81e.b0a038","a5820e64.dc55c","48ac6140.2c50c","17534980.bcb117","26cbf43b.40eaec","d95f858f.451bb8"]]},{"id":"78e7f81e.b0a038","type":"debug","z":"1f149e22.43a482","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":850,"y":860,"wires":[]},{"id":"5a910858.f025a8","type":"inject","z":"1f149e22.43a482","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":280,"y":860,"wires":[["e2a56fda.b415","4476fe10.e2765"]]},{"id":"a5820e64.dc55c","type":"ui_gauge","z":"1f149e22.43a482","name":"","group":"1c20b4b1.269adb","order":1,"width":"3","height":"3","gtype":"donut","title":"availability","label":"% age","format":"{{msg.payload[0].availability | number: 0}} %","min":0,"max":"100","colors":["#ff0000","#e6e600","#2ed600"],"seg1":"50","seg2":"70","x":850,"y":700,"wires":[]},{"id":"48ac6140.2c50c","type":"ui_gauge","z":"1f149e22.43a482","name":"","group":"1c20b4b1.269adb","order":1,"width":"3","height":"3","gtype":"donut","title":"performance","label":"% age","format":"{{msg.payload[0].performance | number: 0}} %","min":0,"max":"100","colors":["#ff0000","#e6e600","#2ed600"],"seg1":"50","seg2":"70","x":850,"y":740,"wires":[]},{"id":"17534980.bcb117","type":"ui_gauge","z":"1f149e22.43a482","name":"","group":"1c20b4b1.269adb","order":1,"width":"3","height":"3","gtype":"donut","title":"quality","label":"% age","format":"{{msg.payload[0].quality | number: 0}} %","min":0,"max":"100","colors":["#ff0000","#e6e600","#2ed600"],"seg1":"50","seg2":"70","x":830,"y":780,"wires":[]},{"id":"26cbf43b.40eaec","type":"ui_gauge","z":"1f149e22.43a482","name":"","group":"1c20b4b1.269adb","order":1,"width":"3","height":"3","gtype":"donut","title":"oee","label":"% age","format":"{{msg.payload[0].oee | number: 0}} %","min":0,"max":"100","colors":["#ff0000","#e6e600","#2ed600"],"seg1":"50","seg2":"70","x":830,"y":820,"wires":[]},{"id":"1d1920d3.2a95ef","type":"join","z":"1f149e22.43a482","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":930,"y":940,"wires":[["594910d.0957ef"]]},{"id":"d95f858f.451bb8","type":"function","z":"1f149e22.43a482","name":"","func":"msg.payload.availability=a;\nmsg.topic='availability';\nmsg.payload.performance=b;\nmsg.topic='performance';\nmsg.payload.quality=c;\nmsg.topic='quality';\nmsg.payload.oee=d;\nmsg.tpoic=e;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":760,"y":920,"wires":[["1d1920d3.2a95ef"]]},{"id":"594910d.0957ef","type":"template","z":"1f149e22.43a482","name":"mail","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"Hello! This is an auto generated test mail from Node Red.\n<div>OEE for MT-2's Small Shed from date {{payload.date}} is observed to be {{d}} %</div>\n<div>where</div>\n<div>Availability = {{a}} % </div>\n<div>Performance = {{a}} % </div>\n<div>Quality = {{c}} % </div>","output":"str","x":1050,"y":940,"wires":[["deb014af.7400a8"]]},{"id":"deb014af.7400a8","type":"debug","z":"1f149e22.43a482","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1190,"y":980,"wires":[]},{"id":"898d2a6f.9fb7b8","type":"MSSQL-CN","tdsVersion":"7_3_B","name":"MT02","server":"192.168.21.169\\SCADASQL","port":"1433","encyption":false,"database":"MT02","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5"},{"id":"1c20b4b1.269adb","type":"ui_group","name":"Group 2","tab":"5074feb8.5be66","order":3,"disp":true,"width":6},{"id":"5074feb8.5be66","type":"ui_tab","name":"home","icon":"dashboard","order":13,"disabled":false,"hidden":false}]

```

I don't want to use multiple function nodes can any one help. Also I'm not able to make the first function node to work as my coding skills are not good.

 ![screenshot 5](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/5/755687c4cd2289da495a9a790e9c0e19802b9619.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [1 May 2021 07:37 UTC](https://discourse.nodered.org/t/define-msg-topic-in-function-node/45008/2 "2021-05-01T07:37:05Z")

</div>

Hi sahsha,

In future you should probably provide more info on what the issue is and what you are trying to do.

Anyhow, I recognise what you are trying to do so i will explain for future -

To avoid having to use join nodes (where not necessary), instead of branching off to separate paths, put everything in series e.g...

inject -\>\> Database \>\> move payload somewhere safe \>\> convert times \>\> build mail \>\> send mail.

* * *

As I dont have simpletime node, i just did the from + to dates in the function...

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/b/fbb8792f0be02821751bce092d3cc9af4aa9e1d4.png)

Result...  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/e/0eaa6536b0ce5b22f7e07a9e6a24b1fe156b9b39.png)

flow...

```auto
[{"id":"34522b81.0f6f24","type":"inject","z":"167a2e98.ad02c1","name":"demo data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"oee\":78.6,\"quality\":94,\"performance\":92,\"availability\":91}]","payloadType":"json","x":840,"y":860,"wires":[["d95f858f.451bb8"]]},{"id":"d95f858f.451bb8","type":"function","z":"167a2e98.ad02c1","name":"add dateStart and dateEnd","func":"//calculate 1st and last day of month\nvar date = new Date(), y = date.getFullYear(), m = date.getMonth();\nvar firstDay = new Date(y, m, 1);\nvar lastDay = new Date(y, m + 1, 0);\n\n//format date to dd-mmm-yyyy\nmsg.dateStart = formatDate(firstDay);\nmsg.dateEnd = formatDate(lastDay);\n\nmsg.payload = msg.payload[0]; //move 1st data row into payload\n\nreturn msg;\n\n\nfunction formatDate(d) {\n let ye = new Intl.DateTimeFormat('en', { year: 'numeric' }).format(d);\n let mo = new Intl.DateTimeFormat('en', { month: 'short' }).format(d);\n let da = new Intl.DateTimeFormat('en', { day: '2-digit' }).format(d);\n return `${da}-${mo}-${ye}`;\n}","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1170,"y":820,"wires":[["594910d.0957ef"]]},{"id":"594910d.0957ef","type":"template","z":"167a2e98.ad02c1","name":"mail","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"Hello! This is an auto generated test mail from Node Red.\n<div>OEE for MT-2's Small Shed from '{{dateStart}}' to '{{dateEnd}}' is observed to be {{payload.oee}} %</div>\n<div>where</div>\n<div>Availability = {{payload.availability}} % </div>\n<div>Performance = {{payload.performance}} % </div>\n<div>Quality = {{payload.quality}} % </div>","output":"str","x":1390,"y":820,"wires":[["deb014af.7400a8"]]},{"id":"deb014af.7400a8","type":"debug","z":"167a2e98.ad02c1","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1550,"y":820,"wires":[]}]

```

---

<div class="post-metadata">

**Author:** ![sahsha](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sahsha/32/43845_2.png) [@sahsha](https://discourse.nodered.org/u/sahsha)\
**Post date:** [1 May 2021 08:16 UTC](https://discourse.nodered.org/t/define-msg-topic-in-function-node/45008/3 "2021-05-01T08:16:26Z")

</div>

@Steve-Mcl Thankyou soo much. Will keep in mind to describe the problem briefly.

I also need function node to give date as below, but will try it myself & if I fail then will request for help again 🥺

1. current date
2. fiscal year i.e. from 'April 2021' to 'March 2022'.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [1 May 2021 08:56 UTC](https://discourse.nodered.org/t/define-msg-topic-in-function-node/45008/4 "2021-05-01T08:56:44Z")

</div>

Here is a function you can use inline (in series)

It adds `msg.dates` to the msg object (doesn't alter or change other msg properties like payload)

You can access things like `msg.dates.thisMonth.start` in a function or you can access values in a template like this `FY from {{dates.lastFY.start}} to {{dates.lastFY.end}}`

here is a demo...  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/2/c2ec8e1cc42eb5a6deec3bbe4aafbbaecc6dfe72.png)

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/9/e9a29e585e8be2bd6d2c9da28f951f99de5acccc.png)

```auto
[{"id":"9a4615e5.dbc3f8","type":"inject","z":"45563bb1.635ce4","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":540,"y":560,"wires":[["ce281cd5.8b903"]]},{"id":"ce281cd5.8b903","type":"function","z":"45563bb1.635ce4","name":"Get dates","func":"var date = new Date(), y = date.getFullYear(), m = date.getMonth();\nvar aprThisYear = new Date(y, 3, 1);\nvar marNextYear = new Date(y+1, 2, 1);\nvar aprLastYear = new Date(y - 1, 3, 1);\nvar marThisYear = new Date(y, 2, 1);\nvar firstDayOfThisMonth = new Date(y, m, 1);\nvar lastDayOfThisMonth = new Date(y, m + 1, 0);\nvar firstDayOfLastMonth = new Date(y, m - 1, 1);\nvar lastDayOfLastMonth = new Date(y, m, 0);\n\n\nmsg.dates = {};\n\nmsg.dates.datetime = date;\nmsg.dates.date = formatDateDDMMMYYYY(date);\n\n\nmsg.dates.thisMonth = {\n start : formatDateDDMMMYYYY(firstDayOfThisMonth),\n end: formatDateDDMMMYYYY(lastDayOfThisMonth),\n}\n\nmsg.dates.lastMonth = {\n start: formatDateDDMMMYYYY(firstDayOfLastMonth),\n end: formatDateDDMMMYYYY(lastDayOfLastMonth),\n}\n\nmsg.dates.thisFY = {\n start : formatDateMMMMYYYY(aprThisYear),\n end : formatDateMMMMYYYY(marNextYear)\n}\n\nmsg.dates.lastFY = {\n start : formatDateMMMMYYYY(aprLastYear),\n end : formatDateMMMMYYYY(marThisYear)\n}\n\nfunction formatDateMMMMYYYY(d) {\n let ye = new Intl.DateTimeFormat('en', { year: 'numeric' }).format(d);\n let mo = new Intl.DateTimeFormat('en', { month: 'long' }).format(d);\n return `${mo}-${ye}`;\n}\n\nfunction formatDateDDMMMYYYY(d) {\n let ye = new Intl.DateTimeFormat('en', { year: 'numeric' }).format(d);\n let mo = new Intl.DateTimeFormat('en', { month: 'short' }).format(d);\n let da = new Intl.DateTimeFormat('en', { day: '2-digit' }).format(d);\n return `${da}-${mo}-${ye}`;\n}\n\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":700,"y":560,"wires":[["44619f3b.d76f4","a3542e90.5052"]]},{"id":"44619f3b.d76f4","type":"debug","z":"45563bb1.635ce4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"dates","targetType":"msg","statusVal":"","statusType":"auto","x":930,"y":560,"wires":[]},{"id":"a3542e90.5052","type":"template","z":"45563bb1.635ce4","name":"demo usage","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"Todays date is '{{dates.date}}'\n\nThis FY starts '{{dates.thisFY.start}}' and ends '{{dates.thisFY.end}}'\nLast FY starts '{{dates.lastFY.start}}' and ends '{{dates.lastFY.end}}'\n\nThis month starts '{{dates.thisMonth.start}}' and ends '{{dates.thisMonth.end}}'\nLast month starts '{{dates.lastMonth.start}}' and ends '{{dates.lastMonth.end}}'","output":"str","x":750,"y":640,"wires":[["70110b99.d28b24"]]},{"id":"70110b99.d28b24","type":"debug","z":"45563bb1.635ce4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":930,"y":640,"wires":[]}]

```

---

<div class="post-metadata">

**Author:** ![sahsha](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sahsha/32/43845_2.png) [@sahsha](https://discourse.nodered.org/u/sahsha)\
**Post date:** [1 May 2021 11:17 UTC](https://discourse.nodered.org/t/define-msg-topic-in-function-node/45008/5 "2021-05-01T11:17:03Z")

</div>

@Steve-Mcl, Thanks again. couldn't have done it with you.

---

<div class="post-metadata">

**Author:** ![sahsha](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sahsha/32/43845_2.png) [@sahsha](https://discourse.nodered.org/u/sahsha)\
**Post date:** [1 May 2021 12:57 UTC](https://discourse.nodered.org/t/define-msg-topic-in-function-node/45008/6 "2021-05-01T12:57:08Z")

</div>

@Steve-Mcl , one more question if I want to get previous day date. Then am I doing it right?  
😬  
check below image  
 ![screenshot 6](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/8/c87341ecc712d7afda533594ad3602139fce359f.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [1 May 2021 13:02 UTC](https://discourse.nodered.org/t/define-msg-topic-in-function-node/45008/7 "2021-05-01T13:02:34Z")

</div>

No. You need to use the date functions...

```auto
var yesterday = new Date();
yesterday.setDate(yesterday.getDate() - 1);

```

---

<div class="post-metadata">

**Author:** ![sahsha](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sahsha/32/43845_2.png) [@sahsha](https://discourse.nodered.org/u/sahsha)\
**Post date:** [1 May 2021 13:11 UTC](https://discourse.nodered.org/t/define-msg-topic-in-function-node/45008/8 "2021-05-01T13:11:59Z")

</div>

@Steve-Mcl Arigatogozaimashita, Sensei .... 🙏

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [1 May 2021 13:18 UTC](https://discourse.nodered.org/t/define-msg-topic-in-function-node/45008/9 "2021-05-01T13:18:57Z")

</div>

itashimashite

---

<div class="post-metadata">

**Author:** ![Paul-Reed](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/paul-reed/32/66906_2.png) [@Paul-Reed](https://discourse.nodered.org/u/Paul-Reed)\
**Post date:** [12 May 2021 19:05 UTC](https://discourse.nodered.org/t/define-msg-topic-in-function-node/45008/10 "2021-05-12T19:05:03Z")

</div>

A post was split to a new topic: [Combine queries and send email](https://discourse.nodered.org/t/combine-queries-and-send-email/45559)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [26 May 2021 19:05 UTC](https://discourse.nodered.org/t/define-msg-topic-in-function-node/45008/11 "2021-05-26T19:05:50Z")

</div>

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