IBM Support

How to find orphan documents in a Notes/Domino database

Technote (FAQ)


How can you identify orphan response documents in an IBM Lotus Notes/Domino database?

Although you can use the simple technique specified in the document " How to Delete Orphan Documents in a Notes Database" (#1090237), this poses a disadvantage of modifying all the non-orphan documents in the database. This in turn may result in large and unnecessary replication events.


To identify orphan documents in a database you can follow the steps below. This technique relies on performing an @DbLookup by Universal Note ID (UNID) of the parent document. If it cannot find the UNID of the parent then it must be an orphan. It will select only one level of orphans at a time (since the child of an orphan is not necessarily an orphan), so it may need to be run several times to identify all levels of orphans.

1. Build a view (called UNID in the example below) which includes as a first column the formula:


- This column must be Sorted but not Categorized. You may add any additional columns needed to identify the documents.
- The view named "UNID" should not have a response hierarchy. Otherwise you will not be able to select orphaned documents.

2. Build an agent with the following attributes:

When should this agent run?: Manually from Actions menu
Which documents should the agent act on?: All documents in view
In the drop-down in the lower right corner of the screen: Select documents in view

Include the following formula in the agent:

SELECT @IsAvailable($Ref) & @IsError(@DbLookup("" : "NOCACHE"; ""; "UNID"; @Text($REF); 1));0

- Before running the agent, the orphan documents need to display in the view "UNID".
- The agent should be run with the focus in the view "UNID".
- If you use a different view name then you will need to replace "UNID" with the correct view name in the formula above.

3. Change to this view from the View menu. Deselect any selected documents (from the menu select Edit > Deselect All) and then run the agent from the Actions menu.

4. Orphaned documents, for instance, those for which the UNID contained in the $REF field cannot be found in the lookup view, will be selected. You can then cut and paste the documents to another database or just delete them.

5. After you have deleted the orphaned documents, rerun the agent again to find the next level of orphans (whose parents you have just deleted).

6. Keep rerunning the agent and cutting/pasting the selected documents until it does not select any documents when it is run.

Note: Beginning in Notes/Domino 5.0 the @AddToFolder function was added. You could use this function in the agent to move the orphan documents into a previously created folder using this variation of the agent previously mentioned:

SELECT @IsAvailable($Ref) & @IsError(@DbLookup("" : "NOCACHE"; ""; "UNID"; @Text($REF); 1));
@AddToFolder("<insert folder name here>";"");

It is best if the agent is designed to act on "All new and modified documents"; otherwise documents will be placed in the folder multiple times.

The folder needs to be created before you run the agent. Also, the folder must have the property "Show response documents in a hierarchy" unchecked/deselected in order to display the orphan documents. This property is found on the second tab of the Folder properties, and is enabled by default.


** Running this agent on a large database on a server could have a performance impact on other users, so it is advised that it be run during periods of low server utilization.

** Ensure the agent has run correctly by checking a few main documents, non-orphaned responses, and a true orphaned response document before starting to delete documents from the database. This is necessary because if you incorrectly enter the formula above and it fails to perform the @DbLookup correctly, it may incorrectly select documents.

Supporting Information:

To verify that the view in Step 1 does not have a response hierarchy, do the following:

1. Open the view in Domino Designer and select Design > View Properties. The InfoBox will display.
2. Select the Options tab and make sure that "Show response documents in a hierarchy" is not selected.

Related information

How to Delete Orphan Documents in a Notes Database

Historical Number


Product Alias/Synonym

Lotus Notes Client
Lotus Domino Designer Client
Notes Designer for Domino

Document information

More support for: IBM Domino Designer

Software version: 8.0, 8.5, 9.0

Operating system(s): Windows

Reference #: 1084828

Modified date: 01 September 2006

Translate this page: