How to make filemaker automatically send an email reminder 1 month prior birthday date? For your information this is how I setup our Filemaker enviroment; Filemaker Server is installed on our Server (Windows Server 2008) and the rest of the clients (domain users) have Filemaker Pro 11 installed on their computer. The Database is loaded on Filemaker Server and clients can access it via Filemaker Pro installed on their PCs. On the database, I have user's record with all their birthday dates, I want to setup filemaker so that it will automatically send an email reminder 1 month before the birthday date comes to the user's record and maybe some other users. Please keep in mind that I want it to do all of this automatically (set it up once and just leave it). Can anyone please be kind enough to explain in detail how to accomplish this? Thank you.

You create a script, that you want to run every day. This script should search your table with persons for birthdays that are current date + 30 days. For the found set, your script should then send out an email for each, with a prefixed text. Could be with substitutions for e.g. names. ( Like; Dear <<Name>> ).

You can set it up to run from a server schedule, that runs every day. You will need to set the mail script to use a SMTP server to send through.

With scripts, that runs serverside, and sends out emails, you should be carefull to put in error trapping, so you don't risk e.g. to send out emails to every person in the table, if your search e.g. fails. When testing such functions, it is a good idea to put in your own email address in the script, instead of the real recipients, so the emails will not hit people by mistake.

Thank you Claus Lavendt for your reply,

A script seems to be a great idea (and I was struggling with some sort of calculation), anyway could you please elaborate more on the script or maybe give an example. I'm no expert and would really appreciate it if you could just write down an example script that I could use/edit it according to my needs.

Thank you again and hope to see more replies on the matter.

I make a couple of assumptions here, since you have not described exactly how your setup is and what your goal is in detail.

Goal: Have the server send out an email daily to people, that have birthday 30 days from current date.
You have a table, here we call it "Person" with records for people. In this table you have a field with "Name" and a field with "Birthday". The birthday field is a date field that is filled with the person's birthday. (day/month/year) Also a field with the person's email address.
You also need to have a SMTP account though which, you can send these emails. The account info must be entered in the "Send Mail" script step.

Script: (just basic example without error trapping etc.)

GoToLayout ( layoutBasedOnPersonTable )
Enter Find Mode
SetField ( Person::Birthday ; <<replaceWithCalculationBelow>> )

Let ( [

~bDay30 = GetAsDate ( Get ( CurrentHostTimeStamp ) ) + 30  // This will give you today + 30 days

] ;
Day ( ~bDay30 ) & "/" & Month ( ~bDay30 ) & "/*"
// The above calc will output like this, if today is 25/08/2015 :  24/9/*  -> the * means wildcard for the year

)

Perform Find

If ( Get ( LastError ) = 0 )    // means that no error occurred in the search and thereby we have found records that match

GoToRecord ( first )

Loop

SendMail ( use your SMTP account details. To address should be the email address from the Person record. The body could be like the calculation below )

Calculation for Body part:

"Dear " & Person::Name & "¶¶" & "We just wanted to wish you a happy birthday that is coming in 30 days from now" & "¶¶¶" & "You get good deals on your birthday" & "¶¶" & "Best wishes from us all"

GoToRecord ( next, exit after last )

End Loop

 

Then you can setup a server schedule to run every day with this script.

 

As I wrote originally, make sure to include error trapping and do some tests. When testing, it is wise to use your own email address in the "TO" field of the Send Mail script step, so you don't risk to send out emails to a lot of people by mistake.

 

Have fun !

// The above calc will output like this, if today is 25/08/2015 :  24/9/*  -> the * means wildcard for the year

You are assuming the date format in use is D/M/Y. Here's a way to make the calculation format-agnostic:

Let ( [
d = Get ( CurrentDate ) + 30
] ;
Substitute ( d ; Year ( d ) ; "*" )
)

There's also the matter of people born on February 29; your script will find them once every 4 years.

See also:
http://fmforums.com/topic/97747-looping-script-for-email-notification/#comment-444405

 

Edited August 25, 201510 yr by comment

"We just wanted to wish you a happy birthday that is coming in 30 days from now" & "¶¶¶" & "You get good deals on your birthday" & "¶¶" & "Best wishes from us all"

Just a small point but you don't need to trap the paragraph marks eg "birthday" & "¶¶" & "Best" = "birthday¶¶Best"

Just a small point but you don't need to trap the paragraph marks eg "birthday" & "¶¶" & "Best" = "birthday¶¶Best"

You are right. I wrote it this way as I was trying to make the calculation a bit more clear.... maybe I failed on that...

I just want to give a big thanks to you all for your contribution regarding the topic,

Claus Lavendt thank you for elaborating more on the script, Comment thanks for reminding me about Feb 28,29 date and Aussie John thank you for clarifying that I don't need to trap the paragraph marks etc...

Taking all that into consideration and long testings, I came up with some problems at first with the script and smtp errors, etc...but now I just want to give you an update that the script is working and that it is sending an email (to my email address first for testing) without a problem. Considering the month February which ends in 28 and sometimes 29, I re-wrote the script to check if today's month is 1 less than bday month then keep sending reminder until ...mmhh I don't know I'm still thinking of the best way of when to stop reminding.

