Picklist Collection vs SQL retrieval

[RJSamp]

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......

[RJLedger]

Remember the PickList object is something that was added later on and existing code still does a SQL access.

[tagersea]

There is only one picklist control so Sage only has to fix it once. We need every ounce of performance improvement so I hope Sage addresses this sooner than later.

Nice catch Samp.

[RJSamp]

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 objPicklist
    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
    ELSE
        REDIM pklArray(0,0)
        pklArray(0,0) = ""
    END IF
    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
End Function