PK from table1 to ID field of table2

Applications Forms
#1

When a new record is created in table1 I need the primary key from that record inserted into a new record on table2. Any help appreciated. Thanks

#2

You can do it in event onafetrinsert with the macros:
/**

  • Insert a record on another table
    */

// SQL statement parameters
$insert_table = ‘my_table’; // Table name
$insert_fields = array( // Field list, add as many as needed
‘field_1’ => “‘new_value_field_1’”,
‘field_2’ => “‘new_value_field_2’”,
);

// Insert record
$insert_sql = ‘INSERT INTO ’ . $insert_table
. ’ (’ . implode(’, ‘, array_keys($insert_fields)) . ‘)’
. ’ VALUES (’ . implode(’, ', array_values($insert_fields)) . ‘)’;

sc_exec_sql($insert_sql);

#3

Thanks for the response alvagar, but I’m confused. How does “‘new_value_field_1’” know that it’s supposed to be the value from another table. When I try this solution it inserts a “0” into a new record on table2 instead of the PK from table1. Thanks

#4

You need insert in a new table table2, then my_table is table2. Field_1, field_2 are your fields of table table2. and “‘new_value_field_1’” is el value you want asign, in this case is your primary key of your table1, and other fields if you want insert more information in table2

#5

OK, now I’m really confused:confused: This is the Macro I’m trying to use:

My tables are tblSuppliers and tblVehicles when I enter a new record into tblSuppliers (PK=SupplierID) I want that new PK value to go into a new record in tblVehicles (The field in tblVehicles is also called SupplierID but is not the PK). Thanks so much for your patience and continued help.

/**

  • Insert a record on another table
    */

// SQL statement parameters
$insert_table = ‘tblVehicles’; // Table name
$insert_fields = array( // Field list, add as many as needed
‘SupplierID’ => “‘SupplierID’”,

);

// Insert record
$insert_sql = ‘INSERT INTO ’ . $insert_table
. ’ (’ . implode(’, ‘, array_keys($insert_fields)) . ‘)’
. ’ VALUES (’ . implode(’, ', array_values($insert_fields)) . ‘)’;

sc_exec_sql($insert_sql);

#6

Insert your record in table_1 and after that select the last record on that table and read the primary key. Thats work with all SQL DBs exept there are meanwhile more records are inserted.

At MySQL DBs you can play with SELECT LAST_INSERT_ID(), on MSSQL DBs use SELECT SCOPE_IDENTITY().

#7

Thanks RHS, I have the SELECT LAST_INSERT_ID() working but I still can’t get that ID into my second table it always comes out as 0
I was thinking that SELECT LAST_INSERT_ID() is stored as a variable but if it is I don’t know how to reference it to use in INSERT RECORD INTO ANOTHER TABLE macro Thanks again for your help.

#8

How you are getting the LAST_INSERT_ID()? (You must get it throught a macro sc_lookup)

#9

Yes, I’m using
sc_lookup(rs, “SELECT LAST_INSERT_ID()”);
echo “SC LastID:”.{SupplierID};
echo “MySQL LastID:”.{rs[0][0]};
Which echos the correct ID but I don’t see how to get that ID into another table
Thanks again

#10

[SIZE=12px]With $new_id_for_table2 = [/SIZE][SIZE=12px]{rs[/SIZE][SIZE=12px][0][0]} you have the ID and can use that for INSERT INTO or UPDATE at the other table. Tip: look at [/SIZE]http://www.w3schools.com/sql/ for SQL-Handling …

#11

Spent most of today on this and no “ahha” moment. Can anyone give me a clue as to what to put in the" Insert a record on another table" macro, and where to put it, to represent [SIZE=12px] [/SIZE][SIZE=12px]{rs[/SIZE][SIZE=12px][0][0]}
Thanks[/SIZE]

#12

Well, that only took a week but finally EUREKA!!! Thank you Alvagar and RHS for your time and patience.
Merry Christmas