Pages

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.

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 precautionCREATE OR REPLACE TRIGGER trg_tapex_remember_pg_itm_buir BEFORE UPDATE OR INSERT ON tapex_remember_page_item FOR EACH ROWBEGIN :NEW.item_name := UPPER (:NEW.item_name);END;/

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

5 comments:

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. :-)

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.

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.