Lookup field: date Type

[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 ?

[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

[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

[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

[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 ?

[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

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

[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

[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 ????

[onoehring]

Hi Ron,

then I would suppose it's a bug worth mentioning in the bug-forum (preferable with that new thread-link).
Olaf

[RonP]

Hi Olaf,
Fair enough !
I'll post a new thread

Thank you for your patience and time

Ron

[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