Filemaker Pro 10: How to get the unique ID of last insert on mySQL tables (ODBC)

Question

I have a filemaker script that inserts a new entry on several imported mySQL tables. I need to get the unique id of these entries as they are created (before or after, either way) so I can refer to them later in the script. This is easy to do in SQL with LAST_INSERT_ID(), but I can't seem to find how in filemaker.

What I have tried that didn't work:

• GetNextSerialValue ( Get ( FileName )&".fp7" ; "jos_users::id" )
  #returns nothing, does not work properly on ODBC tables
• Get(RecordID)
  #Local value does not correspond to the mySQL unique ID

Thanks in advance! Any suggestions are appreciated.

Answer 1

How are you creating the records in your MySQL table? Are you using FileMaker's ESS and native "New Record" command to insert the new record or are you using the Execute SQL[] script step? Or something different?

The next serial value and recordID values are FMP-specific and don't apply here (unless your MySQL primary key is a serial value managed by FMP).

With "New Record" FileMaker sets your record context to the new record. So if your "jos_users" table occurrence is an alias to the MySQL table, calling "New Record" will place you in the context of that record. You can then set a variable to the contents in the "id" column.

Go To Layout ["jos_users" (jos_users)]
New Record/Request
Commit Records/Requests[No dialog]
Set Variable [$lastInsertID; jos_users::id]

This presumes that your MySQL record is given a proper ID at record insertion time and that the record can be committed passing any validation checks you've applied.

If you're creating the records using some other process, let us know and we can advise you on how to get the value.