APEX_ITEM and checkbox processing
Oracle apex_application.GFnn made easy
Few days ago I have been asked for an advise on how to process form/reports based on APEX_ITEM and since this can be very confusing to people new to APEX I decided to share a quick and easy way of processing your records.
We all saw a form looking like this before
and probably we have learned how to process it using something similar to
FOR i IN 1 .. apex_application.g_f01.COUNT LOOPinsert into MY_TABLE (NAME, GENDER)values ( apex_application.g_f01(i), apex_application.g_f03(i) );END LOOP;
That was all smooth sailing so far but what if we wanted to do something that involved multiple checkboxes. This is where things would start to get out of hands.
Problem we all run into are checkboxes unlike with APEX_ITEM.TEXT or APEX_ITEM. SELECT_LIST is that only if they are checked we can loop through them. So very often our loops end up in a dead end and we get ORA-ERRORs.
Looking at the image above I want to select columns that I want to see plus say if I will do aggregate or average calculations on them which is completely optional.
Notice that some of columns that are of data_type varchar or date are disabled how would we go about processing something like this.
Before I go into my example there are really good read outs by Jeffrey and Roel among others which will help to understand it in more details too.
Lets look under the hood. First we would create a Classic report region with SQL as source.
It may be a lot to take in at once but all we are doing is creating few checkboxes columns, one select list and few text display columns. That is all at this stage.
If you set Escape special characters on each column now to NO you should get your tabular form up and running.
To store your selected values we will use APEX collections and a very nifty way how you can easily collect all apex_application.GFnn values using ADD_MEMBERS procedure.
Lets create a SAVE button which will submit the page and on After Submit we will create a PLSQL process to store this data.
Only thing at this stage is to make sure you got your column indexes right. So each of my query columns had a parameter passes in called p_idx which maps than to my PLSQL parameters.
Run the page and select few records and click Save, if you did all of this right at this stage you should have a collection created which holds your values. Click Session at the bottom of DEV tool bar and let's inspect what is in this collection.
Wow! how cool is this in a few minutes we managed to store our values into a collection which we can then use for processing. If you check your ADD_MEMBER and results you will see that we only stored checkboxes we checked.
Sometimes you just want to do just this and process records that were selected by a user.
Please note that if user does not select any row under Select? column there will be no records in there at all so this is something to be aware of. At least there were not errors shown to us as users.
Please note that if user does not select any row under Select? column there will be no records in there at all so this is something to be aware of. At least there were not errors shown to us as users.
How would we store all records regardless if checked or not? Now comes the final spin, let's modify our query to be
As you see we added few bits to our checkboxes and also joined back to our collection so we can show on the screen our previously selected values. Trick that we are doing is we are adding a hidden item to each checkbox that we will set using JS when each of the checkboxes is selected.
To do this we need two piecies of code - one is updated After submit process where we will not be storing checkboxes values at all but we will store values of these hidden items we concatenated to them.
On top of this there is a little javaScript we will add to our page to help us set values on these hidden items.
this is where we will utilize all the classes we added in our SQL query. What this is doing is listening on every checkbox change and if value is checked it will assign value 1 to its hidden element and if it's not then 0. Whole point is for us to then be able to store this value and use it for our processing.
Save and run the page. You should get something similar to a live demo. Here you can find the code for query too.
Is this the only way how you could solve the above problem probably not but I hope this gives you extra ideas for your potential problems. ;)
Happy APEXing,
Lino
Lino
