The Original FileMaker Community
Business Templates - Demo Apps - Video Tutorials -Samples - Help - 46000 Member Forum

The Leading Filemaker Developer Tools

MBS Blog – Find new records with RowID

Get real time updates directly on you device, subscribe now.

« Release notes for our… |
Home |

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

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More