Announcement

Collapse
No announcement yet.

Master/Detail form (total calculation)

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

  • Master/Detail form (total calculation)

    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 make an ajax event to calculate the total for all the numbers entered on the slave - "stipulated" and put it in master "file" at "stipulated_total"

    i created a php method with the name "updMaster" and i inserted this cod:
    //-------------------------------------------------------------------------------
    sc_lookup(dataset, "SELECT SUM (stipulated)
    FROM operation
    where file_id = {file_id}");

    $master_total = {dataset[0][0]};

    sc_master_value('stipulated_total', $master_total);

    sc_exec_sql("UPDATE file
    SET stipulated_total = $master_total
    WHERE id = {id}");
    //-------------------------------------------------------------------------------

    I then put the name of the php method on "updMaster();" to the events on update, on delete and on insert...

    but every time i have this error:
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>
    SQL ERROR's:
    Method updMaster: "SELECT SUM (stipulated) FROM operation where file_id = {file_id_}"
    ()



    SQL ERROR's:
    Method updMaster: "SELECT SUM (stipulated) FROM operation where file_id = {file_id_}"
    ()
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>

    Does anyone have any ideea where i got wrong?
    Last edited by dan.dinu; 03-12-2015, 06:16 AM. Reason: PROBLEM SOLVED
    Thanks for your time & have a nice day,
    Dan

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

  • #2
    I think for field variables {xxx}, you need to put them outside of the quotes and build the string. E.G.

    Code:
    sc_lookup(dataset, "SELECT SUM (stipulated)
    FROM operation
    where file_id = " . {file_id});
    OR (if file_id is a string):

    Code:
    sc_lookup(dataset, "SELECT SUM (stipulated)
    FROM operation
    where file_id = '" . {file_id} . "'");

    Comment


    • #3
      SOLVED - Master/Detail form (total calculation)

      Originally posted by adz1111 View Post
      I think for field variables {xxx}, you need to put them outside of the quotes and build the string. E.G.

      Code:
      sc_lookup(dataset, "SELECT SUM (stipulated)
      FROM operation
      where file_id = " . {file_id});
      OR (if file_id is a string):

      Code:
      sc_lookup(dataset, "SELECT SUM (stipulated)
      FROM operation
      where file_id = '" . {file_id} . "'");
      Hey, thank you for your hint

      I succeeded building it in two ways:

      //////////////////////////////////////////////////
      ////////////////// V2 /////////////////////////////
      $check_sql = "SELECT SUM(stipulated)"
      . " FROM operation"
      . " WHERE file_id =" . {file_id};

      sc_lookup(dataset,$check_sql);

      if(!empty({dataset[0][0]}))
      {

      $master_field = 'stipulated_total';
      $master_total = {dataset[0][0]};

      sc_format_num($master_field, '.', ',', 2, 'S', '1', '');
      sc_master_value($master_field,$master_total);

      $exec_sql = "UPDATE file"
      . " SET stipulated_total = $master_total"
      . " WHERE id = " . {id};

      sc_exec_sql($exec_sql);
      } else {

      $master_total = 0;
      sc_format_num($master_field, '.', ',', 2, 'S', '1', '');
      sc_master_value($master_field,$master_total);

      }

      //////////////////////////////////////////////////
      ////////////////// V1 /////////////////////////////
      /*$check_sql = "SELECT SUM(stipulated)"
      . " FROM operation"
      . " WHERE file_id =" . {file_id};

      sc_lookup(dataset,$check_sql);

      $master_field = 'stipulated_total';
      $master_total = {dataset[0][0]};

      sc_master_value($master_field, $master_total);

      $exec_sql = "UPDATE file"
      . " SET stipulated_total = $master_total"
      . " WHERE id = " . {id};

      sc_exec_sql($exec_sql);
      */
      Thanks for your time & have a nice day,
      Dan

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

      Comment

      Working...
      X