LotusNotes retrieve data like "not in" in sql

Question

i have a problem with displaying data from Lotus documents with filtering on data that exist in other documents. For example: these are two documents: Employees (with fields "EmpName" and "EmpNo") and Docs (with fields "DocNo" and "EmpNo"). And I need to create a view to display the documents from Employees but only which the Docs not contains a documents with the EmpNo. In other words, if in Docs exist document with EmpNo=12 then data from Employees with EmpNo=12 must not be seen.

In SQL I could do this like that: "select * from Employees where EmpNo not in (select EmpNo from Docs)".

Can I do something like that in LotusNotes?

Thanks, Tomasz.

Answer 1

In Lotus Notes, there is no concept of a join and thus no way to filter a set (employees) by relating it to another set (docs).

Since you want to show Employees in your view, you will need to add some indicator to the Employee documents that tells whether there is a Doc document that contains the employees number.

Setup a hidden view that contains all Doc documents with a sorted first column if EmpNo.

Then create a computed field on the Employee form that uses @DbLookup to lookup to that hidden view. You can set that field to 1 if it finds a match or 0 if it does not.

Finally you can then add to your Employees view selection formula a condition that the computed field = 1.

You will still have the issue of getting those employee documents updated as either one changes. The indicator field within the employee document will automatically update when you save it, so that's not such a problem. But if the Doc document changes, things can get out of sync. One way to solve that problem is to create an agent that refreshes all employee documents and trigger that agent after you save the Doc document.

Answer 2

I agree with Ken. An indicator would be necessary for the view to work the best. There are several agent options that might not be as expensive as running the agent every time a doc is edited. You can set the agent to run several times during the day. It just depends on the information need.