10-27-2009 07:32 AM
I thought that picklists were in a collection that was accessed by the Client using the Picklist Object.....
Why is SLX running SQL to retrieve the data every time the user clicks on the picklist control button?
---------- Client SQL ---------
SELECT ID, TEXT, SHORTTEXT, ITEMID, USERID FROM PICKLIST WHERE PICKLISTID = 'k6UJ9B00006V' ORDER BY ID
---------- Executed SQL ----------
SELECT PICKLIST.ID, PICKLIST.TEXT, PICKLIST.SHORTTEXT, PICKLIST.ITEMID, PICKLIST.USERID FROM PICKLIST WHERE PICKLIST.PICKLISTID = 'k6UJ9B00006V'
ORDER BY PICKLIST.ID
For some reason I thought this might be faster simply going to the collection of picklists......
10-27-2009 07:52 AM
10-27-2009 08:59 AM
10-29-2009 07:20 AM
I had to write a function to load the picklist items into an array, store the array as a GlobalInfo variable. The picklist gets loaded into the array OnLoginComplete Global Script which may or may not run with every Ctrl-F5 refresh or changed the picklist values SLX refresh (whichever kind reloads the picklist objects).
I'd hate to see a couple hundred arrays or globalinfo variables of arrays in memory, maybe the picklist object and picklist item objects can reference a massive array of picklist values and get their values from there instead of SQL retrieve after SQL retrieve. recall the Aussie developer who loaded up the entire postal code table for OZ into the Account Detail and Contact Detail View???
'strPicklist - Picklist Name as defined in the picklist manager
' Result is the count of items in the picklist,
' also returns an array of the picklist rows datavalues as a GlobalInfo variable.
Function PutPicklistValuesIntoArray(ByVal strPicklist)
DIM pklArray, pklName, pklCN, pklRS, pklSQL
PutPicklistValuesIntoArray = -1
SET pklCN = NEW SLX_DB
SET pklRS = pklCN.GetNewRecordSet
pklSQL = " SELECT [ITEMID], [ID] , [TEXT] , [SHORTTEXT], [PICKLISTID] "
pklSQL = pklSQL & " FROM PICKLIST WHERE PICKLISTID = "
pklSQL = pklSQL & " ( SELECT ITEMID FROM PICKLIST WHERE PICKLISTID = 'PICKLISTLIST' AND [TEXT] = '" & strPicklist & "' ) "
pklSQL = pklSQL & " ORDER BY ID "
application.Debug.WriteLine " PutPicklistValuesIntoArray SQL: " & pklSQL
pklRS.OPEN pklSQL, pklCN.Connection
IF NOT pklRS.EOF THEN
pklArray = pklRS.GETROWS()
PutPicklistValuesIntoArray = pklRS.recordCount
pklArray(0,0) = ""
pklName = "Picklist_Array" '''' this could be the picklist name, but I don't like GlobalInfo variables with SPACES in them.......REPLACE()
application.GlobalInfo.Add pklName, pklArray
SET pklCN = Nothing
SET pklRS = Nothing