Searching on Related records

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

Searching on Related records

Postby Clive Sanders » Mon Mar 22, 2004 2:24 pm

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.
Clive Sanders
 
Posts: 92
Joined: Mon Jan 26, 2004 7:42 pm

Postby jcompagner » Mon Mar 22, 2004 4:35 pm

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.
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8829
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Postby Clive Sanders » Mon Mar 22, 2004 7:04 pm

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.
Clive Sanders
 
Posts: 92
Joined: Mon Jan 26, 2004 7:42 pm

Postby jcompagner » Mon Mar 22, 2004 11:04 pm

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..
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8829
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet


Return to Programming with Servoy

Who is online

Users browsing this forum: Bing [Bot] and 16 guests