MS CRM Customization - How to Qualify Leads in CRM - Set Lead State and Status in CRM

Title:       MS CRM Customization - How to Qualify Leads in CRM - Set Lead State and Status in CRM
Author:      Greg Dubinovskiy 
Email:       [email protected]
Environment: Microsoft CRM, SQL Server, SQL
Keywords:    Microsoft CRM, MS CRM Customization, MS CRM Leads, How to Qualify Leads in CRM, Set Lead State and Status in CRM, MS CRM Customization Tools, ASP.NET, VB.NET
Level:       Intermediate
Description: Build Process to Qualify Leads
Section      Miscellaneous
SubSection   General

Siccolo Development Articles - MS CRM Customization - How to Qualify Leads in CRM - Set Lead State and Status in CRM

Introduction

The article presented bellow, shows how to setup a process to allow qualify Leads in Microsoft CRM.

In MS CRM, there is a "Convert Lead" process, that allows to Convert a Lead to Account/Contact/Opportunity:

So, it will Create an Account, or Contact, or Opportunity and set Lead status to Qualified

Purposes of this article is to show how to setup a process/flow to only qualify a Lead without creating a Account/Contact/Opportunity record:

Customizing CRM is not hard at all, thanks to semi-open web interface and SQL Server database. This article shows how to develop your own CRM Customization Tool for your business solutions.

Quickly customize Microsoft CRM - CRM Leads - so that you capture and manage the exact data you need to increase sales and service effectiveness. Customize the Microsoft CRM user interface - Learn how to customize the Microsoft CRM user interface by creating and modifying forms, views, and previews. Microsoft CRM allows system integrators to create a highly customized and integrated application. In addition, the solution supports flexible options that enable users to focus only on the features they need to perform the tasks their jobs require.

(see other Siccolo articles about working with Microsoft CRM:
MS CRM Customization - Import Leads from Excel via Email - Create MS CRM Leads Automatically
and MS CRM Customization - How to add custom CRM Report to Reports Grid in CRM )

2. Create user interface - Qualify Lead - Part I - creating data tier

For this purposes, I'm using VS 2003 (but it can be easily converted to VS 2005!), ASP.NET/VB.NET:

- extract data from MS CRM database - LoadLeadInfo()
- set Lead status in MS CRM database - QualifyLead()

Imports System.Data
Imports System.Data.SqlClient

Public Class LeadInfo


    Public Lead_Subject As String


    Public Lead_State As String	
    Public Lead_Status As String

    Public Lead_StateCode As Integer
    Public Lead_StatusCode As Integer


    Public Function LoadLeadInfo(ByVal LeadID As String, _
                                    ByRef ErrorInfo As String) As Boolean

        Try

            Dim strConnectInfo As String = - [your connect string to SQL Server hosting MS CRM database] -
            Dim objConnection As SqlConnection = New SqlConnection(strConnectInfo)

            Dim strSQL As String
            strSQL = "exec dbo.p_Lead_LeadInfo " & "'" & LeadID & "'"

            Dim objCommand As SqlCommand = New SqlCommand(strSQL, objConnection)
            objConnection.Open()

            Dim objData As SqlClient.SqlDataReader
            objData = objCommand.ExecuteReader(System.Data.CommandBehavior.SingleRow Or System.Data.CommandBehavior.CloseConnection)

            With objData
                .Read()
                
		' read Lead information, such as Lead topic:
		Lead_Subject = .GetString(.GetOrdinal("Subject"))

		' we also need Lead State and Lead status:!

                
		Lead_State = .GetString(.GetOrdinal("StateCodeString"))
                Lead_Status = .GetString(.GetOrdinal("StatusCodeString"))

                Lead_StateCode = .GetInt32(.GetOrdinal("StateCode"))
                Lead_StatusCode = .GetInt32(.GetOrdinal("StatusCode"))
		

                .Close()
            End With

            objConnection.Close()

            Return True

        Catch e As Exception
            ErrorInfo = e.ToString
            Return False
        End Try

    End Function

where SQL stored procedure to retrieve Lead record from MS CRM database:

create procedure p_Lead_LeadInfo
(
	@lead_id varchar(255)
)
as
	set nocount on

	select isnull( upper(Subject) , '')		as Subject

		, StateCode
		, StatusCode

		, (select value from dbo.StringMap
			where ObjectTypeCode = 4	-- Leads
				and AttributeName = 'StateCode'
				and AttributeValue = l.StateCode)	as StateCodeString

		, (select value from dbo.StringMap
			where ObjectTypeCode = 4	-- Leads
				and AttributeName = 'StatusCode'
				and AttributeValue = l.StatusCode)	as StatusCodeString

	from dbo.Lead l
	where LeadID = @lead_id

	set nocount off

