AppGini Community Forums

Goodmorning everyone.
I would like your help to solve this problem: My version of apps is 5.70. I have a table named "result of events" with a lookup field "data" (set in "data" format) which takes the data from the "event_date" field of the "events" table. In appgini I set the default sorting of the "result_events" table so that the records are sorted according to the "date" field in descending order. Unfortunately the data is badly ordered In fact the dates are sorted but in alphabetical order: and therefore: 31/10/2018 30/11/2018 01/29/2019 01/28/2018 and so on.
How can I sort dates correctly in the table view?
Thank you!
Fabiano Gazza

Good morning, Fabiano,

can you please check the datatype of the date column. If this is 'date' sorting should be fine. If this is varchar you should change it to 'date'.

Hope this helps!
Regards
Jan

Hi and thank you for your reply. The "event_date" field in the source table is set to the date format while the lookup field in the child table (field named "data" is set to varchar because appgin automatically sets the type of field in the same format as the primary key of the source table and I understand that this setting can't be change. In phpmyadmin the "data" column of the lookup field contains the integer corresponding to the primary key of the records in the source table. In any case, the result in appgini web app is that the "data" field is sorted alphabetically.

I have though about and tried different approaches. No easy solution at the moment.

But you can check out the following workaround:

1. Add an additional field with datatype = datetime and the data format according to you local settings
2. Set the additional field to "[X] Hide in detail view"
3. If you like set the original lookup field to "[X] Hide in table view"
4. Save and re-generate your app
5. In your hooks code files TABLENAME.php:
   After insert and after update, update your database table and put the correct datetime into the additional field using PHP and SQL

Now you should see the extra column in table view. You will be able to sort by datetime now.

Hope this helps.
Regards,
Jan

AppGini 5.75.1056

Hi and thank you very much for the reply. Unfortunately I'm not able to program with mysql and php. Could you suggest me the code to insert? Thank you!

`Hi,

you can try to set the order in the hooks/result_events.php

where events is the table for the lookup and result_events is the field (hope I got that right).

Or if you have some code in the init function, just add the line.

hello and thanks for the help. Sorry but I can't understand the relationship between your first suggestion in which you suggested:
"Add an additional field with datatype = datetime and data format according to you local settings
Set the additional field to "[X] Hide in detail view"
If you like the original lookup field to "[X] Hide in table view"
Save and re-generate your app
In your hooks code files TABLENAME.php:
After insert and after update, update your database using PHP and SQL
Now you should see the extra column in table view. You will be able to sort by datetime now. "
... and the second suggestion containing the code
$ options-> QueryOrder = "order by` events`.`result_events` desc ";
Are the two suggestions related to each other or does one exclude the other?
Thanks for the help and sorry for my incompetence.

however I tried to insert the following function in my table:

function consuntivo_eventi_incontri_init (& $ options, $ memberInfo, & $ args) {

$ options-> QueryOrder = "order by` appointments`.`data_evento `desc";

return TRUE;
}

the "appointments" table is the source table. the "event_date" field is the lookup field that points to the "date_appointment" field of the "appointments" table.

The page gives me this error:

Unknown column 'appointamenti.data_evento' in 'order clause'

@fgazza
just a question : Would it be possible for you to use the date format as : yyyy-mm-dd.
With this format, it should give the same result if sorting is in date or alphabetical order.

Please check the posts' authors. The 2nd proposal is not mine - and I'm afraid the 2nd will not work on lookup fields.

Regards,
Jan

Hello sjohn. Setting the date in yyyy-mm-dd format for all the tables in my application would be a problem because my (Italian) users are not experts and are used to the dd / mm / yyyy format.
I hope someone can suggest another solution.
Thank you!

You already got two solutions

Hi jsetzer
I'm sorry, but I can't fully understand your suggestion. I understand that I should create a new "data" field and set it in the dd / mm / yyyy format. I did this but if I didn't understand it this field should automatically fill with the date in the source table.
If so, I need help writing the code to put in the hook file. Thank you so much!

Hi,
not sure why you post something like
@Jan:

The 2nd proposal is not mine - and I'm afraid the 2nd will not work on lookup fields.

I tested it and it works just fine!

@fgazza
You post

from the "event_date" field of the "events" table

then you update the query with

$ options-> QueryOrder = "order by` appointments`.`data_evento `desc";

and copied wrong spaces and different table/fieldnames

those entries do not match, so it is impossible to provide further information unless you provide the table structure and the lookup-reference.

I am sorry if my assumption was wrong. I did not want to annoy anyone, sorry if I did!

I agree with pböttcher: we all could help better, if we knew the tablenames, column-names and foreign keys. Sometimes a screenshot would be helpful.

If it works, pböttcher's "order by" solution is the shortest and simplest. You should definately give it a try!

Once again sorry for my premature assumption and too difficult alternative idea.

Kind Regards,
Jan

Hi everyone.
I apologize if I have confused the translation of fields and tables. I try to fix it:

I have a main "appuntamenti" table with a "data_appuntamento" field (set with the date format).
I have a child table called "consuntivo_eventi_incontri" with a "data_evento" field which is a lookup field that points to the "data_appuntamento" field in the "appointments" table.
In appgini I have set the sorting order of the "consuntivo_eventi_incontri" table by date descendigly.

As already mentioned, the problem is that the date that appears in the table view of the consuntivo_eventi_incontri table is sorted alphabetically and not in the date format.

As already specified for me it is important that the date format remains set as dd / mm / yyyy.
Thank you for all the help you are giving me.

Here i attach two screenshot

Hi,

ok, no worries to all

please try

function consuntivo_eventi_incontri_init(&$options, $memberInfo, &$args){

$options->QueryOrder="order by appointments.data_appuntamento desc";

}

Hi @pböttcher and thank you for you suggestion!

I tried to enter the code you suggested. Initially it did not work (an error message appeared). I added two apostrophes transforming the code as follows:

$ options-> QueryOrder = "order by 'appointamenti.data_appuntamento' desc";

In this way things work better, that is, records are sorted correctly by date but in ascending and not descending order. So it seems that the "desc" parameter does not work.

Any idea?

Thank you!!!

Hi,

hmmm, what is the error message that you get.

I tried it on my testsytem and it works fine.

can you try
And please make sure to copy the exact code you use into the post.

As in your post

$ options-> QueryOrder = "order by 'appointamenti.data_appuntamento' desc";

would cause an error message as there is an extra space between $ and options.

hello,

$options->QueryOrder="order by 'appuntamenti'.'data_appuntamento' desc"; don't work!

$options->QueryOrder="order by 'appuntamenti.data_appuntamento' desc"; work but no descendent order (the record stay in ascending order!)

Hope there is a way to solve.

Thanks!

Hi,

can you post the result if you put in

$options->QueryOrder="order by appuntamenti.data_appuntamento desc";

If there is an error, please post the error (screenshot help a lot ).

here is the screenshot

And below the entire text associated to the error:

Query:
SELECT `consuntivo_eventi_incontri`.`pri_key` as `pri_key`, IF( CHAR_LENGTH(if(`appuntamenti1`.`data_appuntamento`,date_format(`appuntamenti1`.`data_appuntamento`,'%d/%m/%Y'),'')) || CHAR_LENGTH(`appuntamenti1`.`titolo`), CONCAT_WS('', if(`appuntamenti1`.`data_appuntamento`,date_format(`appuntamenti1`.`data_appuntamento`,'%d/%m/%Y'),''), ' ', `appuntamenti1`.`titolo`), '') /* Evento - Attivita */ as `evento_attivita`, IF( CHAR_LENGTH(`azioni_progetto1`.`n_attivita`) || CHAR_LENGTH(`azioni_progetto1`.`azione`), CONCAT_WS('', `azioni_progetto1`.`n_attivita`, ' ', `azioni_progetto1`.`azione`), '') /* Azione di rif */ as `azione_di_rif`, IF( CHAR_LENGTH(`appuntamenti1`.`tipologia_incontro`), CONCAT_WS('', `appuntamenti1`.`tipologia_incontro`), '') /* Tipologia incontro */ as `tipo_incontro`, IF( CHAR_LENGTH(`appuntamenti1`.`ente_proponente`), CONCAT_WS('', `appuntamenti1`.`ente_proponente`), '') /* Organizzato da */ as `organizzato_da`, IF( CHAR_LENGTH(`appuntamenti1`.`luogo`), CONCAT_WS('', `appuntamenti1`.`luogo`), '') /* Luogo evento */ as `luogo_evento`, IF( CHAR_LENGTH(if(`appuntamenti1`.`data_appuntamento`,date_format(`appuntamenti1`.`data_appuntamento`,'%d/%m/%Y'),'')), CONCAT_WS('', if(`appuntamenti1`.`data_appuntamento`,date_format(`appuntamenti1`.`data_appuntamento`,'%d/%m/%Y'),'')), '') /* Data evento */ as `data_evento`, if(`consuntivo_eventi_incontri`.`data_evento_copy`,date_format(`consuntivo_eventi_incontri`.`data_evento_copy`,'%d/%m/%Y'),'') as `data_evento_copy`, IF( CHAR_LENGTH(`appuntamenti1`.`ora_inizio`) || CHAR_LENGTH(`appuntamenti1`.`ora_fine`), CONCAT_WS('', `appuntamenti1`.`ora_inizio`, '-', `appuntamenti1`.`ora_fine`), '') /* Orario */ as `orario`, `consuntivo_eventi_incontri`.`enti_operatori_coinvolti` as `enti_operatori_coinvolti`, `consuntivo_eventi_incontri`.`enti_altro_personale_coinvolti` as `enti_altro_personale_coinvolti`, `consuntivo_eventi_incontri`.`altri_operatori_volontari` as `altri_operatori_volontari`, `consuntivo_eventi_incontri`.`etich_ore` as `etich_ore`, `consuntivo_eventi_incontri`.`ore_preparazione` as `ore_preparazione`, `consuntivo_eventi_incontri`.`ore_svolgimento` as `ore_svolgimento`, `consuntivo_eventi_incontri`.`ore_rendicontazione` as `ore_rendicontazione`, `consuntivo_eventi_incontri`.`ore_disallestimento` as `ore_disallestimento`, `consuntivo_eventi_incontri`.`etich_soggetti` as `etich_soggetti`, `consuntivo_eventi_incontri`.`nuclei_familiari` as `nuclei_familiari`, `consuntivo_eventi_incontri`.`genitori` as `genitori`, `consuntivo_eventi_incontri`.`minorenni` as `minorenni`, `consuntivo_eventi_incontri`.`insegnanti` as `insegnanti`, `consuntivo_eventi_incontri`.`altri_sogg_comunita_edu` as `altri_sogg_comunita_edu`, `consuntivo_eventi_incontri`.`altro_specif_tipo` as `altro_specif_tipo`, `consuntivo_eventi_incontri`.`altro_specif_n` as `altro_specif_n`, `consuntivo_eventi_incontri`.`dettaglio_evento` as `dettaglio_evento`, `consuntivo_eventi_incontri`.`allega_foglio_firme` as `allega_foglio_firme`, `consuntivo_eventi_incontri`.`allega_verbale` as `allega_verbale`, `consuntivo_eventi_incontri`.`foto_link` as `foto_link`, `consuntivo_eventi_incontri`.`pri_key` as 'consuntivo_eventi_incontri.pri_key' from `consuntivo_eventi_incontri` LEFT JOIN `appuntamenti` as appuntamenti1 ON `appuntamenti1`.`pri_key`=`consuntivo_eventi_incontri`.`evento_attivita` LEFT JOIN `azioni_progetto` as azioni_progetto1 ON `azioni_progetto1`.`prikey`=`appuntamenti1`.`azione_di_progetto` order by appuntamenti.data_appuntamento desc limit 0,2000

Thank you.

Hi,

as you can see in your SQL you have an alias for the appuntamenti` table. Hence you need to reference the alias

appuntamenti` as appuntamenti1

so please try

$options->QueryOrder="order by appuntamenti1.data_appuntamento desc";

GREAT!!!!
IT WORK!!!
Thank you so much!!!
Fabiano

Hello, I'm pretty sure I followed the script posted on this thread, however, I don't see any changes on my project.

So I have a "Cherry" table with "ManuDate" fieldname, and then I have "Micro" table with "ProdDate" fieldname. My Micro.Proddate is set as lookup field to Cherry.Manudate. I did try the solution script above and it didn't work to mine. Any suggestions?