I need to create an Ignition email system that sends emails to hundreds of different potential recipients. When an email needs to be sent, it is written to a SQL database table by another application. Each row in the SQL database table represents a text message. This row contains the email message and up to 6 email addresses. The email addresses are maintained in this other application and changes, deletions, and additions occur on a daily basis. I need to send this emails as they arrive. Due to how the software functions, I will have at most 1 email per minute, so the volume is very low.
At this point, I setup one alarm notification (email_server). I setup one user (email_user) with 3 email addresses as the contract info. I setup one on call roster (oncall_roster) that contains only email_user. I setup one alarm notification pipeline (email_pipe). I have an Ignition script that monitors the SQL database email table (via a stored procedure call). When a new email arrives, the scripts receives the email message and up to 6 email addresses. At this point, I write the email message into a string tag and set an alarm tag to send the email message. Works great, all users in email_user receive the email. It keeps checking the table and when emails arrive it sends them.
The obvious problem is that the email addresses change based on the data in the SQL table and email_user currently has 3 hardcoded email addresses as contact info. What I would like to do is to be able to delete the old email addresses from email_user and add the new email addresses to email_user. Then send the email.
I have way too many users to set them all up in Ignition. And more importantly, they are maintained in another application and the user will not want to have to go maintain the users in both systems (I see where you can add an interface to allow users to change the user information including contact info, but double entry is cumbersome, time consuming, and prone to errors. I just want to have one server, one user, one roster, one pipeline and dynamically change the email addresses via a gateway script. Does anyone know if this is possible? If not, is there another way to dynamically setup email addresses like this?
Sorry for the long explanation (and probably not very clear explanation), but I wasn’t sure how to make it more concise or clear. Any help would be greatly appreciated.