how we can get result as reverse filter in c\al

suppose a table conatins records 1,2,3,4,5,6,7,8,9,10

and i have records in other table  like 1,3,4,5

then it is easy to get records from main table 1,3,4,5 by using filter function or setrange function....

 

but i want to get result set from main table like 2,6,7,8,9,10. is there any function to get these type of result or i have to write the logic for it ?

  • assume that table which contain all records is main table and remaining as sub table.
    take variable = Filter type text

    create a function MyGetfilter() return value  = text

    MyGetfilter()

    SubRec.findfirst;
    repeat
    filter := filter + '&< > ' + subRec.code +'&';
    until(subRec.next = 0);
    if(filter < > '') then
    filter := copystr(filter, 2);
    exit(filter);

    -------------------------------------

    u need to call the above code in page 

     

    setfilter.(code,getfilterEX());

    repeat

    output := output +  code + ',';

    until(next =0)

    just try it once
    thank you

  • Hi,

    I don't think there is a direct function for this. But you can loop through the second table inside main table loop and mark the records in main table which are not matching with the record in second table
  • You could create a flowfield in the main table of type Exist looking up if a record in the subtable exists. Then simple setrange the flow field = false. Then the result set will be the records without a sub-record.

    In the new NAV versions it will even create a single SQL join statement so the performance might be ok.
  • In reply to pdj:

    i also done with same logic... thanks
Related