Get newly inserted record's ID

Applications Forms
#1

I have a form that contains 1 Quote record.

Then there is a button on the form called: ?Accepted?.

When the user selects the button, some PHP code runs to insert a new Sale record based on the quote.

I need to get the new Sales record?s ID (SalesID) directly after it was inserted.

How do I retrieve a newly created record?s ID directly after it was inserted?

#2

It depends on the database you use. But if it’s mysql you can do a sql statement ‘getlastinsertid()’;

#3

thanks for the advice Albert.

In this case I am using MS SQL.

What I am thinking of using is :

    SELECT @new_identity = SCOPE_IDENTITY() 

    SELECT @new_identity as new_identity;

towards the end of my MS SQL StoredProcedure after the insert.

The question however is can I do an “ExecuteSQL” in SC that also returns a value?

And how would I go about calling a StoredProcedure in SC?

#4

In general you do a sc_lookup or sc_select which simply returns values. I.e. with sc_lookup:



$check_sql = "SELECT state_name, region"
   . " FROM States"
   . " WHERE state_id = '" . {field_state_id} . "'";
sc_lookup(rs, $check_sql);

if (isset({rs[0][0]}))     // Row found
{
    {other_field} = {rs[0][0]};
    {other_region} = {rs[0][1]};
}