Conditionally sort LIST OF VALUES

[bunnycat]

Crystal Xl/Oracle

Is there a way to conditonally sort a dynamic cascading prompt group. For example, we would like to give the user the option to sort a pick list by a person name or the employee id. If they select person name, they will see the list in name order or if they select employee id, they will see the pick list in employee id order.

 

[bunnycat]

What I would like to do is to have a static parameter giving the user the option to determine how to sort the pick list.

For example in the static prompt, I would have'NAME' or 'UNIT CODE'.

If Name is selected, the List of Values would be sorted by NAME. Vice versa, if the Unit Code was selected, the list would be sorted by the Unit Code. There are cases, some user will not know the name of an organization but will know the unit code. Vice versa, some will know the organization but not the unit code.

In other words, is there a way to conditionally sort a pick list?

 

[lbass]

Are you giving up on the cascading prompt aspect of this? There is a way to use a static prompt for sort order and then dynamic pick lists.

-LB

 

[raja2008]

May be you can use the description field in the dynamic cascading prompt to have the names displayed with the UnitCode.

When you pick UnitCode for the values area ,pick the Names field for the description.

This way the user will have both the information.

 

[bunnycat]

I guess my question is if I can dynamically sort the List of Values (LOV) based on the user imput?

For example: a list of values (LOV) with two options 'Sort By UIC' - 'Sort by Organization' If the User selects 'Sort By UIC' The pick list below it will be sorted in UIC order or if he selects 'Sort by Organization', the list of values (LOV) will be sorted by organization. Do you know if this can be done with Crystal 2008?

 

[lbass]

You didn't answer my question. Also, I'm not sure what CR capacity 2008 might have.

-LB

 

[bunnycat]

If you solution fits our need, I would love to give up on the cascading prompt. Because we have been pulling our hair out by trying to figure out a way to dynamically sort a pick list.

Could you give me the method of using the static prompt for sort order and then dynamic pick lists? I'll see if we can use your proposed method.

 

[lbass]

Create the static prompt {?order by} as a string parm, with options "Name" and "ID". Then create two commands:

//command:
Select table.`name`+ table.`ID`
from table
Order by table.`name` asc

//command_1:
Select table.`ID`+ table.`name`
From table
Order by table.`ID` asc

Do not link the commands to your main report table, and do not reference them in the body of the report. When you set up your dynamic parameters {?Name} and {?ID}, use the command expression as the value field for {?Name}, and use the command_1 expression for the value field {?ID}. Then set up your record selection formula in the main report like this:

(
(
{?order by} = "Name" and
{table.name}+{table.ID} = {?Name}
)
or
(
{?order by} = "ID" and
{table.ID}+{table.Name}= {?ID}
)
)

If there is only one name per ID, then you could avoid the concatenation, and just use one field for the value and one for the description field, and then adjust the record selection formula accordingly.

-LB