10 Replies Latest reply: Aug 1, 2013 11:07 AM by KarenH RSS

    help in creating dynamic actions to calculate total in tabluar form

    KarenH

      HI Everyone,

       

      We are using apex 4.2 and starting to rewrite some existing applications originally designed in 3.0.   I was wondering if anyone could help me with the following scenario.

       

       

      I have a multi-record tabular form based on a collection called 'species_collection'.  The form allows fisherman the ability to create a electronic trip ticket which contains a species, quantity, price, total dollars along with other descriptive information on the species.  I have created (with much help from this forum) a dynamic action to automatically update the collection when a field is changed.

       

      I am now hoping to create a dynamic action that will automatically do the following:

       

      • when quantity is changed, recalculate total dollars as quantity*price
      • when price is changed, recalculate total dollars as quantity*price
      • when dollars changed, recalculate price as quantity/total dollars.
      • when total dollars is changed, recalculate the GRAND TOTAL

       

      currently, I use embedded calls to javascript and then to application processes...but these are difficult to debug, and it seems that a dynamic action might be cleaner and simpler.

       

      the current query is (I have only included the relevant fields as this is quite a large query):

      SELECT
      apex_item.text(1,seq_id,'','','id="f01_'||seq_id,'','') "DeleteRow",
      seq_id,
      seq_id display_seq_id,
      .....
      apex_item.text(10,TO_NUMBER(c010),5,null, 'onchange="setTotal('||seq_id||')"','f10_'||seq_id,'') Quantity,
       
      apex_item.text(11,TO_NUMBER(c011),5,null,'onchange="getPriceBoundaries('||seq_id||')"','f11_'||seq_id,'') Price,
      
      apex_item.text(12, TO_NUMBER(c012),5,null, 'onchange="changePrice
      ('||seq_id||')" onKeyDown="selectDollarsFocus('||seq_id||',event);"','f12_'||seq_id,'') Dollars
       ......
      from apex_collections
       where collection_name = 'SPECIES_COLLECTION' order by seq_id 
      

       

       

      each field, QUANTITY, PRICE, DOLLARS has an ONCHANGE which in turn call application processes which update the collection.

       

      <script language="JavaScript1.1" type="text/javascript">
      
      function setTotal(row)
      {
         //quantity was entered into form, get values
         var price = $x('f11_'+row);
         var total = $x('f12_'+row);
         var quantity = $x('f10_'+row);
         var nquantity = parseFloat(quantity.value);
         var ntotal;
         var nprice;
         nquantity = nquantity.toFixed(3);
         quantity.value = nquantity;
         //if quantity and price both have values calculate total and save
         if(quantity.value > 0 && price.value > 0)
         {
            ntotal = quantity.value * price.value;
            total.value = ntotal.toFixed(2);
         }
         else
         {
               //if quantity and total both have values calculate price and save
            if(quantity.value > 0 && total.value > 0)
            {
               nprice = total.value/quantity.value;
               price.value = nprice.toFixed(6);
               //check to see if the price entered falls within min/max for that species
               var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=PriceBoard',0);
               get.add('SPECIESPRICE',price.value);
               get.add('SEQUENCEID',row);
               gReturn = get.get();
               if ((gReturn == 'Price entered is too high') || (gReturn == 'Price entered is too low')){alert(gReturn);}
            }
            else  if (quantity.value > 0)
                     total.value = '';
                  else
                  { 
                       total.value = '';
                       quantity.value = '';
                  }
         }
        //saveQPD(row);
         setOverallTotal(); 
      }
      function setOverallTotal()
      {
         var total = 0;
         var nTotal;
         for(i=1;i<=rowCount;i++)
         {
            if(parseFloat($x('f12_'+i).value) > 0)
            {
               total = total + parseFloat($x('f12_'+i).value);
            }
         }
         ntotal = total.toFixed(2);
         document.getElementById("P110_TOTAL").value = ntotal;
         var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=nullProcess',0);
         get.add('P110_TOTAL',ntotal);
         gReturn = get.get();
      }
      function getPriceBoundaries(row) 
      {
         //price was entered into form get all values
         var quantity = $x('f10_'+row);
         var price = $x('f11_'+row);
         var total = $x('f12_'+row);
         var ntotal;
         var nquantity;
         var nprice = parseFloat(price.value);
         nprice = nprice.toFixed(6);
         price.value = nprice;
         //check to see if the price entered falls within min/max for that species
         var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=PriceBoard',0);
         get.add('SPECIESPRICE',price.value);
         get.add('SEQUENCEID',row);
         gReturn = get.get();
         if ((gReturn == 'Price entered is too high') || (gReturn == 'Price entered is too low')){alert(gReturn);}
         //if quantity and price both have a value calculate the total
         if(quantity.value > 0 && price.value > 0)
         {
            ntotal = quantity.value * price.value;
            total.value = ntotal.toFixed(2);
         }
         else
         {
            //if total and price both have a value calculate the quantity
            if(total.value > 0 && price.value > 0)
            {
               nquantity = total.value/price.value;
               quantity.value = nquantity.toFixed(3);
            }
            else
            {
               if(price.value > 0)
                    total.value = '';
               else
               {
                    total.value = '';
                    price.value = '';
               }
            }
         }
         saveQPD(row);
         setOverallTotal();
      }
      function saveQPD(row)
      {
         var quantity = $x('f10_'+row).value;
         var price = $x('f11_'+row).value;
         var total = $x('f12_'+row).value;
         //save quantity
         var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=setQuantity',0);
         get.add('SETVALUE',quantity);
         get.add('SEQUENCEID',row);
         gReturn = get.get();
      //   alert(gReturn);
         //save price
         get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=setPrice',0);
         get.add('SETVALUE',price);
         get.add('SEQUENCEID',row);
         gReturn = get.get();
      //   alert(gReturn);
         //save total
         var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=setTotal',0);
         get.add('SETVALUE',total);
         get.add('SEQUENCEID',row);
         gReturn = get.get();
      //   alert(gReturn);
         
      }
      function changePrice(row)
      {
         //total was entered get all rows
         var quantity = $x('f10_'+row);
         var price = $x('f11_'+row);
         var total = $x('f12_'+row);  
         var ntotal = parseFloat(total.value);   
         var nprice;
         var nquantity;
         ntotal = ntotal.toFixed(2);
         total.value = ntotal;
         //if quantity and total were entered calculate price.
         if (quantity.value > 0 && total.value > 0)
         {
            nprice = total.value / quantity.value; 
            price.value = nprice.toFixed(6); 
            var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=PriceBoard',0);
            get.add('SPECIESPRICE',price.value);
            get.add('SEQUENCEID',row);
            gReturn = get.get();
               if ((gReturn == 'Price entered is too high') || (gReturn == 'Price entered is too low')){alert(gReturn);} 
         }
         //if price and total were entered calculate quantity.
         if (price.value > 0 && total.value > 0)
         {
            nquantity = total.value / price.value;  
            quantity.value = nquantity.toFixed(3);
         }
         if (price.value > 0 && quantity.value > 0)
         {
             ntotal = quantity.value * price.value;
             total.value = ntotal.toFixed(2);
         }
       
             
         saveQPD(row);
         setOverallTotal();        
      }
      
      function selectDollarsFocus(pRow,event)
      {
          tabPress = 0;
          KeyCheck(event);
          if($x('f11_'+ pRow))
          {
                  if(KeyID == 9)
                  {
                      $x('f14_'+ pRow).focus();
                      onFocusAreaFished(pRow);
                      tabPress = 1;
                  }
          }
          else
          {
              if($x('f18_'+ pRow))
              {
                      if(KeyID == 9)
                      {
                          $x('f18_'+ pRow).focus();
                          tabPress = 1;
                      }
              }
              else
              {
                  if(--pRow <= rowCount)
                      if(KeyID == 9)
                      {
                          $x('f08_'+ pRow).focus();
                          tabPress = 1;
                      }
              }
              
          }
      }
      
      
      
      </script>
      

       

       

      I am not very familiar with javascript...but it seems like there must be a simpler way.   Any thoughts on how I could approach this?   thank you!

        • 1. Re: help in creating dynamic actions to calculate total in tabluar form
          Hari_639

          Hello,

           

          Can you set-up an example @ apex.oracle.com and post workspace/username/password details here?

           

          Regards,

          Hari

          • 2. Re: help in creating dynamic actions to calculate total in tabluar form
            KarenH

            Thanks Hari!

             

             

            I just created a workspace on apex.oracle.com:

             

            http://apex.oracle.com/pls/apex/f?p=4500:1000:115903680551049

             

            workspace:  SAFIS

            user: TESTER

            pwd: fish

             

            the application is 17465 (eR)

            the page  is p110.

            The region is: 30 - create/edit landings

             

            you will see the Dynamic actions.

             

            I have never been able to get these applications to run in the oracle workspace b/c of security and also the schema is so large...( you may know how I can better set up), but at least you can take a look to see if there are any obvious issues.

             

            thanks again for your help!

            • 3. Re: help in creating dynamic actions to calculate total in tabluar form
              Hari_639

              Hello,

               

              In my opinion, you should not deploy your application as it is on apex.oracle.com. I was just asking for an example to simulate your problem, Probably you can simplify your issue and can set-up an example at apex.oracle.com. Once the issue is fixed here, then you can easily adopt the solution to your case.

               

              However I tried to run you application and I got Pagr 101 not found error. I made page 110 PUBLIC and try to run it, but again, it never loaded.

               

              Please note it would take much time to understand your application and provide solution to it. So please provide a sample tabular form and provide your simplified requirement using which you can solve your problem by yourself.

               

              Regards,

              Hari

              • 4. Re: help in creating dynamic actions to calculate total in tabluar form
                Tom Petrus

                I'd first worry about translating the onchange/onkeydown attributes to proper dynamic actions.

                 

                What I like to do first in translating these attributes is to first translate them into jQuery code and run that from the console. That allows you to test things first and finetune or correct if necessary, which is a lot easier to do than having to change the code in the dynamic actions each time. Once you have that going, the translation to a dynamic action is simple, since they are based on jQuery.

                 

                - first determine the selector

                - determine the correct event and correct jquery handler

                - correct the code

                 

                Example for field f10. "apex_item.text" will generate an input field with the name attribute set to "f10" (because 10 is the value you gave to the p_idx parameter). On change you want to call a javascript function and pass it the sequence id. Fortunately you store that id in the f01 array.

                Since you want to move the code it is no longer possible to generate the function calls with the sequence id in it. This'll need to be changed so that you retrieve the id from somewhere when you are calling the javascript functions. Since this id is stored on each row this is quite trivial.

                To jquery:

                $("input[name='f10']").change(function(){
                   var lSeq = $(this).closest("tr").find("input[name='f01']").val();
                   getPriceBoundaries(lSeq);
                })
                

                 

                Then translating to a dynamic action:

                This speaks quite for itself. It's an on change event, you use a jquery selector, and the selector is the one show above. As a true action you'll need to use "execute javascript".

                The javascript code in the "Execute javascript" part doesn't change much. "this" becomes "this.triggeringElement"

                   var lSeq = $(this.triggeringElement).closest("tr").find("input[name='f01']").val();
                   getPriceBoundaries(lSeq);
                

                 

                Another example on field f12

                $("input[name='f12']")
                .change(function(){
                   var lSeq = $(this).closest("tr").find("input[name='f01']").val();
                   changePrice(lSeq);
                })
                .keydown(function(event){
                   var lSeq = $(this).closest("tr").find("input[name='f01']").val();
                   selectDollarsFocus(lSeq, event);
                })
                

                 

                The change part is quite easy again. As for keydown, instead of "event" you'll have to use "this.browserEvent" ie

                   var lSeq = $(this.triggeringElement).closest("tr").find("input[name='f01']").val();
                   selectDollarsFocus(lSeq, this.browserEvent);
                
                • 5. Re: help in creating dynamic actions to calculate total in tabluar form
                  KarenH

                  thanks Hari,

                   

                  I will see what I can come up with ...

                  • 6. Re: help in creating dynamic actions to calculate total in tabluar form
                    KarenH

                    Thanks Tom!

                     

                    I realize it is quite challenging to look at my large hunk of code...and as mentioned, most was written in 3.0 when I was quite new to apex.  The javascript was then written by another programmer (since moved on) and unfortunately, I am still struggling to learn javascript so it has been challenging to debug.  All that to say, I really appreciate your suggestion and will start to play around.

                     

                    Thanks for the help.

                    Karen

                    • 7. Re: help in creating dynamic actions to calculate total in tabluar form
                      Tom Petrus

                      I wouldn't necesarily change the existing functions. They seem to work for you and that's great. It's not always a good time-investment to rewrite just because it is possible. After all, looking at the code it looks straightforward enough to understand. I wouldn't mash it together at all.

                      Removing the onchange/keydown attributes is a noble goal however. A named dynamic action is a lot more obvious to show that something is going on. With the functions put in a javascript file it'll look all clean.

                      That being said, my examples should be able to start you off, as most events you've shown can be handled quite the same way. Just chewing it and placing the changed code here won't help much, doing some of it yourself will help you get better. But if you have some problems anyway just let us know

                      • 8. Re: help in creating dynamic actions to calculate total in tabluar form
                        KarenH

                        Thanks again Tom, I appreciate it.

                         

                        so, for the moment...here is what I have tried, but confess, I may have not completely understood (obviously as it is not working). 

                         

                        I have modified the select to the following (I am currently working on the field PRICE, which is f11).

                         

                         

                        SELECT
                        apex_item.text(1,seq_id,'','','id="f01_'||seq_id,'','') "DeleteRow",
                        seq_id,
                        seq_id display_seq_id,
                        .....
                        apex_item.text(11,TO_NUMBER(c011),5,null,null, 'f11_'||seq_id,'') Price
                        from apex_collections......
                        
                        

                        dynamic action:  PRICE CHANGE

                        event:  CHANGE

                        selection type: JQUERY SELECTOR

                        jquery: input[name='f11']

                         

                        true action 1 - alert 'price has changed'

                        true action 2 - javascript

                        var lSeq = $(this.triggeringElement).closest("tr").find("input[name='f01']").val();
                        getPriceBoundaries(lSeq);
                        
                        

                        event scope: DYNAMIC

                         

                         

                        The dynamic action does not fire at all (as the ALERT is not working).  I am wondering if this has anything to do with the fact that there is an earlier DYNAMIC ACTION that also notes when F11 changes:

                         

                        dynamic action: column change

                        event:  CHANGE

                        selection type: JQUERY SELECTOR

                        jquery: input[name='f10'],input[name='f11'],input[name='f12'], .shark_info, .hms_info

                        true action 1: set value javascript expression, p110_id = this.triggeringElement.id

                        true action 2: set value javascript expression, p110_value = this.triggeringElement.value

                        true action 3: set value pl/sql, p110_seq = rtrim(substr(:P110_ID,5,4),'0')

                        true action 3: execute pl/sql

                        declare
                          v_attr number;
                         
                        begin 
                          v_attr := TO_NUMBER (SUBSTR (:P110_id, 2, 2));
                          safis_collections.update_column( :P110_SEQ,
                                                           v_attr,
                                                           :P110_VALUE);
                        end;
                        
                        

                        true action 4:  refresh region landings

                         

                        this works perfectly.   However the next DYNAMIC action which checks the price change does not.....should the two dynamic actions be merged?

                         

                         

                        thanks again for your thoughts!

                        • 9. Re: help in creating dynamic actions to calculate total in tabluar form
                          KarenH

                          oops!  no, I take that back.  It does work as described above!!  I had turned on the condition = NEVER. 

                           

                          I have tried to incorporate changes to both PRICE and DOLLARS into the same DYNAMIC action.... is that recommended?

                           

                          true action:

                          var lSeq = $(this.triggeringElement).closest("tr").find("input[name='f01']").val(); 

                          getPriceBoundaries(lSeq); 

                          selectDollarsFocus(lSeq, this.browserEvent);

                          changePrice(lSeq);

                           

                           

                          when the price is changed, a check is done to see if the value is in the correct range...this seems to work perfectly. 

                          when the dollars is changed, the price is not recalculated, which is what I had hoped with the changePrice call

                          • 10. Re: help in creating dynamic actions to calculate total in tabluar form
                            KarenH

                            got it.  trial and error.

                             

                            the DYNAMIC action for changing price and changing dollars are now two distinct dynamic actions.

                             

                            The DYNAMIC action to update the collection column is now last...and it appears that everything is working...until I change something again.

                             

                            thanks again for your help.

                             

                            Karen