Search Database Related Tables using BCS

The following solution is designed and developed with the help of my friend (Sreeharsha) and Scott Hilier

Environment:

SharePoint 2013 Enterprise with all the Services enabled on the same server.

SQL Server 2008 R2 Standard also on the same SharePoint standalone server.

Requirement:

Configure BCS to search a Database, where the Tables have a parent-child relationship. When searched for any term within the child table, the item linked in the parent item has to be displayed on the result page than the searched term or item from the child table.

The requirement makes sense in a way, that the child tables are considered as paragraphs within a document. When searched for a term within the paragraph, you would expect the link to the document to be displayed. After all the child tables will always be additional information about the parent item.

Design:

While designing for this requirement the data to be retrieved from the parent table and the child table has to be from two different entities, however they need be within the same LOBInstance.

By which, we have control to define the association between these entities within the Model.

Here is how the Method and the method instance need to be defined.

<Method Name=”PartItemToRevisionItem”>

<Properties>

<Property Name=”HideOnProfilePage” Type=”System.Boolean”>true</Property>

</Properties>

<Parameters>

<Parameter Name=”partID” Direction=”In”>

<TypeDescriptor Name=”PartID” TypeName=”System.String” IdentifierEntityName=”PartItem” IdentifierEntityNamespace=”MultiBCS.MultiEntity” IdentifierName=”PartID” ForeignIdentifierAssociationName=”PartItemToRevisionItem” />

</Parameter>

<Parameter Name=”revisionAssociationItemList” Direction=”Return”>

<TypeDescriptor Name=”RevisionItemList” TypeName=”MultiBCS.MultiEntity.RevisionItem[], MultiEntity” IsCollection=”true”>

<TypeDescriptors>

<TypeDescriptor Name=”RevisionItem” TypeName=”MultiBCS.MultiEntity.RevisionItem, MultiEntity”>

<TypeDescriptors>

<TypeDescriptor Name=”RevisionID” TypeName=”System.String” IdentifierEntityName=”RevisionItem” IdentifierEntityNamespace=”MultiBCS.MultiEntity” IdentifierName=”RevisionID” />

<TypeDescriptor Name=”Revision_Name” TypeName=”System.String” />

<TypeDescriptor Name=”Revision_Cost” TypeName=”System.Int32″ />

<TypeDescriptor Name=”PartID” TypeName=”System.String” />

<TypeDescriptor Name=”Revision_Type” TypeName=”System.String” />

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstances>

<Association Name=”PartItemToRevisionItem” Type=”AssociationNavigator” ReturnParameterName=”revisionAssociationItemList” IsCached=”false”>

<Properties>

<Property Name=”AttachmentAccessor” Type=”System.String”></Property>

</Properties>

<SourceEntity Name=”PartItem” Namespace=”MultiBCS.MultiEntity” />

<DestinationEntity Name=”RevisionItem” Namespace=”MultiBCS.MultiEntity” />

</Association>

</MethodInstances>

</Method>

The association method can be written in any entity (parent or child). But the tag that defines the source and the destination can be seen within the MethodInstance.

Silver Bullet:

Of all this requirement the most important part and the one that defines that the child tables need to be crawled as attachments by which I mean that, “when searched for a term in the child table, the item linked in the parent table to be displayed”, the below Tag needs to be added within the method instance, otherwise which multiple results will be displayed for a single record which is unwanted.

<Property Name=”AttachmentAccessor” Type=”System.String”></Property>

Thanks to Scott Hilier to the right pointer on the “Attachment Accessor”  which is significant for this requirement.

Resources:

How to: Crawl associated external content types in SharePoint 2013

Modeling Associations in External Data

 

Advertisements

About Joseph Velliah
As a SharePoint Developer my professional interests tend to be technical and SharePoint focused. I run a blog at "SP RIDER" where you can expect to read HOW TOs and scenarios that I run into during my day to day job. I hope my posts will give back a little to the community that is helped me.

Comments are closed.

%d bloggers like this: