Show CRM Accounts In SharePoint List Using BCS (Business Connectivity Service)

This post describes how to show the CRM Accounts entity in SharePoint List using business connectivity service (BCS).

Keywords: SharePoint, SharePoint 2010, CRM, Integration Between SharePoint and CRM, BCS, Business Connectivity Service. External System

1. Create Domain Account

First of all, you will need to create a domain account that has access to both SharePoint and CRM system.  This account will be impersonated from SharePoint to access CRM system. Here are the steps;

1-      Log on your domain controller machine then create and account. The created one is called “bcsCRM”

2-      Go to CRM system http://<CRMURL>/<OrgName>/default.aspx, go to settings, Administration, Users, then Add new User, in the User Name field write bcsCRM  then tab out, the other information will show.

3-      Save the new user, go to Security Roles section, Manage Roles,  give him a System Administration role

Top
2. Let SharePoint Impersonate this Account

This is done using SharePoint Secure Store Service. Here are the steps;

1-      Go to SharePoint Central Administration

2-      Under the Application Management Section, choose Manage Server Application.

3-      Click on Secure Store service.

4-      Click on New to create new Secure Store Target Application; then do the following

a.       In the Target Application ID write “BCSCRM_ID” or any name you would like.

b.      In the Display Name Write “BCSCRM_ID” or any name you would like.

c.       In the Contact e-mail, write youremail@domain.com.

d.      In the Target Application, select Group.

e.      Click Next.

f.        In the second screen keep everything is in default then click Next

g.       In the third screen, write how is the admins of this Target application, write spadmin and your account

h.      Specify the users/ members of this Target Application, write All Authenticated Users then click OK.

i.         You will find that your Target Application has been created with the name “BCSCRM_ID”

Top
3. Create External Content Type and External List

This section shows how to create an external Content type and external List in the top site collection site. To do this, follow the followings;

1-      Go to the top site collection site and open it using SharePoint Designer.

2-      Click on external Content Types.

3-      Click External Content Type button in the ribbon

4-      Writ the Name and the display Name of the External Content type, in our case write CRMAccounts.

5-      Click “click here to discover external data sources and define operations link”

6-      Click Add Connection Button.

7-      In the Data Source Type, Choose SQL Server.

8-      In the next dialog;

a.       In Database Server, write CRM Server Name.

b.      In the database Name, write <OrgName>_MSCRM

c.       In the connectivity Part, choose Connect with Impersonated Windows Identity.

d.      In the Secure Store Application ID, write BCSCRM_D

e.      In the windows dialog login, write bcscrm & the password.

f.        You will find the CRM in your data source explorer.

g.       Open the CRM.

h.      Choose View.

i.         Right Click on FiltredAccount, then choose Create All Operations.

j.        Choose the fields that you need to show and don’t forget to pick an identifier, the accountid is the identifier in our scenario. In this scenario, we just show Account Name and the Full Name

k.       Right on the CRMAcountss external content type and then choose Create list and forms button from the Ribbon

l.         Fill out the External List Name “Our CRM Accounts” in our scenario

m.    Go to the top site http://intranet/test/mohammed/  and you will find a list called Our CRM Acounts, has been created.

Top
4. Solving Database Respond Throttle

Sometimes the records in CRM entity exceed the DB throttle default which is 2000 records and when you open the list you see this error. “Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

Correlation ID:4869b12c-d844-4d37-848f-c1e80a4b9639”

To solve this problem Open power shell and write the following commands
1- $bdcProxy = Get-SPServiceApplicationProxy | where{$_.GetType().FullName -eq('Microsoft.SharePoint.BusinessData.SharedService.' + 'BdcServiceApplicationProxy')}
2- $dbRule = Get-SPBusinessDataCatalogThrottleConfig -Scope Database -ThrottleType Items -ServiceApplicationProxy $bdcProxy
3- Set-SPBusinessDataCatalogThrottleConfig -Identity $dbRule -Maximum 1000000 -Default 5000

