Updating Statecode, Statuscode values when importing into Microsoft CRM 3.0

I use CRM Extensions ImportManager Studio 2006 when importing and mass up-dating recording in Microsoft CRM 3.0 but I found a problem when I wanted to Import (create) a large number of Tasks, marking them as Completed.  Microsoft CRM would ignore my request to mark them as completed (setting statecode = 1) and set them to active (statecode = 0) instead.  This was because, due to MS CRM logic, you cannot create a completed task.

 

Workaround

 

It suddenly occurred to me that I didn't care what statecode they were set to when imported because I could set up a Workflow rule that would check each Task as it was created, in my case I checked for a particular value I'd set in the Task Category field, and then change the status of the Task entity in the workflow to be Completed!

SMS Alerts to Salesmen regarding new CRM Opportunities

We wanted a direct way of alerting our salesmen that they had a new opportunity assigned to them on Microsoft CRM. They already receive email alerts but this hadn't been achieving a quick enough response rate.

Therefore we decided to create a utility whereby our telemarketers could alert the salesmen via a text message. This utility would need to be activated manually so that the telemarketer could decide if it was appropriate to send a text message or not.

We put a button on the Opportunity form as shown below:

Clicking this but brings up the following "Are you sure?" type window:

And the SMS gets sent.

The code behind the scenes basically does the following:

1. Extracts the User ID of the owner of the Opportunity record.
2. Looks up the Users mobile phone number on CRM.
3. Generates an Email for TextMagic where the email address to sendto address is in the format ###########@textmagic.co.uk where ########### is the Users mobile phone number. The From address is an address we have registered with TectMagic. The emails content contains "Hi, Username, you have a new opportunity on CRM: CompanyName created CreatedByName".
4. TextMagic takes the users mobile number from the To address and then sends the email bodytext to the user via SMS.
   

Code for MS CRM Improved Phonecall Screen for Telemarketing

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="Connections/CRMConn.asp" -->
<%
' This ASP script finds and displays information related to the Account (Company) on a CRM Phone Call form.
' It calculates who the related Account is regardless RegardingObjectType on the Phone Call form.
' In other words if the RegardingObjectType is a Contact, this script will calculate the Parent Account of that Contact
' It does the same if the RegardingObjectType is an Opportunity or another other RegardingObjectType

 

Dim activityid, RegardingId, Obtype, WhatSQL, AccountSQL, ContactSQL, OpportunitySQL, SuperSQL, DontKnowSQL
Dim Company, Revenue, Employees, SIC, SICDESC, IndustryO, ERP, ERPDESC, City, County, ERPLastUpdate, ParentAccount, AccountId
Dim AddiContactSQL
activityid = Request.QueryString("activityid")

 

' Initial SQL to retrieve the RegardingObjectType based on the activityid
WhatSQL = "select regardingobjecttypecode, regardingobjectid from FilteredActivityPointer where activityid='" & activityid & "'"

 

' If the RegardingObjectType is an Account use this SQL
AccountSQL = "select p.activitytypecodename, a.name, a.accountid, p.subject, a.revenue, a.numberofemployees, a.sic, a.new_siccodedescription, a.new_industryother, a.new_erpsystemname, a.new_erpdescription, convert(varchar,a.new_erplastupdate,103) as ERPLastUpdate, a.parentaccountidname, a.address1_city, a.address1_county from FilteredActivityPointer p right join FilteredAccount a on p.regardingobjectid = a.accountid where p.activityid="

 

' If the RegardingObjectType is a Contact use this SQL
ContactSQL = "select a.name, a.accountid, a.revenue, a.numberofemployees, a.sic, a.new_siccodedescription, a.new_industryother, a.new_erpsystemname, a.new_erpdescription, convert(varchar,a.new_erplastupdate,103) as ERPLastUpdate, a.parentaccountidname, a.address1_city, a.address1_county from FilteredAccount a right join FilteredContact c on a.accountid = c.accountid where c.contactid="

 

' If the RegardingObjectType is an Opportunity use this SQL
OpportunitySQL = "select a.name, a.accountid, a.revenue, a.numberofemployees, a.sic, a.new_siccodedescription, a.new_industryother, a.new_erpsystemname, a.new_erpdescription, convert(varchar,a.new_erplastupdate,103) as ERPLastUpdate, a.parentaccountidname, a.address1_city, a.address1_county from FilteredAccount a right join FilteredOpportunity o on a.accountid = o.accountid where o.opportunityid="

 

' If the RegardingObjectType is something else use this SQL
DontKnowSQL = "select a.name, a.accountid, a.revenue, a.numberofemployees, a.sic, a.new_siccodedescription, a.new_industryother, a.new_erpsystemname, a.new_erpdescription, convert(varchar,a.new_erplastupdate,103) as ERPLastUpdate, a.parentaccountidname, a.address1_city, a.address1_county from FilteredActivityParty p right join FilteredAccount a on p.partyid = a.accountid where p.participationtypemask = 2 and p.partyobjecttypecode = 1 and p.activityid="

 

%>
<%
Dim WhatRS
Dim WhatRS_cmd
Dim WhatRS_numRows

 

