1 Reply Latest reply on Oct 16, 2009 1:05 PM by philmodjunk

    using FM to send an SMTP email alert upon [empty field] entry



      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].




        • 1. Re: using FM to send an SMTP email alert upon [empty field] entry

          The simplest approach would be to use the script that creates the blank line in the first place to also send out your email. Can't tell from your description whether that is possible or not.


          Alternatively, you could script a find for the entries with blank lines and send emails out if any are found.


          Assuming you want a email sent if no letter was sent last month...

          If you don't have an actual date field combining the day, month, year columns of your example, create one.


          Enter find mode []

          Set field [yourtable::yourdatefield; Month( get ( CurrentDate ) ) - 1 & "/" & Year ( get ( CurrentDate ) ) ]

          Set Field [YourTable::YourMonthColumn ; "="] //finds all records where the month column is empty//

          Set Error Capture [on]

          Perform Find[]

          Set Error capture [off]

          If [ get ( FoundCount ) > 0 /* There are blank entries */ ]

            Send Mail

          End IF


          There are a lot of possible details here not yet resolved as I don't know exactly what you need but this should get you started.