Aside from validations, you may want some process to do something with values before deleting/updating/creating.
Apex 4.1 introduces some new substitution strings:
http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35125/concept_sub.htm#BEIIBAJD
APEX$ROW_NUM - the row number being processed
APEX$ROW_SELECTOR - If a check has been marked in the checkbox (value will be 'X')
APEX$ROW_STATUS - C if created; D if deleted; U if updated
I wasn't really sure how to work with these, and nor did I really investigate, when I first saw them in the builder guide. A recent thread in the forums, and it all makes sense!
With these changes, you no longer need to deal with the array, but instead can just refer to the above substitution strings in combination with bind syntax to access specific columns. The important steps are that when you first create the validation or process, you specify the tabular form it is associated with. According to the help text of execute condition, you must also specify the execute condition as For Each Row, if referencing any tabular form columns in the process - However, in my tests, I didn't experience this, Once or For Each Row didn't make any difference.
Side note: from what I can see, this doesn't work with Manual Tabular forms, so in that case, you will still need to use the g_f0x array.
For example, lets check the id column matches the row number (not a good example, as sorting will mess this up, but just to give you the idea)
Add a new validation, and specify the type as function returning boolean
return :APEX$ROW_NUM = :ID;(Referencing the column name with bind syntax)
Similarly, if you had some client side function to re-order rows, you could then have a page process:
update tabular_test set num_col = :APEX$ROW_NUM where ID = :ID;
One more example might be that you want to delete child records before performing the multi row delete. Another process:
begin if :apex$row_Selector = 'X' then delete from child_table where some_id = :id; end if; end;
Alas; no need to deal with the apex_application.g_f0x array (for the most part). Just associate a validation/process to a tabular form and make use of these new variables and reference tabular form columns using bind syntax.
Thanks so much for the nice post.In my case, I need to call a function on a given row that would validate the row before committing to the database.
ReplyDeleteLet's say I have a table t with columns x,y,z that are in the tabular form.
I need to validate via a function - my_func(:x,:y) whose input would determine if the row is good to be commited. How can I acomplish this ?
Hi Alice,
DeleteIt's pretty straight forward to set that up - just specify the tabular form when you create the validation, and do as described.
Here's an example: https://apex.oracle.com/pls/apex/f?p=14882:23
Cheers,
Trent
Thanks so much for the example. It's of tremendous help.
ReplyDeleteI tried the example. Both yours and the one provided by Patrick Wolf with the table. Needless to say that after going through trials and tribulations and was finally able to compile the function that gets the messages from the table, and provided all the necessary access, my generic error unique constraint JOB_UK is still displayed instead of the message in the table. I setup the message at the application level under application properties. Not sure what I am missing. Do you by any chance have the code sample the way it is setup in the apex application ?
ReplyDeleteAre you talking about the Error Handling API?
Deleteit is very nice
ReplyDelete