and same idea for QualifyLead() function:

     Public Function QualifyLead(ByVal LeadID As String, _
                                    ByRef ErrorInfo As String) As Boolean

        Try

            Dim strConnectInfo As String = - [your connect string to SQL Server hosting MS CRM database] -
            Dim objConnection As SqlConnection = New SqlConnection(strConnectInfo)

            Dim strSQL As String
            strSQL = "exec dbo.p_Lead_QualifyLead " & "'" & LeadID & "'"

            Dim objCommand As SqlCommand = New SqlCommand(strSQL, objConnection)

            objConnection.Open()

            Dim Rows As Integer = objCommand.ExecuteNonQuery()

            objConnection.Close()

            Return True

        Catch e As Exception
            ErrorInfo = e.ToString
            Return False
        End Try

    End Function

where SQL stored procedure to change Lead status in MS CRM database:

create procedure p_Lead_QualifyLead
(
	@lead_id varchar(255)
)
as
	set nocount on
	update dbo.Lead
		set StateCode = 1
		, StatusCode = 3

	where LeadID = @lead_id
	set nocount off

and to "undo", to revert Lead status back to Open/New:

	update LeadBase 
		set StateCode =0
		, StatusCode = 1
	where LeadID = - [Lead ID value] -

2. Create user interface - Qualify Lead - Part II - creating user inetrface layer

In qualify_lead.aspx code:
User interface loads - show current Lead information, such as Lead Topic, and Lead status:

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        Try
            Dim LeadID As String
            'load data here
            If Not IsPostBack() Then
                LeadID = Request("oId")
                If LeadID = "" Then
                    Me.lblLeadName.Text = "Lead: Unknown"
                    ShowError("Lead ID is missing!")
                Else
                    Dim ErrorInfo As String
                    If Not LoadLeadInfo(LeadID, ErrorInfo) Then
                        ShowError(ErrorInfo)
                    End If
                End If


            End If
            ' Page.DataBind()
        Catch ex_load_page As Exception
            ShowError("Failed to load page:" & ex_load_page.ToString())
        End Try
    End Sub

where LoadLeadInfo() function:

    Private Function LoadLeadInfo(ByVal LeadID As String, ByRef ErrorInfo As String) As Boolean
        Try

            Dim objLeadInfo As New LeadInfo()

            If Not objLeadInfo.LoadLeadInfo(LeadID, ErrorInfo) Then
                Throw New System.Exception("Failed to retrieve Lead info! Error:[" & ErrorInfo & "]")
            End If

            With objLeadInfo
                Me.lblLeadName.Text = "Lead: " & " [" & LeadID & "]"
                Me.lblUserID.Text = Request.ServerVariables("LOGON_USER")

                Me.lblLeadStatus.Text = "Lead: " & " - Status: " & .Lead_Status & "/" & .Lead_State & ""
                If .Lead_StateCode = 1 Or .Lead_StatusCode = 3 Then
                    'already qualified!
                    Me.lblLeadStatus.Text = Me.lblLeadStatus.Text & "- Already Qualified!" & ""
		    'do not allow to Qualify already qualified Lead!
                    Me.cmdQualify.Enabled = False
                Else
                    'ok
                End If
            End With

            Return True

        Catch e As Exception
            lblErrorInfo.Text = e.ToString
            lblLeadName.Text = e.Message
            lblErrorInfo.Visible = True
            Return False
        End Try
    End Function

