Trigger a script when a record is made?

Questions
1

Is there a way to trigger a script when a process (not a user) creates a record?

Is there any way to know when a process has updated a field (not a user) and then execute a script?

In many DB environments, there are triggers that fire when something like an update or insert happens.

Thanks

2

By process, do you mean a FileMaker script, or something external to FileMaker ?

These triggers happen at the db engine level and launch scripts at the db engine level. The reason is that the db engine is not linked in any way to the interface. I wonder if the Data API could trigger a script in FileMaker, and my guess is that the Data API triggered script may not interrupt a script already running.

1 Like
3

AFAIK triggers only work when users execute an action on the UI. Scripts don't trigger.

FileMaker doesn't have the functionality to automatically observe and trigger actions on the DB server , unlike i.e. Postgres.

The only thing that comes to my mind that could serve the purpose is time-triggered scripts on server that observe fields and execute whatever necessary in case of a modified field value.

4

The DataAPI seems to be the way to go. Thanks.

5

Danke!

6

https://www.dracoventions.com/products/2empowerFM/family/script.php

Script scheduler

Might be a path …… free as well

Question: any “process” in FM is, by definition, code execution. Why not code your request into that action?

Also if you look at the steps associated with modified fields - commands typically used in transaction logs -‘you might find a solution there. I use the getmodifiedfield commands to write an audit log of who changed what field from what to what when using these commands.

FWIIW triggers are a relatively new feature of FileMaker. Legacy commands can support what you want and not rely on triggers which, in general, are presentation layer functionality.

7

This is a data-level event coming from JDBC to FileMaker, a system/process event, not a "user" event. User events are what FileMaker seems to only be aware of.

I really need a database "trigger" such as found in other DBs.

For example: https://mariadb.com/kb/en/trigger-overview/

Thanks

8

Where is the record create command initiated from? External to FM?

9

Yes, JDBC

Thanks.

10

I do have a workaround that works.

In the JDBC code, I can call, via the FM DataAPI, a PSoS script on FMS. That's a far cry from having actual DB triggers, but it's workable.

Thanks,

11

There are a number of quasi-DB triggers - all those triggers that are layout based (in layout setup) that are specific to a layout. IF you JDBC import targets a given layout (ideally a utility layout designated for this purpose), then say a "OnRecordCommit" trigger could perform an action. Layout triggers are NOT UI based, but record and layout oriented.

Maybe??? ..........

12

I have been watching this thread to see where it leads. Now that I see that the insert of the records is happening via JDBC, I understand the original post better, and would like to voice agreement with the approach described above, of triggering the script via an API such as DAPI.

Prior to understanding that is a JDBC insert, I was somewhat hanging on to a thought that possibility the recently added On Window Transaction script trigger could help out with this, but I no longer think so, as no window is involved with the JDBC insert.

1 Like
13

Thanks Steve.

Yes, the DAPI approach works fine for this use-case. It takes a bit of coding to make all the calls, process the JSON to get the auth token, make the next call to run the script, but in the end, it does work.

Always appreciate your chiming in.

Thanks.

1 Like
14

Why isn't a server side script that runs every minute or two suitable for this?

If your script is time consuming, run several schedules. Schedule A, starting at 9:03 runs every three minutes. Schedule B, starting at 9:04 runs every three minutes. Schedule C starting at 9:05 runs every three minutes.

Malcolm

15

That could be an alternative, however since the JDBC code knows exactly when the INSERT happened (it just happened in the JDBC code), it can run the PSoS script one time and be done rather than the server having to spend resources on scheduled scripts.

Having said that, although the server script scheduling approach could waste lots of CPU cycles -- running the script often when it's not needed -- I do like the server script scheduling approach and proposed it myself as an alternative.

Thanks, Malcolm.

16

If FM only had optimistic record locking. (Let me decide which to use.)

One client I have uses Visual FoxPro, long obsolete. Yet, Visual FoxPro (still, for this client) has both (1) real database triggers and, (2) five levels of record locking for any possible use-case.

Thanks

1 Like