I will continue and try sending the email reminder now to bday users (not me) and multiple if found set is more than 1.

I will be back if I come up with more problems etc...so please don't get tired of me just yet.

Thank you again and feel free to add more ideas to make this better.

 

feel free to add more ideas to make this better.

Well, since you ask ... I believe it would be better to not to do this at all. Because:

• No one needs to be reminded of their birthday;
• No one goes "Aww, how nice of the computer to remember" over an automated e-mail;
• Some people may view this as inappropriate use of their private data;
• You must do this every day, 365 366 days a year, weekends, holidays, rain or shine - otherwise you risk offending someone.

 

Edited August 27, 201510 yr by comment

Thank you Comment for your thoughts...I totally agree with you (birthdays suppose to be a surprise anyway hehe), I am actually using this idea on a salary increments annually on our company staff.

So every year when a staff salary incremental date comes, I want the current staff, accountant and our boss to be reminded to take procedures in processing the increment on the concerning staff salary, but the process always take long and that is why the reminder should be send 1 month prior the increment date.

So yeah, take away the birthday and replace it with salary incremental date.

I'll upload a working solution for when I'm done. In the meantime I might return to the topic from time to time for more help.

Thank you again.

 

 

Great, so I'm back with 1 problem which I really need some help with.

Script and email (smtp) are working great.

Now that I am going to do this correctly (apply the testing to what's required), I came up with a small problem. I'll try and give you an example in detail so you'll get what I mean;

Example: In "Send Mail" Options window, I'm sending the email to a specific user/record (accountant), and CC it to our boss. And then I tick the "Collect addresses across found set" under CC so that the same email goes to the concerning staff as well. I manually wrote my Subject and all is fine without a problem. Now the problem that I came across is in the "Message" section because I can write anything their and it'll go to the accountant, cc to our boss and other staff in the foundset without a problem, BUT I want to write something that will mention those staff in the foundset as well for example:

"Dear (accountant), please proceed with the bla bla bla...regarding the staff listed below;

<foundset name1> <incremental date>

<foundset name2> <incremental date>

etc...

Thank you"

So something like the above message, how can I do that the proper way?

Thank you and I hope to hear from you soon.

1. Are you really using version 11?
2. What exactly does <incremental date> mean?

 

Loop through your found set before the send email and collect all the staff names in a variable:

set variable [$stafflist; ""]
Go to record [first]
loop
  set variable [$stafflist; $stafflist & "¶" & table::name & " " & table::incdate]
  go to record [next/exit after last]
  end loop
send mail

then insert the $stafflist variable into the message body where you need it.

Edited September 2, 201510 yr by OlgerDiekstra

@comment - yes we're actually using FM11 atm, Incremental Date refers to the staff's salary in which it will get increased annually which I explained earlier on my previous reply.

@OlgerDiekstra - thank you for your reply, it looks like you know exactly what I mean given the script you provided. I'll try it out the best way to my knowledge and I will get back to you if I come up with problems or if it works.

Thank you again and please feel free to add more input on the matter.

 

Thanks a lot here's a working script for me:

Go to Layout [original layout]
Enter Find Mode []
Set Field [Table::IncDate; (Month (Get (CurrentDate))) + 1 & "/*" & "/*"
Perform Find []
If [(Get (LastError) = 0)
    Set Variable [$stafflist:"Names and Incremental Dates"]
    Go to Record [First]
    Loop
        Set Variable [$stafflist; Value:$stafflist & "¶" & Table:Name & " " & Table:IncDate]
        Go to Record [Next;Exit after last]
    End Loop
    Send Mail //Use calculation to write message and put $stafflist variable where I need it.
End If

I run the script on Filemaker Server on schedule.

Thanks again.

Incremental Date refers to the staff's salary

You managed to confuse me earlier when you numbered the name field <foundset name1>, <foundset name2> but not the <incremental date> - leading me to suspect it's not sourced from each individual record in the found set. Now you've managed to confuse me even further: you have a field named Incremental Date that contains a salary?

Anyway, a scripted loop to collect the values is probably the best route to take here.

 

 

set variable [$stafflist; ""]

That doesn't do anything.

 

 

Edited September 2, 201510 yr by comment

Sorry Comment, just ignore the <foundset name1>, etc...I might have written it in a confusing way cos I don't have much knowledge especially in programming & scripting but I thought that would clarify things a bit more but I may be wrong (good thing OlgerDiekstra gets my point). And yes, the "Incremental Date" field refers to the staff's salary IN WHICH the salary is increased annually, That is how my company work with salary anyway....you get a starting salary and then it get increased (incremented) annually until you reach its max (4 years mostly) depending on your performance, etc...

And yes you right about the set variable [$stafflist; ""] that didn't do anything, I struggled with it at first but after reading and more research I found the solution (check my previous post).

Thank you guys again and Sorry Comment for the confusion.

set variable [$stafflist; ""]

That doesn't do anything.

It does, it ensures the variable is empty before entering the loop. However, depending on what happens earlier in the script, I agree that it may not be necessary. I did that because $stafflist is added to itself in the var calc.