Set WhatRS_cmd = Server.CreateObject ("ADODB.Command")
WhatRS_cmd.ActiveConnection = MM_CRMConn_STRING
WhatRS_cmd.CommandText = WhatSQL
WhatRS_cmd.Prepared = true

 

Set WhatRS = WhatRS_cmd.Execute
Obtype = WhatRS("regardingobjecttypecode")
RegardingId = WhatRS("regardingobjectid")
WhatRS_numRows = 0
WhatRS.Close()

 

' Uses appropriate SQL query for the RegardingObjectType
Select Case Obtype
 Case "1"
  SuperSQL = AccountSQL & "'" & activityid & "'"
 Case "2"
  SuperSQL = ContactSQL & "'" & RegardingId & "'"
 Case "3"
  SuperSQL = OpportunitySQL & "'" & RegardingId & "'"
 Case Else
  SuperSQL = DontKnowSQL & "'" & activityid & "'"
End Select
%>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Phone Call Account Retriever</title>
<style>
body {font-family: tahoma,verdana; font-size:11px;}
table {border: 1 solid #333333;}
td.title {color:#FFFFFF; background-color:#336699;}
td.data {color:#000000; background-color:#eeeeee;}
td.contacts {color:#000000; background-color:#dedede;}
</style>
</head>

 

<body topmargin="0" leftmargin="0">
<table width="420" border="0" cellspacing="1" cellpadding="1">
<tr><td valign="top" width="260">
<%
Set WhatRS_cmd = Server.CreateObject ("ADODB.Command")
WhatRS_cmd.ActiveConnection = MM_CRMConn_STRING
WhatRS_cmd.CommandText = SuperSQL
WhatRS_cmd.Prepared = true

 

Set WhatRS = WhatRS_cmd.Execute
Company = WhatRS("name")
Revenue = WhatRS("revenue")
Employees = WhatRS("numberofemployees")
SIC =  WhatRS("sic")
SICDESC = WhatRS("new_siccodedescription")
IndustryO = WhatRS("new_industryother")
ERP = WhatRS("new_erpsystemname")
ERPDESC = WhatRS("new_erpdescription")
City = WhatRS("address1_city")
County = WhatRS("address1_county")
ERPLastUpdate = WhatRS("ERPLastUpdate")
ParentAccount = WhatRS("parentaccountidname")
AccountId = WhatRS("accountid")
WhatRS_numRows = 0
WhatRS.Close()

 

'Return on the Account data to display
%>
<table width="260" border="0" cellspacing="1" cellpadding="1">
  <tr>
    <td width="80" class="title">Company</td>
    <td class="data"><% =Company %></td>
  </tr>
  <tr>
    <td width="80" class="title">Parent</td>
    <td class="data"><% =ParentAccount %></td>
  </tr>
  <tr>
    <td class="title">Revenue</td>
    <td class="data"><% =Revenue %></td>
  </tr>
  <tr>
    <td class="title">Employees</td>
    <td class="data"><% =Employees %></td>
  </tr>
  <tr>
    <td class="title">SIC</td>
    <td class="data"><% =SIC %>C</td>
  </tr>
  <tr>
    <td class="title">SIC Description</td>
    <td class="data"><% =SICDESC %></td>
  </tr>
  <tr>
    <td class="title">ERP</td>
    <td class="data"><% =ERP %></td>
  </tr>
  <tr>
    <td class="title">ERP Description</td>
    <td class="data"><% =ERPDESC %></td>
  </tr>
  <tr>
    <td class="title">ERP Last Update</td>
    <td class="data"><% =ERPLastUpdate %></td>
  </tr>
  <tr>
    <td class="title">City</td>
    <td class="data"><% =City %></td>
  </tr>
  <tr>
    <td class="title">County</td>
    <td class="data"><% =County %></td>
  </tr>
</table>
</td><td class="contacts" valign="top" width="170">
<%
dim FirstName, LastName, JobTitle, Phone

 

AddiContactSQL = "select c.firstname, c.lastname, c.jobtitle, c.telephone1 from FilteredContact c left join FilteredAccount a on c.accountid = a.accountid where a.accountid = '" & AccountId & "' order by c.lastname"
WhatRS_cmd.CommandText = AddiContactSQL
WhatRS_cmd.Prepared = true

 

Set WhatRS = WhatRS_cmd.Execute
WhatRS_numRows = 0

 

do while not WhatRS.eof
FirstName = WhatRS("FirstName")
LastName = WhatRS("LastName")
JobTitle = WhatRS("JobTitle")
Phone = WhatRS("telephone1")
 %><p><b><% =FirstName %>&nbsp;<% =LastName %>:</b>&nbsp;<% =JobTitle %>&nbsp;<% =Phone %></p><%
WhatRS.movenext
loop

 

WhatRS.Close()
%></td></tr></table>
</body>
</html>
<%

 

Set WhatRS = Nothing
%>

 

Mike Harrison | Technical Marketing Executive | Chelford Group Plc | Direct: 01256 685319 | www.chelfordgroup.com