serving the solutions day and night


Wednesday, December 5, 2012

Integrating Dynamics CRM 2011 Data into SharePoint 2010

  1. Open the SharePoint site in SharePoint designer 2010.
  2. Select External Content Types and click External Content Type (ECT).
  3. Use the following settings on it
    Name: CRM Board Entity
    Display Name: CRM Board Entity
    Click on link next to External System.
  4. Once you click there, Click on Add Connection button, select SQL Server, Click OK
  5. Enter the Database server and CRM database, Click OK to connect.
  6. Once the connection is successful expand the Views section, look for FilteredBoard.

  7. Note - Expand the Tables section, select any table, right click on the selected table, select 'Create All Operation'. It will create the list items, view, insert, modify and delete options for the selected table. You can apply this concept to non-crm database also.

  8. Now Right click on the FilteredBoard and Select New Read Item Operation.
  9. Reason for Filtered View
    Filtered Views know about your CRM security permissions – which objects you are able to access and what you are allowed to do with them.
    Filtered Views encapsulate all the standard table joins: base tables to extension tables, picklist, etc.
    Filtered Views exist for all the user-visible CRM objects, including separate views for each activity type.
    Filtered Views work for custom entities.
        dbo.g_board.*, *.*
        dbo.g_board INNER JOIN
        dbo.SystemUserBase AS u ON u.SystemUserId = dbo.fn_FindUserGuid() AND u.IsDisabled = 0 LEFT OUTER JOIN
        dbo.UserSettingsBase AS us ON us.SystemUserId = u.SystemUserId LEFT OUTER JOIN
        dbo.OrganizationBase AS o ON u.OrganizationId = o.OrganizationId LEFT OUTER JOIN
        dbo.StringMap AS statecodePLTable ON
            statecodePLTable.AttributeName = 'statecode' AND statecodePLTable.ObjectTypeCode = 10075 AND
            statecodePLTable.AttributeValue = dbo.g_board.statecode AND
            statecodePLTable.LangId = CASE us.UILanguageId
                WHEN 0 THEN o.LanguageCode ELSE us.UILanguageId END LEFT OUTER JOIN
        dbo.StringMap AS
            statuscodePLTable ON statuscodePLTable.AttributeName = 'statuscode' AND statuscodePLTable.ObjectTypeCode = 10075 AND
            statuscodePLTable.AttributeValue = dbo.g_board.statuscode AND
            statuscodePLTable.LangId = CASE us.UILanguageId
                WHEN 0 THEN o.LanguageCode ELSE us.UILanguageId END CROSS JOIN
        dbo.fn_GetMaxPrivilegeDepthMask(10075) AS pdm
  10. Click next, then select boardid and check box next to Map to Identifier, Click Next
  11. Uncheck the box Data Source Elements. Because view contains lot of columns, it will take more time to display in CRM or Error message.
    Check boardid column (primary and unique), check box next to Map to Identifier, Required and Read-only.
    Check the other columns you want to display. Click Finish and a new Read Item Operation is created
  12. Now again right click on FilteredBoard and select New Read List Operation
    Click Next, Click Add Filter Parameter, then select the 'Click to Add' link next to 'Filter:'.

    Enter the New Filter, select 'Limit' value from Filter Type, check 'Is Default' and Click OK to connect.
    Enter the 100 (any number value, number of record will be display) in the 'Default Value:',
    Click Next and the same steps like above (Read Item Operation),
    Select boardid and Check Map to Identifier and Read only.
    Select the same columns what you selected in previous(Read Item) operation
    Now click Finish.
  13. ECT opertaions sections contains 2 operations (ReadItem and ReadList).
  14. Now Save the ECT, then click 'Create Lists and Forms' buttons, give list a name then click OK
  15. Now open the sharepoint site in brower and open the list and verify it.

If you get error, (mostly the permissions error). Go to SharePoint Central Admin -> Application Management -> Manage Service applications -> Business Data Connectivity Service, Check the service applicatin ('Board'), Click the 'Set Object Permissions' button, add the account or group, give the permissions and Check the box on “Propagate permission to all BCD models…..”) and Press OK button

No comments: