1 2 Previous Next 20 Replies Latest reply on Jul 14, 2017 10:30 AM by anand_gp

    How to compute 3rd field using values from 2 other fields in tabular form

    anand_gp

      Hi,

       

      I need help in calculating value for 3rd field using values from other two fields (Apex 4.2.6 Master-Detail form, detail report region). 

      For example, calculate and assign value to AMOUNT (f04) using other two fields, QUANTITY  (f02) and PRICE (f03). 

      AMOUNT column will be read-only.  When user inputs or modifies QUANTITY  or PRICE value, the AMOUNT has to be updated.

       

      Thanks,

      -Anand

        • 1. Re: How to compute 3rd field using values from 2 other fields in tabular form
          Zapp_Brannigan

          Hi Anand,

           

          Write a DA - OnBlur on both Quantity and Price, I mean point it to the same function towards both the principle fields.

          and then to compute and fill your f04(Amount) and also update as it changes.

          Also you could define validations in your function which will be called OnBlur. first check the input. and then do math to update the third field.

           

          Regards,

          Zapp

          • 2. Re: How to compute 3rd field using values from 2 other fields in tabular form
            anand_gp

            Hi,

             

            I was looking at similar kind of discussion but could not understand it clearly.   Calculated Column on Tabular Form

             

            What do you mean by dynamic action - OnBlur.  Can u please clarify.

            From the above thread, I assume, I should use below code.  But where and how?

             

            $('input[name=f02],input[name=f03]').change( function(){
              parent_row = $(this).parents('tr:first');
              qty   = ( parent_row.find('input[name=f02]').val() == ' ') ? 0 : parseFloat(parent_row.find('input[name=f02]').val() );
              price = ( parent_row.find('input[name=f03]').val() == ' ') ? 0 : parseFloat(parent_row.find('input[name=f03]').val() );
              amt = qty * price;
              parent_row.find('input[name=f04]').val(amt);
            });
            

             

            Thanks,

            -Anand

            • 3. Re: How to compute 3rd field using values from 2 other fields in tabular form
              Zapp_Brannigan

              Hi Anand,

               

              Yes you can use the above code in your Page header. with <script> tags.

               

              My solution contains. creating a DA as below.

              Also OnBlur means Lose Focus in Oracle APEX.

               

               

               

              And then : Execute JavaScript

               

              --> calling your function within the page to which you will pass, triggeringElement, this.

               

              Or you can directly the code you have.

              anand_gp wrote:

               

               

              1. parent_row=$(this).parents('tr:first');
              2. qty=(parent_row.find('input[name=f02]').val()=='')?0:parseFloat(parent_row.find('input[name=f02]').val());
              3. price=(parent_row.find('input[name=f03]').val()=='')?0:parseFloat(parent_row.find('input[name=f03]').val());
              4. amt=qty*price;
              5. parent_row.find('input[name=f04]').val(amt);

               

               

               

              Regards,

              Zapp

              • 4. Re: How to compute 3rd field using values from 2 other fields in tabular form
                anand_gp

                Hi Zapp,

                 

                I did this (and only this) -

                 

                Created dynamic action on the report region -

                JavaScript code as (my column numbers are changed) -

                 

                parent_row=$(this).parents('tr:first');
                qty=(parent_row.find('input[name=f06]').val()=='')?0:parseFloat(parent_row.find('input[name=f06]').val());
                price=(parent_row.find('input[name=f07]').val()=='')?0:parseFloat(parent_row.find('input[name=f07]').val());
                amt=qty*price;
                parent_row.find('input[name=f12]').val(amt);
                

                 

                and changed Element Attribute of both QUANTITY & PRICE columns to -

                onblur="notNull(this);"
                

                 

                Is there anything else I am missing?  Because I don't see any effect of giving values to QUANTITY & PRICE.

                 

                Regards,

                -Anand

                • 5. Re: How to compute 3rd field using values from 2 other fields in tabular form
                  anand_gp

                  I also tried this -

                  Added below code to HTML Header -

                  <script type="text/javascript">
                    function sumItems(){
                  parent_row=$(this).parents('tr:first');
                  qty=(parent_row.find('input[name=f06]').val()=='')?0:parseFloat(parent_row.find('input[name=f06]').val());
                  price=(parent_row.find('input[name=f07]').val()=='')?0:parseFloat(parent_row.find('input[name=f07]').val());
                  amt=qty*price;
                  parent_row.find('input[name=f12]').val(amt);
                    }
                  </script>
                  

                   

                  Updated Element Attribute of both QUANTITY & PRICE columns to -

                   

                  onchange="sumItems();"
                  

                   

                  But Amount column is not getting updated.

                   

                  Regards,

                  -Anand

                  • 6. Re: How to compute 3rd field using values from 2 other fields in tabular form
                    Zapp_Brannigan

                    Hi Anand,

                     

                    Did you try to debug it with console.log , or the call going in or not.

                    Try console logging from DA first.

                     

                    Add before the execute JS. Check whether you are getting valid id's.

                    Console.log(this.triggeringElement.id);

                    Then add alert and same console.log with appropriate parameter to debug the flow.

                    you can add console.log(parent_row); and check whether its correct or not.

                     

                    Let me know, where are you stuck. Also pass 'this' to sumItems function.

                     

                    Regards,

                    Zapp

                    • 7. Re: How to compute 3rd field using values from 2 other fields in tabular form
                      anand_gp

                      I changed my DA javascript to add console log -

                       

                      Console.log(this.triggeringElement.id);
                      parent_row=$(this).parents('tr:first');
                      qty=(parent_row.find('input[name=f06]').val()=='')?0:parseFloat(parent_row.find('input[name=f06]').val());
                      price=(parent_row.find('input[name=f07]').val()=='')?0:parseFloat(parent_row.find('input[name=f07]').val());
                      amt=qty*price;
                      parent_row.find('input[name=f12]').val(amt);
                      

                       

                      and Quantity and price element attribute to -

                       

                      onblur="notNull(this);"
                      

                       

                      while running on debug, I changed values of quantity and price and if I look at "View Debut", I don't see anything is being processed.  There's no log of values changed in quantity or price.  Looks like there is some silly mistake happening.  Can you cross verify if my approach as DA and the next one with HTML Header is correct?  And I am not missing anything else?

                       

                      Thanks for your patience.

                      • 8. Re: How to compute 3rd field using values from 2 other fields in tabular form
                        Zapp_Brannigan

                        Hi Anand,

                         

                        Not the debug in developer toolbar,

                        Open Developer Console and go to console.

                        On browser, right click on web page and inspect element then switch to console tab.

                         

                        Instead of Console.log, you could use alert(this.triggeringElement.Id);

                         

                        Let me know.

                        • 9. Re: How to compute 3rd field using values from 2 other fields in tabular form
                          anand_gp

                          Hi Zapp,

                           

                          As soon as I added " alert(this.triggeringElement.Id);  " started getting below pop-up window -

                          It repeates for some 5 to 6 clicks.

                          And below is what I see in console -

                           

                           

                          Regards,

                          -Anand

                          • 10. Re: How to compute 3rd field using values from 2 other fields in tabular form
                            Zapp_Brannigan

                            Hi Anand,

                             

                            I tried it myself, wrote my own code.

                            var a = this.triggeringElement.id;

                            var len = a.length;

                            var b = a.substr(4, len);

                            var qty_id = 'f01_'+b;

                            var amt_id = 'f02_'+b;

                            var total = 'f03_'+b;

                            var qtyVal = document.getElementById(qty_id).value;

                            var amtVal = document.getElementById(amt_id).value;

                            var sum = parseFloat(qtyVal) + parseFloat(amtVal);

                            $s(total,sum);

                             

                            Regards,

                            Zapp

                            • 11. Re: How to compute 3rd field using values from 2 other fields in tabular form
                              anand_gp

                              Hello Zapp,

                               

                              Yes, it works.  Thanks.  But one issue.  If I make TOTAL field "Display as Text (saves state)", it shows calculated value during entry / edit. but doesn't save to DB on submit.  Since it is calculated field, I don't wnat to keept TOTAL open for edit.  Is there a way?

                               

                              Thanks,

                              -Anand

                              • 12. Re: How to compute 3rd field using values from 2 other fields in tabular form
                                Zapp_Brannigan

                                Hi Anand,

                                 

                                If you are using APEX_ITEM, you should use its p_attributes and  assign 'DISABLED'.

                                 

                                apex_item.text(

                                                         p_idx => 3,

                                                         p_value => MRP * QTY, -- calculated field.

                                                        p_attributes => 'disabled',

                                                         p_item_id => 'f03_00#ROWNUM#') "Gross",

                                                         )

                                You could use a number of other attributes to control your input element.

                                E.g. Checkbox has Checked or unchecked properties.

                                 

                                Regards,

                                Zapp

                                • 13. Re: How to compute 3rd field using values from 2 other fields in tabular form
                                  anand_gp

                                  Hi Zapp,

                                   

                                  I don't use APEX_ITEM.  I use simple Master-Detail form created using wizard.  The calculation is at Detail Region (report).  And all three columns QTY,  PRICE & TOTAL are DB columns.

                                  Is there a way to set it as read-only ?

                                   

                                  One more issue I noticed that, for new-row, the calculation is not happening.  I created one more DA for new row but no luck -

                                   

                                  Name:  Calculate Amount New Row

                                  Event:  Click

                                  Selection type:  Button

                                  Button:  Add Row

                                  True Action:   Execute JavaScript Code

                                  Code:  <the same code you gave>

                                   

                                  Thanks,

                                  -Anand

                                  • 14. Re: How to compute 3rd field using values from 2 other fields in tabular form
                                    Zapp_Brannigan

                                    Hi Anand,

                                     

                                     

                                    So you can just inside the Execute JavaScript when page loads, In the page properties.

                                     

                                    $(function() {}

                                    $('input[name=f04]').addClass('APEX_DISABLED');

                                    );

                                    One more issue I noticed that, for new-row, the calculation is not happening. I created one more DA for new row but no luck -

                                     

                                    Name: Calculate Amount New Row

                                    Event: Click

                                    Selection type: Button

                                    Button: Add Row

                                    True Action: Execute JavaScript Code

                                    Code: <the same code you gave>

                                    The Second thing, you cannot use the same code on a button click. the event should register the current object. in your design its only when you loose focus from the selectors you had selected.

                                    Check How to add auto calculating column into tabular form ?

                                     

                                    Regards,

                                    Zapp

                                    1 2 Previous Next