How to send SMS to different number using Twilio?

[Conard]

I have a database and its design is like this in my Microsoft SQL Database:

Once a student has scanned his fingerprint, the serial port will generate output like this:
{"SID":1,"Date":"20181121","Time":"01:54:54"}

I would like to send a SMS to a student's parent after scanning finger, How can I do it??
I would like to send the SMS to parents' phone number stored inside my database
I have a Twilio account with me.

[Conard]

I convert the serial output into object using JSON Node like this:

[ukmoose]

So if you can identify the student, you would need to then do a database lookup to return the information you need. Then you would need to add the appropriate information to into a message and send that on to a Twilio node. Depending where you live you would also need to register the data you are keeping to comply with data protection legislation.

[Conard]

My idea is using that SID, coming out from serial port.
Then write an SELECT statement with INNER JOIN:
SELECT p.PhoneNo
FROM
student s
INNER JOIN student_parent sp
ON s.SID = sp.SID
INNER JOIN parent p
ON sp.PID = p.PID

Once I get the phone number, then send SMS to that phone number.

But I don't know how to do that, what node should I use? How the flow looks like?
I only know MSSQL & Twilio are required

[Conard]

My idea is using the SID, coming out from the serial port.
Then write an SELECT statement with INNER JOIN to get parent phone number:
SELECT p.PhoneNo FROM student s INNER JOIN student_parent sp ON s.SID = sp.SID INNER JOIN parent p ON sp.PID = p.PID WHERE s.SID = ??

And send the SMS to the parent based on the phone number stored in database
But I don't know how to do it. What node should I use? How the flow design looks like?
I only know MSSQL and Twilio Node are required

[ukmoose]

I've never used MSSQL, but I'd start by searching flows.nodered.org for:
"MSSQL"
then try downloading one and trying.

[Conard]

I can get the phone number now, how can I make it send SMS to the phone number I get?
It seems like Twilio Node has no setting for me to write code on it

[ukmoose]

You shouldn't have two paths coming from your JSON node.
As you are sending two messages to your MSSQL node (node-RED won't just "join" the messages back together again"

On the twilio question, have you read the info panel of the node? Our read the readme for the node on flows.nodered.org?
You can use the change node to move a property of the msg object

[Conard]

Upper Template Node is storing attendance record to MSSQL
Lower Template Node is getting parent's phone number from MSSQL by using SELECT statement I have mentioned above.

I'm using Debug Node to make sure that I can actually get the phone number from MSSQL, You can just ignore the second result (Undefined).

For now, I would like to use the phone number I get from MSSQL, and send SMS using Twilio Node. But Twilio Node can only put a To phone number in node properties, that means only one parent can get the SMS. All SMS will be received by only one phone number (parent), even not his/her children

[ukmoose]

You can use the message to define the sendto phone number as is described in the Nodes readme.
(assuming you are using node-red-node-twilio)

[Conard]

what do you mean? use debug node? I would like to send SMS to different number, but Twilio Node (node-red-node-twilio) allows one To phone number

[Colin]

From the Twilio node's readme " The node can be configured with the number to send the message to. Alternatively, if the number is left blank, it can be set using msg.topic."

[Conard]

Yup, Colin. I read that too
But I dont really understand what that means....
Do I need another node to do it? Or just change the To field to msg.Topic in Twilio Node?

[ukmoose]

You leave the To entry in the node empty.

It then takes the value of msg.topic as the phone number.
Try it with your own number...

[Colin]

You can use a change node between the db node and the twilio node configured to Move msg.payload[0].P_Contact To msg.topic

[Conard]

How can I generatemsg.Topic value?

[ukmoose]

You can use a function node or a change node. But really if you need to ask that it would be worth stopping and doing one of the online tutorials to Node-RED.

[Conard]

Is it correct? Change Node properties

And I always get the error Connection is closed to MSSQL Node. I don't know why

[ukmoose]

Place debug nodes along your flow, so you can see what messages are being passed along and what they contain. Take time to read the documentation such as this page https://nodered.org/docs/user-guide/messages

But time on doing one of the tutorials would be time well spent...

[Conard]

Ok. I have success received the SMS. But message body is empty? How can I edit the message content?