Servoy Developer
Version 3.5.10-build 524
Java version 1.5.0_20-141 (Mac OS X)
Hi all
I am performing a find in a contacts table
Each contact record can have many types (e.g. ‘client’, ‘supplier’, ‘producer’, ‘exporter’ etc)
So one contact record may be both a client and an exporter or also a producer
Many combinations depending upon the contact
The related type data is displayed in a tab panel on the contact form
I have a find method which takes the user to a find form and enters find mode
On the find form I show the tab panel to the related types and have two buttons to ‘search’ or ‘reduce’ which runs the following method:
if (application.getMethodTriggerElementName() == 'reduce')
{
controller.search(false, true);
}
else
{
if (application.getMethodTriggerElementName() == 'search')
{
controller.search();
}
}
So I initiate the find and then enter ‘client’ into the tab panel and hit ‘search’
This correctly returns me 4 records (of which two also have the type producer)
I then initiate find again and enter ‘!producer’ in order to exclude the two producer records and hit ‘reduce’ and I still get 4 records !
I’ve looked at this until my eyes blur and can’t see what basic error I am making here
Would appreciate anybody embarrassing me by telling me what I need to do to reduce a search via a related criterion
The result that I am looking for is to find all contact records that have a type = ‘client’ and then exclude those contact records within that found set which also have a type of ‘producer’
So having found 4 records which have related type = ‘client’ I have the next find search these records for those that also have a type of ‘producer’ and then exclude them from the found set - hence the entry of ‘!producer’
Using ‘producer’ alone and reducing the search finds me only the two records that have both client and producer as related types and so does the opposite of what I need to achieve
So, I run a find for client and return 4 records which have the following related types:
1 - client / producer
2 - client / exporter
3 - client / exporter
4 - client / producer
I then want to reduce the foundset to those records that do not contain producer and so I enter ‘!producer’ and reduce the search which should return me records 2 & 3 but doesn’t !
I am not quite sure if my findings with Find correspond to that problem, but it could be related. Find doesn’t work properly in some circumstances. For example, I have station names in a table and I execute manually a Find (Cmd-F) in the stations name field (in the Servoy HI) and enter for example: Feldbrunnen% || Riedholz%. The entries in the table are:
...
Feldbrunnen
Feldbrunnen, Bahnhof
Feldbrunnen, St. Katharinen
...
Riedholz
Riedholz, Bahnhof
Riedholz, Hinteres Riedholz
...
Sometimes, the result is only the 3 records with names Feldbrunnen…, sometimes the result is (as expected) the 6 records. Sometimes, I find there is even a difference in the result when entering the Find string manually in the field or when entering it with Copy/Paste. I know, it sounds very strange but it is absolutly reproducable, I had quite some time to believe myself it’s like this, but it is. I have this behaviour (at least) since Version 3. Servoy once made in the Find code an enhancement, which led to better Find result, but I am pretty sure there are still problems with Find. And if I have problems with the OR, it’s obvious that there could be also problems with NOT.
By the way, the more OR’s I use in the Find, the worse, i. e. unpredictable the results get. I hope someone from Servoy can shed light to this.
Thanks for the feedback
It seems that we are both experiencing inaccurate search results and so there may be some fundamental reason which affects us both here
I have run my 2 level find manually in Servoy - outside of the method driven process - and still do not get the correct results
It seems to be something to do with not recognising the ‘!’ operator over the relation
The relation is a straight 1-m between contacts and types
The SQL from the Servoy Performance Data is below which is what is run after the second find with the reduce search:
select distinct contacts1289.pk_contact from contacts contacts1289 inner join j_contact_category jcontactcategory1310 on contacts1289.pk_contact=jcontactcategory1310.fk_contact where jcontactcategory1310.j_contact_category_name = ? and jcontactcategory1310.j_contact_category_name != ? order by contacts1289.pk_contact asc limit ?
Even running just a reduce search only on the related data does not return the correct result
So I enter find mode and enter ‘!producer’ and choose reduce search and I get all contact records that have any related types irrespective of what you put after the ‘!’
Can anyone confirm this behaviour with ‘!’ operator over a relation ?
Harry Catharell:
It seems that we are both experiencing inaccurate search results and so there may be some fundamental reason which affects us both here
Yes, that’s what I have experienced in various combinations (can’t remember all of them), even some I would say basic expression as mentioned in your and my previous post don’t seem to work. I tried to enter in my above example !Feldbrunnen% && !Riedholz%, but that doesn’t work either (I also tried with various forms of using paranthesis). Entering !Feldbrunnen% works, though.
It’s quite a big problem, because what can we say to our users? At the moment they know it’s not always working, but it’s not at all satisfying.
Harry - could you keep us up to date on how this is handled please. This is potentially a biggy for us - and will likely cause us to delay roll-out of our search functionality until resolved. A search that produces sporadic results is worse than none at all, we may have to revert to hand coding all searches in SQL (that’s how we did it in the bad old days (.NET)).
Guess quite a few others will be reviewing their own solutions about now!
Hi Harry,
have you tried playing around with using the ‘%’ wildcard symbol in your searches?
Maybe there are some invisible characters causing an issue (like hard spaces or control chars)?
So, I have 5 records which have the following related types:
1 - client / producer / exporter
2 - client / exporter
3 - client / exporter
4 - client / producer
5 - producer
As an addendum to this I set up a small sample solution for submission with data similar to the above in it
5 contact records with various related type records attached via 1-m relation
If I search on that related type from the contact form and put in ‘!producer’ as a search criterion then it returns me 4 records and excludes record 5 which has only a single type the same as my exclusion choice !
So it seems that the NOT operator works on records which have that one type and only that one type related to it ?
When you enter ‘!producer’ the query that is created by servoy will look for
persons that do have contact types other then producer.
Then those persons are shown and all there related records.
So the search returns persons and all related records are shown.
You can verify this by entering ‘!client’ . since person maria only has one contact type (client) maria will not be shown in the result.
So once again, the find is done on the person form and table, so persons are searched and returned.
query and parms :
sql= select distinct persons93.person_id from persons persons93 inner join contact_type contacttype109 on persons93.person_id=contacttype109.fk_person where contacttype109.contact_type != ? order by persons93.person_id asc
parms are : [client]
Personally I’m not surprised that Servoy might not do this, because it’s asking Servoy to do a bit much in terms of a simple (built in) ‘constrain’ function, then complicated by executing it twice with a NOT clause the second time round ?