I'm trying to figure out how I can grab the value of a newly inserted records ID for use in adding data to related tables later. In SQL Server and Access I can use SELECT @@IDENTITY to get this value. This doesn't seem to work for me with Filemaker. What can I use here to make this work? Any information would be greatly appreciated. Thanks!

Hi Dru

You can create a global field that can be carried across scripts and then use the set field script step immediately after your create record step to store the record ID in the global

HTH

Phil

If a button fires the action script, you can also capture the ID with script parameter and pass the parameter instead. It will only work within the same script (or passed to subscript) but it saves using a global on occasion. Vs. 8 allows variables which are a wonderful addition. :wink2:

As LaRetta says. Variables make upgrading to eight worth it alone

Phil

Well I am using FM 8...i just now noticed that I had my thread set to show i'm using version 7....mistake. What's this about variables?

Edited July 4, 200619 yr by Guest

grab the value of a newly inserted records ID for use in adding data to related tables later.

I'm unsure what you mean by using the data later. If you mean later within the same script, you can use script variable. If later (during the client session) but after a script ends, you can use global variables. Example of using script variable would be:

Set Variable [ $ID ; Value: ID ]

Freeze Window

Go To Layout [ layout based upon related table ]

New Record Request

Set Field [ ID ; $ID ]

Commit Records/Requests

Go To Layout [ original layout ]

Script variables will clear when the script ends. Using variables eliminates the need of using script parameters or global fields. There are many other uses for variables but this covers your question, I think. :wink2:

Well, actually, I'm not doing Filemaker scripting via the ScriptMaker. I'm running ASP scripts which utilize SQL to run inserts/updates/deletes on the Filemaker data.

So, I'm connecting via ODBC and this is where the @@IDENTITY stuff comes into play. With Access and SQL Server you can use the @@IDENTITY value within the SQL SELECT statement to grab the value of the last created ID.

I've come up with a workaround that should work. For instance, let's say my script creates a new invoice and I need to grab the invoiceNumber for that new record so that I use it to populate the invoiceItems table with the items purchased on that invoice. I'm creating a recordset that consists of all the InvoiceNumbers and sorting them in descending order:

SELECT * From Invoices WHERE InvoiceNumber <> '' ORDER BY InvoiceNumber DESC

This effectively makes the very first record in my recordset the last one that was just created. I guess it's possible that 2 people could hit this at just precicely the right moment to make it grab the wrong value...but I guess we'll just have to wait and see if it's a problem.

It'd probably be a huge shift, but you can use FM's XML publishing engine in your ASP pages instead of using ODBC.

if you create a new record that way, you automatically get the ID of the new record.

Check out www.fmdotnet.org