Announcement

Collapse
No announcement yet.

[SOLVED] Master Detail (Master Total)

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

  • [SOLVED] Master Detail (Master Total)

    On my first invoice application i need a total for the items on the detail form, and it works really perfectly.
    I have my Total Field as a field created on the Master form since it is calculated field, in other words its not
    a field from the master database.

    i am calling php updatetotal() every time i insert or delete or update a record and it works.

    The problem i have is that i dont know how to calculate back the total when i call back a saved form.
    I know that if i use a real total field on the database this would solve the problem, but i think is more
    professional not to stored calculated fields on database tables.

    Apreciate a lot if someone can help me !!!

  • #2
    Re: Master Detail (Master Total)

    Hi,
    just use the SELECT command.

    SELECT SUM(detail_field) FROM detail_table WHERE detail_order_id = master_order_id

    Use it with the sc_lookup() macro in the onLoad event of the form and set the value of the form field.

    If your master record isn't too long a left join should get you there as well.
    Something like
    SELECT
    master.customer_name,
    master.customer_address,
    Sum(detail.price),
    FROM master
    LEFT JOIN detail ON (detail.order_id = master.order_id )
    GROUP BY master.order_.id, master.customer_name, master.customer_address //make sure to group by all fields of the master table you use in the select


    Lots of possibilities
    j
    sb

    Comment


    • #3
      Re: Master Detail (Master Total)

      Thank you for your answer, but i might be doing something wrong, because it does not work

      i have used on the onload event on the master form.

      sc_lookup(dataset,"select sum(quantity * price) from detail where detail.masterid = master.masterid");
      $mastotal = {dataset}[0][0];
      sc_master_value('mtotal', $mastotal);

      and nothing happens

      is there a mistake ?

      Thank you !!!

      Comment


      • #4
        Re: Master Detail (Master Total)

        Try somethin like this in the onLoad event of the master form.

        $check_sql="SELECT SUM(price*quantity) FROM detail
        WHERE detail.master_id = {master_id}";
        sc_lookup(erg,$check_sql);
        if({erg[0][0]})
        {
        {total_of_master}=number_format({erg[0][0]}, 2,',','.')." "; // or any other currency symbol
        }
        else
        {
        {total_of_master}=number_format(0.00, 2,',','.')." ";
        }

        Hope this helps.

        jsb

        Comment


        • #5
          Re: Master Detail (Master Total)

          Thank you so much !!!!
          Works Perfectly !!!!!!

          Comment

          Working...
          X