Effectively, when the top row in a query changes, I need to send an email.
Anyway that I think of to setup a tag though, it will be emailing when the top row has not changed though.
I am not sure how to prevent false events.
Do I have to do a convoluted thing?
Like one query tag for the row
A counter of some kind with an event script
A memory tag that is only written to from the counter
what code do you have now that triggers to much?
I just imagined that if I made a query tag, the moment my gateway went down, the value would read null and then send an email when it updated.
Or that any hiccup might send an email when the value didn't really change.
Do you want to track any specific value in the row? Or that all values have changed?
You can inhibit the initial script with the initialChange value to not fire off when the gateway is restarting and.
if you want to track changes in the db even when igntion goes down but the db might work with something else, then could store the changes in the database.
we often have "audit" tables i our db's where all create/update/delete events gets in stored per table.
(ofc clean these after a while so it doesnt get to full)
Thanks, that initialChange boolean will help me filter some.
I am thinking that I might set a query tag up to read the top row, and if the initialChange bit is false, then compare to a memory tag, send the email if no match, then write to the tag.
If there is no change, or the initialChange bit is true, then I won't send an email.
I am a little concerned I might miss a change though in that case.
I could look at the timestamp, and email when it changes.
At the end of a shift, if they did great then I insert a new row.
I am to send an email to a manager, so they know.
I could look at just the timestamp or the whole row.
Well I have many machines, it probably is even more complex.
But maybe it makes it simpler.
Maybe I just set a timestamp of when I last sent an email, and send an email of any times since the last timestamp.
Do I create a table of sent events, and compare my list to that?
I am kind of thinking maybe that is easier, but also maybe silly.
I can't tell if I am over thinking it or it is an ideal usage for tables.
I think I will do two table, checked periodically.
SELECT table1.*
FROM table1
LEFT JOIN table2 ON table1.key_column = table2.key_column
WHERE table2.key_column IS NULL;
I also plan to use code from here, to pack the data up into a csv to email.
Export dataset to CSV and send as email attachment - #8 by jmacascas]
Regarding the trigger I am partial to what @victordcq suggested and that's how I like to do things when I have to shell out. Whenever I have process that Vision\Ignition starts, but cannot not directly do (but instead calls out to an external service like emailing does, or SFTP'ing a with a batch file for example) it's nice to have a database table keep track of what jobs those are.
For you, you might want a table like
CREATE TABLE emailJobs(
id int not null primary key,
newValue (if there's a column you want to reference in the email, put it here),
newValue2 (if there's a second column that helps put form the subject or body of the email),
newValue3(and so on for any other Params you may want to grab to help form your email),
tsAttempted datetime default NULL);
Then, somewhere in your application, you must have something like a UPDATE originalTable SET someColumn=? WHERE id=? where hopefully you can tell when a row that you want emailed about is running.
When you see a row that you know needs to be updated in your previous update statement, then INSERT into emailJobs.
Then I would create gateway timer script that once every few minutes looks at your emailJobs database table where the tsAttempted is NULL, and then tries to run that particular email job, and updates the table afterwards.
I believe system.net.sendEmail does not give you confirmation or an error in your script and it really can't because it has to call out to your SMTP server can take a while, etc so any success / errors go straight to the gateway as I recall. However, after you attempt your email, you can then update your tsAttempted column with the time, so if anything does go awry you at least know what time frame to look at in your logs. If I am wrong though, you could always add another column to emailJobs to record your result.
emailJobs now basically acts like a queue for you that will survive GW restarts. SELECT * FROM emailJobs WHERE tsAttempted IS NULL ORDER BY id ASC would give the next job and I would use that in my GW script.
Yah, that part where the SMTP doesn't give me any confirmation is rough.
Does SMTP fault in scripts at all?
Like of my SMTP is down, at least I can detect that with a try and then return before inserting to the attempted table?
Just being able to say ping a SMTP server does not mean anything about are you going to be able to send out an email or anything. According to the docs system.net.sendEmail - Ignition User Manual 8.1 - Ignition Documentation it returns Nothing.
Personally I would just have it fire and forget it. I would hope someone on the email list cares about this process and knows when it should come through and if it doesn't can make a stink about it to someone. But if it's working and then after a while stops working, its more than likely an IT change that has nothing to do with Ignition or anything you did and I'm more than happy to let the "Scream Test" be my notification the same way IT tends to do when they make changes without asking.
Scream Test -
There was a hydraulic engineer that used to joke about making the Scream 9000 that was a shutoff relay that could be installed on equipment for safety, which would use a DFT to detect a scream through noise as a trigger.
Reminded me of it.
I don't know if I can setup a complaint page, detect when someone is upset an email didn't get sent and send it though.
I might be able to make a manual entry page though that can see if someone verified they saw and rewarded the people who did a great job.
Could spam them if they don't ... hmmm
I better set the emailing to a subscribable and unsubscribable email list.
I think I will take another try at just making this a list someone can just look at and export at their leisure.