6 Replies Latest reply on Feb 8, 2016 11:50 PM by Fahri Akar

    Automated email

    mgreen

      Hi Everyone,

       

      I am trying to create a script that would allow me to send automated emails to notify specific users that their instrument is due for calibration. We have each instrument logged into FileMaker with the name of the user, calibration would occur every 3 months. I would really appreciate any help with this!

       

      Thank you,

       

      Maria

        • 1. Re: Automated email
          Fahri Akar

          Create a relationship based on current date.

          - - - - - - - - - - - - - - - - - - - - - - - - -

          This table::CurrenDate (Global Field)= CalibrationCheck::CalDate [Stored Calculation: Logged Date + 90]

          - - - - - - - - - - - - - - - - - - - - - - - - -

          On a hosted file you may use a scheduled script.

          If you open and close your solution everyday, you may use a script trigger on Open.


          Script is similar to:

          - - - - - - - - - - - - - - - - - - - - - - - - -

          Set Field [This table::CurrentDate; Get ( CurrentDate ]

          Commit Records/Requests [no dialog]

          If [Count ( CalibrationCheck::CurrentDate > 0]

              Go to Related Record [Show only related records; Match found set; From table::CalibrationCheck; Using layout: "CalibrationCheck"]

              Go to Record/Request/Page [First]

              Set variable [$Count; Value: 0 ]

              Loop

                    Send Mail [Send via E-mail Client; No dialog; To: "user@company.com"; Subject: "Alert for instrument calibration!"; Message: "Your instrument's calibration is out of date.¶¶Admin"

                     Set variable [$Users; Value: Case ( not IsEmpty ($Users) ; $Users  & ", " ; $Users ) ]

                    Set variable [$Count; Value: $Count + 1 ]

                    Go to Record/Request/Page [Next; Exit after last]

                End Loop

                 Show Custom Dialog ["Message"; "Mail sent for calibration to" & $Count & " users.¶" & "Users  Ids: "  &  $Users]

          End If

          - - - - - - - - - - - - - - - - - - - - - - - - -

          If you set SMTP server settings you do not an email client either.

          mail2.png

          Fahri

          • 2. Re: Automated email
            mikebeargie

            Have you dealt with filemaker server scheduled scripts at all?

             

            A simple script to check for items needing calibration, and emailing the user for each match, would suit your needs.

             

            Something like:

            Enter Find Mode

            Go To Layout [ instruments ]

            Set Field [ instrument::nextCalibrationDate ; "=<" & get(currentdate) ]

            Perform Find

            If [ get(foundcount) > 0 ]

               Loop

                  Send Mail [ smtp, send to contact attached to tool ]

                  Go To Record [ next ; exit after last ]

               End Loop

            End If

            • 3. Re: Automated email
              mikebeargie

              What about days when users don't log in or out, or leave their files open overnight? A server scheduled script is much better suited for tasks like this.

              • 4. Re: Automated email
                mgreen

                I have not, but based on the information you provided it does seem like server scheduled script would work better for my intended purposes.

                So far we have only used FileMaker to store instrument info(serial #s,last calibration,owner) but it would be great if we could receive a notification when calibration is becoming due.

                • 5. Re: Automated email
                  mgreen

                  Thank you for your reply!

                  We have several tabs under our layout, such as instruments, samples, current orders etc. How would I select a specific one?

                  Also there only info on last calibration, from which the 3 month count down would start, so I would need code that can calculate 90 days from last calibration.

                  • 6. Re: Automated email
                    mgreen

                    I am actually wondering how to get it sent to the owner of the instrument, who's name is listed in the layout. Would I first need to link names to their emails?

                    • 7. Re: Automated email
                      Fahri Akar

                      In Send Mail script step you see calculation fields as To, Cc, Bcc, Subject, Message and Attach File.

                      mail3.jpg

                      You have three options with them:

                      1. Manually edit,
                      2. Field definition,
                      3. Specify a calculation.

                      To, Cc, and Bcc fields have an extra Collect addresses across found set checkboxes which allows you to send multiple mails at once.

                      Refer to http://www.filemaker.com/help/13/fmp/en/html/scripts_ref2.37.70.html and http://www.filemaker.com/help/13/fmp/en/html/import_export.17.8.html

                      From the article: "Important  Use Collect addresses across found set with caution because it can generate a lot of email messages. For example, if your found set contains 10 records and you specified a field that contains a single email address in each record, 100 email messages would be generated. Each email address will receive 10 emails."

                       

                      If you already have a list your job is easy.

                       

                      You declared before "We have each instrument logged into FileMaker with the name of the user". You probably have at least two tables and relationship between them.

                      1. Users
                      2. Instruments

                      There must be a relationship from Instruments table to the Users table based on user ID. By using this relationship you will collect the information stored in email field as "users::email"

                       

                      Fahri