Order by date if lookup field

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
fgazza
Veteran Member
Posts: 122
Joined: 2019-04-30 17:37

Order by date if lookup field

Post by fgazza » 2019-05-03 02:17

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

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 295
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Order by date if lookup field

Post by jsetzer » 2019-05-03 04:02

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

fgazza
Veteran Member
Posts: 122
Joined: 2019-04-30 17:37

Re: Order by date if lookup field

Post by fgazza » 2019-05-05 16:15

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.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 295
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Order by date if lookup field

Post by jsetzer » 2019-05-07 13:10

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

fgazza
Veteran Member
Posts: 122
Joined: 2019-04-30 17:37

Re: Order by date if lookup field

Post by fgazza » 2019-05-07 19:54

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!

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 659
Joined: 2018-04-01 10:12

Re: Order by date if lookup field

Post by pböttcher » 2019-05-07 20:26

`Hi,

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

Code: Select all

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

               $options->QueryOrder="order by `events`.`result_events` desc";

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

fgazza
Veteran Member
Posts: 122
Joined: 2019-04-30 17:37

Re: Order by date if lookup field

Post by fgazza » 2019-05-08 10:13

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.

fgazza
Veteran Member
Posts: 122
Joined: 2019-04-30 17:37

Re: Order by date if lookup field

Post by fgazza » 2019-05-08 10:32

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'

sjohn
Veteran Member
Posts: 85
Joined: 2018-05-23 09:32

Re: Order by date if lookup field

Post by sjohn » 2019-05-08 11:02

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

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 295
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Order by date if lookup field

Post by jsetzer » 2019-05-08 11:05

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

fgazza
Veteran Member
Posts: 122
Joined: 2019-04-30 17:37

Re: Order by date if lookup field

Post by fgazza » 2019-05-08 13:05

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!

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 295
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Order by date if lookup field

Post by jsetzer » 2019-05-08 13:12

You already got two solutions ;) :D

fgazza
Veteran Member
Posts: 122
Joined: 2019-04-30 17:37

Re: Order by date if lookup field

Post by fgazza » 2019-05-08 13:56

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!

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 659
Joined: 2018-04-01 10:12

Re: Order by date if lookup field

Post by pböttcher » 2019-05-08 14:45

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.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 295
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Order by date if lookup field

Post by jsetzer » 2019-05-08 15:25

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

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

fgazza
Veteran Member
Posts: 122
Joined: 2019-04-30 17:37

Re: Order by date if lookup field

Post by fgazza » 2019-05-08 16:36

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
002_child_table.jpg
002_child_table.jpg (99.35 KiB) Viewed 1180 times
001_main_table.jpg
001_main_table.jpg (61.45 KiB) Viewed 1180 times

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 659
Joined: 2018-04-01 10:12

Re: Order by date if lookup field

Post by pböttcher » 2019-05-08 17:50

Hi,

ok, no worries to all :-)

please try

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

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

}

fgazza
Veteran Member
Posts: 122
Joined: 2019-04-30 17:37

Re: Order by date if lookup field

Post by fgazza » 2019-05-08 20:22

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!!!

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 659
Joined: 2018-04-01 10:12

Re: Order by date if lookup field

Post by pböttcher » 2019-05-08 21:05

Hi,

hmmm, what is the error message that you get.

I tried it on my testsytem and it works fine.

can you try

Code: Select all

$options->QueryOrder="order by `appointamenti`.`data_appuntamento` desc";
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.

fgazza
Veteran Member
Posts: 122
Joined: 2019-04-30 17:37

Re: Order by date if lookup field

Post by fgazza » 2019-05-09 05:01

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!

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 659
Joined: 2018-04-01 10:12

Re: Order by date if lookup field

Post by pböttcher » 2019-05-09 05:22

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 :-) ).

fgazza
Veteran Member
Posts: 122
Joined: 2019-04-30 17:37

Re: Order by date if lookup field

Post by fgazza » 2019-05-09 05:39

Immagine 001.jpg
Immagine 001.jpg (23.46 KiB) Viewed 1153 times
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.

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 659
Joined: 2018-04-01 10:12

Re: Order by date if lookup field

Post by pböttcher » 2019-05-09 06:29

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";

fgazza
Veteran Member
Posts: 122
Joined: 2019-04-30 17:37

Re: Order by date if lookup field

Post by fgazza » 2019-05-09 10:57

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

Post Reply