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!
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; '
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 = "")
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.