5 Replies Latest reply on Jan 17, 2019 7:04 PM by BillUK

    Dynamic row total column that updates as user inputs and tabs out

    BillUK

      Hi,

       

      I'm using APEX v18.2 and I have rows in my Interactive Grid table where users are entering numeric values for each day of the week. I would like a Weekly Total column which calculates and displays the weekly total based on summing up the values for the individual days of the week. I would like this to update as soon as the user has entered a value for one of the days and tabbed out. The table is as below:

       

       

      ItemMonTueWedThuFriSatSunWeekly Total
      Apples1010101010101070
      Pears20101010202020110
      Oranges243124101045

       

      The items rows are not always fixed and the user could add x number of rows depending on how many items they have on order. Currently this column is for display purposes only and I will not be doing anything else with the Weekly Total value.

       

      My question is, how can I achieve this requirement in APEX. I have tried to follow a couple of posts (below) but not had any luck in implementing this. I would be grateful if someone could please outline the main steps or point me in the correct direction of similar examples.

       

      Interactive Grid Cookbook - Dynamic Total Column page

      https://jeffkemponoracle.com/2016/04/declarative-tabular-form-dynamic-totals/

      https://www.youtube.com/watch?v=eHWtpukGyUE - Oracle APEX | Calculate Tabular Form [Rows & Columns] by JS

       

      Regards

        • 1. Re: Dynamic row total column that updates as user inputs and tabs out
          BillUK

          Hi,

           

          Just wondering whether anyone has any thoughts on this please and can offer any assistance?

           

          Regards

          • 2. Re: Dynamic row total column that updates as user inputs and tabs out
            John Snyders-Oracle

            Hi,

            Both of those links are related to tabular forms but you are using an Interactive Grid which is a very different beast.

            IG uses page items to edit cells and so many of the things that work with normal page items will work with IG column items. For example $v, $s and the apex.item APIs work.

             

            You should give your column items a static id. This is set in column attribute Advanced: Static ID. By Convention I like to use a C_ prefix on the column name. You could use C_MON, C_TUE, C_WED .... C_TOTAL.

             

            In edit mode when a row is activated all the values from the model are loaded into the column items. When the user changes to a different row that new row becomes activated. The value of a column item is for the current active row. For example you can try from the JavaScript console:

            $v("C_MON")

            to get the value of the active Monday cell.

            To calculate the total use code like:

            $s("C_TOTAL", "" + ( parseFloat($v("C_MON"))  + parseFloat($v("C_TUE")) + parseFloat($v("C_WED")) + ... ));

             

            Next the question is how to run that code from an event handler or dynamic action when the cell values change. Rather than put an event handler on every column item it would be simpler to use delegated event handling so you have just one DA.

            Give each of the day columns a class such as "dayValue". Add this to Appearance: CSS Classes (not the one under advanced).

            Give the interactive grid region a static id such as "weekTotals"

             

            Define a DA with settings:

            Event: Change

            Selection Type: jQuery Selector

            jQuery Selector: .dayValue

            Under Advanced set

            Event Scope: Dynamic

            Static Container: #weekTotals

             

            Then for the action use a Execute JavaScript code action with the code from above:

            $s("C_TOTAL", "" + ( parseFloat($v("C_MON"))  + parseFloat($v("C_TUE")) + parseFloat($v("C_WED")) + ... )); // todo fill in the other days

            Notice that this converts the strings into numbers to add them and then converts back to a string (that is what the "" + (...) does.)

             

            When the IG data is fetched from the server the server should fill in the total column which I think you are already doing.

             

            You say you don't need to save the week totals but if you did you should not trust the calculation done on the client and rather have the server redo the sum of each day.

             

            Regards,
            -John

            • 3. Re: Dynamic row total column that updates as user inputs and tabs out
              BillUK

              Hi John,

               

               

              I would like to say a big thank you for your reply and for breaking that down for me into different steps. I have learned a lot from that entire post e.g. fundamentally, at the beginning of the post where you highlight the difference in achieving this between Tabular Forms/Reports and IG's which I wasn't even aware of. Also, the concept of getting a handle on a column value for a particular row.

              When the IG data is fetched from the server the server should fill in the total column which I think you are already doing.

               

               

               

              I'm not currently doing this and the column is just an additional empty column that I have added to the IG component from the left hand side tree in Page Designer. However, taking the above solution on board, I will update the IG SQL query to contain a calculated total column which totals the weekday columns, and then the above solution will take care of dynamically updating that value from a client perspective as users enter quantity items for the individual weekdays.

               

               

              You say you don't need to save the week totals but if you did you should not trust the calculation done on the client and rather have the server redo the sum of each day.

               

               

               

              I will try this out but I presume that upon clicking the 'Save' button of the IG it should automatically do the total calculation from the SQL query and store this value? Or would I need to handle this in a specific way too?

               

               

              Regards

              • 4. Re: Dynamic row total column that updates as user inputs and tabs out
                John Snyders-Oracle

                Hi,

                You say you don't need to save the week totals but if you did you should not trust the calculation done on the client and rather have the server redo the sum of each day.

                 

                 

                 

                I will try this out but I presume that upon clicking the 'Save' button of the IG it should automatically do the total calculation from the SQL query and store this value? Or would I need to handle this in a specific way too?

                You presume that IG is smarter than it can be There is not enough information in the calculated column to know where to write the value back to. If TOTAL where already persisted then it would not be calculated and would just be a normal column that could be updated but probably shouldn't be. This is because the server can't trust the client. Any calculation done with JavaScript on the client should be redone on the server. This is not specific to IG or APEX but a general principal of web apps.

                 

                Any column in the region SQL Query that is calculated needs to have the column attribute Source: Query Only = Yes. Then if you really need to do something with a query only column you would need to change the Interactive Grid DML process to target type = PL/SQL Code where you would have full control to recalculate the total and persist it somewhere in the database. IG has this PL/SQL Code option to handle this and many other advanced use cases.

                 

                Regards,

                -John

                • 5. Re: Dynamic row total column that updates as user inputs and tabs out
                  BillUK

                  Hi John,

                   

                  I've tried the steps outlined in your original post but unfortunately I am unable to get this to work. Every time I update a value for a day, the Weekly Total column is not correctly updating.

                   

                  FYI...

                   

                  The Weekly Total column has been defined as a Display Only type with its Query Only property set to 'Yes'.

                   

                  The steps I followed are as per below:

                   

                  1) For the columns representing the 7 days of the week, I set the 'Appearance: CSS Classes' property to dayValue.

                  2) Gave the Interactive Grid region a Static ID of WeekTotalsIG.

                  3) Define a DA with settings:

                  Event: Change

                  Selection Type: jQuery Selector

                  jQuery Selector: .dayValue

                  Under Advanced set

                  Event Scope: Dynamic

                  Static Container: #WeekTotalsIG

                   

                  4) For the default True action for the DA, choose a Execute JavaScript code action with the following code:

                   

                  $s("C_TOTAL_HRS", "" + ( parseFloat($v("C_DAY1")) + parseFloat($v("C_DAY2")) + parseFloat($v("C_DAY3")) + parseFloat($v("C_DAY4")) + parseFloat($v("C_DAY5")) + parseFloat($v("C_DAY6")) + parseFloat($v("C_DAY7"))));

                   

                   

                  Now when I go into the individual Day columns and update their values, the weekly total column is not updating. Is there anything else that I could be missing? Do I need to put anything for the 'Affected Elements' property of the DA True action?

                   

                  Regards