Calculate Date and send Email

[bdavid]

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 -

[mr_vodka]

You can have a script perform a find for the condition and then email a list of those records. Will that work for you?

[bdavid]

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:

[mr_vodka]

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.

[bdavid]

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!

[smithpuckett]

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!

bdavid

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.

Thanks

[smithpuckett]

Hi! All,

I could use some help as follows:

Setup:

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.

[bcooney]

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.

hth,

Barbara

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

[Vaughan]

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.