
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_SPPrepTempTable
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 = nothingMsgbox “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, iset 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 IfSet 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.
- First we open the specific plugin row.
- Next we use an ADODB.Stream object to stream out the Data value of the plugin into an object.
- 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.
- With the XML file we loop through all of the Stage nodes.
- 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_SPPrepTempTable
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 = nothingMsgbox “Table tmp_FXProcess populated”
End SubSub ReadPlugin(byVal ID)
Dim strSQL, objRS_P
Dim objSTM, objXMLDoc, objCon
Dim strXML, strProcess, strID
Dim objSalesProcessNodes, objSalesProcessNode
Dim objStagesNodes, objStagesNode, objStageNode, objStageNodes, iset 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 IfSet 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 SubSub 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 SubSub 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
If you’re using a .NET Extension (or from some external application) you can use the .NET code I have on Github as well. It reads sales processes but also lets you add/attach a sales process to a record also.
https://github.com/CustomerFX/SalesProcessAdapter