I have implemented a small improvement into my Generic solution for cascading lovs.
If you have a hierarchy of cascading lovs as I have it on my example page, the old version did an AJAX call for all cascading lovs and there cascading lovs, … each time a value was selected. But normally these calls will always result in an empty select list, because the value of the parent lov hasn’t been set yet.
Maybe you already had the “joy” to find out that accessing the “Row Selector” column of an updateable tabular form isn’t that straightforward as expected. Because the corresponding g_f01 array isn’t indexed by row number as the other column-arrays of the tabular form.
Instead it contains as many values as you have checked rows. The value of the array entry contains the “real” row number for which it has been checked. So you can have the situation that g_f01 has a count of 2, but your g_f02 containing the EMPNO contains 15 rows. In the case if the g_f02 is your “driving” array, because you want to process all the records and only for the checked ones you want to do something special, it can get really challenging for a new APEX programmer (and even for a more experienced one).
Do you like the separate APEX error page which is called when APEX raises an error (eg in the MRU/MRD process for tabular forms)? If yes, you don’t have to read further, if your answer is NO you may should read the rest of this posting…
I have done some minor enhancements and bug corrections for the ApexLib framework. It now supports Multiselect lovs, too. For details see the change log.
BTW, did you know that the Generic solution for cascading lovs, also works if the first item is a text item which restrict another lov?
I have updated the example on apex.oracle.com to include a multiselect lov and a lov which depends on a text item.
Change log of version v0.06 14-December-2006:
- Bug# 1615618: ApexLib_TabForm: error raised when no updateable report region exists.
Change log of version v0.05 14-December-2006
- CR# 1615591: ApexLib_Lov: add support for multiselect list.
I have updated the Generic solution for cascading lovs, it offers now supports for popup lovs, too.
I have also updated the example on apex.oracle.com to include a popup lov.
Change log of version 0.03 (12-December-2006):
- Added support for cascading popup lovs.
- Don’t generate a dependency if the referenced item issues a redirect/submit anyway.
- Corrected compilation errors in the ApexLib_Lov_for_flows_schema.pkb
Speed sometimes kills 🙂
Change log of version 0.04 (12-December-2006):
- Corrected a bug in the clearLovNullValues if called from the on-demand-process.
- Corrected wrong check in ApexLib.js.
I still remember the time when I was an APEX newbie and I really got confused with tabular forms (updateable reports). They behaved very different from what I knew about “normal” page items. So what where the issues which confused me?
- It isn’t possible to create any validations on the columns of the report, like I can do it for page items.
- You have to use the APEX_Application.g_fxx arrays instead of using bind variables as for page items.
- To find the correct array you have to look into the generated HTML code for the correct mapping.
- If the order of the columns gets changed, your code gets screwed up, because the columns now maps to a different g_fxx array.
- If a column has a condition or authorization the g_fxx array changes again, in the case the column doesn’t get rendered.
- The code isn’t really readable if there are just references to g_f01, g_f02, …
- The format mask has to be duplicated if the g_fxx is accessed and you want to get the correct data type (eg NUMBER, DATE)
- When you want to get the number of array entries you have to take care that you don’t use the array for a checkbox column, because that just contains the checked values!
- You have no function to find out if the row has changed.
- When the validation of a format mask or required column fails, the error message is very internal. The user doesn’t have a clue which column and which row was effected. And it stops after the first error.
That’s a long list and that’s why I did some programming on the weekend to get a solution for the above short comings, so that newbies and experienced APEX developers don’t have to care anymore.
I think most of us had already the problem, that when the Display Null property for a LOV has been set to “Yes”, that APEX had generating a %null% as indexing value for this “null” lov entry.
The problem now is that when the user selects the “null” entry from the select list, the page item gets populated with the %null%. If you don’t write some code to set the item to NULL when it has %null% as value, the database will raise an error (eg invalid number…).
If you have read my previous posting about Caution when using PL/SQL functions in SQL statements and a related thread on the OTN forum, you know that using the V or NV function in your SQL-where clause can have a performance impact on your query.
I have created wrapper functions for V and NV which use the DETERMINISTIC optimizer hint, so that the optimizer/query engine just calls them once for the query and not for every row.
Have updated the blog entry about “Generic solution for depending select lists/lovs“. The description and the files cover now what do do in case of a version prior to 2.2.x.
Just curious, is somebody already using the solution in his APEX application?