Hit the list again to see it working.

Top

Timer Job For Sandbox Solution

Everybody knows that the sandbox solution on SharePoint 2010 has lots of limitations. One of these limitations is SharePoint 2010 Sandbox solution doesn't have a timer job. How many of us wished to have a timer job under a sandbox solution. Well, this post shows to implement a timer job under a sandbox solution.

Keywords: SharePoint, SharePoint 2010, Timer Job, Sandbox Solution.

Components;

1-      List A with one item a Workflow that starts off manually and when then an item changes.

2-      List B with one item that has an event receiver with item updating and item updated handlers.

Scenario

1-      On list A

a.        Start the workflow manually on the item on the list.

b.      The workflow starts by pausing 1 minute.

c.       After pausing, the work flow updates the item that is in list B.

2-      On list B

a.       On item updating;

                                                                           i.      When the event receiver starts, run the code of the timer job that you need to execute.

b.      On item updated;

                                                                           i.      Update the item on list A and start the workflow on this list programmatically.

3-      When the workflow start programmatically, we will go back to step 1.

Top
Extension

You can have lots of timer jobs that are managed by only List A and List B, you don’t need to have two lists for every timer job. This is by

1-      Have and ID field in list A and ID field in List B.

2-      Every item in List A will have a corresponding item in List B with the same ID. E.g. List A will have items (1, Add Task) & (2, Update Task). List B will have items (1, Add Task) and (2, Update Task).

3-      In List A

a.       Make the workflow updates the item in List B that has the same ID of current item.

4-      In List B

a.       On Item Updating;

                                                                           i.      Check the item ID and run the corresponding timer Job.

b.      On Item Updated;

                                                                           i.      Run the Workflow on List A on the item that has the same ID of list  B updated item.

The scenario will be explained by example in details in later.

Limitations

The timer job code should contain only Sandbox functions.

Very simple, very easy and very productive.

The next figure shows the timer job sandbox scenario.

Top
Source Code

We will create a sandbox VS solution that has;

1-      Content Type for WFs list “Workflows list, which is the list that will carry items; each item will be corresponding to a timer job”.

2-      List Definition for WFs.

3-      List Instance for WFs

4-      Content Type for ERs list” Event Receivers list, which is the list that will have event receivers for item updating and item updated event, each item in this list will be corresponding to a timer job”.

5-      List Definition ERs.

6-      List Instance for ERs.

7-      Workflow based on WFs Content Type and associated to WFs list.

8-      Event Receivers for ERs list

Steps

1-      Create a team site called Sandbox Timer Job

2-      Create Sandbox Solution Called Sandbox Timer Job and point to the Sandbox Timer Job site URL.

3-      Create Content Type  for WFs and name it WFsCT

<?xml version="1.0" encoding="utf-8"?>

