Microsoft CRM 3.0 Experiences: Code for MS CRM Improved Phonecall Screen for Telemarketing

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>

<%
' 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 %> <% =LastName %>:</b> <% =JobTitle %> <% =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

Microsoft CRM 3.0 Experiences

Wednesday, January 02, 2008

Code for MS CRM Improved Phonecall Screen for Telemarketing

0 Comments:

About

About Me

Previous