Reading Sales Process details in the SalesLogix LAN client

The details about a Sales Process, like Stages and steps are contained in the Sales Process plugin data row in the Data column.  The data column is a binary field that stores an XML definition of the sales process.  Here is a sample of how you can read the data and do something with it.  In my case I am outputting it to a temporary table that can be used for some purpose.

Lets take a look at the main code, invoked from a button click event:

Sub Button1Click(Sender)
dim strSQL
dim objRS_SP

    PrepTempTable

    strSQL = “select pluginid from vFXSalesProcessPlugins”

‘create view sysdba.vFXSalesProcessPlugins
‘as
‘select pluginid from plugin where (type = 1 and basedon is not null)
‘union all
‘select pluginid from plugin where type = 1 and basedon is null and pluginid not in
‘(select basedon from plugin where type = 1 and basedon is not null)

    Set objRS_SP = CreateObject(“ADODB.Recordset”)
    objRS_SP.CursorType = 3 ‘adOpenStatic
    objRS_SP.CursorLocation = 3 ‘adUseClinet
    objRS_SP.LockType = 3 ‘adLockOptiomistic
    objRS_SP.Open strSQL, application.GetNewConnection
    with objRS_SP
        while not(.bof or .eof)
            ReadPlugin .Fields(“PLUGINID”).Value
            .movenext
        wend
    end with
    objRS_SP.Close
    set objRS_SP = nothing

    Msgbox “Table tmp_FXProcess populated”
End Sub

First off I am calling a method PrepTempTable.  This ensures my SQL temp table is in the database and if not it creates it.  If it is there it clears it out.

Sub PrepTempTable
        Application.GetNewConnection.Execute “exec sysdba.vFXPrepTempTable”

‘create procedure sysdba.vFXPrepTempTable
‘as
‘IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sysdba].[tmp_FXProcess]’) AND type in (N’U’))
‘truncate TABLE [sysdba].[tmp_FXProcess]
‘else
‘create table sysdba.tmp_FXProcess (SPID varchar(12), SPName varchar(96), SPStage varchar(96))
‘GO
End Sub

Next we are getting all the Sales process plugins (These are plugins with a Type=1). With a recordset of the plugins, we need to loop through them and examine them.  In my case I wanted to get the released plugin records for all released sales process as well as unreleased processes.  Writing a SQL query that would pass though the provider was a little to complex so I handled it with a SQL view called “vFXSalesProcessPlugins”.

Inside the record set looping operation I am calling the ReadPlugin sub.  Lets look at this:

