Problem solve Get help with specific problems with your technologies, process and projects.

Search across multiple databases

There are a wide variety of document types lying around in my company, and most of these documents types have their own unique fields (for example literature has a publisher). After I interviewed the employees from the company, I found that they want a database that allows you to search in all of the documents using the standard fields, but at the same time they want a way that allows searching in a specific document type and use these unique fields.

My idea is to make a database for each document type so that these unique fields can be added to the documents. On top of these document type specific databases is a database that searches all of the document type specific databases on fields that exist with every document type (for example the name of the author).

Is the construction of this database system possible or can the same result can be achieved through an alternative?

There is a lot about your setup that is unclear to me. Are these documents "lying around" in some Notes databases? Multiple databases? Are the databases on servers? Do they have different designs? Are the unique fields actual fields in the Notes sense, or would they have to be parsed out of some larger field, or are they not present yet and will have to be manually added? You talk about "adding" the fields to the documents, which makes it sound like they are not already there.

It makes sense to have multiple databases around each containing specific subsets of documents, with multiple forms, each with special fields added that are specific to the document type. There is no reason all of the documents in a database need to be the same type -- it usually makes more sense to organize them into databases based on their ownership or on who is supposed to have access to them (departmentally, in other words, rather than by form). Certainly, different departments may use some of the same forms, and it's probably easiest to limit the number of different document repository designs by giving them as much as possible all the same forms -- some of which might be unused by some departments -- and providing database-level search tools in each database so that each department can conveniently find its own documents, which it uses most often. You can read about how to do that in this article (or if you don't want to pay for a copy of the article, at least you can download the example database and see how it's done there).

If you want to provide a higher-level search tool that searches across multiple databases for specific form fields, it's certainly possible to do this, and I will discuss a couple of ways.

First, domain search and multi-database search functionalities of Domino let you conduct searches across multiple databases that you have set up in advance. You can read about these in the Domino Administrator help.

The problem with these approaches is that you cannot search for data in specific fields. You could, however, use LotusScript to search the domain index for the text values users enter in the various fields of the search form, and then winnow down the results by iterating through the matches and discarding those that do not match the search form (i.e., where the document is of the wrong form or the values are in the wrong fields). You would present the results in a rich text report with doc links (a folder would be nicer, but unfortunately a folder may only contain documents from a single database).

Another way that might work better is to add a special search functionality to each of the document databases, that would conduct a search of all the document databases including that one. This would be done by referring to a centrally located catalog of searchable document databases, and for each database in the catalog, your LotusScript code would open that database (in the back end, not on screen) and use the FTSearch method to evaluate a query that you have come up with based on the user's input. You would make a collection of these matching documents, sort them as you see fit and present them to the user, again, as a rich text report with doc links. It's a significant amount of coding, but perhaps you could recoup the cost by selling it to others in your same situation.

Do you have comments on this Ask the Expert question and response? Let us know.

Dig Deeper on Lotus Notes Domino Database Management

  • Favorite iSeries cheat sheets

    Here you'll find a collection of valuable cheat sheets gathered from across the iSeries/ community. These cheat ...