Master/Detail form (copy slave on the new copied master)

Applications Forms
, ,
#1

Hello,

i have a master/detail form with the next columns:
master sql name: file
master sql columns: id, order_nr, stipulated_total

slave sql name: operation
slave sql columns: id, file_id, order_nr, stipulated

I’m trying to copy the slave lines that refered to the master id when i press the copy button in the master.

This is my script:
//------------------------------------------------------------------

$create_sql = " CREATE TEMPORARY TABLE 0peration_tmp (id int) ENGINE=InnoDB"
. " AS (SELECT * FROM operation)";

sc_exec_sql($create_sql);

$select_sql = " SELECT *"
. " FROM operation"
. " WHERE file_id = ". {id};

sc_lookup(dataset,$select_sql);

$update_sql = " UPDATE 0peration_tmp SET id=NULL";

sc_exec_sql($update_sql);

$insert_sql = " INSERT INTO operation SELECT *"
. " FROM 0peration_tmp"
. " WHERE file_id = ". {id};

sc_exec_sql($insert_sql);

$drop_sql = " DROP TABLE 0peration_tmp";

sc_exec_sql($drop_sql);

//------------------------------------------------------------------

I put the script in php method from master and i called it “on after insert”

I whant to specfy the fact that on var_dump i got no error, here is the result:

###################
(pdo-mysql): CREATE TEMPORARY TABLE 0peration_tmp (id int) ENGINE=InnoDB AS (SELECT * FROM operation)
(pdo-mysql): SELECT * FROM operation WHERE file_id = 130
(pdo-mysql): UPDATE 0peration_tmp SET id=NULL
(pdo-mysql): INSERT INTO operation SELECT * FROM 0peration_tmp WHERE file_id = 130
(pdo-mysql): DROP TABLE 0peration_tmp
###################

Can anyone tell me where did i got wrong?

master - file.JPG

slave - operation.JPG

#2

Hello man:

This is kind of dificult since you dont have a master id for the detail… remember that when you copy the only thing that happens is that the values get copied into the fields but the new record is not saved…

the only thing I can think of is to do the copy in the afterinsert event of the form… evaluating if it is a copy… for that check the sc_copy macro.

Regards

#3

[QUOTE=kafecadm;34132]Hello man:

This is kind of dificult since you dont have a master id for the detail… remember that when you copy the only thing that happens is that the values get copied into the fields but the new record is not saved…

the only thing I can think of is to do the copy in the afterinsert event of the form… evaluating if it is a copy… for that check the sc_copy macro.

Regards[/QUOTE]

hello,

i never heard about that macro…you want to say “sc_btn_copy” - this is the macro that you were talking about?

#4

Yes… it returns true if you are in the middle of a copy.

Regards

#5

[QUOTE=kafecadm;34137]Yes… it returns true if you are in the middle of a copy.

Regards[/QUOTE]

I insert my script in “{}” of this macro …but no rezult… does anyone have any ideea how can i solved it?

#6

Hey Dan:

Remember you should use it like


if ( sc_btn_copy() )
{
   //your code here
}
#7

SOLVED - Master/Detail copy sequence

[QUOTE=kafecadm;34146]Hey Dan:

Remember you should use it like


if ( sc_btn_copy() )
{
   //your code here
}

[/QUOTE]

Hello,

I solved the problem, thanks for the hint.

Here is how you can copy the slave fields for a copied master:

example:

there are necessaire 3 table:


> 
'file' - master table ();
 ..>>> operation fields ..>>> id, type, path, file_label, file_name, file_description, file_number, order_nr, order_name, part_name, drawing_nr,  drawing_v, stipulated_total, achieved_total, info, duplicate, assembly_picture, scanned_file, add, update_ti, user, update_us
'operation' - slave table;
 ..>>> operation fields ..>>> id, type, file_id, file_number, order_name, part_name, drawing_nr, file_name, order_nr, operation, parts_cod, cost_group_id, cost_center, wi_cod, sdv, stipulated, achieved, worker_name, checker, diference, add, update_ti, user, update_us
'operations_tmp' - table needed to put the information that you whant to copy.
 ..>>> operations_tmp fields ..>>> id, type, file_id, file_number, order_name, part_name, drawing_nr, file_name, order_nr, operation, parts_cod, cost_group_id, cost_center, wi_cod, sdv, stipulated, achieved, worker_name, checker, diference, add, update_ti, user, update_us

in the master table form (file) you must creat 2 php methods:


> 
1) copy_db_before_insert;
2) copy_db_after_insert.

In the first php method “copy_db_before_insert”:


> 
if (sc_btn_copy)
{

// copy records in operations_tmp
	
$check_sql = 'INSERT INTO operations_tmp'
   . ' SELECT *'
   . ' FROM operation ' 
   . " WHERE file_id = '" . {id} . "'";

sc_select(rs, $check_sql);

}
  1. Copy in the second php method “copy_db_after_insert”:

> 
// update records with the new values in operations_tmp

$update_table  = 'operations_tmp';      // Table name
$update_fields = array(   // Field list, add as many as needed
     "file_id = '{id}'",
     "type = '{type}'",
     "file_name = '{file_name}'",
     "file_number = '{file_number}'",
     "order_nr = '{order_nr}'",
     "order_name = '{order_name}'",
     "part_name = '{part_name}'",
     "drawing_nr = '{drawing_nr}'",
 );

$update_sql = 'UPDATE ' . $update_table
			. ' SET '   . implode(', ', $update_fields);
sc_exec_sql($update_sql);

// copy records in operation

$check_sql = 'INSERT INTO operation (type, file_id, file_number, order_name, part_name, drawing_nr, file_name, order_nr, operation, parts_cod, cost_group_id, cost_center, wi_cod, sdv, stipulated, achieved, worker_name, checker, diference)'
   . ' SELECT type, file_id, file_number, order_name, part_name, drawing_nr, file_name, order_nr, operation, parts_cod, cost_group_id, cost_center, wi_cod, sdv, stipulated, achieved, worker_name, checker, diference '
   . ' FROM operations_tmp '
   . " WHERE file_id = '" . {id} . "'";

sc_select(rs, $check_sql);

// delete all records in operations_tmp

$empty_sql = 'truncate operations_tmp';

sc_exec_sql($empty_sql);

After that write “copy_db_before_insert();” in the:


> 
- onAplicationInit;
- onNavigate;
- onScriptInit.

And after that write “copy_db_after_insert();” in the:


> 
 - onAfterInsert.

After that create an dependence and link the id of the master table with file_id of the slave table (select both int)- in the form of the master table.

Done. Have fun.