Filter Problem

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?

kriki

Something like this:

// loop the headers you want to check
recSalesHeader.RESET;
recSalesHeader.SETCURRENTKEY(...);
recSalesHeader.SETRANGE(...);
IF recSalesHeader.FINDSET THEN
  REPEAT
    // filter the lines in current header that have the product you want
    recSalesLine.RESET;
    recSalesLine.SETCURRENTKEY("Document Type","Document No.","Line No.");
    recSalesLine.SETRANGE("Document Type",recSalesHeader."Document Type");
    recSalesLine.SETRANGE("Document No.",recSalesHeader."No.");
    recSalesLine.SETRANGE("No.",'XPTO');
     
    // mark the record if "recSalesLine" is NOT empty
    recSalesHeader.MARK(NOT recSalesLine.ISEMPTY);

  UNTIL recSalesHeader.NEXT = 0;

// show only marked records
recSalesHeader.MARKEDONLY(TRUE);
FORM.RUNMODAL(0,recSalesHeader);

Another solution:With flowfields : if the product is ALWAYS 'XPTO'
-create a flowfield "Select This Record" on header with formula :

Exist("Sales Line" WHERE (Document Type=FIELD(Document Type),Document No.=FIELD(No.),No.=CONST(XPTO)))

// filter the records you need
recSalesHeader.RESET;
recSalesHeader.SETCURRENTKEY(...);
recSalesHeader.SETRANGE(...);
recSalesHeader.SETRANGE("Select This Record",TRUE);
FORM.RUNMODAL(0,recSalesHeader);

Another solution:With flowfields : if the product is variable
-create a flowfilter "Item Filter" as Code20
-create a flowfield "Select This Record" on sales header with formula :

Exist("Sales Line" WHERE (Document Type=FIELD(Document Type),Document No.=FIELD(No.),No.=FIELD(Item Filter)))

// filter the records you need
recSalesHeader.RESET;
recSalesHeader.SETCURRENTKEY(...);
recSalesHeader.SETRANGE(...);
recSalesHeader.SETRANGE("Item Filter",'Some Item');
recSalesHeader.SETRANGE("Select This Record",TRUE);
FORM.RUNMODAL(0,recSalesHeader);

Joaquimcfraga

It worked. Thank you