Filters

Joaquimcfraga

I have 2 tables in a master child relation. I want to select the records from the master table wich child has a specific value.

For instance, on a Order - OrderLines relation, i want to select all Orders wich OrderLines have product 'xpto'.

How can i do this with code?

Miklos_Hollender

1. Apply filters to the salesline, loop through it. Have a temporary table of sales header at hand. For every sales line, try to get the salesheader from the temp table. If it's not there, get it into a record variable and then insert the record variable into the temp tabe: temptable:=recvar; temptable.insert;
After it the temp table contains the records.

OR

2. Apply the filters to the sales line. F.e. if you, I think, will filter on type, no. and document type, create a key that's like no., type, document type, and document no. (It does not have to be an SQL index though - these are performance decisions, not using an index has a cost but maintaining it too has, so create an index only if you think it will be something often used and important). Because you filter on the first 3 fields of the key basically the results will be sorted in document no. order. From that on it's the typical "procedural select distinct" algorithm: compare the document no. to a lastdocno variable, if it's different then it's a new sales order, do something with it. then set the lastdocno to the document no. And do the lastdocno<>document no. check after the loop as well, because the last record might be a separate one-liner order.