« Release notes for our… | Home | News from the MBS Xoj… »

Find new records with RowID

We regularly get the question whether our plugin could tell you the IDs of new records created by the plugin.

The answer is that this information is not available, since FileMaker doesn't have a way in SQL to get the ID of last primary key created. Databases like mySQL have a function called LAST_INSERT_ID to return the serial number generated for a primary key via auto increment. FileMaker doesn't do that and also that wouldn't work for UUIDs, too.

Since all tables have a RowID column, you can always query the maximum RowID like this:

Set Variable [ $RowID; MBS("FM.ExecuteFileSQL"; ""; "SELECT MAX(RowID) FROM \"Contacts\" ")

This queries it for our Contacts table in a test database. Feel free to put your table name there in the quotes. You get back the internal RowID value of the newest record. The RowID increments and is independent of whatever you use a key in the table. This way we can use the same calculation for different tables without knowing what fields are inside.

Now you can use this to query the key field of the last record, e.g. the ContactIdentifier field in our example:

Set Variable [ $ContactIdentifier; MBS("FM.ExecuteFileSQL"; ""; "SELECT \"ContactIdentifier\" FROM \"Contacts\" WHERE RowID = ?"; 9; 13; $RowID)

Now you have the latest ID field value. If you do batch inserts via plugin and you need IDs of all the new records you can do a script like this:

# first make a query to get newest RowID before import
Set Variable [ $RowID; MBS("FM.ExecuteFileSQL"; ""; "SELECT MAX(RowID) FROM \"Contacts\" ") ]

# now create/import new records
Set Variable [ $Status; MBS( "FM.InsertRecordCSV"; ""; "Contacts"; "FirstName¶LastName¶City"; $tsv) ]

# now query IDs of new records
Set Variable [ $NewIDs; MBS("FM.ExecuteFileSQL"; ""; "SELECT \"ContactIdentifier\" FROM \"Contacts\" WHERE RowID > ?"; 9; 13; $RowID)

Finally you have the IDs for your new records. Unless someone else created one in-between with another session on the same server. But that may be another topic.
Let me know what you think about using RowID values!

See also: ROWID in FileMaker SQL Commands
22 05 21 - 11:56