<Elements xmlns="http://schemas.microsoft.com/sharepoint/">

   <Field ID="{EE2C4296-B8F6-4E14-A3BF-D6479D619630}" Name="IsWorking" DisplayName="Is Working" Type="Boolean" Description="Determines if the WF or the ER is working " Group="_Sandbox TimerJob Columns" Required="FALSE" Sealed="FALSE" />

  <Field ID="{756A3594-231E-4F8D-BDED-B7CF62F72A6A}" Name="TimerJobID" DisplayName="Timer Job ID" Type="Text" Description="Timer Job ID" Group="_Sandbox TimerJob Columns" Required="FALSE" Sealed="FALSE" />

  <Field ID="{1AA858AE-56E6-4495-BC08-5048DDF58257}" Name="TimerJobName" DisplayName="Timer Job Name" Type="Text" Description="Timer Job Name" Group="_Sandbox TimerJob Columns" Required="FALSE" Sealed="FALSE" />

  <Field ID="{FBC6E407-8E46-4024-BA3D-7EFBDBB42370}" Name="LastTimeRan" DisplayName="Last Time Ran" Type="Text" Description="Last Time Ran" Group="_Sandbox TimerJob Columns" Required="FALSE" Sealed="FALSE" />

  <!-- Parent ContentType: Item (0x01) -->

  <ContentType ID="0x01007aa0ccf9b3354ce69a95c198aaa555e6"

               Name="WFsCT"

               Group="_Sandbox TimerJob Content Types"

               Description="This content type is used forworkflows list"

               Inherits="TRUE"

               Version="0">

    <FieldRefs>

      <FieldRef ID="{fa564e0f-0c70-4ab9-b863-0177e6ddd247}" Name="Title" DisplayName="Status" Required="FALSE"  Sealed="FALSE" ShowInNewForm="TRUE" ShowInDisplayForm="TRUE" ShowInEditForm="TRUE" />

      <FieldRef ID="{EE2C4296-B8F6-4E14-A3BF-D6479D619630}" Name="IsWorking" DisplayName="Is Working" Required="FALSE"  Sealed="FALSE" ShowInNewForm="TRUE" ShowInDisplayForm="TRUE" ShowInEditForm="TRUE" />

      <FieldRef ID="{756A3594-231E-4F8D-BDED-B7CF62F72A6A}" Name="TimerJobID" DisplayName="Timer Job ID" Required="FALSE"  Sealed="FALSE" ShowInNewForm="TRUE" ShowInDisplayForm="TRUE" ShowInEditForm="TRUE" />

      <FieldRef ID="{1AA858AE-56E6-4495-BC08-5048DDF58257}" Name="TimerJobName" DisplayName="Timer Job Name" Required="FALSE"  Sealed="FALSE" ShowInNewForm="TRUE" ShowInDisplayForm="TRUE" ShowInEditForm="TRUE" />

      <FieldRef ID="{FBC6E407-8E46-4024-BA3D-7EFBDBB42370}" Name="LastTimeRan" DisplayName="Last Time Ran" Required="FALSE"  Sealed="FALSE" ShowInNewForm="TRUE" ShowInDisplayForm="TRUE" ShowInEditForm="TRUE" />

    </FieldRefs>

  </ContentType>

</Elements>

4-      Add List Definition From Content Type based off WFsCT and name it WFsDef and create list instance for it.

5-      Modify the View to show all columns by modifying the <ViewFields>section as the following.

<ViewFields>

          <FieldRef Name="Title" DisplayName="Status"/>

          <FieldRef Name="IsWorking" DisplayName="Is Working"/>

          <FieldRef Name="TimerJobID" DisplayName="Timer Job ID"/>

          <FieldRef Name="TimerJobName" DisplayName="Timer Job Name"/>

          <FieldRef Name="Log" DisplayName="Log"/>

</ViewFields>

Top
6-      List Instance with data should look.

<?xml version="1.0" encoding="utf-8"?>

<Elements xmlns="http://schemas.microsoft.com/sharepoint/">

  <ListInstance Title="Workflows"

                OnQuickLaunch="TRUE"

                TemplateType="10001"

                Url="Lists/WFs"

                Description="List that the workflow will be triggered from">

<Data>

      <Rows>

        <Row>

          <Field  Name='Title'> Add Task </Field>

          <Field  Name='IsWorking'>False</Field>

          <Field  Name='TimerJobID'>1</Field>

          <Field  Name='TimerJobName'>Add Task</Field>

          <Field  Name='LastTimeRan'>-</Field>

        </Row>

        <Row>

          <Field  Name='Title'> Update First Task </Field>

          <Field  Name='IsWorking'>False</Field>

          <Field  Name='TimerJobID'>2</Field>

          <Field  Name='TimerJobName'>Update First Task</Field>

          <Field  Name='LastTimeRan'>-</Field>

        </Row>

      </Rows>

    </Data>

  </ListInstance>

