13 Replies Latest reply: Apr 4, 2013 4:40 PM by MaribelQuiroz111 RSS

    Calculated Column on Tabular Form

    826570
      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
          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
            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
              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
                <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
                  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
                    Jaydip Bosamiya
                    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
                      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
                        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
                          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
                            make the column readonly instead of disabling it.
                            • 11. Re: Calculated Column on Tabular Form - Checksum Error
                              826570
                              Thanks vee, it worked.
                              • 12. Re: Calculated Column on Tabular Form - Checksum Error
                                hekuran23
                                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
                                  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