Monday, January 21, 2008

APEX by Example - Default value of Shuttle

I got some questions about setting the default value of a shuttle. The example can be seen here.

Basically a Shuttle are two lists: on the left you'll see all values, which you can select by moving it to the right. If you already want some values on the right when you open the page, how do you do that?

The APEX components in my example: a Region, a Shuttle item and a Computation.
Creating the region and the Shuttle isn't that hard. The query of the Shuttle is:

SELECT ename, empno FROM emp ORDER by 1
The computation is of type "PL/SQL Function Body". The actual code looks like this:
What does it do?
It fills an array "emp_list" with all the values you want to have already selected (on the left in the shuttle). In my case the first 3 records.

The most important part is the last line APEX_UTL.TABLE_TO_STRING. Which transforms the array in a way so the shuttle can understand it. A string, with in between the values a ':'.

That's just one way of doing it. In APEX you can follow different ways to accomplish the same result. Hope it helps some of you.

15 comments:

Patrick Wolf said...

Dimitri, very useful tip! From what I have read on the forum a lot of people have problems understanding the Shuttle/Multi-Select-Lists/Checkboxes and how to set them.

BTW, wouldn't it be better to use the "Source value or expression" property of the Shuttle? Because if the computation is defined as "Before Header", it will be executed each time the the page is rendered...

Patrick

Dimitri Gielis said...

Hi Patrick,

Sure you can use another method to assign the values. I guess it depends case by case; when to use a computation (and what type of computation), when to use a process, with or without conditions, when to use the source value/expression ...

As I said in my post: so many ways to get the same result ;-)

Dimitri

Anonymous said...

Dimitri,
I'm new to APEX. I'm trying to use shuttle box in my application. The situation is as given below. Left side of shuttle box should be populated by a query and the right side of the shuttle box should be populated from a different query. I should be able to shuttle between both the boxes. Is it possible? If you could throw some light on it, it would be really helful. Thanks!

Shaan

Carina said...

Don't forget to set the Shuttle properties properly. In LOV, set 'Display Extra Values' to YES.

For the life of me I couldn't figure out why mine wasn't working. It was just because of the little thing.

Udit kr Jain said...

Hello Dimitri,

You have a really wonderful blog. I am new to APEX...i m trying to have a default value for a select list LOV...My lov is a dynamic one.... can i achieved that?

Pls let me know, how?

Thanks!

Udit

Udit kr Jain said...

Hey Dimitri,
i figer out that defauly value one...

Now can u pls let me know that like in a report builder we have lexical parameter same can we have in APEX too?

thanks!
udit

MOTIGA said...

Hello,
I start with APEX, I would like to know how I can do to filter a query report to the selected items in a shuttle. For example:
SELECT a, b, c FROM table WHERE c in (: P_ITEM_SHUTTLE)

Tiago V said...

Hi Patrick,

I'd like to know how to get the values in the shuttle.


Thank you.

Tiago V

John C Smith said...

Dimitri, the shuttle is perfect and you have a great explanation of how to use them. My requirement is to populate the existing values. I can do this with most fields using a dynamic action. I can’t seem to get this to work for a shuttle or select list where I have multiple values. Any help would be appreciated.

Anonymous said...

Big thanks to Ana above for the helpful tip about leaving display value as null.

I struggled as well for a while before I looked at the comments.

Kudos to Dimitri for this very helpful post.

Anonymous said...

Dear Dimitri,
This is gurujothi from India, Am new to apex,Just now i came to know about your blog,
Thank you for your work,here I have the requirement
in my form two columns are there
1.leave_type 2.leave_id
the first one is LOV and when I select the value for first_column the second one needs to be filled automatically
for example.

if 1st is casual the second is ca
if first is sick the second is si and etc.,
how can i get it?
Please Help me'
Thank you ,
With regards,
Gurujothi

Anonymous said...

Exactly what I needed. Thank you!!

Unknown said...

Hi, just as Patrick Mentioned, here is another way of implementing the solution.

Step 1 : Create a shuttle page item with the required LOV with Display and Return values.
e.g
SELECT DISTINCT QUEUE_MEMBER D, QUEUE_MEMBER R
FROM TBL_ISSUES_QUEUE

Step 2 : In the same shuttle item under source, choose "source type" as sql query(return colon separated values)

Step 3 : In the SQL, you can use listagg function:

e.g SELECT listagg(QUEUE_MEMBER , ':') WITHIN GROUP (ORDER BY QUEUE_MEMBER) FROM TBL_ISSUES_QUEUE WHERE (some condition)

This will populate the right hand side of the shuttle.
Hope it helps.

Regards,
viczsaurav

Anonymous said...

Thanks it works :) !1!!

Unknown said...

Also works by setting the shuttle's "Default" to type "SQL Query returning colon delimited list", where the query selects the values to be pre-selected - i.e. in the second list box.