AppGini Community Forums

Hello, I try to explain my problem.

I have a table (codes) like this:

ID_code, COD, price
--------------------------------
1, A, 10
2, B, 20
3, A, 11

Now, I have a table (rows) like this

ID_row, COD2, Qty

Where COD2 is ID_code

In Appgini I created a look up field on rows.COD2 linked with parent codes.ID_Code

Now the problem is when I try to show (on drop down field) just the LAST ID_Code:

I can show just the last ID_Code by using Advanced feature on look up field in appgini but......the search on drop down DON'T WORK ANY MORE.

To filter just the last ID_Code I have tried many sql variant like:

SELECT a.ID_code, a.COD
FROM codes a
LEFT OUTER JOIN codes b
ON a.COD = b.COD
AND a.ID_Code < b.ID_CODE
WHERE b.COD IS NULL

or

SELECT a.ID_code, a.COD
FROM codes T1
WHERE ID_Code = (
SELECT max(ID_Code)
FROM codes T2
WHERE T1.COD=T2.COD
)

The result is always the same. I see the last values.....but the search functions does not work.
Without Advanced query, search is ok....

http://178.156.224.229/vuoti/1.png With Advanced... still search!!

http://178.156.224.229/vuoti/2.png Without Advanced... everything ok

Any suggestion?

I have also created a view based on codes table that show just last values for ID_code.

And after I tried to apply SELECT on that view in Advanced setting for look up field, leaving the original table as parent table, because of historical values....

No way. In the front end, searching on drop down field, still searching for ever.....

Of course with default query on Advanced , everything goes excelent, but I see duplicates for values, not just the last.

Solved!
Using right join on view.

Now dropdown field is searchable.