Sorting by items in Dynamic Valuelists instead of actual val

[transcan]

In main list view, one can sort by clicking on the field title.

However, I am using dynamic valuelists instead of the actual values in the field. When trying to sort, it sorts, for example, by numerical code (actual data) instead of the last name or item category (dynamic valuelists). How can the sort be aware or trained to sort by the items within the dynamic valuelists instead of the actual data in the field?

Thanks in advance.

[shannah]

Create a grafted field with the field value and sort on that.

.e.g in the fields.ini file you would have something like:

Code: Select all

__sql__ = "select t1.*, t2.valuelistvalue from mytable t1 left join valuelisttable t2 on t1.valuelistid=t2.valuelistid


From here on you can work with the valuelistvalue field as if it is a field in your table. I.e. you can add a section to your fields.ini to set the settings on it. In these cases you would usually hide the valuelist id field in list view and show the valuelist value field instead.

[transcan]

Thanks. I having trouble and need more details to correctly modify the code:

Given:

table1
=====
last
first
employee_no


table2
=====
field1
field2
...
employee_name
...
fieldx

Working:

valuelists.ini for table2 contains:

[Last Name] (vocabulary for employee_name in table2)
__sql__ = "SELECT employee_no, last FROM table1 ORDER BY last"

The above correctly does what it does but does not sort by last name but employee_no when in list view.

Problem (to sort correctly):

The below goes in fields.ini for table2 under [employee_name] or [valuelistvalue] ?:

Using the example code as a template:

Code: Select all

__sql__ = "select t1.*, t2.valuelistvalue from mytable t1 left join valuelisttable t2 on t1.valuelistid=t2.valuelistid"


I've gotten only this far:

Code: Select all

__sql__ = "select table1.*, table2.valuelistvalue from table1 left join table2 on table1.valuelistid=table2.valuelistid"


Is something else also substituted or missing?

Do I create or place under :

[employee_name]

[valuelistvalue]
__sql__ = "select table1.*, table2.valuelistvalue from table1 left join table2 on table1.valuelistid=table2.valuelistid"
...
...

What am I missing?

Feels like the second week of Algebra I.

Thanks in advance.

[transcan]

Problem Solved using information from:

[url]
viewtopic.php?t=4404#21835
[/url]