Question: Do you have an example for how to add fields to the treeview in the Add
Opportunities Products screen in SalesLogix? I'm trying to show the
actual id, quantity, and name but it seems like only one field can be
displayed.
Answer: Use a separator to concatenate the fields together:
'Including Script - System:SLX Database Support
'Including Script - System:SLX_Common
'Including Script - System:ColorConstants
option explicit
Dim gAcctID 'Global AccountID for this form
DIM gParentID
DIM gSelectedAcctID
Dim gMode 'Global Mode variable (Add or Edit)
Dim objSLXDB 'Global SLX DB Object for this form
Dim retValue 'function Return Value
Dim gOldCurrValue
Dim gCurrentValue 'Value of the grid cell when the edit started
Dim InitialLoadDone
DIM CurrentParentNode
DIM CurrentNodeText
DIM CurrentNodeType
SUB INIT(AcctID)
InitialLoadDone = False
lblTreeLoading.Caption = Application.Translator.
Localize ("Click to Load Parents and Members...")
gAcctID = AcctID
frmparentmembertreeview.CurrentID = AcctID
SET objSLXDB = NEW SLX_DB
application.BasicFunctions.ProcessWindowMessages
application.BasicFunctions.ProcessWindowMessages
cmdAddChildren.Visible = FALSE
cmdAddCounty.Visible = FALSE
cmdClearTree.Visible = FALSE
cmdClearChildren.Visible = FALSE
End Sub
Sub AXFormOpen(Sender)
cmdAddChildren.Visible = FALSE
cmdAddCounty.Visible = FALSE
cmdClearTree.Visible = FALSE
cmdClearChildren.Visible = FALSE
End Sub
Sub AXFormChange(Sender)
InitialLoadDone = False
lblTreeLoading.Caption = Application.Translator.Localize ("Click to Load Parents and Members...")
gAcctID = txtAccountID.Text
SET objSLXDB = NEW SLX_DB
application.BasicFunctions.ProcessWindowMessages
application.BasicFunctions.ProcessWindowMessages
cmdAddChildren.Visible = FALSE
cmdAddCounty.Visible = FALSE
cmdClearChildren.Visible = FALSE
End Sub
Sub cmdTreeRefreshClick(Sender)
Call LoadTree
cmdAddChildren.Visible = FALSE
cmdAddCounty.Visible = FALSE
cmdClearTree.Visible = TRUE
End Sub
Sub treeSDRSClick(Sender)
gSelectedAcctID = Sender.Selected.Tag
SET CurrentParentNode = Sender.Selected
cmdAddChildren.Visible = TRUE
cmdClearChildren.Visible = TRUE
CurrentNodeText = Sender.SELECTED.TEXT
IF LEFT(CurrentNodeText, 6) = "County" THEN
CurrentNodeType = "County"
cmdAddCounty.Visible = FALSE
ELSE
CurrentNodeType = ""
cmdAddCounty.Visible = TRUE
END IF
End Sub
Sub lblTreeLoadingClick(Sender)
If InitialLoadDone = False then
lblTreeLoading.Caption = "Loading ..."
lblTreeLoading.Visible = True
application.BasicFunctions.ProcessWindowMessages
application.BasicFunctions.ProcessWindowMessages
Call LoadTree()
lblTreeLoading.Visible = False
InitialLoadDone = True
End If
End Sub
Sub treeSDRSDblClick(Sender)
Dim objNode
Dim NewLineID
If treeSDRS.Selected is Nothing then Exit Sub
If Sender.Selected.Tag = "" Then Exit Sub
cmdAddChildren.Visible = FALSE
set objNode = Sender.Selected
gAcctID = Sender.Selected.Tag
application.BasicFunctions.SetCurrentAccountID gAcctID
End Sub
'------------------------------------------------------------------------------------------
Sub LoadTree
Dim rsSDRS
Dim objNewNode
DIM objRootNode
DIM objParentNode
DIM objMemberNode
Dim SQL
Dim strAccountID
Dim strType
Dim strAccount
Dim strPID
Dim strParentID
DIM strEnrollment
treeSDRS.Nodes.Clear
gAcctID = "" & txtAccountID.Text
IF gACCTID = "" THEN EXIT SUB
gParentID = "" & txtParentAccountID.TEXT
IF gParentID = "" THEN EXIT SUB
SQL = BuildParentAccounts_SQL_PV(gAcctID, gParentID )
IF SQL = "" THEN EXIT SUB
SET objSLXDB = NEW SLX_DB
Set rsSDRS = objSLXDB.GetNewRecordSet
rsSDRS.Open SQL, objSLXDB.Connection
Set objParentNode = treeSDRS.Nodes.Add (Nothing, "BEGIN: ")
While not (rsSDRS.EOF)
strAccountID = "" & rsSDRS.Fields(0).Value
strType = "" & rsSDRS.Fields(1).Value ''''This is now Hierarchy Level CODE as of February 9, 2008 RJ Samp.
strAccount = "" & rsSDRS.Fields(2).Value
strPID = "" & rsSDRS.Fields(3).Value
strParentID = "" & rsSDRS.Fields("PARENTID").Value
strEnrollment = "" & rsSDRS.Fields("ENROLLMENT").Value
Set objParentNode = treeSDRS.Nodes.AddCHILD (objParentNode,
strType & ": " & strAccount & " : " & strPID & " : "
& strEnrollment )
objParentNode.TAG = strAccountID
rsSDRS.MoveNext
Wend
rsSDRS.Close
Set rsSDRS = Nothing
TreeSDRS.FullExpand
''' TreeSDRS.FullCollapse
gSelectedAcctID = ""
cmdAddChildren.Visible = FALSE
End Sub
FUNCTION BuildParentAccounts_SQL_PV(TID, PID)
DIM Parent_AccountID
DIM This_AccountID
DIM sqlSelect
DIM sqlFROM
DIM sqlWHERE
DIM sqlOrderBY
BuildParentAccounts_SQL_PV = ""
IF TID = "" THEN EXIT FUNCTION
This_AccountID = TID
Parent_AccountID = PID
sqlSelect = "SELECT DISTINCT A1.ACCOUNTID, A1.HIERARCHY_LEVEL_CODE, A1.ACCOUNT, A1.PID, A1.ENROLLMENT, "
sqlSelect = sqlSelect & " A1.HIERARCHY_LEVEL, A1.PARENTID "
sqlFROM = " FROM ACCOUNT A1 "
sqlWhere = " WHERE (A1.ACCOUNTID = '" & THIS_AccountID & "' ) "
sqlOrderBy = " ORDER BY A1.HIERARCHY_LEVEL DESC, A1.ACCOUNT "
IF Parent_AccountID > "" THEN
'''
sqlWhere = sqlWhere & " OR (A1.ACCOUNTID = '" & Parent_AccountID & "' )"
While NOT (Parent_AccountID = "")
'''' This SQL Call keeps going farther and farther up the ParentID Chain.
Parent_AccountID = "" & GetField("PARENTID", "ACCOUNT", "ACCOUNTID = '" & Parent_AccountID & "' ")
IF Parent_AccountID > "" THEN
sqlWhere = sqlWhere & " OR (ACCOUNTID = '" & Parent_AccountID & "' )"
END IF
''' MSGBOX Parent_AccountID
WEND
END IF
BuildParentAccounts_SQL_PV = sqlSelect & sqlFROM & sqlWHERE & sqlOrderBY
APPLICATION.Debug.WriteLine " BuildParentAccounts_SQL: " & BuildParentAccounts_SQL_PV
END Function
Function treeSDRSGetSelectedIndex(Sender, ByRef Node)
End Function
Sub cmdAddChildrenClick(Sender)
''''' MSGBOX gSelectedAcctID
Dim rsSDRS
DIM objParentNode
Dim SQL
Dim strAccountID
Dim strType
Dim strAccount
Dim strPID
DIM strEnrollment
IF gSelectedAcctID = "" THEN EXIT SUB
Set rsSDRS = objSLXDB.GetNewRecordSet
IF CurrentNodeType <> "County" THEN
SQL = "SELECT ACCOUNTID, HIERARCHY_LEVEL_CODE, ACCOUNT, PID,
ENROLLMENT FROM ACCOUNT WHERE PARENTID = '" & gSelectedAcctID &
"' ORDER BY ACCOUNT "
ELSE
DIM COUNTYCODE
COUNTYCODE = TRIM(RIGHT(CurrentNodeTEXT, LEN(CurrentNodeText) - 8))
SQL = "SELECT ACCOUNTID, HIERARCHY_LEVEL_CODE, ACCOUNT, PID,
ENROLLMENT FROM ADDRESS AD INNER JOIN ACCOUNT AC ON AD.ADDRESSID =
AC.ADDRESSID "
SQL = SQL & "WHERE COUNTY = '" &
COUNTYCODE & "' AND PARENTID = '" & gSelectedAcctID & "'
ORDER BY ACCOUNT "
END IF
rsSDRS.Open SQL, objSLXDB.Connection
While not (rsSDRS.EOF)
strAccountID = "" & rsSDRS.Fields(0).Value
strType = "" & rsSDRS.Fields(1).Value ''''This is now Hierarchy Level CODE as of February 9, 2008 RJ Samp.
strAccount = "" & rsSDRS.Fields(2).Value
strPID = "" & rsSDRS.Fields(3).Value
strEnrollment = "" & rsSDRS.Fields(4).Value
Set objParentNode = treeSDRS.Nodes.AddCHILD
(CURRENTParentNode, strType & ": " & strAccount & " : "
& strPID & " : " & strEnrollment)
objParentNode.TAG = strAccountID
rsSDRS.MoveNext
Wend
rsSDRS.Close
Set rsSDRS = Nothing
TreeSDRS.FullExpand
''' TreeSDRS.FullCollapse
gSelectedAcctID = ""
cmdAddChildren.Visible = FALSE
cmdClearChildren.Visible = FALSE
End Sub
Sub cmdClearTreeClick(Sender)
treeSDRS.Nodes.Clear
cmdClearTree.Visible = FALSE
cmdAddChildren.Visible = FALSE
cmdClearChildren.Visible = FALSE
cmdAddCounty.Visible = False
End Sub
Sub cmdAddCountyClick(Sender)
''''' MSGBOX gSelectedAcctID
Dim rsSDRS
DIM objParentNode
Dim SQL
Dim strCounty
cmdClearChildren.Visible = FALSE
cmdAddCounty.Visible = False
IF gSelectedAcctID = "" THEN EXIT SUB
Set rsSDRS = objSLXDB.GetNewRecordSet
SQL = "SELECT DISTINCT COUNTY FROM ADDRESS AD INNER JOIN ACCOUNT AC
ON AD.ADDRESSID = AC.ADDRESSID WHERE COUNTY IS NOT NULL AND PARENTID =
'" & gSelectedAcctID & "' ORDER BY COUNTY "
rsSDRS.Open SQL, objSLXDB.Connection
While not (rsSDRS.EOF)
strCounty = "" & rsSDRS.Fields(0).Value
Set objParentNode = treeSDRS.Nodes.AddCHILD (CURRENTParentNode, "County: "& strCounty )
objParentNode.TAG = gSelectedAcctID
rsSDRS.MoveNext
Wend
rsSDRS.Close
Set rsSDRS = Nothing
TreeSDRS.FullExpand
End Sub
Sub cmdClearChildrenClick(Sender)
CurrentParentNode.DeleteChildren
cmdClearChildren.Visible = FALSE
End Sub