11/26/2024 7:26:01 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss writing script in Architect plugins for SalesLogix & general SalesLogix customization topics (for Windows client only). View the code of conduct for posting guidelines.
|
|
|
|
Dynamic Picklist Population
Posted: 18 Aug 06 3:15 PM
|
fiogf49gjkf0d On the Opportunity Detail screen I have added a picklist field with the picklistName of Business Unit. This is not tied to a database field. Next on the screen is the Opp Product field which is tied to Opportunity.SubType. A request came in to add the Business Unit field to this screen and to allow the Opp Product picklist to display only products associated with the selected Business Unit. For various reasons, I cannot use multiple picklists to create the heirarchy of the relationship between these two fields. So, I have written code to dynamically populate a picklist (Dynamic Opp Product) based on the Business Unit Picklist and the Opp Products picklist. When I click on the Business Unit button to get the associated picklist, I select one value and then click the button on the Opp Products field. The Opp Products field picklist is then populated with only those products associated with the selected Business unit. However, this only works on the first time that I come into SLX. If I scroll to the next Opp record and select a business unit, the previous Opp Product list comes up even though the values (via QA) are set to the current business unit's opp products. I have to select an invalid Opp Product, which then changes the Opp Product.Text = "", then click on the elipses button again to get the correct corresponding Opp Product picklist. I am not sure where/what I need to do to get the picklist to "refresh". Any thoughts/suggestions?
Here is my coding; the SetBusinessUnit function is used to set the BU appropriately when scrolling through Opps since the Business Unit is not tied to a specific field in the database. That part works slick!
Sub AXFormChange(Sender)
strCrntOppID = frmOpportunityDetail.CurrentID
Application.BasicFunctions.ProcessWindowMessages 'This call is used because the browser control will sometimes
'returen invalid character to the HTML property causing the
'browser control to come up blank.
If strCrntOppID = "" then
'Global is needed because the OppID gets cleared if Insert Opp is called from the Account View
strCrntOppID = Application.BasicFunctions.GlobalInfoFor("OppID") 'DNL
Application.BasicFunctions.GlobalInfoClear "OppID" 'DNL
End If
LoadSnapShot
SetDateFormat
SetColorForStatus
pnlSnapShot.Enabled = Not pklStatus.ReadOnly
if pklOppProduct.Text <> "" then
SetBusinessUnit
else
pklBusUnit.Text = ""
end if
End Sub
Sub SetBusinessUnit
dim rs
dim strOppProdTC
dim strBusUnitPID
set rs = createobject("ADODB.Recordset")
set rs.activeconnection = application.GetNewConnection
rs.open "Select ShortText From Picklist Where Text = '" & pklOppProduct.Text & "' and picklistid in (select itemID from picklist where text = 'Opp Products')"
strOppProdTC = rs.Fields("ShortText").Value
rs.close
set rs = nothing
set rs = createobject("ADODB.Recordset")
set rs.activeconnection = application.GetNewConnection
rs.open "Select ItemID From PickList Where Text = 'Business Unit'"
strBusUnitPID = rs.Fields("ItemID").Value
rs.close
set rs = nothing
set rs = createobject("ADODB.Recordset")
set rs.activeconnection = application.GetNewConnection
rs.Open "Select Text from PickList where PickListID = '" & strBusUnitPID & "' and ShortText = '" & strOppProdTC & "'"
pklBusUnit.Text = rs.fields("Text").Value
rs.close
set rs = nothing
End Sub
'SRS 8/2006 Created to dynamically populate a picklist from another picklist
Sub BuildPickList(ByVal strListName, ByVal strTableName, ByVal strFieldName, ByVal intIncrement)
Dim strSQL
dim strDynamicPID
dim intRecCount
dim intSeqNO
dim i
dim strValue
dim strPID
dim objRS, objOppProdRS
set objRS = CreateObject("ADODB.Recordset")
set objRS.ActiveConnection = Application.GetNewConnection
'Get the ItemID for the Given Picklist Name
strSQL = "Select ItemID From PickList Where Text = '" & strListName & "'"
objRS.Open strSQL
if objRS.RecordCount = 0 then
msgbox "PickList name " & strListName & " not found."
else
strDynamicPID = objRS.Fields("ItemID").Value
end if
objRS.Close
set objRS = nothing
'Get new items to populate picklist
set objRS = CreateObject("ADODB.Recordset")
set objRS.ActiveConnection = Application.GetNewConnection
set objOppProdRS = CreateObject("ADODB.Recordset")
set objOppProdRS.ActiveConnection = Application.GetNewConnection
objOppProdRS.CursorLocation = adUseClient
objOppProdRS.LockType = adLockBatchOptimistic
objOppProdRS.CursorType = adOpenStatic
if pklBusUnit.TextCode = "" then
MsgBox "No value selected. Please select the appropriate Business Unit."
pklBusUnit.SetFocus
pklBusUnit.Popup
exit sub
else
strSQL = "Select " & strFieldName & ", ShortText From " & strTableName & " Where ShortText = '" & pklBusUnit.TextCode & _
"' And PickListID = (Select ItemID From PickList Where Text = 'Opp Products')"
end if
objRS.Open strSQL
if objRS.RecordCount = 0 then
MsgBox "No value selected. Please select the appropriate Business Unit."
pklBusUnit.SetFocus
exit sub
end if
intSeqNo = intIncrement
'Clear the old picklist
Application.GetNewConnection.Execute "Delete From Picklist Where PickListID = '" & strDynamicPID & "'"
'Populate the new picklist
For i = 1 to objRS.RecordCount
strValue = objRS.Fields(strFieldName).Value
objOppProdRS.Open "Select * from Picklist where PickListID = '" & strDynamicPID & "'"
objOppProdRS.AddNew
strPID = Application.BasicFunctions.GetIDFor("PickList")
objOppProdRS("PickListID") = strDynamicPID
objOppProdRS("ItemID") = strPID
objOppProdRS("ID") = cStr(intSeqNo)
objOppProdRS("Text") = strValue
objOppProdRS("ShortText") = objRS.Fields("ShortText").Value
objOppProdRS("UserID") = "ADMIN"
objOppProdRS("DefaultIndex") = "-1"
objOppProdRS.UpdateBatch
objOppProdRS.Close
objRS.MoveNext
intSeqNo = intSeqNo + intIncrement
Next
objRS.Close
set objRS = nothing
set objOppProdRS = nothing
End Sub
Sub pklBusUnitChange(Sender)
Call BuildPicklist("Dynamic Opp Product","PickList","Text",1)
End Sub
 |
|
|
|
Re: Dynamic Picklist Population
Posted: 19 Aug 06 5:18 PM
|
fiogf49gjkf0d Is there any reason you cant use a Dropdown listbox instead. It will save you some coding. Otherwise I think you will have to force a full database refresh (Ctrl-F5) to get the picklist to update.
Just a thought.
Brian Segers |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|