Solved

Send one email if multiple rows are added to a table

  • 16 April 2021
  • 1 reply
  • 116 views
N
Rank
Badge +3

Me again! Hi!

I’m not sure the best way to go about this. I tend to over-complicate things!

As everyone is aware, app messages write to the FNDCN_Application_Message_TAB. For a very specific type of error, I need an email to go out and say “look at this record - it has an error”. I set up a custom API that writes those very specific messages to a new table. I can set up a scheduled task to run every hour to fill that table, and then set up an email to run when there are new records added, but that would mean getting one email per record, not per incident. One thing failing could affect dozens and dozens of records and write mass amounts of data to the table. I need a way to somehow send every message but not each in its own email.

Thoughts? 

 

 

 

 

 

 

 

 

 

icon

Best answer by NickPorter 16 April 2021, 18:58

View original
This topic has been closed for comments

1 reply

NickPorter
Rank
Userlevel 6
Badge +18

So are you intending to send an email saying “Error X has occurred one or more times, go  and check them out” or “Error X occurred and here is a list of ALL messaged IDs that had it...<list>” ?

For the first variant you could create your table using a DISTINCT clause (or take from your existing table and create another one to simplify with the distinct errors) and then email using an event against that every so often (e.g. every hour).  That would be simpler if that gives you what you want. Clean up the table once you send the email.

The second variant would be more complex requiring you to track every instance of a message against each error type, include that list of message IDs and then handle the cleanup of the table so you don’t keep reporting on the prior messages for an error that happens again.  Again, run it every  hour or whatever interval.

Depending on the error message that occurs, you may not even be able to readily compare errors to see if 2 message IDs with the same fundamental problem have the exact same message.  You would likely need to truncate or parse the text for specific strings only.

Nick

Terms & ConditionsCookie settings