search in a related file

Classic Servoy
1

I have one table: relaties and one table: adressen with a relation.

I have the following method

controller.find();
bedrijfsnaam = "#%"+globals.zoekrelaties+"%"
controller.newRecord();
relaties_to_adressen.plaats = "#%"+globals.zoekrelaties+"%"
controller.search();

with this method you can search on name and place the same way.
Everything is working fine, but..

it only works when there is a related record with a value in: plaats.
for example: I have one record. bedrijfsnaam has value: test
and there are no adresses attached to this record,
the method is returning: there are no record found!
is this right behaviour?

2

I tend to believe this is correct.

You have a relation and search is in this case like a SQL 'SELECT * FROM bla bla WHERE relaties.bedrijfsnaam = bla AND adressen.plaats = bla…

3

That’s corrrect.
The search only performs on existing relation.(relation_to_addresses)
If you don’t have an address attached to your relation it’s left out of the search.
We have have this on our list to be fixed.

underlying SQL will then do something like:
select columns from relations where bedrijfsnaam = “#%”+globals.zoekrelaties+“%”
UNION
select columns from relations, addresses where addresses.plaats = “#%”+globals.zoekrelaties+“%”

4

HJK, I think you would get the result you’re looking for if you do two separate find&searches, if you make the second search to extend of the foundset

controller.find();
bedrijfsnaam = “#%”+globals.zoekrelaties+“%”
controller.search(false,true);
controller.find();
relaties_to_adressen.plaats = “#%”+globals.zoekrelaties+“%”
controller.search(false,false);

5

pbakker is mijn makker!
That’s exactly what I want
THANKS! :D

6

:roll:

7

just a minor adjustment:

the first search must be: controller.search(true,true) //In my case

or else, the next time you start the method, it does not search the complete database anymore.