Hi,
I've a sorting problem with a lookup field date Format.
The value of the "parent table field" is correct displayed in the table view of the desired table
However if I sort on that field, it is sorted as an integer value instead of an date value.
So if there are a number records with the same day-value but different month values, all days are sorted sequentially instead of sorted like the date sequence
Is here a solution for ?
Lookup field: date Type
Lookup field: date Type
- Attachments
-
- 01_lookup-autofill-datefield.jpg (103.77 KiB) Viewed 2649 times
-
- 02_result_lookup_wrong_sort.jpg (42.21 KiB) Viewed 2649 times
Re: Lookup field: date Type
Hi Ron,
strange and seems to be like a bug.
I did test on a lookup fields that shows text, but is linked to the ID of a foreign table that is integer. This "text"-lookup is sorted as expected.
Maybe the problem is because you have used the AUTO-FILL? Does it work when you remove that checkbox (for testing only)?
But: Are you sure you have ONLY the DATUM AANVRAAG field as sort criteria? This could be a reason why the sorting seems wrong (but indeed is correct).
Anyways, I would think you should mention this as a bug as the field should be sorted by what is shown ... if there is no other criteria .
Olaf
strange and seems to be like a bug.
I did test on a lookup fields that shows text, but is linked to the ID of a foreign table that is integer. This "text"-lookup is sorted as expected.
Maybe the problem is because you have used the AUTO-FILL? Does it work when you remove that checkbox (for testing only)?
But: Are you sure you have ONLY the DATUM AANVRAAG field as sort criteria? This could be a reason why the sorting seems wrong (but indeed is correct).
Anyways, I would think you should mention this as a bug as the field should be sorted by what is shown ... if there is no other criteria .
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: Lookup field: date Type
Hi Olaf,
Thank you for your reply and suggestion.
However If I don't use the AUTO-FILL the a picklist of dates is shown, that is not what I to present.
My AppGini Version is: 23.17 Build 1557
Thank you for your reply and suggestion.
However If I don't use the AUTO-FILL the a picklist of dates is shown, that is not what I to present.
My AppGini Version is: 23.17 Build 1557
- Attachments
-
- 03_result_lookup_wrong_sort_Auto_fill_not_selected.jpg (33.74 KiB) Viewed 2607 times
Re: Lookup field: date Type
Hi Ron,
then your first image is not matching your request I believe? There the Auto-Fill-checkbox IS checked. And I think if it would be checked, the user would be unable to select from the dropdown (as it's autofilled once some other value from the parent table has been selected in another lookup).
Can you reformulate the SQL and sort by date your self? This should work ... maybe ... also, maybe you will need to CAST the field das DATE in the SQL when you sort
Something like
(see: https://dev.mysql.com/doc/refman/8.0/en ... tions.html and https://www.w3schools.com/sql/func_mysql_cast.asp )
Olaf
then your first image is not matching your request I believe? There the Auto-Fill-checkbox IS checked. And I think if it would be checked, the user would be unable to select from the dropdown (as it's autofilled once some other value from the parent table has been selected in another lookup).
Can you reformulate the SQL and sort by date your self? This should work ... maybe ... also, maybe you will need to CAST the field das DATE in the SQL when you sort
Something like
Code: Select all
SELECT CAST(YOUR_FIELDNAME AS DATE) AS myDate ORDER BY myDate ASC;
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: Lookup field: date Type
Hi Olaf,
The first picture is what I want to have. Take over the date from table "aanvragen".
If I try your SQL (thank you), then I don't get the wanted result.
Gives a result:
So, You still think it is a bug ?
The first picture is what I want to have. Take over the date from table "aanvragen".
If I try your SQL (thank you), then I don't get the wanted result.
Select `aanvragen`.`Datum_aangevraagd`,`qse`.`datum_aanvraag`, CAST(`qse`.`datum_aanvraag` AS DATE) as mydate from `qse`
JOIN `aanvragen`
on `aanvragen`.id = `qse`.`datum_aanvraag`
order BY mydate;
Gives a result:
I think it strange that only the last two rows come with a datevalueAanvragen.ID Aanvragen.datum_aanvraag Qse.datum_aanvraag Result
187 17-11-2023 187 NULL
188 17-11-2023 188 NULL
189 3-11-2023 189 NULL
176 18-9-2023 176 NULL
177 19-9-2023 177 NULL
178 19-9-2023 178 NULL
190 15-12-2023 190 NULL
104 17-8-2022 104 4-1-2000
105 23-9-2023 105 5-1-2000
So, You still think it is a bug ?
Re: Lookup field: date Type
Hi Ron,
your SELECT should not pull the ID - as one can see in your output.
It seems to me the output shows
Did you run your SQL in phpmyadmin/adminer/console to see the result?
Also: is this JOIN correct?
your SELECT should not pull the ID - as one can see in your output.
It seems to me the output shows
Code: Select all
ID;date A;ID (again);date B (some other date)
Also: is this JOIN correct?
Shouldn't there be a join from ID to ID_foreign, something like`aanvragen`.id = `qse`.`datum_aanvraag`
Olaf`aanvragen`.id = `qse`.`id_aanvragen`
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: Lookup field: date Type
Olaf,
According to me the SQL is correct.
Table `qse` links to table `aanvragen' based on `aanvragen`.'Datum_aangevraagd`
The point is not that the presented `datum_aanvraag` in qse_view.php is wrong, that is correct pulled from table `aanvragen`
The problem is that the sort goes wrong.
According to me the SQL is correct.
Table `qse` links to table `aanvragen' based on `aanvragen`.'Datum_aangevraagd`
The point is not that the presented `datum_aanvraag` in qse_view.php is wrong, that is correct pulled from table `aanvragen`
The problem is that the sort goes wrong.
- Attachments
-
- 04_tabledefinitions.jpg (116.64 KiB) Viewed 2346 times
Re: Lookup field: date Type
Hi Ron,
I see.
Still, your SQL in your post ( viewtopic.php?p=22057#p22052 )
If you like, drop me a link to the AP file and I will try to reproduce the error to come up with a solution.
Olaf
I see.
Still, your SQL in your post ( viewtopic.php?p=22057#p22052 )
should show only 3 (three) columns, but in the results you post, there seem to be 4 (four) columns ... thus, something seems not right to me here.Select `aanvragen`.`Datum_aangevraagd`,`qse`.`datum_aanvraag`, CAST(`qse`.`datum_aanvraag` AS DATE) as mydate from `qse`
JOIN `aanvragen`
on `aanvragen`.id = `qse`.`datum_aanvraag`
order BY mydate;
Code: Select all
Aanvragen.ID Aanvragen.datum_aanvraag Qse.datum_aanvraag Result
187 17-11-2023 187 NULL
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: Lookup field: date Type
Olaf,
I've found this theread:
viewtopic.php?f=7&t=2993&p=9913&hilit=% ... d%27#p9913
I'll try this first.
However, it was posted in 2018, so a common issue, time for a good solution ????
I've found this theread:
viewtopic.php?f=7&t=2993&p=9913&hilit=% ... d%27#p9913
I'll try this first.
However, it was posted in 2018, so a common issue, time for a good solution ????
Re: Lookup field: date Type
Hi Ron,
then I would suppose it's a bug worth mentioning in the bug-forum (preferable with that new thread-link).
Olaf
then I would suppose it's a bug worth mentioning in the bug-forum (preferable with that new thread-link).
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: Lookup field: date Type
Hi Olaf,
Fair enough !
I'll post a new thread
Thank you for your patience and time
Ron
Fair enough !
I'll post a new thread
Thank you for your patience and time
Ron
Re: Lookup field: date Type
Hi Ron,
unfortunately I wasn't a big help this time. Still worth looking for a solution. You never stop learning
Take care
Olaf
unfortunately I wasn't a big help this time. Still worth looking for a solution. You never stop learning
Take care
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view