SETFILTER 'NOT IN' QUESTION

Soloper
Soloper Member Posts: 102
edited 2010-11-22 in NAV/Navision Classic Client
Hi all;

I have two tables (lets say TABLE1 and TABLE2) and need a set of records from TABLE1.
TABLE1 has a field named "ID", TABLE2 has fields named "ID", "FIELDA" and "FIELDB".

The mission is that :

I need all IDs from TABLE1 except some filtered IDs from TABLE2.

The code looks like as following;

TABLE2.RESET;
TABLE2.SETFILTER("FIELDA",'BLABLA');
TABLE2.SETFILTER("FIELDB",'BLABLA');

IF TABLE2.FIND('-') THEN
BEGIN

TABLE1.RESET;
TABLE1.SETFILTER("ID", NOT IN TABLE2."ID")
...
SOME LOOP
...
...
END;

I know the underlined part has a syntax error. I Just want to show what I am trying to do.

Thanks you all for your help.

Comments

  • Soloper
    Soloper Member Posts: 102
    Any suggestion?

    Or should I try it with temp table?

    Thanks.
  • pduck
    pduck Member Posts: 147
    You can do it with a temporary table or the MARK-functionality. The process would be the same, so I descripe it with the MARK.

    You can MARK(true) all records in the first table using a loop. Then filter the second table like you want and loop through the second table. In every loop you filter the record in the first table equal to your ID of the second tables record. If a record will be found in table one then set MARK(FALSE) (DELETE when using the temporary table solution). At the end only those records in table 1 are marked which had no ID in the second table. You can filter the result using MARKEDONLY on the first table.

Categories