SQL statement for a Primary key

[Steverino]

I'm trying to write an SQL statement for the primary key, ContactID (a serial number). My SQL statement is "SELECT MAX(ContactID)+1 from CONTACTS" -- the statement works (as long as prevent user modification of ContactID is turned off), but it takes far too long to execute. Shouldn't adding a single record to filemaker with ODBC be fairly quick? I don't get it -- ContactID is indexed, so it should be a relatively simple procedure. Is there another SQL statement that I should be using for the primary key?

[Steverino]

Again, I feel like I'm talking to myself. OK, a cash $$ prize for anybody who can provide the SQL statement for adding a new, unique record to FM from another source.

[Anatoli]

FWIW -- FM hasn't got primary key. It is using internal numbering.

Cannot you use Records -- New Record? Or script step?

[Steverino]

With OutlookConnect I am required to assign a primary key. Is there an SQL statement that would work with FM's internal numbering? Or with my serial numbers?

[omiossec]

Using an agregation fonction with filemaker will make your request very slow.

So you better use something like select contactid from table_name order by contactid

[Steverino]

That seemed to do the trick! How can I repay you?

[Steverino]

Actually, on further inspection, it doesn't work -- it overwrites the first record in FM. (And it does take a long time to execute.) What should I do instead?

[Crashin]

Are you still watching this thread? I can't say how you can speed-up that specific query, but I can tell you how I handled the new record entry from our web server.

In the table on my web server (MySQL) I added a field called "upload_date" and set it to a null value upon creation of the record in MySQL. Then, in FileMaker I created a script to import new records using ODBC and selected only the records with a null value in the upload_date field. Upon completion of the import I perform another query (in the same script) to change all null upload_date values in the MySQL table to the current timestamp, thus preventing the records from being uploaded again.

This might seem complicated, or maybe not. But, it is EXTREMELY fast.

[Steverino]

That does sound like a decent solution, but I'm now trying out a plug-in made by Productive Computing (Outlook Manipulator) to do the trick instead. Thanks though.