Searching a Database with related table using BCS

In the Searching a Database using BCS article, I mentioned we would use the [DimProductCategory] and the [DimProductSubcategory] tables. But in that article, I just use the [DimProductCategory] table. Somebody may be confused with that article that why I mentioned two tables but only one is used. Let me show what I'm gonna do in this article.


In this article, I will show you how to search two related tables.
------------------------------------------------------------------------------------
If you haven't create the setup a data source for [DimProductCategory] table, please refer to the previous article to do that.


Setup related table
1. Open your Microsoft SharePoint Designer 2010, go to External Content Types.

 2. Click External Content Type from the New ribbon

3. Click the New external content type, enter a name, press the Tab button, the Display Name should be filled automatically. Finally, click the Click here to discover external data sources and define operations.

 4. Find out the [DimProductSubcategory] table, and then right click it and then select Create all Operations

 5. In the All operations dialog, click Finish button to finish the dialog. Then, you will see those operations in External Content Type Operations section.

6. Still right click the [DimProductSubcategory] table, click New Association item in the menu.

7. In the Association dialog, click the Browse... button

8. In the External Content Type Selection dialog, select the first one (the master table external content type), and then click OK button, the External Content Type Selection dialog will be closed

9. In the Association dialog, single click the drop down button, and then select the foreign field, and then click the Next button.

10. In the coming step, select the foreign key, and then check the Map to Identifier checkbox, and click the Finish button

  11. Right now, all dialogs are closed. In the SharePoint Designer, click the Summary View from the Views ribbon

12.Select the EnglishProductSubcategoryName from the Fields section, and then click the Set as Title from the ribbon.

13. Click the Save button from left top and then click the Create Profile Page from the ribbon and then close your SharePoint Designer.

 Set Permission for the related table
---------------------------------------------------------------------------------------------
1. Open your Central Administration page, click the Manage service applications under Application Management.

2.  Click Business Data Connectivity Service.

3. Select your related table and then click the Set Object Permissions from the ribbon.

4. Enter your account, click the Add button, and then elect all CheckBox in the permission list. Click OK button from the bottom.

Re-crawl your content source
-------------------------------------------------------------------------------------------
1. Go to your Central Administration page, click Manage service applications under Application Management -> your content query application -> Content Sources (left navigation panel). Click your content source, and then select Start Full Crawl

2. After the crawling complete, go to your FAST Search center web site, enter some keyword like "Bikes", then you will get your result.

3. If you click the second result item, Bikes, you will see the relation profile page.