Sort order in drop down filter.

[creotom]

Hi all.
I have a field that is filled from another list using databasejoin using a dropdown list. I would like to have the values in the dropdown sorted by another column in that table rather than alphabetically.

Is this possible.

I know I can do it for list view. I have specified that the list be sorted by a special column Ive created called "sort". I would also like to have the dropdown appear the same way.

Thanks in advance.

 

[troester]

Set "Joins where and/or order by statement (SQL)" field to

order by sort

 

[cheesegrits]

You might want to put backtick quotes around the `sort`, in case it's a MySQL reserved word. Can't remember if it is, but looks like one of those names that could well be.

-- hugh

 

[creotom]

Perfect! Thanks guys. A small item but it makes a big difference to the logic of the page.

 

[creotom]

Sorry guys back again.

It works fine for the drop down on the form but I have the field set as a drop down filter on the list view as well. Can I affect the sort order of this drop down. It is still appearing by id or label and I would like to sort by the sales_status_sort field.

Thanks in advance.

 

[rob]

hi I've had a go at fixing this, its acuatlly quiet complex.
You will need to update from github and then to get the order by to apply edit your database join, and under the list view settings -> filters section,

* change the 'order by' option to 'none'
This will then use the db join's 'Joins where and/or order by statement (SQL)' query when building the filter list

 

[creotom]

Hi thanks so much for taking the time to answer me.

I've updated from github this morning. But I dont have the option for none. I have Order by: label or Value.

Could I be doing something wrong.

Thanks in advance.

 

[troester]

I can't find it, too. It seems the modification didn't make it into GitHub, Rob keeping it secret

 

[rob]

yes sorry my bad, I'd not actually commited it! Should be there now, sorry for the delay!

-Rob

 

[creotom]

I tried the new update and now I'm getting a massive sql error on the list page.
Kind of panicking now so uploading the previous version to try and fix it.

 

[troester]

Now it's impossible to select a dbjoin label (always showing "-select a table-"), selecting the value is ok.

And this gives a SQL error
Unknown column 'cityccd.citya'
where citya is the column name of the dbjoin element which should be the (not selectable) column name of the joined table.

 

[creotom]

Reverting to the previous version didn't fix the SQL error for me.

 

[troester]

Did you check your element settings? Maybe it has lost the label and it can be reset with the previous version.

 

[creotom]

No all the elements for that list still have their labels.
The only thing I can think of at this stage is to wipe it all and start from scratch.

 

[troester]

No, I didn't mean the element labels but in the settings of your dbjoin element the selected label from the joined table.

 

[creotom]

Sweet!
That worked!
I'm grinning like an idiot. Thank you so much!
I was actually on the verge of starting from scratch, I'll have to buy you a beer some time.

 

[troester]

Thanks.

Resetting GitHub to
"added: element list filter option - "none" allows for overriding with db join element where/order statement"
it's showing the new option "none" but working like before:

selecting "Label" filter query is ...order by xx.id
!!!! I think Label and value were interchanged all the time

selecting "value" or "none" filter query is ....order by xx.my_name

The next GitHub commit is breaking the element
https://github.com/Fabrik/fabrik/issues/419

 

[mhelm]

This doesn't work for me:
I have a list tbl_gait with the elements id, nameger and gait_sort.
I use this as a dropdown-element (table: tbl_gait; value: id; Label: nameger) and I want the dropdown to be ordered like in gait_sort, so I added "ORDER BY `{thistable}`.`gait_sort` ASC" in element where/order statement - this worked until GitHub 3.0.5.6xx.
After updating to 3.0.6.3 I get a filter query error:
ORDER BY `tbl_gait`.`gait_sort` ASC ORDER BY `tbl_gait`.`id` ASC
Obviously the ORDER BY is applied two times.

Element List filter option is already set to "NONE".

thanks in advance!
mhelm

 

[rob]

I just re-tested and its working for me, not sure what to suggest.
Do you have advanced filters turned on? or a prefilter added or a url filter, or a pre-filter set in the menu item?

 

[mhelm]

I played a bit with the options: The error occurs as soon as I set the "filter type" in List view settings-->filter to dropdown (all other options e.g. field or Auto-complete work).
Hope this helps...