Simple FileMaker Pro Calculation and Script (Date Related): filemaker, calculation, script, scripting

by: cogitivePosted on 2009-02-19 at 21:23:55ID: 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.

by: jazzviolinPosted on 2009-02-19 at 21:44:34ID: 23689152

FileMaker Pro 9 - I'm hosting the master file online with a hosting company that hosts FMP databases.

by: cogitivePosted on 2009-02-19 at 23:41:25ID: 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

by: jazzviolinPosted on 2009-02-20 at 00:02:40ID: 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.

by: cogitivePosted on 2009-02-20 at 03:31:15ID: 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

by: jazzviolinPosted on 2009-02-20 at 03:36:40ID: 23690873

Awesome. I will attempt to this tomorrow when I get FMP 10. Thank you for your help.

by: jazzviolinPosted on 2009-02-20 at 07:19:56ID: 23692728

Having trouble getting it to email. A direction sheet would be wonderful.

by: cogitivePosted on 2009-02-20 at 15:02:55ID: 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.

by: jazzviolinPosted on 2009-02-20 at 15:22:11ID: 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.

by: lesouefPosted on 2009-02-20 at 15:47:01ID: 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.

by: lesouefPosted on 2009-02-20 at 15:50:03ID: 23697408

last bit, does not need fm10...

by: cogitivePosted on 2009-02-20 at 17:27:43ID: 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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

Select all Open in new window

by: cogitivePosted on 2009-02-20 at 17:30:56ID: 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.

by: cogitivePosted on 2009-02-20 at 17:39:52ID: 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?

by: lesouefPosted on 2009-02-21 at 00:28:34ID: 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.

by: lesouefPosted on 2009-02-21 at 00:29:06ID: 23698947

sorry to be late on comments, but I sleep in Europe...

by: jazzviolinPosted on 2009-02-21 at 13:28:40ID: 23701875

I am on a Mac. I'm attempting the solutions above.

by: lesouefPosted on 2009-02-22 at 00:24:13ID: 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

by: lesouefPosted on 2009-03-23 at 17:02:56ID: 23963470

Solutions provided were valid, this is a classic question which is left over for me

by: jazzviolinPosted on 2009-03-23 at 17:07:46ID: 23963496

Okay I'm going to get back to this issue now. Didn't know there was a time limit for paid subscriptions.

Question

Simple FileMaker Pro Calculation and Script (Date Related)

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

filemaker

calculation

script

scripting

FileMaker Pro Database

Databases Miscellaneous

Scripting Languages

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press