Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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?
    Attached Files
    Last edited by dan.dinu; 03-12-2015, 06:35 AM. Reason: adding some pictures
    Thanks for your time & have a nice day,
    Dan

    //覧覧覧覧覧覧覧
    Scriptcase Version 8.00.0030
    Types pe_mysql_bronze

  • #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

    Comment


    • #3
      Originally posted by kafecadm View Post
      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
      hello,

      i never heard about that macro..you want to say "sc_btn_copy" - this is the macro that you were talking about?
      Thanks for your time & have a nice day,
      Dan

      //覧覧覧覧覧覧覧
      Scriptcase Version 8.00.0030
      Types pe_mysql_bronze

      Comment


      • #4
        Yes... it returns true if you are in the middle of a copy.

        Regards

        Comment


        • #5
          Originally posted by kafecadm View Post
          Yes... it returns true if you are in the middle of a copy.

          Regards
          I insert my script in "{}" of this macro ..but no rezult.. does anyone have any ideea how can i solved it?
          Thanks for your time & have a nice day,
          Dan

          //覧覧覧覧覧覧覧
          Scriptcase Version 8.00.0030
          Types pe_mysql_bronze

          Comment


          • #6
            Hey Dan:

            Remember you should use it like
            PHP Code:
            if ( sc_btn_copy() )
            {
               
            //your code here

            Comment


            • #7
              SOLVED - Master/Detail copy sequence

              Originally posted by kafecadm View Post
              Hey Dan:

              Remember you should use it like
              PHP Code:
              if ( sc_btn_copy() )
              {
                 
              //your code here

              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:
              PHP Code:
              [QUOTE]
              'file' master table ();
               ..>>> 
              operation fields ..>>> idtypepathfile_labelfile_namefile_descriptionfile_numberorder_nrorder_namepart_namedrawing_nr,  drawing_vstipulated_totalachieved_totalinfoduplicateassembly_picturescanned_fileaddupdate_tiuserupdate_us
              'operation' slave table;
               ..>>> 
              operation fields ..>>> idtypefile_idfile_numberorder_namepart_namedrawing_nrfile_nameorder_nroperationparts_codcost_group_idcost_centerwi_codsdvstipulatedachievedworker_namecheckerdiferenceaddupdate_tiuserupdate_us
              'operations_tmp' table needed to put the information that you whant to copy.
               ..>>> 
              operations_tmp fields ..>>> idtypefile_idfile_numberorder_namepart_namedrawing_nrfile_nameorder_nroperationparts_codcost_group_idcost_centerwi_codsdvstipulatedachievedworker_namecheckerdiferenceaddupdate_tiuserupdate_us
              [/QUOTE
              in the master table form (file) you must creat 2 php methods:
              PHP Code:
              [QUOTE]
              1copy_db_before_insert;
              2copy_db_after_insert.
              [/
              QUOTE
              In the first php method "copy_db_before_insert":
              PHP Code:
              [QUOTE]
              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);

              }
              [/
              QUOTE
              2) Copy in the second php method "copy_db_after_insert":
              PHP Code:
              [QUOTE]
              // 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);
              [/
              QUOTE
              After that write "copy_db_before_insert();" in the:
              PHP Code:
              [QUOTE]
              onAplicationInit;
              onNavigate;
              onScriptInit.
              [/
              QUOTE
              And after that write "copy_db_after_insert();" in the:
              PHP Code:
              [QUOTE]
               - 
              onAfterInsert.
              [/
              QUOTE
              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.
              Thanks for your time & have a nice day,
              Dan

              //覧覧覧覧覧覧覧
              Scriptcase Version 8.00.0030
              Types pe_mysql_bronze

              Comment

              Working...
              X