Hello!
I think I found a wrong behaviour on Text Auto Complete Fields.
I want to filter the result set of a Text Auto Complete Field by using a NOT IN statement, like this:
SELECT V.VENCODIGO, V.VENNOMBRE
FROM fac_vendedores V WHERE V.VENACTIVO=1 AND V.VENVISITA=1
AND V.VENCODIGO NOT IN (SELECT A.VENCODIGO FROM VIMVISXMED A WHERE A.VXMACTIVO = 1 AND A.MEDCODIGO=1)
ORDER BY V.VENNOMBRE
This returns no results in the generated form, but it does if I run this query directly on database. I changed scriptcase data type to Select, and it also works, it returns the correct rows.
I figured out that the problem is in the NOT IN statement. If I delete that part, the query works on the Text Auto Complete field in the generated form, but it’s not the query I want to execute.
SELECT V.VENCODIGO, V.VENNOMBRE
FROM fac_vendedores V WHERE V.VENACTIVO=1 AND V.VENVISITA=1
ORDER BY V.VENNOMBRE
Help please!