« MBS FileMaker Plugin,… | Home | Webservice vs. Plugin… »

Record Creation Script Trigger

Today we want to share two ways to trigger scripts via MBS Plugin on record creation.

There has been a request to have 

 

Auto Enter Script Trigger

 

First you can define a field TriggerScriptOnCreate with an auto calculation like this:

 

MBS("FM.RunScript"; "Contacts"; "RecordCreated"; Get(RecordID))

 

We set the option to not replace existing values. This calculation now runs on every record creation in FileMaker Pro. The plugin function returns OK, which is written to the record.

The script will now run whenever you create a new record or duplicate one as the calculation runs. We pass the record ID so the script could check the record.

 

Timer Script Trigger

 

Other people may create records on the same database hosted on a server. So you may want to regularly check whether there are new records and we just automated it via counting records via SQL to trigger a script when the count changes. Here is the sample script:

 

# StartRecordCreatedTrigger

 

Set Variable [ $count ; Value: MBS( "FM.ExecuteFileSQL"; "Contacts"; "SELECT Count(*) FROM Contacts"; 9; 13 ) ] 

If [ MBS("IsError") ] 

Show Custom Dialog [ "SQL Error?" ; $$LastSQLCount ] 

Else

Set Variable [ $$LastSQLCount ; Value: $count ] 

# free old if still runs

Set Variable [ $r ; Value: If(Length($$RecordCreatedScheduleRef) > 0 ; MBS("Schedule.Release"; $$RecordCreatedScheduleRef); "") ] 

# launch new evaluate schedule

Set Variable [ $$RecordCreatedScheduleRef ; Value: MBS( "Schedule.EvaluateAfterDelay"; 60; "Let( [ $SQLCount = MBS( \"FM.ExecuteFileSQL\"; \"Contacts\"; \"SELECT Count(*) FROM Contacts\"; 9; 13 ); $e = MBS(\"IsError\"); $r = Wenn($SQLCount ≠ $$LastSQLCount; MBS(\"FM.RunScript\"; \"Contacts\"; \"RecordCr… ] 

End If

 

As you see we query an initial count number and put it in a global variable. We schedule an evaluate and let's take a look on the expression without the backslashes needed:

 

Let( [

$SQLCount = MBS( "FM.ExecuteFileSQL"; "Contacts"; "SELECT Count(*) FROM Contacts"; 9; 13 );

$e = MBS("IsError");

$r = Wenn($e = 0 AND $SQLCount $$LastSQLCount; MBS("FM.RunScript"; "Contacts"; "RecordCreated"));

$$LastSQLCount = Wenn($e; $$LastSQLCount; $SQLCount)

]; "")

 

As you see we query again the count of records via SQL and if that number changes, trigger the script and remember the new count. If an error is reported from SQL, we ignore this for now as the file may be closed currently?

 

To stop this later, we can release the schedule and have the plugin stop it:

 

# StopRecordCreatedTrigger

 

# free old if still runs

Set Variable [ $r ; Value: If(Length($$RecordCreatedScheduleRef) > 0 ; MBS("Schedule.Release"; $$RecordCreatedScheduleRef); "") ] 

Set Variable [ $$RecordCreatedScheduleRef ; Value: "" ] 

 

As you see we just call Schedule.Release function to release if the variable is set and then clear it.

By using the MBS Plugin to schedule this, we can do the Evaluate even while a script is running already. Your scripts can use OnTimer if needed as we don't need that.

 

Let us know if you have comments or questions.

Claris FileMaker Plugin
03 09 20 - 18:00