Jump to content

Calculate Date and send Email

This topic is 4389 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I would like to set up a trigger of sorts to send an email for any record in the database that is based on an upcoming event date where the event date is less than 2 weeks away from a stored target completion date.

I understand the logic needed (I have conditional formatting set up already to visually highlight the date), but is there a simple way to do this per record check (aside from looping through all the records to check this condition)?

Hope I explained this OK....

Thanks -

Link to comment
Share on other sites

Thanks for the response Mr. V!

Yep - I kinda figured I would need something like what you are suggesting. Turns out I already have a script that spits out a pretty report for this condition, so I guess could use the "guts" of that to pull out the records I need into the email.

At the risk of revealing my infinitely small knowledge of FM, how do I set up this script to run automatically each day to check the records and send the email? A script trigger that gets fired when anyone logs in for the day, set a flag that it's been run for the day, and then prevent all other logins from running it until the next day?

Thanks again - have one on me! :wholeyard:

Link to comment
Share on other sites

You could use a dedicated copy of Pro on its own box to do this ( robot ) with a scheduling program.

You could have it run with your first user that logs in but then you have to set the date in a record perhaps in a dedicated table and check against that to make sure no one else runs it again when they log in.

If you have server, you can have server run a server side script.

Link to comment
Share on other sites

  • 1 year later...

Cool idea about the scheduling the script on the server. I have deployed on FM server, so I'll go that route.

Thanks again for the help!


Would you be willing to share the script you wrote for this. I have the exact same issue but I'm struggling to write the script.


Link to comment
Share on other sites

Hi! All,

I could use some help as follows:


Database hosted on remote server

Filemaker Pro 11

Windows 7

Email program: Outlook 2010

I have an End Date field in a table and want the user (multiple users who access the database on the server) to receive an Email which will alert them when the End Date of a record falls to 14 days from today's date (current date). I know that I can send Email from FM via Outlook and think I can work that part out but I'm really struggling with the calculation/script for the date. Also, I'm not quite sure how to set up the script trigger and loop through the records. The Email could be sent by just one person who will log on to the server at least once weekly or could be triggered by each individual account holder for their own records (I have another field in another table in the same database which is related to the first table which contains the account name for the person the record(s) belongs to.

Any help will be greatly appreciated.

Link to comment
Share on other sites

I would create a flag calc field in the table that equals 1 when an email is due,

flag_EmailDue_c = If ( Date - Get (CurrentDate) ≥ 14 and isempty (EmailSentDate) ; 1 ; "").

Now the fun part. You need a server script scheduled for once a day:

1. Finds all records where flag_EmailDue = 1

2. Loops thru the found set , sending an email to the Staff email address related to the record (you'll need their email address in their staff record). Also, use Set Field (EmailSentDate); Get (CurrentDate). The server script halts if Get(foundcount)=0.

You don't need Outlook at all. FM supports SMTP directly.



Oh, I see that this is really a continuation of the same topic. I've merged the threads.

Link to comment
Share on other sites

If the script runs on the server you'll NEED to send the mail using the direct SMTP option. BTW the Send Mail by SMTP was introduced in FMP 10 so you'll need FMS 10 or later.

Link to comment
Share on other sites

This topic is 4389 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.