Jump to content

Birthday reminder from Filemaker via email


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

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 !

  • Like 1
Link to comment
Share on other sites

// 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 by comment
  • Like 2
Link to comment
Share on other sites

"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"

  • Like 2
Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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 by comment
  • Like 2
Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by OlgerDiekstra
  • Like 1
Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

This topic is 3131 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.