Are you a user?

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.

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).

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.

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.

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.

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

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

Click on Edit Category .

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

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!

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.

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.

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

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

I’m documenting my journey into a land where I’m not completely comfortable…javascript.  While I have been a software developer and DBA at times during my career, it’s been a while since I’ve had to do much with javascript.  So, like one of my students recently said, “I code in Google.NET.” 

A client had a requirement for cascading drop-down menus and found a great post by Marc Anderson about cascading drop-downs on a SharePoint form.  In fact, Marc’s turned it into a three-part series, culminating in a jQuery library for SharePoint Web Services.  I tried helping him implement part one and part two without much success.  There seems to be a real problem with the javascript function getElementsByTagName in IE and it not returning a correct array of objects when it’s called…e.g. getElementsByTagName(“td”).  So, after spending five or six hours not getting anywhere, I decided to try the jQuery library version. 

First, you need to get a copy of jQuery.  You can do that here.  This needs to be referenced in your code.  A simple way to do this is to create a document library in SharePoint and upload your javascript files.  Of course, the library must be able to be read by anyone using the code.  You can see the library I set up and jquery-1.3.2.min.js that has been uploaded (along with other things).

Then you need to get a copy of the SPServices jQuery library and upload it.  It is out on CodePlex here. 

In the code window in SPD, you can reference the libraries thusly:

<script language="javascript" type="text/javascript" src="http://portal.awbikes.local/SiteDirectory/wf/JS/jquery-1.3.2.min.js"></script>

<script language="javascript" type="text/javascript" src="http://portal.awbikes.local/SiteDirectory/wf/JS/jquery.SPServices-0.2.8.js"></script>

Important Note

It is important to note that I’ve found that the order in which the libraries are referenced is important!  So important that, if they are reversed, you’ll likely get an “‘SPServices’ is null or not an object” error.  This was plaguing me for several hours.

Here’s a picture.  Click on it to make it bigger.

Then you have to “call” the javascript function.  It was recommended to me to use the document.ready syntax. 

<script language="javascript" type="text/javascript">
$(document).ready(function() {
        $().SPServices.SPCascadeDropdowns({
        relationshipWebURL: "http://portal.awbikes.local/SiteDirectory/wf",
        relationshipList: "SubServiceTypes",
        relationshipListParentColumn: "Title",
        relationshipListChildColumn: "SubServiceCategory",
        parentColumn: "AM Sub Service",
        childColumn: "Sub Service Category"
        });
    });
</script>

Again, a picture of the code.

relationshipWebURL

The relationshipWebURL can be left blank as long as the relationshipList is in your site.  Otherwise, specify the URL of the site that has the relationship list.

relationshipList

The relationship list is a simple list with two columns that indicates what the allowable values are (column2) for each value in column1.  So, if you a relationship of Country/State, you’d have to list a country many times (50 for the U.S.) and enter a unique state on each line.  If you had two countries (U.S. and Canada), each time a country is selected in the primary drop-down list, the secondary drop-down would populate with the list of states (or provinces) valid for the selected country. 

relationshipListParentColumn

The relationshipListParentColumn is the internal name of the primary or first column in the relationship list.  The internal name can vary greatly from the displayed name.  Usually, if you put spaces in a column name, you’ll find the spaces are replaced by _X0020_, but you also have to be careful about other liberties that SharePoint takes when creating internal field names.  To view the internal field name, go to the list settings and click on the column name.  You can look at the end of the URL and see the internal field name where it says &Field=XXXX.  Here’s an example.

You’ll also see the underscores and other “unsafe” characters have been URL-encoded.  %5F is the encoded version of the underscore (_) character.  So, AM Sub Service becomes AM_x0020_Sub_x0020_Service.

relationshipListChildColumn

The relationshipListChildColumn is the column that will change and be filtered based on the selection of the parent column.  Similar to the parent column, you need to specify the internal field name for this column.

parentColumn

The parentColumn is the display name of the parent drop-down list on the form.  You can find this in the form in code view in SPD.  This corresponds to the title of the control.  If you use the IE developer toolbar (for IE7 or prior versions), or use the built-in developer tools in IE8 (F12), you can find the properties of the control, including the title.  Here’s IE7 with the “Find by Click” function enabled in the developer toolbar.

You can also look in the code view in SPD and find the <datafields> tag.  Here you’ll see a listing of all the data fields’ internal and friendly names.  Here you can see AM_x0020_Sub_x0020_Service_x0020 and AM Sub Service 1.  I’m not really sure where the ending “1” went on the internal name…must be a SharePoint weirdness.

childColumn

The childColumn is similar to the parentColumn except it’s the one that get populated based on the selection of the parent column.  You need to specify its friendly name or title.

Again, looking at the <datafields> tag you can see Sub_x0020_Service_x0020_Category and Sub Service Category as the internal and friendly field names.

My Problem

My problem was I was receiving a “‘SPServices’ is null or not an object” error whenever I tried to use the code.  Here’s what the debugger was showing from within the executing code.

It turns out that the problem was resolved by changing the order of the script references as stated previously in this post.

Application

Now, on to the fun stuff.  Here we are, in a SPD workflow using the “Collect Data From a User” action.  This action creates a custom task form that is used to “collect” (imagine that) data from a user when the workflow creates a task and pauses for task completion.  We want to use the cascading fields within the custom task form.

Within the workflow I’ve opened up the custom task form that was created by the action in the workflow. 

It is within the PlaceHolderMain area I’ve strategically placed the code that will control the drop-down lists.

Executing the Edit task function which uses the custom task form, you can see that I have success!

In the application of cascading drop-down menus on a custom task form I still have one problem to resolve.  When I complete the task, an unexpected error occurs.  In thinking about this, I’m wondering if this might be caused by the extra values added by the javascript not being accounted for by the form.  I still need to check this out and determine the source of this error.