hi, you are facing the task to edit one tables data as well as an associated relationship table by id on a single apex page? you think that a multiselect list control will do for the relationship table entries but do not know how to integrate it? iff so, you are at the right stop. the following article will give you a screenshot tour of a step-by-step procedure with appropriate annotations.
placing and feeding the multiselect list control
#1 at first, on the dedicated edit page, create a new item within the form region.
#2 next, select a control of type Multiple Select.
#3 next, hand in an item name which is, by the way, actually the name of the variable (:P5_X2CHANGESET) you will later use to load and retrieve the items data (the id’s of the associated relationship table, respectively).
#4 next, define how apex populates the multiselect list entries and the variable on page load. this is not a static list-of-values object from the shared components, as you may already know it, but a regular query with the two dedicated columns to denote a display value at position 1 and an id value at position 2. note how the query uses :P5_XROLLOUT_ID to fetch, in terms of the application, the changesets that are available for a relationship with this rollout.
#5 next, declare and beautify the gui presentation of the multiselect list control.
#6 next, define how apex preselects the multiselect list entries (after the list has been filled). that is, specify what selection of id values from step #4 will lead to a highlightning of 0..n display values on page load. note the usage of the apex_util-package to convert the pl/sql-collection-based result of the bulk select into a colon-delimited list (yes, apex hands around its collections of values as a string variable, not comma- but colon-separated / nice to know but hard to find on the net iff you targetly search for it / have a look at APEX_UTIL for a reference).
DECLARE v_vc_arr2 APEX_APPLICATION_GLOBAL.VC_ARR2; BEGIN select xchangeset_id bulk collect into v_vc_arr2 from xrollout_2_changeset where xrollout_id = :P5_XROLLOUT_ID; return APEX_UTIL.TABLE_TO_STRING(v_vc_arr2); END;
#7 next, request that the items value (remember :P5_X2CHANGESET from step #3) will be reloaded for every page access, disregarding the page caching mechanisms.
so far, you should be able to display your page properly. the multiselect list filled, any 0..n list entries highlighted.
reading the multiselect list control and applying any changes
#8 next, define, by means of a process, what happens after a submit of the page. to achive that create a new process on the dedicated page.
#9 next, have a process of type PL/SQL for execution since, at the end, you will want to manipulate the associated relationship table.
#10 next, define a process name and the execution point within the processes workflow.
#11 next, layout the process as a pl/sql anonymous block, wiping and than re-inserting into the associated relationship table. again, note the usage of the apex_util-package, this time doing the inverse of step #6. there is no commit nessecary or allowed here because this will break the transaction border established by submitting the page.
#12 next, success and/or error messages, not employed.
#13 next, conditions, not employed
#++ oops, there was a typo in the code for step #11. use the following instead.
declare v_vc_arr2 APEX_APPLICATION_GLOBAL.VC_ARR2; begin v_vc_arr2 := APEX_UTIL.STRING_TO_TABLE(:P5_X2CHANGESET); delete from xrollout_2_changeset where xrollout_id = :P5_XROLLOUT_ID; declare idx number := v_vc_arr2.first(); begin while (idx is not null) loop INSERT INTO xrollout_2_changeset (xrollout_id, XCHANGESET_ID) values(:P5_XROLLOUT_ID, v_vc_arr2(idx)); idx := v_vc_arr2.next(idx); end loop; end; end;