Determine index value of record I just inserted

Scriptcase 9 General
#1

I insert a record into my master form and have an index which is automatically generated called {poid}

In the same app, I am then generating detail records that obviously need the {poid} value.
What is the best way to determine the value of {poid}?

Currently, my approach is super cludgey.

I generate a timestamp value and write it in the header.
I then search the header table for a record with that value and read the {poid} value.
I then write back the header record to clearout the timestamp
It works but not elegant

#2

Several approaches.

In most databases you can retrieve the indexid after insert. In mysql getlastinsertid. Retrieve it into a global variable and you can use it within your application as part of the key of the detail data set.

Other approach is manually by doing a select max to retrieve the highest key and perform your database actions within a transaction. Advantage is that if it fails (ie. because another person has been using the same key because of a concurrent situation) you can perform a roll-back and retry. This will prevent orphan records.

#3

In MSSQL, you should be able to retrieve it with a code similar to this one :
[INDENT]$sqlQuery=“INSERT INTO [My query]”;
sc_exec_sql($sqlQuery);
sc_lookup(rs,“SELECT SCOPE_IDENTITY()”);
$index={rs[0][0]};[/INDENT]