« LiteSync and Xojo | Home | MBS Xojo Conference S… »

ROWID in FileMaker SQL Commands

Did you know ROWID keyword?

It's the name for a special keyword in SQL for FileMaker to reference the column with the record ID. So a query for ROWID in SQL looks like this and gives back all record IDs of the table:

MBS("FM.ExecuteFileSQL"; ""; "SELECT ROWID FROM MyTable")

This is the same number given by Get(RecordID) for each record. Same you can load ROWID based on the given ID:

MBS("FM.ExecuteFileSQL"; ""; "SELECT ROWID FROM MyTable WHERE \"MyID\" = ?"; 9; 13; $MyID)

This returns record ID based on the value $MyID in the MyID field.

Once you know a row ID, you can also find the same record in SQL:

MBS("FM.ExecuteFileSQL"; ""; "SELECT * FROM MyTable WHERE ROWID = ?"; 9; 13; $RecordID)

The great thing with ROWID is that you can use it as ID column in SQL functions, which work independent of the actual ID column the table may have. With Min() and Max() functions you can query the range of record IDs in the table:

MBS("FM.ExecuteFileSQL"; ""; "SELECT MIN(ROWID) FROM \"MyTable\"")
MBS("FM.ExecuteFileSQL"; ""; "SELECT MAX(ROWID) FROM \"MyTable\"")

Once you know the range, you can make queries to get chunks of records by ROWID selection:

MBS("FM.ExecuteFileSQL"; ""; "SELECT * FROM \"MyTable\" WHERE ROWID BETWEEN 5 AND 10 ")

This returns up to six records with ROWIDs from 5 to 10.
15 12 18 - 11:43