Odd filter search criteria in V6?

Classic Servoy
1

I have a column with the following data:

HDA
HDC
HDB
HBA
HBC
HBD

After going into Find Mode and using HD% then Search, my result-set returns HD’s but also HB’s??? This is a related field from a parent record.

What might cause this behaviour in my system?

2

You search over the relation? Maybe on parent record has HDx AND HBx as child?

3

patrick:
You search over the relation? Maybe on parent record has HDx AND HBx as child?

We don’t do anything fancy with the search Patrick. The form has a related field, we put the form in search mode and use criteria in the field - and the criteria is not honoured???

Should we be using a relation in the search ?? How would that work?

4

Ian,

Can you show a snippet of your seraching code?

Rob

5

rgansevles:
Ian,

Can you show a snippet of your seraching code?

Rob

I have two seperate events - one on a Find Button

forms[event.getFormName()].foundset.find()
``` The other on a Search Button

forms[event.getFormName()].foundset.search(true,false)


Nothing fancy!
6

When you fill the related field with “HD%”, you search for parent records that have children with HDx.
But a parent can have more children, some might be HDx and some might be HBx.
All the records in your search result have related records with HDx, but if the first related record has HBx, it will show that in your field.

Like Patrick said.

7

Joas:
When you fill the related field with “HD%”, you search for parent records that have children with HDx.
But a parent can have more children, some might be HDx and some might be HBx.
All the records in your search result have related records with HDx, but if the first related record has HBx, it will show that in your field.

Like Patrick said.

OK sort’a makes sense - but the question is, how to do that search and return ONLY records that have that criteria??

8

Kahuna:
the question is, how to do that search and return ONLY records that have that criteria??

That is exactly what you get.

But I guess you want “records that don’t have other criteria”. ;)

I don’t think that can be done using find mode. You have to load the records using a query:

foundset.loadRecords("SELECT parent_id FROM parents WHERE NOT EXISTS (SELECT child_id FROM children WHERE children.parent_id = parents.parent_id AND child.value LIKE ?)", ["HB%"]);
9

Ian,

Your search was ‘find all parents that have one or more children with value HDB%’

Maybe you should change that to: ‘find all children that have value HDB%’ and from that childrens foundset go to the parents, so use the inverted relation.

Rob

10

rgansevles:
Ian,

Your search was ‘find all parents that have one or more children with value HDB%’

Maybe you should change that to: ‘find all children that have value HDB%’ and from that childrens foundset go to the parents, so use the inverted relation.

Rob

Thanks to all for clarity. And to you Rob for a great solution.

Cheers