Page 1 of 1
Lookup field: date Type
Posted: 2024-01-04 10:35
by RonP
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 ?
Re: Lookup field: date Type
Posted: 2024-01-04 15:03
by onoehring
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
Re: Lookup field: date Type
Posted: 2024-01-04 18:50
by RonP
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
Re: Lookup field: date Type
Posted: 2024-01-06 13:50
by onoehring
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
Code: Select all
SELECT CAST(YOUR_FIELDNAME AS DATE) AS myDate ORDER BY myDate ASC;
(see:
https://dev.mysql.com/doc/refman/8.0/en ... tions.html and
https://www.w3schools.com/sql/func_mysql_cast.asp )
Olaf
Re: Lookup field: date Type
Posted: 2024-01-06 16:30
by RonP
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.
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:
Aanvragen.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
I think it strange that only the last two rows come with a datevalue
So, You still think it is a bug ?
Re: Lookup field: date Type
Posted: 2024-01-07 18:56
by onoehring
Hi Ron,
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)
Did you run your SQL in phpmyadmin/adminer/console to see the result?
Also: is this JOIN correct?
`aanvragen`.id = `qse`.`datum_aanvraag`
Shouldn't there be a join from ID to
ID_foreign, something like
`aanvragen`.id = `qse`.`id_aanvragen`
Olaf
Re: Lookup field: date Type
Posted: 2024-01-08 11:43
by RonP
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.
Re: Lookup field: date Type
Posted: 2024-01-12 10:13
by onoehring
Hi Ron,
I see.
Still, your SQL in your post (
viewtopic.php?p=22057#p22052 )
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;
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.
Code: Select all
Aanvragen.ID Aanvragen.datum_aanvraag Qse.datum_aanvraag Result
187 17-11-2023 187 NULL
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
Re: Lookup field: date Type
Posted: 2024-01-12 17:34
by RonP
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 ????
Re: Lookup field: date Type
Posted: 2024-01-13 09:34
by onoehring
Hi Ron,
then I would suppose it's a bug worth mentioning in the bug-forum (preferable with that new thread-link).
Olaf
Re: Lookup field: date Type
Posted: 2024-01-13 10:25
by RonP
Hi Olaf,
Fair enough !
I'll post a new thread
Thank you for your patience and time
Ron
Re: Lookup field: date Type
Posted: 2024-01-18 15:00
by onoehring
Hi Ron,
unfortunately I wasn't a big help this time. Still worth looking for a solution. You never stop learning
Take care
Olaf