</Elements>

7-      Create Content type for the list that will have Event Receivers, call it ERsCT. Don’t create new columns, we will use the created ones

<?xml version="1.0" encoding="utf-8"?>

<Elements xmlns="http://schemas.microsoft.com/sharepoint/">

  <!-- Parent ContentType: Item (0x01) -->

  <ContentType ID="0x01001f77ec68827f46719fec01cf60c37685"

               Name="ERsCT"

               Group="_Sandbox TimerJob Content Types"

               Description="This content type is used event receivers list"

               Inherits="TRUE"

               Version="0">

    <FieldRefs>

      <FieldRef ID="{fa564e0f-0c70-4ab9-b863-0177e6ddd247}" Name="Title" DisplayName="Status" Required="FALSE"  Sealed="FALSE" ShowInNewForm="TRUE" ShowInDisplayForm="TRUE" ShowInEditForm="TRUE" />

      <FieldRef ID="{EE2C4296-B8F6-4E14-A3BF-D6479D619630}" Name="IsWorking" DisplayName="Is Working" Required="FALSE"  Sealed="FALSE" ShowInNewForm="TRUE" ShowInDisplayForm="TRUE" ShowInEditForm="TRUE" />

      <FieldRef ID="{756A3594-231E-4F8D-BDED-B7CF62F72A6A}" Name="TimerJobID" DisplayName="Timer Job ID" Required="FALSE"  Sealed="FALSE" ShowInNewForm="TRUE" ShowInDisplayForm="TRUE" ShowInEditForm="TRUE" />

      <FieldRef ID="{1AA858AE-56E6-4495-BC08-5048DDF58257}" Name="TimerJobName" DisplayName="Timer Job Name" Required="FALSE"  Sealed="FALSE" ShowInNewForm="TRUE" ShowInDisplayForm="TRUE" ShowInEditForm="TRUE" />

      <FieldRef ID="{FBC6E407-8E46-4024-BA3D-7EFBDBB42370}" Name="LastTimeRan" DisplayName="Last Time Ran" Required="FALSE"  Sealed="FALSE" ShowInNewForm="TRUE" ShowInDisplayForm="TRUE" ShowInEditForm="TRUE" />

    </FieldRefs>

  </ContentType>

</Elements>

8-      Create List Definition From Content Type based of ERsCT with list instance and call it ERsDef. Also change the view to show all columns as sown above.

9-      List Instance with data should look like this

<?xml version="1.0" encoding="utf-8"?>

<Elements xmlns="http://schemas.microsoft.com/sharepoint/">

  <ListInstance Title="Event Receivers"

                OnQuickLaunch="TRUE"

                TemplateType="10002"

                Url="Lists/ERs"

                Description="List that the event receivers (timerjob codes) are triggered from">

<Data>

      <Rows>

        <Row>

          <Field  Name='Title'> Add Task </Field>

          <Field  Name='IsWorking'>False</Field>

          <Field  Name='TimerJobID'>1</Field>

          <Field  Name='TimerJobName'>Add Task</Field>

          <Field  Name='LastTimeRan'>-</Field>

        </Row>

        <Row>

          <Field  Name='Title'> Update First Task </Field>

          <Field  Name='IsWorking'>False</Field>

          <Field  Name='TimerJobID'>2</Field>

          <Field  Name='TimerJobName'>Update First Task</Field>

          <Field  Name='LastTimeRan'>-</Field>

        </Row>

      </Rows>

    </Data>

  </ListInstance>

</Elements>

Top
Using SharePoint designer, create a reusable workflow based off WFsCT and write the following actions then publish

The most important part here is updating the Event Receivers List.

1-       Create event Receiver for ERsDef and the event sources are; Item Updateting and Item Updated with the following code;

public override void ItemUpdating(SPItemEventProperties properties)

