This discussion is archived
13 Replies Latest reply: Apr 4, 2013 2:40 PM by MaribelQuiroz111 RSS

Calculated Column on Tabular Form

826570 Newbie
Currently Being Moderated
Hello Everybody,

I have a tabular form with the following SQL as region's source:

select
"EMPNO",
"EMPNO" EMPNO_DISPLAY,
"ENAME",
"JOB",
"SAL",
"COMM",
nvl(sal,0) + nvl(comm,0) total_sal,
"DEPTNO"
from "#OWNER#"."EMP"


When I run the Form first time, it comes up with all rows showing total_sal column as well. But when I change values for sal or comm columns, the total_sal does not change unless I submit the values. Is there a way to change the total_sal value before submitting the changes to the database?

Thanks
  • 1. Re: Calculated Column on Tabular Form
    vee Guru
    Currently Being Moderated
    You will need to have a Dynamic action( or JS event handler ) which sets the "total column" when either of the dependent columns are changed* (onchange event)
  • 2. Re: Calculated Column on Tabular Form
    826570 Newbie
    Currently Being Moderated
    I think that Apex does not allow Dynamic Actions on tabular forms. Writing Javascript in the Header might be helpful?
  • 3. Re: Calculated Column on Tabular Form
    riedelme Expert
    Currently Being Moderated
    Zeekay wrote:
    When I run the Form first time, it comes up with all rows showing total_sal column as well. But when I change values for sal or comm columns, the total_sal does not change unless I submit the values. Is there a way to change the total_sal value before submitting the changes to the database?
    Tough problem to solve

    I'm just brainstorming but ...

    If you had a form object on the pagle like a text field you could change the value through JavaScript. You could make the field be non-enterable. I personally don't like the way that would appear visually on the screen but its all I can think of just now :(

    I hope a better answer comes along!
  • 4. Re: Calculated Column on Tabular Form
    vee Guru
    Currently Being Moderated
    <li>check the page source(or with firebug) to find the names(HTML attribute 'name') of the dependent columns (sal,comm) input elements, say these are " *f05* " and " *f06* " (they should be fxx format where xx in 1..50 ..the global arrays )
    <li>check the name(HTML attribute 'name') of the _"total"_ column , let that be " *f10* "

    Add the following JS code to be excuted on page
    $('input[name=f05],input[name=f06]').change( function(){
      parent_row = $(this).parents('tr:first');
      sal = ( parent_row.find('input[name=f05]').val() == ' ') ? 0 : parseFloat(parent_row.find('input[name=f05]').val() );
      comm = ( parent_row.find('input[name=f06]').val() == ' ') ? 0 : parseFloat(parent_row.find('input[name=f06]').val() );
      total = sal + comm;
      parent_row.find('input[name=f10]').val(total);
    });
    NOTE: Not tested

    A Dynamic action that is triggered on change of those elements would work as well.
  • 5. Re: Calculated Column on Tabular Form
    826570 Newbie
    Currently Being Moderated
    Hi Vee,

    The code you sent is not giving any result. Now I have entered the following code in the page header:


    function f_test(){
    $x('f08_0001').value = $x('f05_0001').value+ $x('f06_0001').value;
    }
    </script>


    In column SAL's Element Attributes I have given onBlur="javascript:f_test()"

    Now if I change the salary amount for any row, it changes total salary of first row (because I have hard coded the row in javascript shown above). How can I avoid hardcoding the row?

    Secondly, if the commission is 200 and I change salary to 100 the total salary shows 100200, instead of 300.

    Thanks for your help.

    Zeekay
  • 6. Re: Calculated Column on Tabular Form
    787361 Journeyer
    Currently Being Moderated
    hi,

    you have almost done with your code. you just need to search "how to add two numbers in javascript" as it contact two strings by default.

    now if you have your column read only, you can put a <div id="auto generated id"></div> tag in your column item. now if you want to set the value of two added column you can easily get the element by document.getElementById(divTagId) and set the value.

    i suggest you to also read the javascript tutorial with this.

    Thanks,
    Jaydip
  • 7. Re: Calculated Column on Tabular Form
    vee Guru
    Currently Being Moderated
    How can I avoid hardcoding the row?
    That is the reason I have used the array names than the id's
    Judging from your code, the arrays involved are f05,f06 and f08. Did you try changing the f10 string in my code to f08.
    Try this
    $('input[name=f05],input[name=f06]').change( function(){
      parent_row = $(this).parents('tr:first');
      sal = ( parent_row.find('input[name=f05]').val() == ' ') ? 0 : parseFloat(parent_row.find('input[name=f05]').val() );
      comm = ( parent_row.find('input[name=f06]').val() == ' ') ? 0 : parseFloat(parent_row.find('input[name=f06]').val() );
      total = sal + comm;
      parent_row.find('input[name=f08]').val(total);
    });
    the total salary shows 100200, instead of 300.
    The problem that you face (string concatenation) is because those variables are identified as strings. Use the Number function(In my code I have used parseFloat ) on the value items before you add them.
  • 8. Re: Calculated Column on Tabular Form
    826570 Newbie
    Currently Being Moderated
    The following javascript is working perfectly. The function is called on blur on Sal and Comm columns. So, whenever I change sal or comm it recalculates total salary column. Now the problem is that if I show total_salary column as Text Field it saves on submit successfully. But if I disable the total_salary field it gives checksum error. How can I handle this issue? Thx.

    <script language = "JavaScript" type="text/javascript">
    function f_total_salary(){
    for(i=0;i<document.wwv_flow.length;i++)
    {
    document.wwv_flow.f07.value = parseFloat(document.wwv_flow.f05[i].value)+
    +parseFloat(document.wwv_flow.f06[i].value);
    }
    }
  • 9. Re: Calculated Column on Tabular Form - Checksum Error
    826570 Newbie
    Currently Being Moderated
    Hi,

    I am getting checksum error on tabular form after disabling a calculated column e.g total_salary. If I enable this column, the record is save successfully. But I don't want to allow users to edit this column that's why I need this to be disabled.

    Any help?

    Thanks
  • 10. Re: Calculated Column on Tabular Form - Checksum Error
    vee Guru
    Currently Being Moderated
    make the column readonly instead of disabling it.
  • 11. Re: Calculated Column on Tabular Form - Checksum Error
    826570 Newbie
    Currently Being Moderated
    Thanks vee, it worked.
  • 12. Re: Calculated Column on Tabular Form - Checksum Error
    hekuran23 Newbie
    Currently Being Moderated
    Hi there,

    I'm quite new in APEX, I'm struggling with the same issue as the guy who found the solution but I do not understand where to put the script that he wrote.

    My calculation is Price * Quantity = Total_price which are cells inside the Tabular Form.

    In the forum it says only in page, but when I go to see the page attributes I see JavaScript, HTML Header, header and footer. I'm confused maybe as I'm new in this.


    WHERE DO I PUT THIS:
    $('input[name=f05],input[name=f06]').change( function(){
    parent_row = $(this).parents('tr:first');
    sal = ( parent_row.find('input[name=f05]').val() == ' ') ? 0 : parseFloat(parent_row.find('input[name=f05]').val() );
    comm = ( parent_row.find('input[name=f06]').val() == ' ') ? 0 : parseFloat(parent_row.find('input[name=f06]').val() );
    total = sal + comm;
    parent_row.find('input[name=f08]').val(total);
    });

    and WHERE DO I PUT THIS:
    <script language = "JavaScript" type="text/javascript">
    function f_total_salary(){
    for(i=0;i<document.wwv_flow.length;i++)
    {
    document.wwv_flow.f07.value = parseFloat(document.wwv_flow.f05.value)+
    +parseFloat(document.wwv_flow.f06.value);
    }
    }


    I understand that I have to change the ID of input, f05, f06, f07 to mine.

    It may sound as spoon feeding but I have tried and I'm trying my best to find the solution.


    Please help me out.

    Thank you in advance.

    Edited by: hekuran23 on Mar 4, 2013 2:40 PM
  • 13. Re: Calculated Column on Tabular Form - Checksum Error
    MaribelQuiroz111 Newbie
    Currently Being Moderated
    I thinks a similar question
    how do that mi TabularForm fills with date the other table.....

    for example this is my query

    select
    "SECCION_ORIGEN",
    "T_DOC",
    "SERIE",
    "NRO_DOCUMENTO",
    "NRO_ITEM",
    "IDORG_ARTICULO",
    "SKU_ARTICULO",
    "IDARTICULO",
    "IDARTICULO_ITEM",
    "CANTIDAD",
    "IDORG_OC",
    "TIPO_OC",
    "NRO_OC",
    "IDMONEDA",
    "COSTO_UNITARIO",
    "COSTO_TOTAL",
    "ESTADO"
    from "#OWNER#"."MOVS_ALM_ARTICULOS_ITEMS"

    I want that when i put SKU_ARTICULO value "IDARTICULO" and "IDARTICULO_ITEM" get values from the table ARTICULOS
    pleasee if someone know how did this i appreciate this

    :D

Legend

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