AppGini Community Forums

Appgini 24.17

I have a lookup field, and my SQL statement is like this:
SELECT `m_title`.`id`, `m_title`.`title` FROM `m_title`
WHERE `m_title`.`is_t1`=1 AND `m_title`.`is_active`=1

I don't want the LOV to be sorted.

When putting the above in the advanced lookup option, the LOV in the drop-down will be sorted by title by itself, which is not what I want.

When I changed the SQL statement to below to force it sorted by id (which is equivalent to unsorted):
SELECT `m_title`.`id`, `m_title`.`title` FROM `m_title`
WHERE `m_title`.`is_t1`=1 AND `m_title`.`is_active`=1
ORDER BY 1

the drop-down now becomes empty, with a message "No matches found!".

When I changed the SQL statement to the following:
SELECT `m_title`.`id`, `m_title`.`title` FROM `m_title`
WHERE `m_title`.`is_t1`=1 AND `m_title`.`is_active`=1
ORDER BY 2

again, the drop-down becomes empty, with a message "No matches found!".

and if I put a semicolon to terminate the statement, like this:
SELECT `m_title`.`id`, `m_title`.`title` FROM `m_title`
WHERE `m_title`.`is_t1`=1 AND `m_title`.`is_active`=1
ORDER BY 1;

again, the drop-down becomes empty, with a message "No matches found!".

From the above observation, I suspect the bug is, as long as there is ORDER BY in the statement in advanced lookup option, it will cause the LOV to be empty.

And if we don't put any ORDER BY in the statement, it will work, but the list is not unsorted (or sorted by id number). It will somehow sorted by other field, which sometimes is not what we want.

OK, I managed to make it work with this SQL statement:

SELECT `m_title`.`id`,`m_title`.`title` FROM `m_title`
WHERE `m_title`.`is_t1`=1 AND `m_title`.`is_active`=1
ORDER BY `m_title`.`id`


and when generate the files, need to select "Overwrite all".

Maybe helpful:
https://www.mysqltutorial.org/mysql-bas ... ct-random/
https://www.educba.com/mysql-order-by-random/

Hi Jsetzer,

The scenario is like this:

Sample of records created:

Mr.
Ms.
Mrs.
Madam
Mx.
Master
Dr.
Prof.
Capt.
Gen.
Maj.
Col.
LT.
Sgt.
Insp.
Cpl.
.
.
.


When it is sorted by itself:

Capt.
Col.
Cpl.
Dr.
Gen.
Insp.
LT.
Madam
Maj.
Master
Mr.
Mrs.
Ms.
Mx.
Prof.
Sgt.
.
.
.

So sometimes the alphabetically sorted order is not the more preferred order, as the 1st list is preferable.

as long as ORDER BY in the statement is working properly, then we can produce the order as in 1st list, by using the ID field or another field with numbers that can determine the items' position in the list.

and good news is, I already managed to make ORDER BY works.

Just to share another use case and the working SQL statement.

This is the database table:

by default, the dropdown will show like this, which mixed up the desired sequence:

and I have formatted the dropdown to be like this:

by using this code:

I decided to sort it descending as more records belong to younger generation, so I put the frequently selected LOV on top of the list.