In my previous post Custom Cross-List Search Development Pitfalls (Part One) I talked about the importance of managing Meta data in order to provide effective ways for users to search for documents. Meta data can be managed via "Managed Properties" in MOSS or having some concerted policy and standards for naming of columns in WSS. In Part One I illustrated a scenario where a column could be renamed to a display name that was also was being used on another document library. This scenario produced one display name pointing to two SharePoint columns with two different internal names. So a column with the display name of "Age" pointed to two columns in the site collection one with an internal name of "Customer Age" and another with an internal name of "Age". I showed how a WSS search solution would have to generate a CAML query that "OR" the two where criteria to the two different internal columns. Unfortunately, this will not return any results.

<Where>

    <Or>

        <Eq>

            <FieldRef Name="Customer_x0020_Age" />

            <Value Type="Number">25</Value>

        </Eq>

        <Eq>

            <FieldRef Name="Age" />

            <Value Type="Number">25</Value>

        </Eq>

    </Or>

</Where>

The Problem with "OR" in WSS

The reason the above CAML query "where" will not return any results is that in order for an "OR" to return results in WSS the two columns must exist in the document library where the document is stored. For instance, if I want to query for documents in a site collection that have an "AccountType" = Consumer OR "LoanType" = Auto, the only documents that will be returned are documents that have both "AccountType" and "LoanType" columns defined in the document library. Documents that have only one of the columns defined in the document library will not be returned even if the criterion matches. Increasing the number of "OR" columns in the CAML increases the chances that no documents will be returned.

The Problem with "OR" in MOSS

The problem with "OR" also occurs in MOSS but with a different twist. A custom search solution using MOSS could use FullTextSqlQuery class which uses SQL to search. If you "OR" two managed properties in the where clause and the crawled property that is mapped to the managed property does not exist in the document library then that record will not be returned even if it matches the other managed property criteria. In addition, even if the crawled property (column) does exist in the document library the record will not be returned if the crawled property for that document contains a null value.

For example consider this scenario:

            AccountType            LoanType

Document 1        Consumer            Does not exist

Document 2        Does not exist            Auto

Document 3        Consumer            Null

Document 4        Consumer            Boat

Document 5        Corporate            Auto

SELECT Title,owsAccountType,owsLoanType,owsLinkFilename FROM SCOPE() WHERE (owsAccountType = 'Consumer' OR owsLoanType = 'Auto') ORDER BY Title

Given the above SQL only Document 4 and Document 5 will be returned.

The Problem with Sorting

So let's say you avoid the problem with "OR" in both WSS and MOSS. Unfortunately, the same problem reveals itself when sorting on columns that don't exist or have null values. If you sort by columns that don't exist then those records are not returned.

The Problem with Nulls in WSS

A custom search solution that generates CAML must make adjustments to handle columns values that may contain nulls. If a column contains a null value and it is included to be returned in the results, then it will not be returned in the results unless you add the "Nullable=true" attribute to the "FieldRef" element in the "ViewFields" element of a CAML query.

<ViewFields>

<FieldRef Name="Title" Nullable="TRUE" ></FieldRef>

<FieldRef Name="AccountType" Nullable="TRUE" ></FieldRef>

<FieldRef Name="LoanType" Nullable="TRUE" ></FieldRef>

</ViewFields>

Summary

This two part series on developing custom cross-list search solutions showed you the importance of managing Meta data and some of the problems with the "OR" logic. At this time there seems to be no work around for the "OR" logic. Hopefully, we may see either a hot fix or fix in a service pack release.