Using Omit in a search

Questions, tips and tricks and techniques for scripting in Servoy

Using Omit in a search

Postby drookwood » Thu Jan 15, 2004 12:04 pm

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.
drookwood
 
Posts: 290
Joined: Tue Nov 11, 2003 7:08 pm
Location: Somerset, UK

Postby IT2Be » Thu Jan 15, 2004 12:20 pm

The answer lies in my answer to your earlier post http://forum.servoy.com/viewtopic.php?t=1058.
Marcel J.G. Trapman (IT2BE)
SAN partner - Freelance Java and Servoy
Servoy Components - IT2BE Plug-ins and Beans for Servoy
ServoyForge - Open Source Components for Servoy
User avatar
IT2Be
Servoy Expert
 
Posts: 4766
Joined: Tue Oct 14, 2003 7:09 pm
Location: Germany

Postby drookwood » Thu Jan 15, 2004 12:29 pm

IT2BE wrote: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.
drookwood
 
Posts: 290
Joined: Tue Nov 11, 2003 7:08 pm
Location: Somerset, UK

Postby IT2Be » Thu Jan 15, 2004 12:36 pm

OK, but by using a newRecord command you can do multipe finds...
Marcel J.G. Trapman (IT2BE)
SAN partner - Freelance Java and Servoy
Servoy Components - IT2BE Plug-ins and Beans for Servoy
ServoyForge - Open Source Components for Servoy
User avatar
IT2Be
Servoy Expert
 
Posts: 4766
Joined: Tue Oct 14, 2003 7:09 pm
Location: Germany

Postby drookwood » Thu Jan 15, 2004 1:04 pm

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.
drookwood
 
Posts: 290
Joined: Tue Nov 11, 2003 7:08 pm
Location: Somerset, UK

Postby IT2Be » Thu Jan 15, 2004 3:04 pm

I read that you can use AND and OR searches (did not test them though). Would that solve your issue?
Marcel J.G. Trapman (IT2BE)
SAN partner - Freelance Java and Servoy
Servoy Components - IT2BE Plug-ins and Beans for Servoy
ServoyForge - Open Source Components for Servoy
User avatar
IT2Be
Servoy Expert
 
Posts: 4766
Joined: Tue Oct 14, 2003 7:09 pm
Location: Germany

Postby drookwood » Wed Jan 21, 2004 10:12 pm

IT2BE wrote: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.
drookwood
 
Posts: 290
Joined: Tue Nov 11, 2003 7:08 pm
Location: Somerset, UK

Example solution demonstrating 'omit' problem in searching

Postby chris » Mon Jan 26, 2004 3:59 am

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.
Attachments
Find_Omit_Problem.servoy
(5.46 KiB) Downloaded 504 times
chris
 
Posts: 82
Joined: Wed Dec 31, 2003 8:24 pm

Postby chris » Fri Jan 30, 2004 9:29 am

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

Thank you.
chris
 
Posts: 82
Joined: Wed Dec 31, 2003 8:24 pm

Postby Jan Blok » Tue Feb 10, 2004 8:39 pm

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')
Jan Blok
Servoy
Jan Blok
 
Posts: 2684
Joined: Mon Jun 23, 2003 11:15 am
Location: Amsterdam

Postby chris » Wed Feb 11, 2004 6:24 am

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.
chris
 
Posts: 82
Joined: Wed Dec 31, 2003 8:24 pm

Postby Jan Blok » Wed Feb 11, 2004 10:47 am

chris wrote: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)
Jan Blok
Servoy
Jan Blok
 
Posts: 2684
Joined: Mon Jun 23, 2003 11:15 am
Location: Amsterdam


Return to Methods

Who is online

Users browsing this forum: No registered users and 12 guests

cron