While working with select list and shuttle, when we want to display values into right side of shuttle depending upon selection from select list, using numerous methods we can achieve this,today i'll show the simplest way it can be done without any hassle.
After trying different things which all worked according to the requirement,I found a simple solution which can be more effective though preventive measures must be taken before using it :)
We will create a select list (item(p1_dept) + lov) based on department table,which shows how many departments we have.
SELECT
dname d,
deptno r
FROM dept
Now, we will create a shuttle based on value employees table and put a deptno as filter in this sql,so whenever we select deptno from select list it will pass those values into shuttle sql,make sure to fill cascading lov parent items.(p1_dept)
SELECT
ename ,
empno
FROM emp
WHERE deptno = :p1_dept --p1_dept is page item
After trying different things which all worked according to the requirement,I found a simple solution which can be more effective though preventive measures must be taken before using it :)
We will create a select list (item(p1_dept) + lov) based on department table,which shows how many departments we have.
SELECT
dname d,
deptno r
FROM dept
Now, we will create a shuttle based on value employees table and put a deptno as filter in this sql,so whenever we select deptno from select list it will pass those values into shuttle sql,make sure to fill cascading lov parent items.(p1_dept)
SELECT
ename ,
empno
FROM emp
WHERE deptno = :p1_dept --p1_dept is page item
After finishing basic stuff we will create a dynamic action - pure client side to wrap it up.
Create action :
When : After refresh
Selection Type : Item(s)
Item(s) : Shuttle Item -- In may case it is P1_SHUTTLE
True action:
Execute Javascript Code :
var emps = document.getElementById('P1_SHUTTLE_LEFT').options;
var allValues = "";
for (index = 0, len = emps.length; index < len; ++index) {
allValues += emps[index].value.concat(":");
}
$s('P1_SHUTTLE',allValues); //here we can use apex_item setValue to set values.
Other ways...
we can use Set value as in true action
Settings - > set type -> sql statement
select listagg( empno, ':' ) within group (order by upper( ename ) ) ret
from emp
where deptno = :P1_DEPT
Items to Submit : P1_DEPT
Affected Elements :
Selection Type : Item(s)
Item(s) : My_Shuttle
Final output :
Also you can have a look here https://community.oracle.com/thread/3591836 which is another way of setting values.
test/test
Hope you like it!!
