Help with lookup field in appgini project (advanced query)

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
fgazza
Veteran Member
Posts: 205
Joined: 2019-04-30 17:37

Help with lookup field in appgini project (advanced query)

Post by fgazza » 2020-10-20 17:42

Hi. I have a table "budget" with a lookup field named "project".
I am trying to customize the query in the advanced functions of the appgini lookup field so that in the "project" field of the "budget" table appear two concatenated fields of the "projects" table, ie the "project" and "start_conclusion" fields, limited to records of the "projects" table in which the "active" field has the value "YES".

I tried with this custom query but it doesn't work:

SELECT `projects`.`pkey`, `projects`.`project`, `projects`.`start_conclusion` CONCAT_WS('', `projects`.`project`, `projects`.`start_conclusion`) FROM `projects` WHERE `projects`.`active` = 'YES' ORDER BY `project`


571/5000
Help with lookup field in appgini project.

Hi. I have a table "budget" with a lookup field named "project".
I am trying to customize the query in the advanced functions of the appgini lookup field so that in the "project" field of the "budget" table appear two concatenated fields of the "projects" table, ie the "project" and "start_conclusion" fields, limited to records of the "projects" table in which the "active" field has the value "YES".

I tried with this custom query but it doesn't work:

Can anyone help me figure out where am I wrong?

Thank you so much!

Fabiano

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

Re: Help with lookup field in appgini project (advanced query)

Post by jsetzer » 2020-10-20 18:47

SELECT `projects`.`pkey`, `projects`.`project`, `projects`.`start_conclusion` CONCAT_WS('', `projects`.`project`, `projects`.`start_conclusion`) FROM `projects` WHERE `projects`.`active` = 'YES' ORDER BY `project`
  • As far as I know the advanced query should return two values: the primary key and the display text.
  • Your query retrns more than two fields and
  • I think there is a comma "," missing before concat_ws()
  • Additionally I think there is a mistake with quotation in first parameter of concat_ws. It should be ',' but it looks like '',
Maybe this is what you want (not tested):

Code: Select all

SELECT `pkey`, CONCAT_WS(',', `project`, `start_conclusion`) FROM `projects` WHERE `active` = 'YES' ORDER BY `project`
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

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

Re: Help with lookup field in appgini project (advanced query)

Post by fgazza » 2020-10-20 19:06

thank you so much Jan!
Finally I solved it restoring the default query and simpy adding the WHERE condition.

My query now work and it's:

Code: Select all

SELECT `projects`.`pkey`, IF(CHAR_LENGTH(`projects`.`project`) || CHAR_LENGTH(`projects`.`start_conclusion`), CONCAT_WS('', `projects`.`project`, ' - ', `projects`.`start_conclusion`), '') FROM `projects` WHERE `projects`.`active` = 'YES' ORDER BY 2
Of course your code seems to be more simple than my so i will try it... really i don't understand the "IF(CHAR_LENGTH" and "|| CHAR_LENGTH" code in the default appgini query.

Thank you so much!

Fabiano

Post Reply