Once user clicked on a button to Qualify a Lead:

   Private Sub cmdQualify_Click(ByVal sender As System.Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles cmdQualify.Click
        Dim ErrorInfo As String
        If Not QualifyLead(ErrorInfo) Then
            ShowError("Failed to Qualify Lead! - " & ErrorInfo)
        End If
    End Sub

    Private Function QualifyLead(ByRef ErrorInfo As String) As Boolean

        Dim Result As Boolean


        Try
            Dim LeadID As String = Request("oId")
            If LeadID = "" Then
                Throw New System.Exception("Lead ID is missing!")
            End If

            Dim objLeadInfo As New LeadInfo()

            If Not objLeadInfo.QualifyLead(LeadID, ErrorInfo) Then
                'show error!
                Throw New System.Exception("Failed to Qualify Lead! Error:[" & ErrorInfo & "]")
            Else
                'refresh screen!
                LoadLeadInfo(LeadID, ErrorInfo)
            End If

            Return True

        Catch e As Exception
            lblErrorInfo.Text = e.ToString
            lblLeadName.Text = e.Message
            lblErrorInfo.Visible = True
            Return False
        End Try

    End Function

3. Modifing MS CRM Lead screen

So, at this point, we have a user interface to set/change Lead status to Qualify. Now, we just need to add a button to menu on Lead Screen:

For this, we need to modify isv.config file, under [MS CRM web server]\inetpub\wwwroot\_Resources:

And that's it. We just developed our own CRM customization tool. As you can see Microsoft CRM Customization can be done with various tools.

4. Customizing the Microsoft CRM Application

Main navigation pane - You can add side tabs to the navigation pane (on the left) in each module: Home, Workplace, Sales, Service, and Reports.
Global menu bar - You can add menus between the GoTo menu and the Help menu in the global menu bar.
Detail form menu bar - You can add menus to the left of the Help menu to the following detail forms: Account, Contact, Lead, Opportunity, Incident (Case), Quote, SalesOrder (Order), and Invoice.
Detail form toolbar - You can add buttons to the toolbar on the following detail forms: Account, Contact, Lead, Opportunity, Incident (Case), Quote, SalesOrder (Order), and Invoice.
Detail form navigation pane - You can add side tabs to the navigation pane (on the left) in the following detail forms: Account, Contact, Lead, Opportunity, Incident (Case), Quote, SalesOrder (Order), and Invoice.

Integration setup You can find the XML file for application integration on the Microsoft CRM Web server at: [installation drive and folder]\wwwroot\_Resources\isv.config

Edit this file to add menus, side tabs, and buttons to your system. Making a copy of the original configuration file before editing is recommended.

The Microsoft CRM application reads this configuration file when the application is started.

Customization file
To enable your customizations, you set the ISVIntegration setting in the web.config file to On. Web.config is located at: [installation drive and folder]\wwwroot\web.config The ISVIntegration setting is used as a performance optimization. When there are no customizations to be rendered, the value for this setting should be Off.

Value: On or Off

Parameter passing When a user selects a customized menu item, side tab, or button, the specified URL opens. Application-level links open in the same window. Detail form–level links open in a new window. The current object type and object ID are passed as parameters if the PassParams flag is set to 1. (For more information, see the XML Reference topic.)

Integration into a Detail Form Toolbar
You can add custom buttons to the toolbar in detail forms. When a user clicks one of these buttons, the URL opens in a new window. The following figure shows two Test buttons on an account detail form.

ToolBar
The ToolBar element represents a toolbar that is added to an object detail form in the Web application configuration file isv.config. Example

<ToolBar ValidForCreate="0" ValidForUpdate="1">
   <Button Title="Test" ToolTip="Info on Test"
        Icon="/_imgs/ico_18_debug.gif"
        Url="http://www.microsoft.com"
        PassParams="1" WinParams="" WinMode="0"/>
   <ToolBarSpacer/>
   <Button Title="Test" ToolTip="Info on Test"
        Icon="/_imgs/ico_18_debug.gif"
        Url="http://www.microsoft.com"
        PassParams="1" WinParams="" WinMode="1"/> 
</ToolBar>

Toolbar Attributes

ValidForCreate - Specifies whether the toolbar should be shown in the detail form window when an object is being created.
ValidForUpdate - Specifies whether the toolbar should be shown in the detail form window when an object is being updated.

History

no improvements so far. nearly perfect.

Article keywords: Microsoft CRM customization, CRM Management, Leads, OPENROWSET, @@error, @@rowcount, t_create_lead trigger, xp_readmail, xp_findnextmsg, xp_sendmail, xp_deletemail, CRM SystemUser

Back To Articles Page

Free Mobile Management For SQL Server(s!) - Siccolo - SQL Management Tool

Questions? Suggestions? Concerns? - email me to [email protected] Greg Dubinovsky © 2007
or share your thoughts at Siccolo Blog

web sponsor - siccolo.com. well being sponsor - Enabling clinical and operational value across the continuum of care.

Siccolo - SQL Server Management Tool For Mobile Devices is packed with built-in functionality and tools. Siccolo delivers a rich set of management tools for both DBAs and sys admins. SQL Server management has always been an area of DBA concern. The new Management Tool For Mobile Devices - Siccolo - has simple "Enterprise Manager" and the "Query Analyzer". Siccolo is a management tool for the MS SQL Server with administration capabilities and a database query tool. The administration features provide users the ability to browse database structures. An integrated query tool allows users to quickly create, edit and execute SQL queries and scripts. Siccolo also provides an export tool to allow users to easily save and email execution results. Siccolo helps database professionals save time and increase their productivity by utilizing a more efficient approach to database management - use their Windows Mobile empowered device while sipping margarita on the beach For increased security, Siccolo is configured to run under SSL with IIS authentication.

Siccolo

Run SQL Query window (with semi-automated Insert/Update/Delete/Select Statements) Open/Save SQL Scripts in multiple tabs Save/Email results
Explore SQL Server Browse databases and database objects (tables/views/stored procedures) Browse/Manage SQL Server jobs Browse/Manage windows services (start/stop) View event logs (Application, System)
Manage SQL Server - restart MS SQL Server service and SQL Agent