Sub ReadPlugin(byVal ID)
Dim strSQL, objRS_P
Dim objSTM, objXMLDoc, objCon
Dim strXML, strProcess, strID
Dim objSalesProcessNodes, objSalesProcessNode
Dim objStagesNodes, objStagesNode, objStageNode, objStageNodes, i

    set objCon = application.GetNewConnection
    strSQL  = “SELECT PLUGINID,NAME,DATA FROM PLUGIN WHERE PLUGINID='”& ID & “‘”
    Set objRS_P = CreateObject(“ADODB.Recordset”)
    objRS_P.CursorType = 3 ‘adOpenStatic
    objRS_P.CursorLocation = 3 ‘adUseClinet
    objRS_P.LockType = 3 ‘adLockOptiomistic
    objRS_P.Open strSQL, objCon  ‘Provider
    ‘ The Data Field is  a blob so we need to stream it in
    Set objSTM = CreateObject(“ADODB.STREAM”)
    objSTM.Type = adTypeBinary
    objSTM.Open
    objSTM.Write objRS_P.Fields(“DATA”).Value
    strID = objRS_P.Fields(“PLUGINID”).Value
    strProcess = objRS_P.Fields(“NAME”).Value
    objRS_P.Close
    set objRS_P = nothing
    ‘ Create XML Object and Load the Sales Process XML in to XML Object
    ‘ ———————————————————————
    objSTM.Position = 0
    objSTM.Type = adTypeText
    objSTM.Charset =”utf-8″
    Set objXMLDoc = CreateObject(“Msxml2.DOMDocument”)
    objXMLDoc.async = false
    strXML = objSTM.ReadText(objSTM.Size)
    If objXMLDoc.loadXML(strXML)= False Then
       msgbox “Invaild Sales Process XML”
       Exit Sub
    End If

    Set objSalesProcessNodes = objXMLDoc.documentElement.selectNodes(“//SalesProcess”)
    Set objSalesProcessNode = objSalesProcessNodes.item(0)
    Set objStagesNodes = objSalesProcessNode.selectNodes(“Stages”)
    Set objStagesNode = objStagesNodes.item(0)
    Set objStageNodes = objStagesNode.selectNodes(“Stage”)
    For i = 0 To objStageNodes.length – 1
        Set objStageNode = objStageNodes(i)
        WriteRecord strID, strProcess, objStageNode.selectSingleNode(“Name”).Text, objCon
    Next ‘ Stage Loop
End Sub

This sub accepts a single parameter, the Plugin ID of the sales process record to read. 

1. First we open the specific plugin row. 
2. Next we use an ADODB.Stream object to stream out the Data value of the plugin into an object.
3. Then we create an XML document and load in the streamed plugin data into this.  At this point we have a workable XML document we can navigate through and read.
4. With the XML file we loop through all of the Stage nodes.
5. Inside the loop we finally call  a WriteRecord sub and pass into it values to use.

The WriteRecord sub inserts data into our temp table:

Sub WriteRecord(byval id, byval name, byval stage, objCon)
dim objRS_SP, strsql
    Set objRS_SP = CreateObject(“ADODB.Recordset”)
    objRS_SP.CursorType = 3 ‘adOpenStatic
    objRS_SP.CursorLocation = 3 ‘adUseClinet
    objRS_SP.LockType = 3 ‘adLockOptiomistic
    strSQL  = “SELECT * FROM tmp_FXProcess WHERE 1=2”
    objRS_SP.Open strSQL, objCon
    objRS_SP.AddNew
    objRS_SP.Fields(“SPID”).Value = id
    objRS_SP.Fields(“SPNAME”).Value = name
    objRS_SP.Fields(“SPSTAGE”).Value = stage
    objRS_SP.Update
    objRS_SP.Close
    set objRS_SP = nothing
End Sub

That is it.  lets take a look at it all together:

option explicit

Sub Button1Click(Sender)
dim strSQL
dim objRS_SP

    PrepTempTable

    strSQL = “select pluginid from vFXSalesProcessPlugins”

‘create view sysdba.vFXSalesProcessPlugins
‘as
‘select pluginid from plugin where (type = 1 and basedon is not null)
‘union all
‘select pluginid from plugin where type = 1 and basedon is null and pluginid not in
‘(select basedon from plugin where type = 1 and basedon is not null)

    Set objRS_SP = CreateObject(“ADODB.Recordset”)
    objRS_SP.CursorType = 3 ‘adOpenStatic
    objRS_SP.CursorLocation = 3 ‘adUseClinet
    objRS_SP.LockType = 3 ‘adLockOptiomistic
    objRS_SP.Open strSQL, application.GetNewConnection
    with objRS_SP
        while not(.bof or .eof)
            ReadPlugin .Fields(“PLUGINID”).Value
            .movenext
        wend
    end with
    objRS_SP.Close
    set objRS_SP = nothing

    Msgbox “Table tmp_FXProcess populated”
End Sub

Sub ReadPlugin(byVal ID)
Dim strSQL, objRS_P
Dim objSTM, objXMLDoc, objCon
Dim strXML, strProcess, strID
Dim objSalesProcessNodes, objSalesProcessNode
Dim objStagesNodes, objStagesNode, objStageNode, objStageNodes, i

    set objCon = application.GetNewConnection
    strSQL  = “SELECT PLUGINID,NAME,DATA FROM PLUGIN WHERE PLUGINID='”& ID & “‘”
    Set objRS_P = CreateObject(“ADODB.Recordset”)
    objRS_P.CursorType = 3 ‘adOpenStatic
    objRS_P.CursorLocation = 3 ‘adUseClinet
    objRS_P.LockType = 3 ‘adLockOptiomistic
    objRS_P.Open strSQL, objCon  ‘Provider
    ‘ The Data Field is  a blob so we need to stream it in
    Set objSTM = CreateObject(“ADODB.STREAM”)
    objSTM.Type = adTypeBinary
    objSTM.Open
    objSTM.Write objRS_P.Fields(“DATA”).Value
    strID = objRS_P.Fields(“PLUGINID”).Value
    strProcess = objRS_P.Fields(“NAME”).Value
    objRS_P.Close
    set objRS_P = nothing
    ‘ Create XML Object and Load the Sales Process XML in to XML Object
    ‘ ———————————————————————
    objSTM.Position = 0
    objSTM.Type = adTypeText
    objSTM.Charset =”utf-8″
    Set objXMLDoc = CreateObject(“Msxml2.DOMDocument”)
    objXMLDoc.async = false
    strXML = objSTM.ReadText(objSTM.Size)
    If objXMLDoc.loadXML(strXML)= False Then
       msgbox “Invaild Sales Process XML”
       Exit Sub
    End If

    Set objSalesProcessNodes = objXMLDoc.documentElement.selectNodes(“//SalesProcess”)
    Set objSalesProcessNode = objSalesProcessNodes.item(0)
    Set objStagesNodes = objSalesProcessNode.selectNodes(“Stages”)
    Set objStagesNode = objStagesNodes.item(0)
    Set objStageNodes = objStagesNode.selectNodes(“Stage”)
    For i = 0 To objStageNodes.length – 1
        Set objStageNode = objStageNodes(i)
        WriteRecord strID, strProcess, objStageNode.selectSingleNode(“Name”).Text, objCon
    Next ‘ Stage Loop
End Sub

Sub WriteRecord(byval id, byval name, byval stage, objCon)
dim objRS_SP, strsql
    Set objRS_SP = CreateObject(“ADODB.Recordset”)
    objRS_SP.CursorType = 3 ‘adOpenStatic
    objRS_SP.CursorLocation = 3 ‘adUseClinet
    objRS_SP.LockType = 3 ‘adLockOptiomistic
    strSQL  = “SELECT * FROM tmp_FXProcess WHERE 1=2”
    objRS_SP.Open strSQL, objCon
    objRS_SP.AddNew
    objRS_SP.Fields(“SPID”).Value = id
    objRS_SP.Fields(“SPNAME”).Value = name
    objRS_SP.Fields(“SPSTAGE”).Value = stage
    objRS_SP.Update
    objRS_SP.Close
    set objRS_SP = nothing
End Sub

Sub PrepTempTable
        Application.GetNewConnection.Execute “exec sysdba.vFXPrepTempTable”

‘create procedure sysdba.vFXPrepTempTable
‘as
‘IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sysdba].[tmp_FXProcess]’) AND type in (N’U’))
‘truncate TABLE [sysdba].[tmp_FXProcess]
‘else
‘create table sysdba.tmp_FXProcess (SPID varchar(12), SPName varchar(96), SPStage varchar(96))
‘GO
End Sub