SQL OPTION IN LOOKUP

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
landrea
Posts: 27
Joined: 2018-08-04 13:09

SQL OPTION IN LOOKUP

Post by landrea » 2019-03-20 19:45

I have a lookup field in a table,
this lookup is connected 3 tables

what I want to do is reduce the list if one of these tables already selected a value from the list

I added a field in the lookup table called decrizione_inv and in my idea this field is empty or null if noone of these 3 tables have selected the value from the list

before show the list I think I need to filter using sql, I think is the advanced option in appgini where you can write sql

so I tryed it, but I get a empty list becase I think sql request is not precise

TEST:

SELECT `dt_inventario`.`id_inventario`, `dt_inventario`.`num_inventario`,
`dt_inventario`.`descrizione_inv` FROM `dt_inventario` LEFT JOIN `dt_tip_inventario` as dt_tip_inventario1 ON `dt_tip_inventario1`.`id_tip_inventario`=`dt_inventario`.`categoria_inv` WHERE `dt_inventario`.`num_inventario` IS NULL OR `dt_inventario`.`num_inventario` = '' ORDER BY 2

landrea
Posts: 27
Joined: 2018-08-04 13:09

Re: SQL OPTION IN LOOKUP

Post by landrea » 2019-03-20 19:58

ok, I think i wrong field, i need to look the descritpion,
sorry, the concept wasn't wrong

SELECT `dt_inventario`.`id_inventario`, `dt_inventario`.`num_inventario`,
`dt_inventario`.`descrizione_inv` FROM `dt_inventario` LEFT JOIN `dt_tip_inventario` as dt_tip_inventario1 ON `dt_tip_inventario1`.`id_tip_inventario`=`dt_inventario`.`categoria_inv` WHERE `dt_inventario`.`descrizione_inv` IS NULL OR `dt_inventario`.`descrizione_inv` = '' ORDER BY 2

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: SQL OPTION IN LOOKUP

Post by pbottcher » 2019-03-20 20:40

Hi,

I think in AggGini you will see only the second field in the lookup. (first being the ID, second the value).
Also I'm not sure I understood what you try to search for, or want to have in the lookup. Maybe you can make an example.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

landrea
Posts: 27
Joined: 2018-08-04 13:09

Re: SQL OPTION IN LOOKUP

Post by landrea » 2019-03-21 12:56

the sql simply create this:

when you select a elements from the list and you associate one to these 3 tables (I separated in 3 tables because they have notihng to do each others, but this element), next time the element is not in the list any more, for doing this I simple added another filed where you write manually is associated, immidatly after the association, the users do it, the sql look this field and if the field is empy hide the element from the list

I can do it with hooks, after saving the form I can reach this element and update it, but I changed a little idea and I deleted some hooks I made, because you need to handle lof of events, the case user change something, the case the element is update, so, I change idea and if appllication do something automatically or quite near what I need, with a little manual work for the user, I prefer use these functiuons

I'm going to use hooks more in union and joint of tables, I tested soluction to create custom page

Post Reply