Page 1 of 1
Need help at: Lookup Advanced
Posted: 2019-06-04 15:00
by onoehring
Hi,
I am trying to do something quite simple:
I want to show 2 columns in a dropdown.
As suggested here (
viewtopic.php?f=2&t=3015&p=9990&hilit=l ... nced#p9991 ) I have created an additional field in my AppGini "Table" named "Vorkommen". This field is integer and empty. The field has no meaning, but to be available in the drop down as second field.
The advanced SQL query is
Code: Select all
Select
ecomo_Location.Bezeichnung,
Count(ecomo_Container.ID_Location) As Vorkommen
From
ecomo_Container Right Join
ecomo_Location On ecomo_Container.ID_Location = ecomo_Location.ID_Location
Group By
ecomo_Location.Bezeichnung
But in the generated application, nothing is shown
Please see images for more context.
What do i need to change in AppGini to simply show the 1st dropdown "Bezeichnung" (i.e. 13-1-1) and in the second dropdown the count of that should be shown as calculated by the query column "Vorkommen" - for 13-1-1 there should be a 0.
- ec1.png (36.69 KiB) Viewed 4673 times
- ec2.png (56.82 KiB) Viewed 4673 times
Olaf
Re: Need help at: Lookup Advanced
Posted: 2019-06-04 15:10
by onoehring
Hi,
one problem seems solved: My query was definetly missing the primary key.
I added the ID_Location to the query and at least the first column (Bezeichnung) is shown right now.
Olaf
Re: Need help at: Lookup Advanced
Posted: 2019-06-04 18:17
by pbottcher
Hi, you can try
select id_location, Bezeichnung from (Select ecomo_Location.id_location, concat_ws(' ',ecomo_Location.Bezeichnung,count(ecomo_Container.ID_Location)) as Bezeichnung from ecomo_Container Right join ecomo_Location on ecomo_Location.ID_Location = ecomo_Container.ID_Location group by ecomo_Location.Bezeichnung) ecomo_Location
use only one field Bezeichnung
Re: Need help at: Lookup Advanced
Posted: 2019-06-05 06:14
by onoehring
Hi pbötcher,
thank you for that suggestion. I did it in a similar way, using "Beschreibung" only, but CONCAT directly:
Code: Select all
Select
ecomo_Location.ID_Location,
CONCAT (ecomo_Location.Bezeichnung, ' (', Count(ecomo_Container.ID_Location), ')')
From
ecomo_Container Right Join
ecomo_Location On ecomo_Container.ID_Location = ecomo_Location.ID_Location
Group By
ecomo_Location.ID_Location,
ecomo_Location.Bezeichnung
Strange, that the other way did not work.
Olaf
Re: Need help at: Lookup Advanced
Posted: 2019-06-05 06:33
by pbottcher
Hi,
good to see that you got it working. Did you test that you can search also for the count(ecomo_Container.ID_Location) value?
If I remember correctly that was an issue. But may work now.
Re: Need help at: Lookup Advanced
Posted: 2019-06-05 06:47
by onoehring
Hi pbötcher,
I am not sure what search you mean.
I posted an annoyance here (
viewtopic.php?f=11&t=3035 ) where it actually seems, that one can not search for something in an "advanced" lookup field.
Did you mean that?
Olaf
Re: Need help at: Lookup Advanced
Posted: 2019-06-05 06:59
by pbottcher
Hi,
in your example posted at the top. if you search for 0 would you see now the entry 13-1-1 (0) ?
Re: Need help at: Lookup Advanced
Posted: 2019-06-05 07:00
by pbottcher
In the other post I would assume you have an sql error happening. If the select drop down does not come back, usually there is an sql happening behind the scene
Re: Need help at: Lookup Advanced
Posted: 2019-06-05 07:23
by onoehring
Hi,
no, that seems not to be the problem - as the SQL is run (and presented in the dropdown) correctly.
SQL is not
Code: Select all
Select
ecomo_Location.ID_Location,
CONCAT (ecomo_Location.Bezeichnung, ' (', Count(ecomo_Container.ID_Location), ')')
From
ecomo_Container Right Join
ecomo_Location On ecomo_Container.ID_Location = ecomo_Location.ID_Location
Group By
ecomo_Location.ID_Location,
ecomo_Location.Bezeichnung
This shows up like you can see in the picture, but searching does not limit the results (as it should be I suppose) once they have all been displayed.
I posted a relevant screencast here (.asf file):
https://dl.olaf-noehring.de/?t=8a940625 ... eed4b4951b
AppGini Screenshot
- ec3.png (42.87 KiB) Viewed 4645 times
Olaf
Re: Need help at: Lookup Advanced
Posted: 2019-06-05 07:32
by pbottcher
Hi could you try my code I posted
Re: Need help at: Lookup Advanced
Posted: 2019-06-05 07:45
by onoehring
Hi,
I copied your code from above
Code: Select all
select id_location, Bezeichnung from (Select ecomo_Location.id_location, concat_ws(' ',ecomo_Location.Bezeichnung,count(ecomo_Container.ID_Location)) as Bezeichnung from ecomo_Container Right join ecomo_Location on ecomo_Location.ID_Location = ecomo_Container.ID_Location group by ecomo_Location.Bezeichnung) ecomo_Location
It DOES work!
Great.
I then changed your code to add brackets around the count
Code: Select all
select id_location, Bezeichnung from (Select ecomo_Location.id_location, concat(ecomo_Location.Bezeichnung,' (',count(ecomo_Container.ID_Location),')') as Bezeichnung from ecomo_Container Right join ecomo_Location on ecomo_Location.ID_Location = ecomo_Container.ID_Location group by ecomo_Location.Bezeichnung) ecomo_Location
and now this works as well.
- ec5.png (5.67 KiB) Viewed 4640 times
Thank you for your time and knowledge.
Olaf