Tey Kitthajaroenchai · Aug 29, 2019

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!

2 6 120 1


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

So, does it work for you?

Seems correct.

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.