The solution to the problem you describing is an SQL query which uses NOT EXIST sub query, changing the Servoy SQL generator to be able to generate this is quite some work, so we decided to provide “Form by query” like:
controller.loadRecords("select products.productid from products where products.supplierid = 2 and products.unitprice < 10 order by products.productid");
And put the change for the SQL generator on the planning.
controller.loadRecords(SQLSelect) will be possible in Servoy 2.0 rc5
limitations/requirements for SQLSelect are:
-must start with ‘select’
-must contain ‘from’ and ‘order by’ keywords
-must at least select from the table used in Servoy Form
-cannot contain ‘group by’ or ‘having’
-all columns must be fully qualified like ‘orders.order_id’
-the selected columns must be the (Servoy Form) table primarykey columns (alphabetically ordered like ‘select a_id, b_id,c_id’)