using FM to send an SMTP email alert upon [empty field] entry
We use Filemaker Pro 8.5 v2 as client and Filemaker Server 8.0 v4. The database is of course kept on the server so that many people can log in to the Filemaker service. We use this database to keep track of sending personalized "Letters From Home" on a monthly basis to support deployed troops - letters written by a great many people. The problem is that some soldiers don't get their letter sent by their sponsor on some months (say, the sponsor forgot that month) , and we need some way to be alerted to this oversight.
I have already read that we can configure FM to send out SMTP emails. I also know that FM can be configured to detect alerts upon certain things, such as when an expiration date has been exceeded. I'd like to know whether there is a way to have FM check when there is an empty field and send an alert at that time. Here is my example:
[record for] Corporal Sean Smith has received 27 Letters From Home. The record below starts at number 28. We always ask people to skip a line if they can see that a month was skipped.
MONTH DAY YEAR LETTER NO. AUTHOR OF LETTER
8 16 2009 28 Sally D.
9 11 2009 29 Spencer T.
11 20 2009 31 Jack H.
12 07 2009 32 Sally D.
As you can see above, the person responsible for sending Corporal Smith a letter in the month of October failed to do so.
By design, a blank line is present except for the first columnm which must be filled in order to force FM to get to the next line.
The only problem is getting people to report when this has occurred. Is there a way (or a software add-on) to get FM to send out an email to a program manager when such an empty field has been created? It could be a script which we implement every Friday for instance. What we are looking at here from a programming standpoint is [send email if the most recent line minus one line, has an empty field].