{

            base.ItemUpdating(properties);

            if (properties.ListTitle == "Event Receivers")

            {

                int timerJobID = (properties.AfterProperties["TimerJobID"] != null) ? int.Parse(properties.AfterProperties["TimerJobID"].ToString()) : 0;

                if (timerJobID > 0)

                {

                    switch (timerJobID)

                    {

                        case 1:

                            AddTask(properties.OpenWeb());

                            break;

                        case 2:

                            UpdateFirstTask(properties.OpenWeb());

                            break;

                        default:

                            break;

                    }//end switch

                }//end if (timerJobId > 0)

            }//end if (properties.ListTitle == "Event Receivers")

         

 }//end function

public override void ItemUpdated(SPItemEventProperties properties)

{

            base.ItemUpdated(properties);

            if (properties.ListTitle == "Event Receivers")

            {

                //get timerJob ID

                int timerJobID = (properties.AfterProperties["TimerJobID"] != null) ? int.Parse(properties.AfterProperties["TimerJobID"].ToString()) : 0;

                //get the item that should be updated in WF list

                SPWeb web = properties.OpenWeb();

                SPSite site = web.Site;

                SPList WFsList = web.Lists["Workflows"];

                SPQuery query = new SPQuery();

                query.Query = "<Where><Eq><FieldRef Name='TimerJobID'/><Value Type='Text'>" + timerJobID.ToString() + "</Value></Eq></Where>";

                SPListItem WFListItem = null;

                SPListItemCollection WFListItems = WFsList.GetItems(query);

                if (WFListItems.Count > 0)

                {

                    WFListItem = WFListItems[0];

                    //update list item in the WF list

                    web.AllowUnsafeUpdates = true;

                    WFListItem["LastTimeRan"] = DateTime.Now.ToString(); ;

                    EventFiringEnabled = false;

                    WFListItem.Update();

                    EventFiringEnabled = true;

                    web.AllowUnsafeUpdates = false;

                    //find and start the workflow that is associated to this list item

                    var assoc = WFsList.ContentTypes["WFsCT"].WorkflowAssociations.GetAssociationByName("TimerJobWF-Assoc", CultureInfo.InvariantCulture);

                    if (assoc != null)

                    {

                        var result = site.WorkflowManager.StartWorkflow(WFListItem, assoc, string.Empty);

                    }

                }//end  if (WFListItems.Count > 0)

            }//end if (properties.ListTitle == "Event Receivers")

           

        }//end function

  private void AddTask(SPWeb web)

        {

            SPSite site = web.Site;

            SPList TasksList = web.Lists["Tasks"];

            SPListItem TaskItem = TasksList.Items.Add();

            web.AllowUnsafeUpdates = true;

            TaskItem["Title"] = "New task from the timer Job";

            EventFiringEnabled = false;

            TaskItem.Update();

            EventFiringEnabled = true;

            web.AllowUnsafeUpdates = false;

        }

private void UpdateFirstTask(SPWeb web)

{

            SPSite site = web.Site;

            SPList TasksList = web.Lists["Tasks"];

            if (TasksList.Items.Count > 0)

            {

                SPListItem TaskItem = TasksList.Items[0];

                web.AllowUnsafeUpdates = true;

                TaskItem["Title"] = "Ran at " + DateTime.Now.ToString ();

                EventFiringEnabled = false;

                TaskItem.Update();

                EventFiringEnabled = true;

                web.AllowUnsafeUpdates = false;

            }

}

Top

   12-      Deploy the solution.

   13-      Very Important , go to the workflow again and re-write the action Update Event Receivers. Every time you deploy the sandbox solution, you will need to re-write this action again.

   14-      Associate the Workflows list to the workflow TimerJobWorkFlow that has been created in SharePoint Designer. Name the association “TimerJobWF-Assoc”. Make the workflow starts manually and on change as show below.

15-      In Workflows list, select first item, click workflows in ribbon, start the workflow, enjoy.

Download Project

Top