New post
Question
· Aug 29, 2019

Dynamic/SQL Drop down list for Business Operation Property Setting

#Business Operation #SQL #Ensemble

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!

Discussion (6)0
Log in or sign up to continue
Tey Kitthajaroenchai Aug 30, 2019 to Eduard Lebedyuk

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.

0 0
Eduard Lebedyuk Aug 30, 2019 to Tey Kitthajaroenchai
  • 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.

0 0