Muhammad Ali Khan: Relating/Connecting MS CRM 4.0 Data With Custom External Content Type Data In SharePoint 2010

[Blog bot]

Источник: http://malikhan.wordpress.com/2010/0...arepoint-2010/
==============


In this post, i will first display the data from MS CRM 4.0 and from my custom external type(SQL Server Database) in my SharePoint 2010 portal and then show you how to relate(connect) these two different external content type. I got this idea from the “Fabain Williams SharePoint” blog from here SharePoint 2010 Business Connectivity Services Teaser with Multiple External Content Types surfacing in One External List.

As i saw the above post, i thought i could use it to solve my old problem in SharePoint 2007 . I remember last year one of my customer asked me to display and relate the data from MS CRM 4 and Axapta on SharePoint portal and i end up writing my own custom Web part, since there exits no relationship between these two external content type in MOSS 2007.

Note:- There exits NO PHYSICAL RELATIONSHIP between the two external content type i am going to use below. So here we go:-

Step 1: Setup the MS CRM Account External Content Type:-

First i have to setup my external content type to fetch data from MS CRM 4. I already did that in one of my earlier post. You can see the complete post here (Using Business Connectivity Services(BCS) In SharePoint 2010 For Connecting To MS CRM 4.0). Here is how MS CRM Accounts look like. (The important field to note here is the “AccountNumber” since we will be relating the two external content type on this column).



Step 2: Setup the Other External Content Type(SQL Server Database):-

I then created another “External Content Type”, this time to a SQL Server Database, for this you can use this post from Microsoft (How to: Create an External Content Type Based on a SQL Server Table).

Here is how my table(CompanyProjects) in custom database look like. This tables contains the list of the projects against a particular MS CRM Account. As you can see i have put the AccountNumber field in both “MS CRM External Content Type” (above) and in my custom (CompanyProjects) table below, since we will be relating(connecting) the data from these two different source on this field.



and here is the data i manually put in.



The next step is to create the external content type for the above table( you can do it by following the steps here How to: Create an External Content Type Based on a SQL Server Table). And then i created a SharePoint 2010 List for this external content type, see below. (The important field to note here is the “AccountNumber” since we will be relating the two external content type on this column).



Step 3:- Create the “Site Page” which will host these two external content type:-

Go to Site Actions ==> New Page. A New Page Dialog will popup. Name the page and click create.



It will open the page in the design mode, now we will insert our two external content type. Click on the “Insert” ribbon, and then click on the “Existing List” button. Select “MS CRM Accounts” list and then click add.





Here is it how it looks like now. (Note by default the view of the WebPart will be show only the BDC Identity, Edit the WebPart and select the view to your desired view). Note the “AccountNumber” in the below view.



Similarly, click on the add “Existing List” button and add the “My Custom Projects” list this time. The whole page will now look like this. Note again the “AccountNumber” in the list.



As you can see, both our MS CRM Account and our other external content type (Projects) are on the page now, showing the complete data from both the list. Now the final step is to relate/connect these two lists.

Step 4:- Relate/Connect the Two External Content Type:-

In the SharePoint 2010 portal, Click “Page” ribbon and then from the “Edit” dropdown, select “Edit in SharePoint 2010” Designer, this will open the page in “SharePoint 2010” designer.

Once in SharePoint 2010 designer, select the “MS CRM Account” web part and click “Add Connection” in the ribbon.



Now we will create the relationship (well its not really a relationship, its just a connection) between the two WebParts below. A wizard will startup, here is the step by step process to create this connection.









Make sure you select the correct columns while defining this connect from both list (i.e. AccountNumber)

A connection between two webparts is now setup. Save the Page in SharePoint 2010 designer, and the open the page in the browser. Here is how the page now looks like. Click on any MS CRM Account Row (Click on ICON to select the row)





You can see now the “Project List” is now filtered on the MS CRM Account you selected from the above List (Based on the “Account Number”).

All i can say “Coooooool”. i remembered i had to write a custom WebPart for this to happen in SharePoint 2007.

Actually, i didn’t stop here , in fact i created one more relationship between my “Custom Projects” and the details of the Tasks “Task List”. so my projects will be filtered on the selected “MS CRM Account” and then my “Tasks” list will be filtered on my selected “Custom Project”. Here is how it looks like. (i am sure you can follow and repeat the above steps to achieve this).



So the connection between MS CRM Account and Custom Project is on “AccountNumber” and the connection between “Custom Project” and “Task List” is on “ProjectId”.

Finally, does it means, we don’t have to create our own WebPart for displaying related data from two different “External Content Type”. Well off course not, SharePoint 2010 does provide you a very good platform for connecting non-related “External Content Type” with features of sorting, filtering, creating custom views with basically no code. But there may still exits scenarios where you have to create your own WebPart.

That’s all for now.




Источник: http://malikhan.wordpress.com/2010/0...arepoint-2010/