Need help at: Lookup Advanced

[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 3236 times

ec2.png (56.82 KiB) Viewed 3236 times

Olaf

[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

[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

[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

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

[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

[pbottcher]

Hi,

in your example posted at the top. if you search for 0 would you see now the entry 13-1-1 (0) ?

[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

[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 3208 times

Olaf

[pbottcher]

Hi could you try my code I posted

[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 3203 times

Thank you for your time and knowledge.
Olaf