This discussion is archived
4 Replies Latest reply: Mar 20, 2013 6:24 AM by Tom Petrus RSS

Looping through tabular form collection prior to page submit

Mark BT52 Newbie
Currently Being Moderated
Hi,

I have a tabular region which has updateable from and to dates.
As these are altered (on change jquery selector) it automatically updates the time taken (X weeks and Y days) column using this

fromdate_id = 'f04_' + $(this.triggeringElement ).attr('id').substr(4);
todate_id = 'f05_' + $(this.triggeringElement ).attr('id').substr(4);
$x('P44_DATE1').value = $("#" + fromdate_id).val();
$x('P44_DATE2').value = $("#" + todate_id).val();

Then fires a PLSQL true action to get the time difference from the two date items (basically I can do this easily in PLSQL and have no idea how to do it in JS, but that's beside the point)

However. I also want to update the page items outside the tab form region showing the overall start and end dates.
These need to be the min of the from dates in the table and the max of the to_dates in the table.

I tried adding another true action to my DA to do the following

for i in 1..apex_application.g_f04.count loop.....
:P44_START_DATE := least(:P44_START_DATE,apex_application.g_f04(i) etc etc......
end loop;

But it didn't work. I suspect I can't loop around that collection in a DA?

Is there any way that I can loop through the tab form rows and get the min max dates immediately on one of them changing?

Edited by: Mark BT52 on Mar 19, 2013 10:17 AM

Edited by: Mark BT52 on Mar 19, 2013 10:19 AM
  • 1. Re: Looping through tabular form collection prior to page submit
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    The tabular form arrays are only available on page submit. In your case, I would create a collection and put the tabular form on top. Every change in the tabular form would automaticaly update the corresponding collection member and you can then access the collection values using a dynamic action.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    http://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
  • 2. Re: Looping through tabular form collection prior to page submit
    VC Guru
    Currently Being Moderated
    Mark BT52 wrote:
    Hi,

    I have a tabular region which has updateable from and to dates.
    As these are altered (on change jquery selector) it automatically updates the time taken (X weeks and Y days) column using this

    fromdate_id = 'f04_' + $(this.triggeringElement ).attr('id').substr(4);
    todate_id = 'f05_' + $(this.triggeringElement ).attr('id').substr(4);
    $x('P44_DATE1').value = $("#" + fromdate_id).val();
    $x('P44_DATE2').value = $("#" + todate_id).val();

    Then fires a PLSQL true action to get the time difference from the two date items (basically I can do this easily in PLSQL and have no idea how to do it in JS, but that's beside the point)

    However. I also want to update the page items outside the tab form region showing the overall start and end dates.
    These need to be the min of the from dates in the table and the max of the to_dates in the table.

    I tried adding another true action to my DA to do the following

    for i in 1..apex_application.g_f04.count loop.....
    :P44_START_DATE := least(:P44_START_DATE,apex_application.g_f04(i) etc etc......
    end loop;

    But it didn't work. I suspect I can't loop around that collection in a DA?

    Is there any way that I can loop through the tab form rows and get the min max dates immediately on one of them changing?

    Edited by: Mark BT52 on Mar 19, 2013 10:17 AM

    Edited by: Mark BT52 on Mar 19, 2013 10:19 AM
    I don't think you need to access the collection array's here, this can be done by a dynamic action with some jquery/javascript code to loop through and check your logic

    See http://api.jquery.com/each/
  • 3. Re: Looping through tabular form collection prior to page submit
    Mark BT52 Newbie
    Currently Being Moderated
    Thanks for that.
    Yes I think I will always build updateable reports on apex collections from now on. I found myself having to replace the MRU process anyway due to the display non db column item messing up the checksum, so I might do that.

    Also yes it looks like I could use Javascript with the each function, but I was really trying hard to avoid doing JS date manipulation. Don't think I can avoid it forever though.

    Actually I think I still have a problem, because at what point will my defined apex_collection be populated with the values from the tabular form?
    Is it as it is being filled in? I don't want to have to press a button to submit the tab-form row changes and inserts into my collection before doing my processing.

    Edited by: Mark BT52 on Mar 20, 2013 6:18 AM
  • 4. Re: Looping through tabular form collection prior to page submit
    Tom Petrus Expert
    Currently Being Moderated
    Mark,
    Here is some example code which may help you.
    Some remarks though: as pointed out in the code comments: this will only loop over dates in the current page of the report, not over all records matching the source. This might be sufficient when your report has no multiple pages, but if it does have multiple pages, and you want to loop over all those records then you will have to change your setup. You will have to use ajax to update values when they are changed at the very least, especially when a date is changed. If you don't do that the unsubmitted values won't be evaluated when trying to determine the min and max date.
    As for date arithmetic: yes, you can do this in javascript. But you will need extra javascript which is able to correctly parse your dates to a javascript Date object. I didn't opt for this here because this would include an extra javascript file. Instead, I tried to keep the date arithmetic in PLSQL code in the ondemand process. The whole dateformat thing is really something to keep an eye out for. In this code I'm assuming format 'DD-MON-YYYY'.

    Example of code in a change event on the input items in the columns FROMDATE and TODATE
    $("td[headers='FROMDATE'] input:visible,td[headers='TODATE'] input:visible").change(function(){   
       var lParentRow = $(this).closest("tr"),
           lFromDate = lParentRow.find("td[headers='FROMDATE'] input:visible").val(),
           lToDate = lParentRow.find("td[headers='TODATE'] input:visible").val(),
           l$DateDiff = lParentRow.find("td[headers='DATEDIFF'] input:visible"),
           laFromDates = [], 
           laToDates = [];
    
       //Both loops will store all values in the FROMDATE and TODATE columns in 2 arrays   
       //NOTE: "this" used in the each-function will be in the context of the function,
       //not the "this" of the change event
       $("td[headers='FROMDATE'] input:visible").each(function(){
          laFromDates.push($(this).val());
       });
       $("td[headers='TODATE'] input:visible").each(function(){
          laToDates.push($(this).val());
       });
    
       //x01 and x02: using these because i don't want to use explicit page items for this
       //x01: from date
       //x02: to date
       //f01: array with all from dates
       //f02: array with all to dates
       //NOTE: the values in f01 and f02 will ONLY contain the values CURRENTLY on the PAGE
       //      So looping over f01 will --NOT-- loop over all values in the actual source table!
       $.post('wwv_flow.show', 
              {"p_request"      : "APPLICATION_PROCESS=date_compare",
               "p_flow_id"      : $v('pFlowId'),
               "p_flow_step_id" : $v('pFlowStepId'),
               "p_instance"     : $v('pInstance'),
               "x01"            : lFromDate,
               "x02"            : lToDate,
               "f01"            : laFromDates,
               "f02"            : laToDates
               },
               function(data){
                  if(data){
                     //parse the return which is JSON
                     var lRet = $.parseJSON(data);
                     //put the date difference in the date diff column
                     l$DateDiff.val(lRet.timeDiff);
                     //set min and maxdate items
                     $s('P44_MINDATE', lRet.minDate);
                     $s('P44_MAXDATE', lRet.maxDate);
                  };
               }
             );
    });
    On demand process "date_compare"
    DECLARE
       l_fm VARCHAR2(12) := 'DD-MON-YYYY';
       l_from DATE;
       l_to DATE;
       l_diff NUMBER;
       l_least DATE;
       l_greatest DATE;
       l_response VARCHAR2(400);
    BEGIN
       l_from := to_date(apex_application.g_x01, l_fm);
       l_to   := to_date(apex_application.g_x02, l_fm);
       l_diff := l_to - l_from;
       
       FOR i IN 1..apex_application.g_f01.count 
       LOOP
          --least date of from dates
          l_least := LEAST(to_date(apex_application.g_f01(i), l_fm)), l_least);
          
          --greatest date of to dates
          l_greatest := GREATEST(to_date(apex_application.g_f02(i), l_fm)), l_greatest);
       END LOOP;
       
       --return data in a json object
       l_response := '{"timeDiff":"'||l_diff||'","minDate":"'||to_char(l_least, l_fm)||'","maxDate":"'||to_char(l_greatest, l_fm)||'"}';
       htp.p(l_response);
    END;

Legend

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