lotus notes - LotusNotes retrieve data like "not in" in sql -

i have problem displaying data lotus documents filtering on data exist in other documents. example: these 2 documents: employees (with fields "empname" , "empno") , docs (with fields "docno" , "empno"). , need create view display documents employees docs not contains documents empno. in other words, if in docs exist document empno=12 data employees empno=12 must not seen.

in sql that: "select * employees empno not in (select empno docs)".

can in lotusnotes?

thanks, tomasz.

in lotus notes, there no concept of join , no way filter set (employees) relating set (docs).

since want show employees in view, need add indicator employee documents tells whether there doc document contains employees number.

setup hidden view contains doc documents sorted first column if empno.

then create computed field on employee form uses @dblookup lookup hidden view. can set field 1 if finds match or 0 if not.

finally can add employees view selection formula condition computed field = 1.

you still have issue of getting employee documents updated either 1 changes. indicator field within employee document automatically update when save it, that's not such problem. if doc document changes, things can out of sync. 1 way solve problem create agent refreshes employee documents , trigger agent after save doc document.