2. PHPRunner General questions
  3. ORDER BY CASE in Lookup Wizard
This topic is locked

ORDER BY CASE in Lookup Wizard

 		2/26/2008 10:55:17 AM
PHPRunner General questions
S
 spintz author

I want to do something like this for my order by field, which is dependent on another field -

order by (case abbreviation when 'NEW' then 1 else 2 end), abbreviation


The dropdown only allows for selecting a specific column in the order by, what about a custom order by option? I know the custom is not available at this time, but then where to modify the order by clause for the dynamic lookup?
A
 		alang 2/26/2008

Have you tried just entering the order by statement in the field rather than using the Change button - it should work as long as it is valid SQL. I have done this where I want to order by fields that are not present in the primary table and therefore not presented as an option using the Change button - seems to work OK.
S
 		spintz author 2/27/2008

Have you tried just entering the order by statement in the field rather than using the Change button - it should work as long as it is valid SQL. I have done this where I want to order by fields that are not present in the primary table and therefore not presented as an option using the Change button - seems to work OK.


Thanks for the reply. My question was wrong in the first place, I wanted to add an order by for the field, when it's dependent on another field. For example, I have to ID, MainID and SubID, let's say. When adding a record, you select MainID, and the SubID is auto-populated based on the changed MainID. This all works fine, but I wanted to order the SubID fields when they're updated from the MainID.
J
 		Jane 2/28/2008

Hi,
to add ORDER BY to dependent dropdown box select corresponding field in the Order by dropdown on the "Edit as" settings dialog on the Visual Editor tab.
S
 		spintz author 2/28/2008

Hi,

to add ORDER BY to dependent dropdown box select corresponding field in the Order by dropdown on the "Edit as" settings dialog on the Visual Editor tab.


However, that does not allow for advanced ORDER BY, such as ORDER BY CASE. A custom field for ORDER BY would be nice.
J
 		Jane 2/29/2008

Hi,
unfortunately it's impossible to use calculated field in ORDER BY clause in the dropdowns on the "Edit as" settings dialog.