2. PHPRunner General questions
  3. [SOLVED]  Column sorting on a lookup field
This topic is locked
[SOLVED]

 Column sorting on a lookup field

 		12/9/2009 4:26:25 PM
PHPRunner General questions
S
 scotthallett author

Greetings, please let me know if this is default behavior.
I have a table called "employees" - in this table is a "department" field, but it is a lookup field:
employee table:

emp_id - int(10)

name - varchar(50)

email - varchar(50)
department table:

[color="#ff0000"]department_id - int(10)

department - varchar(50)
So - naturally - I have on the add page that this is a "lookup" field and use the lookup wizard.
It links it by "department_id", and displays and sorts it based on "department"
But, when sorting the column in the "list" grid, it doesn't sort them on "department" (alphabetically), but rather on department_id.
It appears to be the same for simple search as well.
I know there is a article on this, but I think I need to make my employees table's SQL script to have INNER JOIN?
Is this correct?
If so, can someone suggest the proper SQL command to update the above tables to?
Thanks all!
J
 		Jane 12/10/2009

Hi,
please check this article:

http://www.xlinesoft.com/articles/search_sort_lookup_values.htm
S
 		scotthallett author 12/14/2009

Thanks,
Yes - I was using that document - but admit I didnt have my inner join perfect - and it is also important (for those who have the same problem) to have (both) the foreign key _id from the primary table and lookup field.
In my case it was this:
SELECT

employee.employee_id,

employee.first_name,

employee.last_name,

employee.email,

employee.password,

employee.phone_number,

employee.department_id,

department.department [color="#0000ff"]<-- View, Search and List for this field

FROM

employee

INNER JOIN department ON (department.department_id=employee.department_id)
Once you do this, then you can select "Add" and "Edit" on the foreign look up _id field - and then select "List", "Search" & "View" fields.
Sorry that I hadnt scrutinized that doc enough - but the answers are all in there :-)
M
 		marke 1/14/2010

Jane
I have a similar problem - but can't get it to work with the information you provided.
My query is:

SELECT

`tblinput`.`idinput`,

`tblinput`.`simulationid`,

`tblinput`.`name` AS `name`,

`tblinput`.`type`,

`tblinput`.`description` AS `description`,

`tblinput`.`fileid`,

`tblinput`.`componentid`,

`tblinput`.`couplingtype`,

`tblinput`.`couplingfrequency`,

`tblinput`.`couplingfrequencytype`,

`tblinput`.`spatialregrid`,

`tblinput`.`spatialregridtype`,

`tblinput`.`timeregrid`,

`tblinput`.`updateDate` AS `updateDate`,

`tblinput`.`updateBy` AS `updateBy`,

`tblinput`.`modelid` AS `modelid`,

`tblactivity`.`shortname` AS `activityname`,

`tblactivity`.`idtblactivity` AS `activity`

FROM `tblinput`

INNER JOIN `tblsimulation` ON `tblinput`.`simulationid` = `tblsimulation`.`idtblsimulation`

INNER JOIN `tblexperiment` ON `tblsimulation`.`experimentid` = `tblexperiment`.`idexperiment`

INNER JOIN `tblactivity` ON `tblexperiment`.`activityid` = `tblactivity`.`idtblactivity`

ORDER BY `tblinput`.`simulationid`, `tblinput`.`name`


The query works fine. I have selected all fields to be available in Add and Edit
I want to use tblactivity.idtblactivity to be the category control in a dependent dropdown BUT the fields from joined tables are not available as a category control. This does work if I use a non-joined field as the category control - but for this case I need to use a joined field.
So then I tried an alternative solution modifying the WHERE clause on the dropdown definition directly:
"idtblactivity = '".$values["activity"]."'"
idtblactivity is the field in the table being used for the lookup, and I was hoping that $values['activity'] would hold the value I needed from the query above. But this doesn't work either (reports: undefined variable: values in tblinput_settings.php)
Is it a limitation of PHPRunner that I can't use joined variables as a category control in dependent dropdown - the preferred solution. If so is there another work around I can use.
thanks
Mark
J
 		Jane 1/15/2010

Mark,
unfortunately PHPRunner doesn't support joined fields as category field/category control.

As workaround you can create view in the database and then use this view as lookup table on the Edit as settings dialog.
Here is a helpful link:

http://dev.mysql.com/doc/refman/5.0/en/show-create-view.h