Post
Ask Question

SharePoint 2013/2016 - Search Database Related Tables Using BCS

Environment

SharePoint 2016 Enterprise with all the services enabled on the same server.

SQL Server 2016 Standard server.

Note: This solution should work in SharePoint 2013 as well. 
 

Requirement

Configure BCS to search a Database, where the Tables have a parent-child relationship. When searching for any term within the child table, the item linked in the parent item has to be displayed on the result page then 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 searching 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 to 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.

  1. <Method Name=”PartItemToRevisionItem”>  
  2.     <Properties>  
  3.         <Property Name=”HideOnProfilePage” Type=”System.Boolean”>true</Property>  
  4.     </Properties>  
  5.     <Parameters>  
  6.         <Parameter Name=”partID” Direction=”In”>  
  7.             <TypeDescriptor Name=”PartID” TypeName=”System.String” IdentifierEntityName=”PartItem” IdentifierEntityNamespace=”MultiBCS.MultiEntity” IdentifierName=”PartID” ForeignIdentifierAssociationName=”PartItemToRevisionItem” />  
  8.         </Parameter>  
  9.         <Parameter Name=”revisionAssociationItemList” Direction=”Return”>  
  10.             <TypeDescriptor Name=”RevisionItemList” TypeName=”MultiBCS.MultiEntity.RevisionItem[], MultiEntity” IsCollection=”true”>  
  11.                 <TypeDescriptors>  
  12.                     <TypeDescriptor Name=”RevisionItem” TypeName=”MultiBCS.MultiEntity.RevisionItem, MultiEntity”>  
  13.                         <TypeDescriptors>  
  14.                             <TypeDescriptor Name=”RevisionID” TypeName=”System.String” IdentifierEntityName=”RevisionItem” IdentifierEntityNamespace=”MultiBCS.MultiEntity” IdentifierName=”RevisionID” />  
  15.                             <TypeDescriptor Name=”Revision_Name” TypeName=”System.String” />  
  16.                             <TypeDescriptor Name=”Revision_Cost” TypeName=”System.Int32″ />  
  17.                             <TypeDescriptor Name=”PartID” TypeName=”System.String” />  
  18.                             <TypeDescriptor Name=”Revision_Type” TypeName=”System.String” />  
  19.                         </TypeDescriptors>  
  20.                     </TypeDescriptor>  
  21.                 </TypeDescriptors>  
  22.             </TypeDescriptor>  
  23.         </Parameter>  
  24.     </Parameters>  
  25.     <MethodInstances>  
  26.         <Association Name=”PartItemToRevisionItem” Type=”AssociationNavigator” ReturnParameterName=”revisionAssociationItemList” IsCached=”false”>  
  27.             <Properties>  
  28.                 <Property Name=”AttachmentAccessor” Type=”System.String”></Property>  
  29.             </Properties>  
  30.             <SourceEntity Name=”PartItem” Namespace=”MultiBCS.MultiEntity” />  
  31.             <DestinationEntity Name=”RevisionItem” Namespace=”MultiBCS.MultiEntity” />  
  32.         </Association>  
  33.     </MethodInstances>  
  34. </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 requirements, 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 multiple results will be displayed for a single record, which is unwanted.

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

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

Resources

 


Similar Articles