Monday, September 14, 2009

Updateable Interactive Report for APEX

A colleague had a requirement where he had over 10,000 rows of data which had to be updateable. Using Interactive Reports (IR) was the preferred approach as it would allow users to filter the data to modify the rows they wanted to before submitting the page. Tabular forms wouldn't work since the page would be to large. This is the solution that I proposed to make "Updateable IRs".

The following solution will work with IRs and standard reports with pagination. If the users applies filters or paginates to another set of data, the changes they make will remain in the collection. This is using a similar technique that I wrote about for APEX Report with checkboxes (advanced). To summarize this process:
  • Store the current query data into a collection as well as the md5 checksum.
  • Build an IR against the collection and use APEX_ITEMs to display input fields.
  • When a user changes a field, we submit that change to the collection.
  • Once the user is done with their changes you'll need to process the collection as required. In the last step in this example I have a query that will help identify changed rows.

You can do a lot with this approach but if you don't have an urgent need I'd suggest holding off until APEX 4.0. They're some security issues that would need to be addressed before launching this code in a public application. I didn't include the security updates in this example since I did not want to lose scope of the base functionality. Updating the code to make it secure shouldn't be too difficult.

Here's the link to the demo: http://apex.oracle.com/pls/otn/f?p=20195:2300



- Create IR Report Region
Note: You can use this for regular reports with pagination as well

SELECT e.empno,
apex_item.text (2,
ac.c002,
NULL,
NULL,
'class="updateableIR" seqid="'
|| ac.seq_id
|| '" attrNum="2"'
) ename,
apex_item.text (3,
ac.c003,
NULL,
NULL,
'class="updateableIR" seqid="'
|| ac.seq_id
|| '" attrNum="3"'
) job,
apex_item.select_list_from_query
(4,
ac.c004,
'select E.ENAME d, E.EMPNO r from emp e where E.EMPNO != '
|| e.empno,
'class="updateableIR" seqid="' || ac.seq_id || '" attrNum="4"',
'YES',
NULL,
'- Manager -'
) mgr,
apex_item.text (5,
ac.c005,
NULL,
NULL,
'class="updateableIR" seqid="'
|| ac.seq_id
|| '" attrNum="5"'
) hiredate,
apex_item.text (6,
ac.c006,
NULL,
NULL,
'class="updateableIR" seqid="'
|| ac.seq_id
|| '" attrNum="6"'
) comm,
apex_item.select_list_from_query
(7,
ac.c007,
'SELECT d.dname d, d.deptno r FROM dept d',
'class="updateableIR" seqid="'
|| ac.seq_id
|| '" attrNum="7"',
'YES',
NULL,
'- Department -'
) deptno
FROM apex_collections ac, emp e
WHERE ac.collection_name = :p2300_collection_name AND ac.c001 = e.empno


- Create Region Items
- Hidden & Protected: P2300_COLLECTION_NAME

- Create Page Computation
Note: This is just to set the collection name. You can call it whatever you want

Item: P2300_COLLECTION_NAME
Computation Point: Before Header
Computation Type: Static Assignment
Computation: P2300_IR_COLLECTION

- Create a Page Process (PL/SQL)

Name: Load Collection
Process Point: On Lead - Before Header

-- This creates the collection if it isn't created yet.
DECLARE
v_collection_name apex_collections.collection_name%TYPE
:= :p2300_collection_name;
v_reset_flag VARCHAR2 (1) := 'N';
BEGIN
-- Create collection if it does not exist or reset collection required
IF apex_collection.collection_exists
(p_collection_name => v_collection_name) =
FALSE
OR v_reset_flag = 'Y'
THEN
apex_collection.create_collection_from_query
(p_collection_name => :p2300_collection_name,
p_query => q'! SELECT empno,
ename,
job,
mgr,
TO_DATE (hiredate, 'DD-MON-YYYY'),
comm,
deptno
FROM emp !',
p_generate_md5 => 'YES'
); -- Generated md5 is important to help identify changed columns
END IF;
END;


- Create HTML Region to store JS code:
Note: This uses jQuery and Tyler Muth's jApex plugin. You'll have to upload the .js files as static files in Shared Components.








- Create Application Process:
Name: AP_UPDATE_COLLECTION_CELL
Process Point: On Demand


