Searching on Related records

Classic Servoy
1

I am having problems excluding records when conducting a search on related records, the problem only seems to exist where there is more than one related record.

The client is a Hotel chain. I have two tables, Names and Stays. The Names table contains Name records, each of which has a unique name_id. The stay table contains hotel stay records, each of which has a unique stay record, and each stay record also has a name_id field. The Names table is linked to the Stay table by the name_id. This a one to many relationship, i.e. each name record could have one or more associated stay reocrds.

I have a form that displays all the fields for the Name record and a portal showing all the associated stay records. One of the stay fields ‘market_code’ is a text field, and has a three character code in each record.

If I search for all Name records with ‘LEI’ in the portal field market_code, I get the correct result, i.e. all the Name records that have LEI in the market_code in any of the related stay records.

If however, I try and search for all Name records that don’t have LEI in the market_code (!LEI), my result file includes those Name records that do have LEI in the market_code if there is more related .

How do I exclude these records! Am I doing something wrong. I am using the Find on the Menu bar.

2

this is currently not possible.
You will get the results back of the parent if the parent table also has childs that are returned with the not qeury you specify.
It is difficult and expensive to get around that.

3

Maybe I didn’t explain myself clearly, or I have misunderstood you reply.

What I want to do is to eliminate any Parents that may have a related record with ‘LEI’ in the market code. In other words, if any of the related records have ‘LEI’ in the market code, exclude the parent.

4

i understood you completely. That is not possible currently with the normal finds of servoy.

Because the query needed for such a result requires a sub query..

you could do it youreself i believe (not tested)

var argsArray = new Array();
argsArray[0] = ‘LEI’;
controller.loadRecords(“select parenttable.parentid from parenttable where parenttable.parentid not in (select childtable.parentid from childtable where marketcode = ?) order by parenttable.parentid”,argsArray );

please look at the controller.loadRecords for a better description.

also this is pretty expensive query..