Dynamic/SQL Drop down list for Business Operation Property Setting

Hi, I’m trying to setup a Business Operation where the Property is a drop down list of Strings.  I was able to produce the desired effect but only using a hard coded list like Property LookupProp As %String(VALUELIST = ",value1,value2").  Instead I would like it to be populated from lookup table so I created a method to use SQL to fetch the result set from a Lookup table and then return it as the desired string. But doesn't allow me to do declare the property using the method like this: Property LookupProp As %String(VALUELIST = ##class(MyClass).GetLookupValues());

ClassMethod GetLookupValues() As %String
{
                SET keyQuery = "SELECT DataValue FROM Ens_Util.LookupTable WHERE TableName = 'KeyLookup' order by DataValue"
                write !,keyQuery
                SET tStatement = ##class(%SQL.Statement).%New()
                SET qStatus = tStatement.%Prepare(keyQuery)
                IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
                SET pOutput = tStatement.%Execute()
               
                // do pOutput.%Display()
                set tReturnList = ""
                WHILE pOutput.%Next() {
                                // write !,"pOutput.DataValue "_pOutput.DataValue
                                // set $LIST(tReturnList, *+1) = pOutput.DataValue
                                set tReturnList = tReturnList_","_pOutput.DataValue
               
                return tReturnList
}

Is there some way to do this?  

Thanks  in advance!

Answers

You can write a custom selector.

Check this doc: section "Passing Values to the context Property of a Control".

Examples are available in Ens.ContextSearch.
 

Hi Eduard,

Thanks for the response, yes you mentioned this in a similar thread: https://community.intersystems.com/post/set-value-propertys-displaylist-valuelist but I could not get it to work until now. 

I've modified my code as follows:This is the new class:

Class JMH.EnsSearchUtils Extends Ens.ContextSearch
{
ClassMethod GetKeyLookup(Output pCaption As %String, Output pTopResults, Output pResults, ByRef pParms As %String, pSearchKey As %String = "") As %Status
{
set tStatus = $$$OK
    kill pResults, pTopResults
set pCaption = ""
set tStatement = ##class(%SQL.Statement).%New()
set strSQL="SELECT DataValue FROM Ens_Util.LookupTable WHERE TableName = 'KeyLUT' order by DataValue"
    set tStatus = tStatement.%Prepare(strSQL)
if tStatus '= 1 Quit $$$ERROR($$$GeneralError, "No SQL Tables found")
     set rset = tStatement.%Execute()
while rset.%Next(.tStatus) {
set pResults($i(pResults)) = rset.%Get("DataValue")
}
quit tStatus
}
}

In the Business Operation.

1.  Still declare the property like so 'Property KeyLookup; '

2.  In the Parameter SETTINGS:  "KeyLookup:Additional:selector?context={JMH.EnsSearchUtils/GetKeyLookup}";

To break this down:

KeyLookup is the property name 

Additional is the Additional Settings Category area of the Business Operation where this property will be displayed in the Portal

The rest is used to call the ContextSearch class and method to return the result values for the property.

Yes, it works now with the updates mentioned previously.  Although I must admit I'm not entirely sure what all the parameters for the methods mean:

(Output pCaption As %String, Output pTopResults, Output pResults, ByRef pParms As %String, pSearchKey As %String = "")

  • pCaption -  return localized caption which would be shown above results
  • pResults  - all results go here
  • pTopResults  - copy some of the results here to show them on top
  • pParms  - local array in which you can pass params
  • pSearchKey - no idea

For example

Parameter SETTINGS = "XSLТ:Basic:selector?context={isc.util.EnsSearchUtils/GetXDatas?class=util.XDatas}"

And inside the method you would get

zw pParms 

pParms("class") = "util.XDatas"

Here's sample code

ClassMethod GetXDatas(Output pCaption As %String, Output pTopResults, Output pResults, ByRef pParms As %String, pSearchKey As %String = "") As %Status
{
    Set tStatus = $$$OK
    Kill pResults, pTopResults
    Set pCaption = $$$Text("My Localized Caption")

    Set tClass = $get(pParms("class"))
    If tClass '= "" {
        Set tClassObj = ##class(%Dictionary.CompiledClass).%OpenId(tClass)

        For i=1:1:tClassObj.XDatas.Count() {
            Set tName = tClassObj.XDatas.GetAt(i).Name
            Set pResults($i(pResults)) = tName
            
            Set:tName["import" pTopResults($i(pTopResults)) = tName
        }
    }
    Quit tStatus
}

}

It would search for all XDatas in class parameter (utils.XDatas in our case) and show those of them containing Import word at the top. Caption would be "My Localized Caption" but you can add localizations to it.

Thanks for the explanation Eduard.  This is helpful as I found the documentation a bit sparse.