-- AP_UPDATE_COLLECTION_CELL
BEGIN
apex_collection.update_member_attribute
(p_collection_name => apex_application.g_x01,
p_seq => apex_application.g_x02,
p_attr_number => apex_application.g_x03,
p_attr_value => apex_application.g_x04
);
END;


- Query to see which rows were changed

SELECT ac.collection_name, ac.seq_id, ac.c001, ac.c002, ac.c003, ac.c004,
ac.c005, ac.c006, ac.c007, ac.md5_original,
CASE
WHEN apex_collection.get_member_md5
(:p2300_collection_name,
ac.seq_id) =
ac.md5_original
THEN 'NO'
ELSE 'YES'
END row_change
FROM apex_collections ac
WHERE collection_name = :p2300_collection_name

27 comments:

  1. Martin,

    I like it! Good post.

    Regards,
    Dan

    ReplyDelete
  2. Very creative thinking. Thanks for sharing.

    Ashish
    http://www.dbcon.com

    ReplyDelete
  3. Looks cool. But did you try to set highlighting? If you use the dropdown list for the expression you don't get what you want (and if you just enter something it doesn't work either). The same holds for selecting a filter via the column header. Sorting doesn't work, computations don't work, charts won't work....so it is not websheets yet....
    Cheers
    Roel

    ReplyDelete
  4. Hi Roel,

    You're absolutely right. Filtering or applying any IR functionality against columns that have inputs isn't going to work. To help out, I've changed the demo app to remove functionality on those columns. I've also set the deptname as display only so that you can filter on it and still see your changes etc.

    You're also right that this is not a websheets replacement. It does what it does and nothing more. Its main goal is to allow end users to filter on non-input columns then update the appropriate data. Anything more than that and this is not the right solution.

    Thanks for your feedback.

    Martin

    ReplyDelete
  5. Hi Martin,

    Really interesting solution.
    Just one suggestion for the next version - I think you don't have to copy all data to collection - you could use outer join in IR query - then collection will have only updated rows.
    It's not websheets but this solution is very helpful.

    Lev

    ReplyDelete
  6. Hi Lev,

    Using the outer join is an excellent idea for rows that can't be updated. You also don't need to add all the columns into the collection, just the updateable ones.

    I wanted to leave things as simple as possible for this example, so please don't take it as a production ready solution.

    Thanks for your feedback.

    Martin

    ReplyDelete
  7. Martin,

    I think that a viable workaround for the APEX_ITEM display issue (Roel - September 15, 2009 1:25 AM) is as follows:

    - for each APEX_ITEM item, also include the 'raw' value from the collection - do not make this visible in the report. Educate users to only use this column for filtering etc and to not make it visible in the report.
    - disable filter etc on the apex_item item, but not on the 'raw' value
    - user can now filter on the 'raw' value, which will appear in the column list under the 'Other' option group.

    Until APEX matures to the point that this functionality is available out of the box we have to do workarounds like this in order to meet our Business requirements.

    Thanks,

    Paul Duncan

    ReplyDelete
  8. Martin

    I'm trying to use your code to build a Questionnaire.
    I have build the collection, the IR report loaded the Jquery and jApex script into static files.
    The problem is the changes aren't being reflected from screen to collection.
    I'm not sure what
    //Set all updateableIR columns onChange events
    eludes to.
    Do I need to setup onChange events for the text, radiogroups and select lists I have setup in the IR.
    I'm a PL/SQL programmer with no javascript

    Regards
    Derek

    ReplyDelete
  9. Hi Derek,

    I may have mislabeled my comment "//Set all updateableIR columns onChange events" really binds the onchange event to all your updateable columns.

    So no, as long as you include that class on columns that have inputs, as in my example, everything should be taken care of you with the above code.

    If you're still having issues, you could post you code on apex.oracle.com and I can take a look.

    Martin

    ReplyDelete
  10. Hi Martin

    I have put a stripped down version of my Questionnaire app to save time on to apex.oracle.com
    I have put in the custom_auth used by oracles sample I hope that helps
    URL http://apex.oracle.com/pls/apex/f?p=39536:1:127057230376098:::::

    demo/winton

    The code to update the collection is copied pretty much exactly, but doesn't update either, no surprise

    If there's an access problem let me know what I need to do.

    Extremely grateful for your reply

    Thanks
    Derek

    ReplyDelete
  11. Hi Derek,

    I just tried and I think I can see the problem, however I'd need access to your workspace to modify the application.

    Could you please create a developer user and send me the workspace login credentials to myfirstnameATclarifit.com?

    Thank you,

    Martin

    ReplyDelete
  12. Hi Martin

    The developer signon should be with you now.

    Thanks
    Derek

    ReplyDelete
  13. Thanks Martin

    For your help in getting my Updateable IR working with your code.

    This is a really great piece of software I'm sure many besides myself are finding it immensely useful.

    Thanks
    Derek

    ReplyDelete
  14. hi Martin,

    I created the variosu regions, collections & then uploaded the JS queries etc bu the updateable columns are not updatng or saving the changes done. how do i resolve this now? it seems ot be same problem like Derek had is what I'm facing now? (ie Changes are not saving)

    ReplyDelete
  15. I checked that the jquery file I put out in the static file was a new version (jquery-1.4.min.js). I changed the javascript to indicate this new version as
    //SCRIPT src="#APP_IMAGES#jquery-1.4.min.js" type=text/javascript></SCRIPT// . However this is still not working as it should? Any insights is assisted

    ReplyDelete
  16. Hi Ananth,

    Do you develop in Firefox and use Firebug? Can you look at the console to see if anything is happening when you change a value in the IR?

    Thank you,

    Martin

    ReplyDelete
  17. Thanks martin for your questions. I'm using Internet Explorer 7.

    I will chec kthe console and then post my comment again for that.

    ReplyDelete
  18. Hi Martin,

    your example is also useful several years later.

    Unfortunately I have a problem with the update. I can not see any reaction also using a button to refresh the result query. Maybe it is possible to give a possibility to download your page for this example ?

    Many thanks,
    Birthe

    ReplyDelete
    Replies
    1. Hi Birthe,

      I'm happy to hear that it's still useful to people several years later. I won't be publishing a downloadable demo as it's an old post (and all the code is above).

      Here are some tips to help debug:
      - Put some logging information in your Application Process which updates the collection. This will let you know if you're actually updating the collection.
      - Use firebug to make sure the AJAX request is going through.
      - Create another report region that is a SELECT * FROM APEX_COLLECTIONS to see what you currently have in your collections. You can create a button/dynamic action to refresh this report to see if your collection is being updated.

      Hope this helps,

      Martin

      Delete
  19. Hi Martin,

    I was trying your example in apex 4.2 but it throws an error while creating on demand process for "AP_UPDATE_COLLECTION_CELL" as
    error : " To create an on-demand page process, at least one application level process must be created with the type 'ON-DEMAND'". Please tell me how to overcome that and also you have mentioned about uploading .js files please tell me where i will get that. I
    have pasted your Java script given on this page to Html Header so is it correct.

    Regards,
    Rajat

    ReplyDelete
    Replies
    1. Please read the section called "Create Application Process" in this post.

      Delete
  20. Hello Martin,
    First of all thank so much.

    I am facing Issue in "downloading editable Interactive Report"
    I am using APEX version -> Application Express 4.0.2.00.07 ,Database 11g.

    One of my field in the Interactive Report (Dname ) is editable means user can update the value of DNAME but when I am downloading my I.R (csv format ) using Action button ,the DNAME is coming in the below format,I want to be in the same format as EMPNO.

    EMPNO DNAME
    1234 <input type="hidden" ..........so on

    Note: I am using I.R query as
    SELECT
    "EMPNO",
    "APEX_ITEM.HIDDEN(1,EMPNO)|| APEX_ITEM.TEXT(2,DNAME) "DNAME"
    FROM EMP.

    Please Help.

    Thank you..
    Regards,
    Gaurav

    ReplyDelete
    Replies
    1. Hi Gaurav,

      This article should resolve your issue: http://www.talkapex.com/2010/06/how-to-only-display-column-when.html

      Martin

      Delete
  21. Hi. How can I add new row to updateable interactive report?

    ReplyDelete
  22. Hi Martin,

    Can you share the application for Apex 5.0 version. I am not able to get the jquery and jApex plugins.

    ReplyDelete
    Replies
    1. Jaya, as this is an old post I won't be updating it for APEX 5.0. Also APEX 5.1 has the Interactive Grid which is much better than this solution.

      Delete