APEX is a great development tool, it’s simple and fast to build our forms and reports, until…. someone asks you for a change that from their perspective seems simple, but you know is going to be a major problem. Why? well unlike the universe there’s a number of limitations in APEX. How many times have you asked yourself “Why can we only create one interactive report per page?” and “Why can we only create one tabular form per page?”. On this occasion it was the latter question I found myself asking in APEX 4.1 (and previously in 4.0, 3.2, 3.1…).
Now the requirement of two tabular forms on a page is a question I had to answer thanks to a late change in the development of an application that was in the final stages of testing before “Go Live”. The real problem I faced was that my simple APEX page was not so simple it had a wizard driven tabular form with validations, plus a number of dynamic actions, plugins, dialogs, accordion, and multiple custom form regions. Avoiding a redesign was my number one aim so the question I had to ask myself was… knowing that I can’t create two wizard driven tabular forms, would it be possible to have a wizard driven tabular form and a manual tabular form on the same page? If so this would mean that I could save a load of time as I would only need to add my additional manual tabular form and some supporting PLSQL processing code.
In theory it seemed pretty straight forward, and I was pretty sure I’d performed the same technique in earlier version of APEX. For my second manual tabular form I would create my form items using the APEX_ITEM package and give them a higher sequence of f0x array names than what’s defined in my wizard driven tabular form. I also decided to use a collection based method for simplicity.
First we define a View that will determine our collection column mapping.
CREATE OR REPLACE VIEW USER_COMPETENCIES_COL_VW AS SELECT c001 id , c002 competency_id , c003 competency , c004 pdp_id , c005 target_level , c006 current_level , c007 notes FROM apex_collections WHERE collection_name = 'COMPETENCY';
Next we create our report region with a query that has APEX_ITEM API calls to create our form items. Noting the fact that we don’t need to create a hidden item for our competency ID column as it’s already stored in session state in the collection. We’re just expecting the user to enter the data in for each competency.
SELECT competency_id , competency , apex_item.select_list(48,target_level,'1;1,2;2,3;3,4;4,5;5',NULL,'YES',NULL,'Please Select...') target_level , apex_item.select_list(49,current_level,'1;1,2;2,3;3,4;4,5;5',NULL,'YES',NULL,'Please Select...') current_level , apex_item.textarea(50,notes) notes FROM user_competencies_col_vw
In the above example I’d recommend using a high f0x sequence just to ensure that you won’t run into an issue with adding more columns to your wizard built tabular form. Also the above query is actually based on an APEX collection which is created on page load. In the above example there are a finite number of competencies that I want a user to complete. Here’s an example of the collection creation code e.g.
PROCEDURE create_competency_col AS l_query VARCHAR2(32767); l_collection_name VARCHAR2(200) := 'COMPETENCY'; BEGIN IF apex_collection.collection_exists(l_collection_name) = TRUE THEN apex_collection.delete_collection ( p_collection_name => l_collection_name ); END IF; l_query := 'SELECT NULL id'|| ', competency_id'|| ', competency'|| ', NULL pdp_id'|| ', NULL target_level'|| ', NULL current_level '|| 'FROM COMPETENCY'; apex_collection.create_collection_from_query ( p_collection_name => l_collection_name , p_query => l_query ); EXCEPTION WHEN OTHERS THEN -- custom error handler goes here RAISE; END create_competency_col;
Pretty simple, and that’s what I initially thought as it displayed and rendered Ok. So thinking that the job was almost done (after writing the custom processing code) during testing I noticed that on validation error my wizard driven tabular form wouldn’t return any data and I would get an “ORA-01403 No Data Found” issue. I also realized that my manual tabular form was empty as well. What the? How could introducing some new f0x arrays cause a problem for my other wizard created tabular form especially when it knows nothing about them?
Well since I didn’t have the APEX source code to trawl through to find my answer, I did have a couple of experiments which I could conduct. I had an assumption that the problem was within the APEX wwv_flow.accept procedure thinking it must loop through every f0x array parameter. Knowing that there were more rows in my f48,f49,f50 arrays than were in my wizard driven tabular form I concluded that APEX was trying to access an array index which was empty.
Since we do have access to the f0x arrays on page submit my first test was to move part of my application page process that reads the f0x arrays and updates the collection and put it in a separate process that runs “Before computations and Validations”. In this new process I would read the f48,f49,f50 arrays and update their values within the collection and then manually reset them. e.g.
PROCEDURE update_competency_col ( p_pdp_id IN pd_pdp.pdp_id%TYPE ) AS l_query VARCHAR2(32767); l_empty_array apex_application_global.vc_arr2; l_collection_name VARCHAR2(200) := 'COMPETENCY'; BEGIN FOR i IN 1..apex_application.g_f50.count LOOP apex_collection.update_member_attribute ( p_collection_name => l_collection_name , p_seq => i , p_attr_number => 5 , p_attr_value => apex_application.g_f49(i) ); apex_collection.update_member_attribute ( p_collection_name => l_collection_name , p_seq => i , p_attr_number => 6 , p_attr_value => apex_application.g_f50(i) ); END LOOP; -- -- To ensure that the existing tabular form for objectives still works -- we need to reset the arrays. This is done before computations & validations -- apex_application.g_f48 := l_empty_array; apex_application.g_f49 := l_empty_array; apex_application.g_f50 := l_empty_array; EXCEPTION WHEN OTHERS THEN -- custom error handler goes here RAISE; END update_competency_col;
Note: in the above you will need to work out the correct f0x mapping and p_attr_number per column and update your code accordingly.
The reason why we can reset the f0x arrays in questions is because we have already extracted their values and saved them into the collection/session state. If all validations pass we will then process and save the collection data. Validating the manual tabular form data will simply involve querying the collection data and performing your validation tests, whilst we’re still able to use the inbuilt APEX tabular form validations on our wizard built tabular form. When a validation failure occurs there’s no problems for our manual tabular form because when the page re-renders it will read and display the data already saved in the collection which is the basis of our report query.
To process the collection we create another PLSQL process that runs after “Computations and Validations” and could be something similar to the following merge statement:
PROCEDURE process_competency_col ( p_pdp_id IN pd_pdp.pdp_id%TYPE ) AS BEGIN MERGE INTO user_competencies au USING ( SELECT id , competency_id , competency , nvl(pdp_id,p_pdp_id) pdp_id , target_level , current_level FROM user_competencies_col_vw ) nu ON (au.id = nu.id) WHEN MATCHED THEN UPDATE SET au.target_level = nu.target_level , au.current_level = nu.current_level WHEN NOT MATCHED THEN INSERT ( au.id , au.competency_id , au.pdp_id , au.target_level , au.current_level ) VALUES ( nu.id , nu.competency_id , nu.pdp_id , nu.target_level , nu.current_level ); EXCEPTION WHEN OTHERS THEN -- custom error handler goes here RAISE; END process_competency_col;
The end result? it works!
In summary the steps to manage both a wizard driven tabular form and manual tabular form is to use a collection to hold you manual tabular form data and reset the f0x arrays used by it before computations and validations. It’s a design technique that saved me the heartache of a page redesign and an uncomfortable discussion with the project manager and business sponsor
Now the only thing missing is how to easily add rows to the manual tabular form without refreshing the page. Whilst this wasn’t a requirement for me this time if you need this I would advise that you create a button and bind a dynamic action on click with some jQuery code to copy the last row of the manual tabular form and increment the HTML input id’s by 1. Here’s a good starting point described on Stack Overflow.
Please Note: the above technique of manipulating global variables defined in APEX_APPLICATION or WWV_FLOW is not supported by the APEX development team. I do not take any responsibility for any undesired affect that this might cause in your application please test the process and make sure it works as expected.
Lastly if an APEX development team member was to stumble over this, would it be possible to have radio groups supported in the APEX_ITEM package in APEX 4.2 to be used within manual tabular forms?
P.S. You might also find the following post quite useful from Denes Kubicek on the same subject posted a number of years back.
Today seems to be going from bad to worse. Not only am I fighting a number of IE6 issues with APEX and CSS I’ve run into a couple of issue with tabular form validations in APEX 4.1.
Please Note: the workarounds which I’m about to describe in this post are not supported by the APEX team and that they may not work in future versions of APEX. Please proceed with caution. I take no responsibility for any negative outcomes from this.
To circumvent the problem I created a “PLSQL Function Returning Boolean” validation which runs after all others validations and strips the line breaks for the first two columns e.g.
BEGIN FOR i IN 1.. apex_application.g_f01.count LOOP apex_application.g_f01(i) := regexp_replace(apex_application.g_f01(i), '('||CHR(10)||'|'||CHR(13)||')',''); apex_application.g_f02(i) := regexp_replace(apex_application.g_f02(i), '('||CHR(10)||'|'||CHR(13)||')',''); END LOOP; RETURN TRUE; END;
The point to note is that our validation always returns TRUE as we’re not actually validating anything, just running some PLSQL to strip the line breaks. I find this hack quite useful in certain situations, since we can’t use computations as they are bound to page and application items. In order to work out which f0x array you need to strip the EOL characters from, use firebug or an equivalent DOM inspector.
Now if you want to keep line breaks in your tab form columns and only strip them to avoid validation errors then set a condition on this validation to “Inline Validation Errors Displayed”.
BEGIN ''; RETURN TRUE; END;
Please Note: in the above code there is an intentional space between the colon and item name because wordpress is replacing these with smilies.
The next step is that we create one more fake validation which is the last in out list (i.e. has the highest display sequence). It is a “PLSQL Function Returning Error Text” validation which has the following PLSQL:
In the end it’s a pretty heavy and ugly solution for something which is expected to be built-in within APEX, but the bright side is that APEX is still flexible enough for us to code workarounds when there’s bugs within the product.
This is a follow up post to “Combining your item validations into one, without losing inline display”. Whilst most of the techniques I describe (present and past) are not officially supported, the point to note is that they simply exist because APEX currently does not provide the required functionality. The thing I like about APEX is that there is a lot of flexibility to interact with the engine during page rendering and processing, at the same time though we need to ensure that we do this in a safe and future proof manner, or at least be aware of the risks and properly document them.
So the point of today’s post is to fulfil the requirement of creating complex tabular form validations without losing the benefits of inline display. Now I’m sure this will be coming in the next APEX 4.1 release, but as always I have an immediate requirement. So to fill the void in the meantime I’ve extended the “LOG_VALIDATION_ERROR” function defined in the earlier post to include support for tabular forms ( it’s simply a wrapper for code centralization purposes ).
-- --------------------------------------------------------------------------- -- -- Procedure : log_validation_failure -- Author : Matt Nolan -- Private/Public : Public -- Description : Wrapper for logging validation failures in the APEX engine -- --------------------------------------------------------------------------- -- Revision History -- Date Author Reason for Change -- --------------------------------------------------------------------------- -- 02 FEB 2011 M.Nolan Created. -- --------------------------------------------------------------------------- PROCEDURE log_validation_failure ( p_item IN VARCHAR2 , p_message IN VARCHAR2 , p_location IN VARCHAR2 DEFAULT 'INLINE_WITH_FIELD' , p_column_id IN VARCHAR2 DEFAULT NULL , p_column_name IN VARCHAR2 DEFAULT NULL , p_row_num IN VARCHAR2 DEFAULT NULL ) AS l_app_id VARCHAR2(10) := apex_application.g_flow_id; l_page_id VARCHAR2(10) := apex_application.g_flow_step_id; l_item_id apex_application_page_items.item_id%TYPE; l_index PLS_INTEGER; BEGIN apex_application.add_validation_error ( p_message => p_message , p_error_display_location => p_location , p_page_item_name => p_item , p_column_id => p_column_id , p_column_name => p_column_name , p_row_num => p_row_num ); IF apex_application.g_debug THEN apex_debug_message.log_message('Custom Validation'); apex_debug_message.log_message('...Item: '||p_item); apex_debug_message.log_message('...Message: '||p_message); apex_debug_message.log_message('...Location: '||p_location); IF p_column_id IS NOT NULL THEN apex_debug_message.log_message('...Column ID: '||p_column_id); apex_debug_message.log_message('...Column Name: '||p_column_name); apex_debug_message.log_message('...Row Number: '||p_row_num); END IF; END IF; EXCEPTION WHEN OTHERS THEN -- custom error handler goes here RAISE; END log_validation_failure;
Here’s an example of how we would code a custom tabular form validation ( Note: its a PLSQL page level validation which always returns TRUE as we add the errors ourselves within the function):
FUNCTION mytabform_validation ( p_tabform_pk IN APEX_APPLICATION_GLOBAL.VC_ARR2 , p_tabform_column IN APEX_APPLICATION_GLOBAL.VC_ARR2 , p_column_id IN VARCHAR2 , p_column_name IN VARCHAR2 , p_message IN VARCHAR2 ) RETURN BOOLEAN AS l_student_id user_volts_reservation_day_vw.student_id%TYPE; l_reservation_id user_volts_reservation_day_vw.reservation_id%TYPE; l_message VARCHAR2(500); BEGIN FOR i IN 1.. p_tabform_column.count LOOP l_pk_id := p_tabform_pk(i); l_value := p_tabform_column(i); IF NOT some_validation ( p_pk => l_pk_id , p_value => l_value , p_message_override => l_message ) THEN log_validation_failure ( p_item => NULL , p_message => nvl(l_message,p_message) , p_location => 'INLINE_WITH_FIELD_AND_NOTIFICATION' , p_column_id => p_column_id||'_'||lpad(i,4,'0') , p_column_name => p_column_name , p_row_num => i ); END IF; END LOOP; -- -- Always return true as we validate and add the error messages ourselves -- RETURN TRUE; EXCEPTION WHEN OTHERS THEN -- your custom error handler goes here RAISE; END;
And here is how we’d call the validation, by passing in the column arrays which we are interested in validating along with the column_id of the field we are validating and its corresponding name. In this example I also pass through the tabular form primary key as well to use in table lookups during validation.
RETURN mytabform_validation ( p_tabform_pk => apex_application.g_f02 , p_tabform_column => apex_application.g_f03 , p_column_id => 'f03' , p_column_name => 'STUDENT_ID' , p_message => 'Student is booked on another course!' );
Note: the “column_id” and “g_f0x” array items can be determined by using firebug and inspecting the input elements in the tabular form on the page. Alternatively you could omit them and do a lookup of the APEX Data Dictionary within the validation based on the column name passed through to identify the required id’s.
If you need extra validation control on your tabular form but are uncomfortable with using the undocumented “ADD_VALIDATION_ERROR” procedure, then have a look at an alternative post Denes Kubicek wrote late last year. Otherwise I hope you find this information useful, as it’s helped me on my current project immensely.
Finally if one of the APEX development team members happens to read this ( not naming and shaming names ) a little inside knowledge of whether this will break in the next 4.1 release would be invaluable.