Using Omit in a search

[drookwood]

In filemaker it is possible to use the omit function within a scripted find as a second find request i.e. find all contacts living in London but omit those with the surname Peters.

How do I do this in Servoy?

For what I have in mind, using ! to locate records not equal to a value is not quite the same. I want to find all contacts linked to a certain site and omit those linked to a certain work task. The trouble is that contacts will be linked to multiple work tasks and therefore using ! to find those contacts NOT linked say to taskID 4 will still find contacts linked to taskID 4 if they are also linked say to taskID 5. The option to omit those contacts linked to taskID 4 should in theory solve the problem of removing any linked to taskID4 even if they are also linked to other tasks.

[IT2Be]

The answer lies in my answer to your earlier post http://forum.servoy.com/viewtopic.php?t=1058.

[drookwood]

The answer lies in my answer to your earlier post http://forum.servoy.com/viewtopic.php?t=1058.

I don't follow - previous response was in relation to finding ranges. I do not need to find a range as this will still include the contacts I want to exclude if they are also linked to another taskID which is included in the range/s on either side of the specific taskID I am trying to omit.

[IT2Be]

OK, but by using a newRecord command you can do multipe finds...

[drookwood]

Still doesn't do it.

Consider this:

contact A is related to task 1
contact A is related to task 2
contact B is related to task 1

If I find all contacts related to task 1 I will get both A and B, even if I create a second find request to find contacts NOT related to 2, because contact A is related to both 1 and 2. I need to be able to omit contacts related to 2 (even if they are related to other tasks).

Even a single request to find contacts NOT related to task 2 will still find contact A because he IS related to task 1!

In Filemaker this was simple.

[IT2Be]

I read that you can use AND and OR searches (did not test them though). Would that solve your issue?

[drookwood]

I read that you can use AND and OR searches (did not test them though). Would that solve your issue?

Sadly no - the basic problem remains! I still can't specifically omit all contacts related to task 1 if they are also related to another task. Impasse at the moment but thanks for the suggestions.

[chris]

I have the same need as drookwood - to allow users to omit records in the master table that are related to certain records in the child table.

In FM this is a breeze with the 'omit' button. I can't find a way to do it in servoy with the '!' criteria.

I'm uploading a (shoddy) example solution that demonstrates the probloem. If you go to the People form and
1) click CTRL-F to enter find mode
2) type 'Exec' in the yellow sal_code field
3) CTRL-N to create new rqst
4) type '!Reg' in yellow sal_code field
5) F3 to perform search

It finds Ann, David, and Bob, even though Ann does not have a related record with 'Reg' in the sal_code field. I'm sure this is behaving as intended (it's finding people who have 'Exec' and people who don't have 'Reg', and thus finds Ann) - but there seems to be no way to exclude people who have 'Reg' as there is in FM by simply clicking 'omit.'

Also notice that when you scroll between multiple requests, criteria entered in related fields disappears (noted in bugs forum).

Thank you.

[chris]

Still hoping for an answer on this one. Any progress? Is the problem clear?

Thank you.

[Jan Blok]

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:

Code: Select all

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')

[chris]

Thank you ! That is looking really good.

Will this replace the current method of using SQL to retrieve data in a form (load dataset)?

Looking forward to this and to the documentation that goes with it. Thanks for the response.

[Jan Blok]

Will this replace the current method of using SQL to retrieve data in a form (load dataset)?

No, thats still possible but of less use since we have this. (controller.loadRecords(dataset) has a limit from about ~200 rows, because of the SQL IN query being generated)