This discussion is archived
7 Replies Latest reply: Jan 6, 2013 1:33 AM by Ahmed Alsaied RSS

How to set value and do calculation on tabular form

user517360 Newbie
Currently Being Moderated
Hi all expert, first of all happy new year !!

I have a question which i have been looking for a solution for quite a while now. I have been reading a lot of posting and those are not really what i am looking for. it seems the solution is something to do with Jquery. Hope that someone can point to me blogs or website that has a sample code :) …. Have been writing some stuff and not been able to work. i know handling tabular form is a bit tricky comparing setting dynamic action on page items …. I am building an apps and the following is my env info.

Apex Version - 4.2
DB Version and edition - Database Express
Web server used.I.e. EPG, OHS, ApexListner Standalone or with J2EE container - Apex Listener Standalone

I have a TABULAR FORM tie to a table called INV_DTL and have the following fields.
[col1] - row selector (Which is a checked box)
[col2 - HIDDEN] - INV_DTL_NO (This is a running number which uniquely identify the rows, also PK)
[col3- HIDDEN] - HEADER_NO
[col4] - PROD_CODE (Is a LOV which look up product code)
[col5] - Prod Desc (I have a joint to display the details automatically)
[col6] - Qty (user to enter the qty)
[col7] - Unit Price (I have to get the unit price from T_PRODUCT table and populate the unit price in this col i need to populate this due to logic. So i did not do a join)
[col8] - Discount % ((I have to get the unit price from T_PRODUCT table and populate the disc % in this col, i need to populate this due to logic. So i did not do a join)
[col9] - Amount (i need a process to calculate this - (Unit price X qty) - disc amt)
[col10] - discount amount (a process to calculate discount amy - (disc %/100)*(Unit Price X Qty))

The question here is how do i fire a dynamic action whey the select list is selected at COL4 and then populate the unit price and disc % after getting the value from T_PRODUCT and at the same time calculate the amount and disc amt.

Thanks a million.
Alvin
  • 1. Re: How to set value and do calculation on tabular form
    714270 Pro
    Currently Being Moderated
    user517360 wrote:
    Hi all expert, first of all happy new year !!

    I have a question which i have been looking for a solution for quite a while now. I have been reading a lot of posting and those are not really what i am looking for. it seems the solution is something to do with Jquery. Hope that someone can point to me blogs or website that has a sample code :) …. Have been writing some stuff and not been able to work. i know handling tabular form is a bit tricky comparing setting dynamic action on page items …. I am building an apps and the following is my env info.

    Apex Version - 4.2
    DB Version and edition - Database Express
    Web server used.I.e. EPG, OHS, ApexListner Standalone or with J2EE container - Apex Listener Standalone

    I have a TABULAR FORM tie to a table called INV_DTL and have the following fields.
    [col1] - row selector (Which is a checked box)
    [col2 - HIDDEN] - INV_DTL_NO (This is a running number which uniquely identify the rows, also PK)
    [col3- HIDDEN] - HEADER_NO
    [col4] - PROD_CODE (Is a LOV which look up product code)
    [col5] - Prod Desc (I have a joint to display the details automatically)
    [col6] - Qty (user to enter the qty)
    [col7] - Unit Price (I have to get the unit price from T_PRODUCT table and populate the unit price in this col i need to populate this due to logic. So i did not do a join)
    [col8] - Discount % ((I have to get the unit price from T_PRODUCT table and populate the disc % in this col, i need to populate this due to logic. So i did not do a join)
    [col9] - Amount (i need a process to calculate this - (Unit price X qty) - disc amt)
    [col10] - discount amount (a process to calculate discount amy - (disc %/100)*(Unit Price X Qty))

    The question here is how do i fire a dynamic action whey the select list is selected at COL4 and then populate the unit price and disc % after getting the value from T_PRODUCT and at the same time calculate the amount and disc amt.

    Thanks a million.
    Alvin
    You can call a javascript function on change of the select list values of COL4. If using a manual tabular form (where you generate the form elements using functions defined within the APEX_ITEM package), you can follow the approach that I described in the thread below:
    Enable Disable APEX_ITEM

    When using a wizard generated tabular form, you can call the JS function from the column's "Element Attribute" field as I do here:
    Re: Disable column in tabular form.


    Now, within the JS function you can call an AJAX on-demand application process to do the necessary computations. In case, the computations are simple and you do not need to involve database I/O, you can do the computations directly inside the javascript function.

    To get row number of the current tabular form row (that triggered the JS function), you can use the technique used in the thread above :
    var vRow = pThis.id.substr(pThis.id.indexOf('_')+1);
    You can then use the row number to update the other columns of the affected row.


    Hope the above helps.



    Thanks,
    Rohit
  • 2. Re: How to set value and do calculation on tabular form
    Ahmed Alsaied Newbie
    Currently Being Moderated
    Hi Alvin ,
    please see my example here
    https://apex.oracle.com/pls/apex/f?p=2523:1:8584049255931:::::
    to add new row just enter in any key
    Steps :-
    1- Make Application Process
    declare
     px01 varchar2(100) := dbms_assert.enquote_literal(apex_application.g_x01);
     v_price  varchar2(30);
     v_sql varchar2(1000) := 'select Unit_price from INV_DTL where rownum <=1 and PROD_CODE = ';
    begin
       v_sql := v_sql || px01;
       execute immediate v_sql into v_price;
      htp.prn(v_price);
    exception
     when others then 
       v_price := substr(sqlerrm,1,30);
       htp.prn(v_price);
    end;
    2- in HTML Header Page input javascript code like this example
    <script type="text/javascript">
    function getPrice(pThis) {
       var tid = pThis.id.replace("f04","f05");
       ajaxRequest = new  htmldb_Get(
                     null,$v('APP_ID'),'APPLICATION_PROCESS=GET_PURCH_PRICE',0); 
       ajaxRequest.addParam('x01',$v(pThis));
       ajaxResponse = ajaxRequest.get(); 
       $s(tid,ajaxResponse );
    }
    
    function sumItem(pThis) {
       var pid = pThis.id.replace("f06" ,"f05");
       var tid = pThis.id.replace("f06" ,"f07");
       $s(tid, Number($v(pid)) * Number($v(pThis)));
    }
    
    function sumItems(){
        function getVal(item){
       if($x(item).value != "")
         return parseFloat($x(item).value);
       else
         return 0;
        }
        $x('P15_TOTALS').value = 
      getVal('P15_TOTALS') - (getVal('P15_DISCOUNT_POUND'));
      }
    
    </script>
    3- product code column in Element Attributes : onchange="getPrice(this);"
    4- you can get the totals by dynamic action
    Regards,
    Abo Yahya
  • 3. Re: How to set value and do calculation on tabular form
    user517360 Newbie
    Currently Being Moderated
    Hi Rohit,

    Thank you very much with the reply.

    Alvin
  • 4. Re: How to set value and do calculation on tabular form
    user517360 Newbie
    Currently Being Moderated
    Hi Abo Yahya,

    Thank you very much with the in depth and clear explanation. I have got it working !! However, I have one more qn and i hope u do not mind.

    The fetching of the price is good now. But i also need to fetch disc % from the product table. However, if i add in one field, it does not work.
    declare
     px01 varchar2(100) := dbms_assert.enquote_literal(apex_application.g_x01);
     v_price  varchar2(30);
     v_disc varchar2(8);
     v_sql varchar2(1000) := 'select Unit_price, p_disc from t_product where rownum <=1 and product_code = ';
    begin
       v_sql := v_sql || px01;
      execute immediate v_sql into v_price;
      htp.prn(v_price);
      htp.prn(v_disc);
    exception
     when others then 
       v_price := substr(sqlerrm,1,30);
    --   v_disc := substr(sqlerrm,1,8);
       htp.prn(v_price);
    --   htp.prn(v_disc);
    end;
    Do i need to construct another v_sql and have 2 "execute immediate ..... " statement?

    With the inclusion a new field namely discount % i need to add in another field to display at the tabular form right?
    function getPrice(pThis) {
       var tid = pThis.id.replace("f04","f06","f07"); <=f07 is the column index for discount % in the tabular form.
       ajaxRequest = new  htmldb_Get(
                     null,$v('APP_ID'),'APPLICATION_PROCESS=GET_PRICE',0); 
       ajaxRequest.addParam('x01',$v(pThis));
       ajaxResponse = ajaxRequest.get(); 
       $s(tid,ajaxResponse );
    }
    Am i right to do it this way?

    Thanks a million

    Alvin
  • 5. Re: How to set value and do calculation on tabular form
    714270 Pro
    Currently Being Moderated
    I am glad that your question is answered. However, you may need to mark the appropriate post as helpful/correct. It will help all of us in the forum.
  • 6. Re: How to set value and do calculation on tabular form
    user517360 Newbie
    Currently Being Moderated
    Hi Abo Yahya,

    Finally figured out why my sumItem(this) function return 0. This is because i have set the unit_price display as set to "display as text (saves state)" under the column attribute settings. WHen i set it to text field, it worked beautifully. However, I do not want the user to edit this field and i want it to just display. Is there away to make this field just display at the same time the java script function work?

    Thanks
    Alvin
  • 7. Re: How to set value and do calculation on tabular form
    Ahmed Alsaied Newbie
    Currently Being Moderated
    Hi Alvin ,
    please share your example in apex.oracle.com
    Abo Yahya

Legend

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