Thursday, July 2, 2009

APEX: Saving item values for each user

Someone asked me today if APEX could remember input values for specific page items. For example if you have a page with report parameters could APEX remember the report parameters that the user last used the next time they logged in?

Note: Please read comments below as APEX does support this out of the box on an individual item basis. This solution is to make the option configurable for large applications.

APEX doesn't support this out of the box, however it does have some great features which can enable you to do this. You can use cookies for this but I wanted to make the solution work no matter where the user was accessing the application from.

To make things a bit more difficult, I don't want to remember all item values on a page so I must be able to control which items are "remembered" and which items aren't. I can do this by using a naming convention in my items, however I don't want to rename all my page items (I already have a lot of them). Instead I decided to create a table which will list all the items a user can remember.

You can try the demo here (follow the instructions on the page).


CREATE TABLE tapex_remember_page_item(
application_id NUMBER NOT NULL,
page_id NUMBER NOT NULL,
item_name VARCHAR2(255) NOT NULL);

-- You don't need to add a UK, however it may be a good idea.
ALTER TABLE tapex_remember_page_item ADD(
CONSTRAINT tapex_remember_page_item_uk1
UNIQUE (application_id, page_id, item_name));

-- Since I name all my APEX items in uppercase, just do this as an extra precaution
CREATE OR REPLACE TRIGGER trg_tapex_remember_pg_itm_buir
BEFORE UPDATE OR INSERT
ON tapex_remember_page_item
FOR EACH ROW
BEGIN
:NEW.item_name := UPPER (:NEW.item_name);
END;
/

INSERT INTO tapex_remember_page_item
(application_id, page_id, item_name)
VALUES (20195, 1800, 'P1800_DEPTNO');


INSERT INTO tapex_remember_page_item
(application_id, page_id, item_name)
VALUES (20195, 1800, 'P1800_MIN_SAL');


For this example we'll store the values as APEX Preferences, however you could easily create your own preferences table to manage your data. I think they're several advantages to managing the preferences in your own table, however if you have a small application with a limited number of users then I'd recommend using the APEX_UTIL preference options


Create 2 Application Processes:

AP_GET_PAGE_ITEM_PREFS
On Load: Before Header (page template header)


DECLARE
BEGIN
FOR x IN (SELECT item_name
FROM tapex_remember_page_item
WHERE :app_page_id = page_id
AND :app_id = application_id) LOOP
apex_util.set_session_state (p_name => x.item_name,
p_value => apex_util.get_preference (p_preference => x.item_name,
p_user => :app_user
)
);
END LOOP;
END;



AP_SET_PAGE_ITEM_PREFS
On Submit: After Page Submission - After Computations and Validations


DECLARE
BEGIN
FOR x IN (SELECT item_name
FROM tapex_remember_page_item
WHERE :app_page_id = page_id
AND :app_id = application_id) LOOP
apex_util.set_preference (p_preference => x.item_name, p_value => v (x.item_name), p_user => :app_user);
END LOOP;
END;


For those of you that are curious APEX Preferences are stored in : apex_030200.wwv_flow_preferences$ where apex_030200 is the schema name for APEX (could also be called flows_xxxxxx)

5 comments:

  1. Martin,

    Thanks for posting this method.

    But isn't this what the Save Preferences Page Processes are for? I haven't used them yet but it seems like it's exactly where you'd use these. A user sets a page item via a button or whatever, then submits the page. The Save Preference process saves that to the Apex table you referenced. Then you'd use a Load Preference process on the Page Rendering.

    Or maybe I don't understand these processes yet? I'm happy to learn if you can clue me in. :-)

    ReplyDelete
  2. Hi Stew,

    You could use the Save Preference Process, however you'd need to do this for each item that you want to set. This could get tedious in a larger application and not easily configurable in a SaaS application.

    If you do go with the Save Preference Process, you'll need to alter your items so that the Default values is obtained from the Preference.

    You can do this using existing APEX functionality, but it may be easier to have 2 processes and a configuration table. It all depends on the requirements.

    ReplyDelete
  3. Martin,

    Thanks for the clue (which I didn't have). I should have looked through your code enough to notice that you were saving multiple preferences in fewer steps than it would take via the Apex process.

    When I get a requirement to do something like this, I'll definitely put your code to use.

    Thanks again

    ReplyDelete
  4. Hi Martin,

    like Stew said, I would also say that APEX supports "Saving Item values for each user" out of the box. It's easily controllable with the "Save Preferences" process and by defining the page item based on the preference. Your solution is for someone who likes to use a more generic and configurable approach.

    I just want to clarify that, because if someone just reads your blog posting, he could get the impression that there is really no out-of-the-box feature, which is not true.

    Regards
    Patrick

    ReplyDelete
  5. Hi Patrick,

    Thanks for the feedback. I've updated the post to include a note about your comment so people don't get confused with the approach I suggested.

    Martin

    ReplyDelete