i have an sql table connect by ODBC on my Filemaker project.
Is possible to send an email when a new record is write in the sql table?
and by what action(s) is/are new records created in that table?
There's no built in feature of FileMaker that would send out such an email but you might be able to manage it via whatever user action creates the new record.
Record is created by another software, an ERP. I can only "read" that table.
I was afraid that this was the case.
Is there any kind of timestamp field that shows the date/time on which a new record was created?
If so, you might use either a server scheduled script or a client side script controlled via InstallOnTimerScript to periodically check for records with creation timestamps later then the time the script last checked for additions.
Without that, I suppose you might track the total number of records in the table and send out an email when the number changes, but if records are being both added and deleted, you might miss some cases where a change was made.
thanks a lot, i'll use a script that control the timestamp field.
I don't think an FMP level product supports DB Triggers, which sounds like what you want.
But, if the record you're adding is in, say, MySQL, you could create a trigger there:
How to Create Database Triggers in MySQL
Possibly create a simple trigger like:
DELIMITER $$CREATE TRIGGER t_notification_insert AFTER INSERT ON [table_being_inserted]FOR EACH ROW BEGIN INSERT INTO `notification_queue` (`sent`) VALUES (0);END$$DELIMITER
Then, I might write a small Java program that runs every minute or so (automatically via Cron or Windows scheduler) to check the notification queue. If there's something in the queue, the email gets sent.
fmpdude wrote: Then, I might write a small Java program that runs every minute or so (automatically via Cron or Windows scheduler) to check the notification queue. If there's something in the queue, the email gets sent. Not difficult.
If you have to add records to another table and handle the application layer in Java, then trigger with cron or something, how is that different than handling in FileMaker as philmodjunk suggests? It is just as capable and easier to do there. That adds quite a few requirements where FM is already in use.
Also, the user may not have access to the backend database to add a trigger.
Your scheduled task in FM can add a record to a queue table and trigger a script to send an email.
I replied when I read Phil's reply: "There's no built in feature of FileMaker that would send out such an email but you might be able to manage it via whatever user action creates the new record."
I was thinking "DB Trigger", not "user action", however. I work with enterprises databases so much, DB Triggers are second nature. Hence, my posting. I'm constantly in environments where devs sling together a little glue code here and there as well so the tiny Java program I ... suggested ... didn't seem like the end of the world.
And, of course, the user may not have access to the back-end database. I wasn't accounting for every nuanced possibility.
Just a possible alternative for an ODBC use-case.
Up to the OP to consider I guess.
I certainly don't read fmpdude's post as criticism of my suggestion nor do I see that it implies any superiority over it. It looks like a good alternative for the OP to evaluate and consider.
And I welcome criticism of my suggestions. I don't take those personally (at least I try not to ) and the ensuing discussion can sometimes produce a better suggestion and teach me something.
Correct, there was zero criticism meant in any way.
Your approaches/postings are amazing and I always learn something every time I read anything you post.
Thank you, Phil.
Retrieving data ...