11
May
10

Multi-valued (Multivalue) Fields (Managed Properties) in Search not Returning All Values

Update 20100908:  Leslee completed this in the production environment and it worked the first time.  I’m calling it a wrap!

Update 20100826:  We just did this in our staging environment and got it to work the first time through!

Update 20100802:  I’ve rewritten this post a couple of times, adding more detail as we find it.  We think we might be on to finding "the recipe," but we’ll only know for sure when we repeat the process in the staging and production environments and get repeatable results.  This post is "in-work."

Oh man, this has been driving us CRAZY (just ask Leslee)!  We have a multi-valued column in SharePoint (Subject) and we couldn’t get all the values to show up in the search results.  All we could get is the last value.  See this post: Always returning last value.

We had different results before we upgraded to SP2.  In SP1, we were getting some weird results with some of the values being returned as ;#Value;# or some such nonsense.  We hope we don’t see that again.

Currently, we are on SharePoint 2007 SP2 with the February 2010  June 2010 cumulative update with the infrastructure updates installed.

There were a few hints from Ontolica (Surfray) from this post.

From this post by Michael Schau, we started to deduce a method to fix the problem.  Here’s what we came up with.

Overview:

  • Remove the mapping between your managed property and crawled property.
  • De-select the "Include values for the property in the search index" field on your crawled property.
  • Edit your crawled property category and select the checkbox to “delete all unmapped crawled properties.”
  • Reset all crawled content.
  • Perform a full crawl.
  • Add your mapping back between your managed and crawled property.
  • Change the HasMultipleValues column to True for your managed property in the MSSManagedProperties table in the Shared Services database (void your warranty).
  • Change the VariantType column to 4127 for your crawled property in the MSSCrawledProperties table in the Shared Services database (void your warranty again).
  • Perform another full crawl.
  • Perform an incremental crawl (not sure if this is really necessary, but it’s pretty quick).
  • Perform an IISRESET on all SharePoint boxes.

Let’s start with the ugly part first.  You’re not going to like this but, if MS doesn’t have software that correctly updates the database, then we’ll do it manually.  After all, it’s just a database!

Find your SSP database in SQL Server Management Studio.

image

Find the MSSManagedProperties table and open it for editing.  Locate your managed property using its FriendlyName and change the HasMultipleValues column to "True" (or 1).

image

image

Now find the MSSCrawledProperties table and open it for editing.  Locate your crawled property using its PropertyName and change the VariantType column to 4127.  We found when you change the VariantType to 4127 the Multi-valued field changes to "Yes."  All of the multi-valued fields we could find had this variant type.

image

image

The variant type for a text field is 31 (0000000011111).  The variant type that shows up on a multi-valued text field is 4127 (1000000011111).  Could it be that the leading bit identifies the field as multi-valued?  Here’s an incomplete table for the crawled properties in the SharePoint category.

Variant Type Multi-valued Decimal Binary
Text False 31 0000000011111
Text True 4127 1000000011111
Binary False    
Binary True    
Integer False 20 0000000000010
Integer True    
Yes/No False 11 0000000001011
Yes/No True    
Decimal False 5 0000000000101
Decimal True    
Date/Time False 64 0000001000000
Date/Time True    

image

You are done modifying the database.  Great job!  You’ve now voided your warranty.

Now we’re going back to Central Administration in the SSP where your search is located.

Remove the mapping for the crawled property from the crawled metadata store.  You can get there from the Metadata Properties page by clicking on Crawled Properties.

image

Find and edit your crawled property.  Remove the mapping and de-select the "Include values for the property in the search index" field.

image

Find your crawled property group (SharePoint in our case) and drill into it.

image

Click on Edit Category .

image

Select the checkbox to delete all unmapped crawled properties and click OK.

image

Reset all crawled content and perform a full crawl.  I hope you don’t have too much data!  Perhaps you should have thought to limit it before you started!

image

After the full crawl has completed, you need to add your property back.  It should be in the crawled properties.  You need to map it back to your managed property.  After you have it added back, perform ANOTHER full crawl, after which, you should see the property is correctly classified as multi-valued.

image

What does this get you?  Now, if you look at the raw XML search results for your managed property, you should see the multiple values returned.  In this example, we’re looking at a managed property called owssubject.

image

And, if you apply your XSLT, you should get something that’s nice looking, like this!

image

Search terms:

Multivalue fields in SharePoint search not returning all values

Multivalued fields in SharePoint search returning single value

Multivalued fields SharePoint search not working – broken

Can’t get multivalue field to return more than one result in Sharepoint search

Advertisements

5 Responses to “Multi-valued (Multivalue) Fields (Managed Properties) in Search not Returning All Values”


  1. 1 David
    October 1, 2010 at 7:13 pm

    Thank you for taking the time to post this!
    I stumbled upon it while trying to resolve two search issues for a client.
    Scenario:
    Created a new web app with new SSP and proper configuration.
    We began with the governance, taxonomy, metadata, search etc…
    Now as we migrate the old files to the new environment we have encountered two issue.
    -issue one: on any Office 2003 (doc/xls) file several defined metadata fields are reset after moving to new site (for example if a doc had check boxed meta around location, team, department then when the doc was opened on the new site all these fields would be unchecked.
    -issue two: the crawl is kicking back “The filtering process coupld not load the item. This is possibly caused by an unrecoginized item format or item corruption” but only on any .doc or .xls file. Any docx, pdf, xlms etc.. works fine.
    These are likely related and after reading your post i have a couple ideas i will attempt in test.
    We reapplied the filters. Environment is MOSS2007 sp1, 64bit WFE, SQL 2005 64bit

  2. 2 Danny
    December 2, 2010 at 7:54 am

    I am having same problem with Taxonomy fields in Sharepoint 2010. Only this solution doesn’t work on 2010 😦

  3. 4 larry tenison
    February 4, 2011 at 11:07 am

    Do any of you reading this thread have any clues as to why a simple CHOICE column defined at the site collection level, associated with a site collection content type, which is used in a Doc Lib and has dozens of filled-in columns using the choices, does NOT show up as a crawled property? This is not a multi-valued choice column, just a simple single valued drop-down list. I have dozens of other such columns defined the same way and they all show up as “ows_xxxx” where xxxx = the name of the column. One particular instance is not showing up and I can’t figure out why.

  4. 5 stanton
    March 25, 2011 at 10:30 am

    Encountered this issue on our site and found your solution, however we did not want to void our warranty. We contacted MS Premier Support and they offered two workarounds that did not involve modifying the database manually and voiding the warranty.

    Workaround #1 (verified to work)
    – Remove crawled properties, reset index, etc.
    – Create a dummy list in the site collection
    – Create a dummy column that is a lookup to your lookup list and allows multiple values
    – Create a dummy list item and select more than one value for the lookup value.
    – Incremental crawl
    – Add both crawled properties to managed property (the existing one and the new dummy one).
    – Full crawl.
    – Now the results should have multiple values. It’s magic.

    Workaround #2 (unverified but should work)
    – Since SSP determines multivalue based on first crawled item, create another site collection and put dummy content in it that has multiple values in it.
    – Crawl the dummy site collection first with the mulitple values then crawl then crawl
    your main content


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Asif Rehmani’s SharePoint Videos

SharePoint-Videos

Click to access a wealth of SharePoint videos

SharePoint Rx

SharePoint Rx Home

Categories

Posts by Date

May 2010
M T W T F S S
« Apr   Jun »
 12
3456789
10111213141516
17181920212223
24252627282930
31  
Support Wikipedia

%d bloggers like this: