Issue handling LOV sorting for lookup field

Please report bugs and any annoyances here. Kindly include all possible details: steps to reproduce, expected result, actual result, screenshots, ... etc.
Post Reply
ppfoong
Veteran Member
Posts: 48
Joined: 2021-07-13 16:46

Issue handling LOV sorting for lookup field

Post by ppfoong » 2024-08-18 14:41

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.

ppfoong
Veteran Member
Posts: 48
Joined: 2021-07-13 16:46

Re: Issue handling LOV sorting for lookup field

Post by ppfoong » 2024-08-19 13:11

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".

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1879
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Issue handling LOV sorting for lookup field

Post by jsetzer » 2024-08-19 13:25

Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.14 Revision 1665 + all AppGini Helper tools

ppfoong
Veteran Member
Posts: 48
Joined: 2021-07-13 16:46

Re: Issue handling LOV sorting for lookup field

Post by ppfoong » 2024-08-22 06:48

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.

ppfoong
Veteran Member
Posts: 48
Joined: 2021-07-13 16:46

Re: Issue handling LOV sorting for lookup field

Post by ppfoong » 2024-09-04 12:17

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

This is the database table:
DB_table.png
DB_table.png (24.17 KiB) Viewed 147 times

by default, the dropdown will show like this, which mixed up the desired sequence:
Default.png
Default.png (41.3 KiB) Viewed 147 times

and I have formatted the dropdown to be like this:
Dropdown.png
Dropdown.png (58.75 KiB) Viewed 147 times

by using this code:
Query.png
Query.png (20.45 KiB) Viewed 147 times

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. 8-)

Post Reply