There is a service running which monitors files in a folder on a server. Whenever files are copied to this folder, the files are moved and a record is written to a filemaker pro database through an ODBC connection. In that same process I need the ID of the newly created row to create a related record in another table (with a foreign key constraint). In Microsoft SQL Server, I'd use @@identity to do this, in Oracle, I'd use the sequence.CURRVAL. How would I get the identity field of a newly created row in Filemaker?
+2
A:
I'm not an experienced user of the ODBC interface into FileMaker, but I can suggest a possible solution that might work.
I'm assuming you're using an INSERT statement to get the data into FileMaker, that there's an "ID" number field in the table you're creating a record in, and that the ID field is set to auto-enter a serial number. If this is the case, the MAX function is supported according to FileMaker's ODBC documentation.
SELECT MAX (Table.ID) FROM Table
Chuck
2009-02-12 17:49:43
this might work functionally, but There is race conditions in this situation mentioned where if another row is inserted in between the initial write and the checking of the ID, there might be issues.. though if they are one right after another, i don't see how it would be an issue, just possibly.
stephenbayer
2009-02-12 21:29:14
I'm currently seeing if this will work in my situation.
stephenbayer
2009-02-12 21:31:25
I did think of that, but didn't mention it. I wasn't sure if that was proper etiquette here, as this is the first answer I've given. If that's an issue, let me know. I have another idea for a solution, but it's more complex.
Chuck
2009-02-12 22:34:27
This worked for my situation.
stephenbayer
2009-02-18 20:31:03
A:
Could you solve this by generating a unique ID before inserting the new row and then adding this ID into each record? This would enable you to identify both records after they're created.
I'd also be asking why you need this functionality. You're adding records into 2 different tables when a file is copied into a folder. Obviously, I have no idea what the solution is, so I can't advise one way or another.
My advice at this point however, would be to re-consider putting the logic of organising FileMaker data in your ODBC process. It sounds like the original record is supposed to act as a marker or trigger. Can the ODBC process be left as just a mechanism to create a record and the logic of how that record is used be left to another process, say a FM script?
Matt Haughton
2009-02-15 20:25:13