This discussion is archived
9 Replies Latest reply: Apr 4, 2007 3:41 PM by 135285 RSS

Number of currently displayed rows in report with pagination?

545907 Newbie
Currently Being Moderated
Maybe I'm thinking to complicated, but ...

In my report I use the HTMLDB_ITEM.CHECKBOX function in order to display a checkbox for each row:

HTMLDB_ITEM.CHECKBOX(1,"ID",NULL,:P311_ASSIGNED,':') " "

Some of the checkboxes are selected depending on whether there is an entry for them in a database table.

As the report returns hundreds of rows, I use pagination (e.g. 15 rows per page).

After checking/unchecking the checkboxes and submitting the page by clicking the save button, I want to do the following: For each row CURRENTLY DISPLAYED on the screen (1) check, whether its checkbox is currently checked or not (2a) If it is checked and there is not yet an entry -> add row to the db table (2b) If it is unchecked and there is an entry in the db table -> remove row from db table.

How can I, on the server side, find out which rows (row ids) are currently displayed (using pagination), so that I check each of the currently displayed row ids for their existence in the database table? Is there any item stored on the server side, which tells me the row number of the first row currently displayed? Is there an item I can refer to, which tells me the amount of rows, which are displayed on one page?

Your help would be very much appreciated!

Konrad
  • 1. Re: Number of currently displayed rows in report with pagination?
    545907 Newbie
    Currently Being Moderated
    In other words:

    Can I store the value of the GET Parameters (is this the right term??) into my page items?

    E.g. The URL of my pagination button looks like this:
    "http://127.0.0.1:8080/apex/f?p=100:311:2783340070674861:pg_R_24691154217004916:NO&pg_min_row=6&pg_max_rows=5&pg_rows_fetched=5"

    Can I store the value of pg_min_row, pg_max_rows and pg_rows_fetched into page items? How would that work?

    Thanks a lot...
  • 2. Re: Number of currently displayed rows in report with pagination?
    545907 Newbie
    Currently Being Moderated
    No answers for this? I would need it urgently and just can't figure out an alternative solution... thanks !
  • 3. Re: Number of currently displayed rows in report with pagination?
    60437 Employee ACE
    Currently Being Moderated
    Konrad,

    I don't understand what you are needing to do exactly but those values are available as v(''FLOW_CURRENT_MIN_ROW' ), v('FLOW_CURRENT_MAX_ROWS'), and v('FLOW_CURRENT_ROWS_FETCHED'), so you can experiment with those.

    Scott
  • 4. Re: Number of currently displayed rows in report with pagination?
    545907 Newbie
    Currently Being Moderated
    Scott, thanks for your immediate answer! In the APEX User's Guide I could not find anywhere the information about how to reference these values.

    I try to explain once more, what I need the values for:

    1. In the database I have a table called "ASSIGNMENTS". The table has two columns, "Customer_ID" and "Event_ID", which are both foreign keys to the tables Customer and Event.

    2. Now I created a report which lists all Customers. I use HTMLDB_ITEM.CHECKBOX in order to display a checkbox for each customer in the first column. Some of the checkboxes are selected, some not. This depends on whether the customer is already assigned to a currently selected event or not.

    3. After I uncheck a checkbox, which is checked in the beginning by default (as there is an entry of the customer in the table "ASSIGNMENTS") and I click "SAVE", the entry needs to be deleted from table Assignments. If I check a checkbox, which was not checked in the beginning, an entry for the checked customer needs to be added to table Assignments.

    4. If I did not use the built in report pagination and just listed all customers together in the report (let's say 300 customers), it would be easy: I would just delete all entries for the selected event in table Assignments and then add a row for each of the customers who was submitted by the selected checkboxes.

    5. Yet I do use pagination. Only 15 customers are displayed per page. Let's say I'm on the first page. 2 out of the 15 displayed customers are already assigned to the Event (they have an entry in table Assignments). If I uncheck the two assigned customers and then click "SAVE", I can not just delete all entries for the selected event from table Assignments. After clicking SAVE, only those customers should be removed from table Assignments, who are currently displayed on the page and have been un-checked!

    Therefore, after the user clicks SAVE, I need to check all currently displayed customers (customer IDs), if they have an entry in the table assignments. That's what I need the values current_min_row, current_max_row etc. for.

    Or is there an other way how to find out which customers are currently displayed on the page (I would need a table/array/list of all customer ids displayed on the screen).

    Thanks again very much. Your help is appreciated a lot :)
  • 5. Re: Number of currently displayed rows in report with pagination?
    60437 Employee ACE
    Currently Being Moderated
    Excellent level of detail in that description. Unfortunately, I do not have time to study it. Please press on and I'm sure someone will jump in to help.

    Scott
  • 6. Re: Number of currently displayed rows in report with pagination?
    135285 Oracle ACE
    Currently Being Moderated
    Hi Konrad,

    I will jump in. :-) Just have a few minutes before a meeting.

    I think you don't have to make it that complicated, where you have to know the current pagination.

    1) Your HTMLDB_ITEM.CHECKBOX has to contain as checked value #ROWNUM# as the row selector would do it.
    2) You have to create a hidden item where you store your customer id
    3) create another hidden item where you store if the customer has already the assignment
    4) In your process you are now able to loop through your arrays.
    DECLARE
        vRowNumber BINARY_INTEGER;
        vFound BOOLEAN;
    BEGIN
        -- insert new event assignments
        FOR ii IN 1 .. WWV_Flow.g_f01.COUNT -- your checkbox
        LOOP
            vRowNumber := WWV_Flow.g_f01(ii);
            -- no assignment yet?
            IF WWV_Flow.g_f03(vRowNumber) IS NULL -- your hidden field where you store if you have an assmnt
            THEN
                INSERT INTO xxx VALUES (WWV_Flow.g_f02(vRowNumber)); -- your customer id
            END IF;
        END LOOP;
        -- delete old event assignments
        FOR ii IN 1 .. WWV_Flow.g_f03.COUNT -- your hidden field where you store if you have an assmnt
        LOOP
            -- only if the event was already assigned
            IF WWV_Flow.g_f03(ii) IS NOT NULL
            THEN
                vFound := FALSE;
                FOR jj IN 1 .. WWV_Flow.g_f01.COUNT -- your checkbox
                LOOP
                    -- is the event still checked?
                    IF WWV_Flow.g_f01(jj) = ii
                    THEN
                        vFound := TRUE;
                        EXIT;
                    END IF;
                END LOOP;
                --
                IF NOT vFound
                THEN
                    DELETE xxx WHERE CUSTOMER_ID = WWV_Flow.g_f02(ii);
                END IF;
            END IF;
        END LOOP;
    END LOOP;
    Haven't tested the code, but I think it should show the idea.

    Hope that helps
    Patrick
    ------------------------------------------------------------------------------------
    Check out my APEX-blog: http://inside-apex.blogspot.com
  • 7. Re: Number of currently displayed rows in report with pagination?
    545907 Newbie
    Currently Being Moderated
    Thanks very much to Scott and Patrick! Patrick, this was exactly what I needed!

    Have a nice weekend!
    K.
  • 8. Re: Number of currently displayed rows in report with pagination?
    373349 Newbie
    Currently Being Moderated
    Patrick,
    I have exactly the same problem.
    my query is:
    select
    htmldb_item.checkbox(1,a.deal_id,decode(b.deal_id, null,null,'CHECKED')) sel,
    htmldb_item.hidden(2,decode(b.deal_id,NULL,'0000',b.deal_id)) sel_ind,
    a.deal_name
    from a, b
    where a.deal_id = b.deal_id(+).

    I tried to use following to handle the checked rows and unchecked rows:

    First, update all rows in this page with a unchecked value:
    FOR i in 1..HTMLDB_APPLICATION.G_F02.count
    LOOP
    begin
    update b set del_ind = 'N',
    where deal_id = HTMLDB_APPLICATION.G_F02(i);
    end;
    END LOOP;

    Then use the following to update the checked rows:
    FOR i in 1..HTMLDB_APPLICATION.G_F01.count
    LOOP
    begin
    update b set check_ind = 'Y'
    where deal_id = HTMLDB_APPLICATION.G_F01(i);
    end;
    END LOOP;

    But the problem is that HTMLDB_APPLICATION.G_F02.count is always 0.

    Can you tell me what I was missing?

    Any response will be really appreciated.

    June
  • 9. Re: Number of currently displayed rows in report with pagination?
    135285 Oracle ACE
    Currently Being Moderated
    June, do you still have this problem?

    Patrick