?
Solved

Simple FileMaker Pro Calculation and Script (Date Related)

Posted on 2009-02-19
21
Medium Priority
?
1,285 Views
Last Modified: 2012-05-06
I would like to create an automated email that is sent to me based on a certain date.

For example:

Client = John Doe
Email = john@domain.com
EventDate  = 09/20/2009
Balance = $100
BalanceDue = 30 Days before EventDate

---

Send automated email to myself on BalanceDue stating that Client at Email needs to pay Balance immediately.
0
Comment
Question by:jazzviolin
  • 7
  • 7
  • 6
20 Comments
 
LVL 5

Expert Comment

by:Peter Harris
ID: 23689063
The answer may be different depending on the version of FileMaker being used and whether it is on a server.

Can you please provide the FileMaker Pro version you are using and FileMaker Server version if on a server.
0
 

Author Comment

by:jazzviolin
ID: 23689152
FileMaker Pro 9 - I'm hosting the master file online with a hosting company that hosts FMP databases.  
0
 
LVL 5

Expert Comment

by:Peter Harris
ID: 23689646
You need to run a script (daily) that finds records the where BalanceDueDate = Today, then email each of the matching clients.

FileMaker 10 has an "onTimer" script trigger which could help to do the job. Also if you had the database on FM Server v10 it can run scheduled scripts.

It is worth checking with your FM hoster to see if they offer any scheduled script capability.

Uusally with FileMaker 9 you will need to use a plugin (or other extra software) like one these options:

Troi Automater
or
myfmbutler DoScript
or
myFMbutler AutoSender

http://www.troi.com
http://myfmbutler.com
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:jazzviolin
ID: 23689730
Okay I'm purchasing Filemaker 10 Advanced tomorrow.  Can you outline step-by-step instructions on how to do this with 10 then?  Thank you for your support.
0
 
LVL 5

Expert Comment

by:Peter Harris
ID: 23690850
I have put together a demo file for you.

One limitation is that you need a FileMaker window to be open for the program to continue to run. It also needs a net connection of course to send the email. So essentially you need a computer somewhere with the database open.
It is OK to have the database hosted with an online service but you still need a 'workhorse' computer to handle the emailing process.
The 'workhorse' can do other things, it just has to keep the 'special' FileMaker window open.

I've tested the solution and it happily emails me (I put in it my gmail settings which I've now taken out) every 60 seconds. You might want to test it on that to start with and later change it to 86400 (60 * 60 * 24).

You can download it from here:
http://cognitive.com.au/solutions.htm
0
 

Author Comment

by:jazzviolin
ID: 23690873
Awesome.  I will attempt to this tomorrow when I get FMP 10.  Thank you for your help.
0
 

Author Comment

by:jazzviolin
ID: 23692728
Having trouble getting it to email.  A direction sheet would be wonderful.
0
 
LVL 5

Expert Comment

by:Peter Harris
ID: 23697172
I will put together details in a few hours. In the meantime try and obtain you SMTP settings (from you email service provider). That is outgoing mail server, name, pass, SSL ON or off.
0
 

Author Comment

by:jazzviolin
ID: 23697288
Will do.  I examined your script closely and thought I understood it and had it, but for some reason, it never emailed.
Thank you.
0
 
LVL 28

Expert Comment

by:lesouef
ID: 23697392
you can avoid to leave it open all the time if you set your script to be the startup script, and set that same script to close the file upon completion, then put a task in the scheduler to start fm + the file @ a certain time.
if this script is one script among a more complex application, simply do a separate fm file with a script which will open the main file, execute the mail script then closes the main file, then closes itself, thus avoiding the main app to close when used directly. the cheapest timer over all. the other advantage is that if the app is closed for some reason, the scheduler always works even after a reboot ir a server disconnection.
0
 
LVL 28

Expert Comment

by:lesouef
ID: 23697408
last bit, does not need fm10...
0
 
LVL 5

Accepted Solution

by:
Peter Harris earned 1000 total points
ID: 23697802
The idea of running the script when opening the file is good.
To have built-in SMTP emailing you will need v10.

The script that actually does the emailing is in the attached snippet. Create this script first and test it to see that it runs correctly.

You will need to edit the details of who is emailed and your own SMTP email settings.

To make the script run when opening the database:
1. Open the FileMaker database.
2. Choose 'File Options...' from the file menu.
3. Turn on 'Perform script:' check box and specify your emailing script.

As discussed by lesouef above set up Windows Scheduler (unless you are on a Mac) to open your FileMaker database every day.

If the database is going to be opened and closed repeatedly each day then you don't want all the emailing to happen several times so I suggest a field that just hold whether emailed for that event or not which can be part of the IF check before emailing and then after emailing put "Yes" in the field with a 'Set Field' command.

If you want to do the processing on a 'workhorse' system and leave it alone to do it's job you wont need to use the Windows Scheduler and can just run it as in the demo. This may avoid any issues of a portable system trying to email all the time in situation with different SMTP settings or email availability.
Go to Layout [ Clients ] 
Show All Records 
Go to Record/Request/Page [ First ] 
Loop 
    If [ Get ( CurrentDate ) = TriggerSolution::BalanceDue ] 
    Send Mail [ Send via SMTP Server; To: TriggerSolution::Email; Subject: "Test"; Message: "You owe " &  
       TriggerSolution::BalanceDue; 
       Name: "You"; Email Address: "name@googlemail.com"; Reply-To Address: "name@googlemail.com"; SMTP Server: 
       "smtp. googlemail.com"; Port: 465; Use SSL; Authentication Type: Plain Password; 
       User Name: "name@googlemail.com" ] [ No dialog ] 
End If 
Go to Record/Request/Page [ Next; Exit after last ] 
End Loop 

Open in new window

0
 
LVL 5

Expert Comment

by:Peter Harris
ID: 23697812
I re-read the lesouef comment above which suggests a separate file to run the process which is a good idea and avoid re-running the opening script issue.
0
 
LVL 5

Expert Comment

by:Peter Harris
ID: 23697839
Perhaps if you could confirm: are you on Windows or Mac or other? Could you have a 'workhorse' or would you prefer not to?
0
 
LVL 28

Assisted Solution

by:lesouef
lesouef earned 1000 total points
ID: 23698945
if on mac, you use the cron feature to trigger actions.
If you have no script ability under osX, you can use the excellent cronX utility which has a GUI for the crontab setup file.
0
 
LVL 28

Expert Comment

by:lesouef
ID: 23698947
sorry to be late on comments, but I sleep in Europe...
0
 

Author Comment

by:jazzviolin
ID: 23701875
I am on a Mac.  I'm attempting the solutions above.
0
 
LVL 28

Expert Comment

by:lesouef
ID: 23703834
made a mistake up there, the GUI for crontab edition is "cronnix"
http://www.versiontracker.com/dyn/moreinfo/macosx/9478
let me know if help is still needed otherwise
0
 
LVL 28

Expert Comment

by:lesouef
ID: 23963470
Solutions provided were valid, this is a classic question which is left over for me
0
 

Author Comment

by:jazzviolin
ID: 23963496
Okay I'm going to get back to this issue now.  Didn't know there was a time limit for paid subscriptions.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What we learned in Webroot's webinar on multi-vector protection.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses
Course of the Month15 days, 8 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question