Append an external (crawled) property from a BCS source to a search refiner

Append an external (crawled) property from a BCS source to a search refiner

In this case I will use a field from an SQL table as a refiner in my SharePoint Search. The SQL table contains information about ORDERS from external ERP system. This ORDERS list is accessible via a BCS connect from SharePoint and also you can search for those ORDERS via SharePoint search. There is a default refiner called AUTHOR in SharePoint search. So “author” is here similar to the creator of the ORDER.

Here is the SQL table. You can see the “VorAngelegtVon” field which contains the person who created the ORDER in the ERP System:

This SQL table is integrated in SharePoint via BCS. (How to do this is not part of this post). The result looks like this:

If you start a search with the searchterm “ERP_Orders” (ERP_Orders is the profile site name for this BCS source) you get a result set shown below:

This resultset contains entries from the BCS list “ERP_Orders” and you see only two refiners are available -> Sites which contains the source and the Modified Date

Next step is to find out if there is a crawled property containing the field “VorAngelegtVon” from SQL table / BCS Source. To do this I used PowerShell.

From PowerShell execute this command:

(There is no fundamental different using FAST Search or SharePoint Search in this case. You only have to check out using the correct cmdled )

Get-FASTSearchMetadataCrawledProperty -filter <name of the field>

(You see that I used the FAST search version. For SharePoint Search the cmdlet is SPEnterpriseSearchMetadataCrawledProperty)

In our case the command is: Get-FASTSearchMetadataCrawledProperty -filter VorAngelegtVon

The result shows that there is a Crawled Property containing the data we want to use. The name is “read listelement.vorangelegtvon” and it from the category “Business Data”, so we are on the right path.

Now let’s see the details for the property “Author”. This is an existing managed property, so we have to use another cmdlet:

get-FASTSearchMetadataManagedProperty -name <name of the managed property>

(For SharePoint Search the cmdlet is SPEnterpriseSearchMetadataManagedProperty)

In our case the command is: get-FASTSearchMetadataManagedProperty -name Author

You see that the property “MappingDisallowed” is set to False. That mean we can map our crawled property to this managed property. The property “Author” is also flagged as “RefinementEnabled”. This is necessary to be shown in the refiner panel.

To map the crawled property “VorAngelegtVon” to the managed property “Author” use the following command:

$vm = Get-FASTSearchMetadataManagedProperty -Name Author

$vc = Get-FASTSearchMetadataCrawledProperty -filter VorAngelegtVon

New-FASTSearchMetadataCrawledPropertyMapping -Managedproperty $vm crawledproperty $vc

(For SharePoint Search the cmdlet is SPEnterpriseSearchMetadataMapping)

To check if the mapping is created run this command:

Get-FASTSearchMetadataCrawledPropertyMapping -name Author   

Before the mapping is taking any effect a crawl of the contendsource containing the BCS source is necessary. An incremental crawl is enough.

After the crawl is finished you can work with the new refiner and filter the ORDERS by Author:

This can be the stage for a search driven solution showing the ORDER from the ERP system refined by the Creator / Author….

IMPORTEND: this works only for managed property who default set as a refiner. (Flagg “RefinementEnabled” of the managed property must be set to “true”).  If the managed property is not set as a refiner by SharePoint default you must edit the XSLT for the Refinement Panel -> How to do this you can find for example here:  SharePoint analyst hq