This discussion is archived
10 Replies Latest reply: Aug 1, 2013 9:07 AM by KarenH RSS

help in creating dynamic actions to calculate total in tabluar form

KarenH Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points