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.
Issue handling LOV sorting for lookup field
Re: Issue handling LOV sorting for lookup field
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".
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".
Re: Issue handling LOV sorting for lookup field
Maybe helpful:
https://www.mysqltutorial.org/mysql-bas ... ct-random/
https://www.educba.com/mysql-order-by-random/
Code: Select all
ORDER BY RAND()
https://www.educba.com/mysql-order-by-random/
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.14 Revision 1665 + all AppGini Helper tools
<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 readabilityAppGini 24.14 Revision 1665 + all AppGini Helper tools
Re: Issue handling LOV sorting for lookup field
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.
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.
Re: Issue handling LOV sorting for lookup field
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.
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.