Zen Pages: How to update table pane using javascript event handler

Question

Question

ED Coder · Jan 6, 2021

Hi, I am creating a zen page which has a table pane, and loads data, using the onCreateResultSet. I have two controls which allows the user to add a date and type.

I want to add them as parameters into my sql query and update the tablepane. Can you advice on how I can do that?

ClientMethod Fetch() [ Language = javascript ]
{
// i can get the values from my controls here

var par1 = zenPage. .getComponentById('dtfrom').value;

// how can I insert this value par1 and write a new sql query using the value above and update the tablepane
}

Product version: Ensemble 2018.1

Discussion (5)1

Log in or sign up to continue

Timothy Leavitt · Jan 7, 2021

I generally don't use OnCreateResultSet, but here's a sample with it:

Class DC.Demo.ZenPage Extends %ZEN.Component.page
{

XData Contents [ XMLNamespace = "http://www.intersystems.com/zen" ]
{
<page xmlns="http://www.intersystems.com/zen">
<fieldSet legend="Filter" layout="horizontal">
<text label="Name Starts With:" onchange="zen('myTable').setProperty('parameters',1,zenThis.getValue())" />
<dateText label="Date:" onchange="zen('myTable').setProperty('parameters',2,zenThis.getValue())" />
<button onclick="zen('myTable').executeQuery()" caption="Filter" />
</fieldSet>
<tablePane id="myTable" OnCreateResultSet="CreateResultSet">
<parameter value="" />
<parameter value="" />
</tablePane>
<button onclick="zenPage.Populate()" caption="Repopulate Data" />
</page>
}

ClassMethod CreateResultSet(Output pSC As %Status, pInfo As %ZEN.Auxiliary.QueryInfo) As %ResultSet
{
    Set nameFilter = pInfo.parms(1)
    Set dateFilter = pInfo.parms(2) // Will be in ODBC format
    Set query = ##class(%ResultSet).%New()
    Set query.RuntimeMode = 1 // ODBC
    Set sql = "select Name, SomeDate from DC_Demo.SampleData"
    Set conditions = ""
    If (nameFilter '= "") {
        Set conditions = conditions_$ListBuild("Name %STARTSWITH ?")
        Set parameters($i(parameters)) = nameFilter
    }
    If (dateFilter '= "") {
        Set conditions = conditions_$ListBuild("SomeDate = ?")
        Set parameters($i(parameters)) = dateFilter
    }
    If (conditions '= "") {
        Set sql = sql _ " where "_$ListToString(conditions," and ")
    }
    Set pSC = query.Prepare(sql)
    If $$$ISERR(pSC) {
        Quit $$$NULLOREF
    }
    Set pSC = query.Execute(parameters...)
    If $$$ISERR(pSC) {
        Quit $$$NULLOREF
    }
    Quit query
}

ClassMethod Populate() [ ZenMethod ]
{
    Do ##class(DC.Demo.SampleData).%KillExtent()
    Do ##class(DC.Demo.SampleData).Populate(20,,,,0)
    &js<zen('myTable').executeQuery();>
}

}

And the data behind it (minus storage definition):

Class DC.Demo.SampleData Extends (%Persistent, %Populate)
{

Property Name As %String;

Property SomeDate As %Date;

}

0 0

score 0 · Answer 1 · 2021-01-07T15:57:45-05:00

This also demonstrates how to securely add multiple filters to a query - using the ? syntax, *not* just concatenating strings in directly. The "variadic arguments" syntax where you pass in an integer-subscripted array to be any number of arguments (including 0) is super handy for this.

score 0 · Answer 2 · 2021-01-08T05:30:08-05:00

That worked a charm Timothy, thank you. Can I ask, if I want to add an additional date field, how can I add a between clause in the createSearchResult set method? so

Where name starts with

And Somedate BETWEEN ?(datefilter1) and ?(datefilter2)

score 0 · Answer 3 · 2021-01-08T08:55:14-05:00

Here's an updated example for that. I've also updated the example to use %SQL.Statement, which is newer and better than %ResultSet; I didn't realize it would work with OnCreateResultSet initially.

Class DC.Demo.ZenPage Extends %ZEN.Component.page
{

XData Contents [ XMLNamespace = "http://www.intersystems.com/zen" ]
{
<page xmlns="http://www.intersystems.com/zen">
<fieldSet legend="Filter" layout="horizontal">
<text label="Name Starts With:" onchange="zen('myTable').setProperty('parameters',1,zenThis.getValue())" />
<dateText label="Start Date:" onchange="zen('myTable').setProperty('parameters',2,zenThis.getValue())" />
<dateText label="End Date:" onchange="zen('myTable').setProperty('parameters',3,zenThis.getValue())" />
</fieldSet>
<tablePane id="myTable" OnCreateResultSet="CreateResultSet">
<parameter value="" />
<parameter value="" />
<parameter value="" />
</tablePane>
<button onclick="zenPage.Populate()" caption="Repopulate Data" />
</page>
}

ClassMethod CreateResultSet(Output pSC As %Status, pInfo As %ZEN.Auxiliary.QueryInfo) As %SQL.Statement
{
    Set nameFilter = pInfo.parms(1)
    Set startDateFilter = pInfo.parms(2) // Will be in ODBC format
    Set endDateFilter = pInfo.parms(3) // Will be in ODBC format
    Set query = ##class(%SQL.Statement).%New()
    Set query.%SelectMode = 1
    Set sql = "select Name, SomeDate from DC_Demo.SampleData"
    Set conditions = ""
    If (nameFilter '= "") {
        Set conditions = conditions_$ListBuild("Name %STARTSWITH ?")
        Set parameters($i(parameters)) = nameFilter
    }
    If (startDateFilter '= "") && (endDateFilter '= "") {
        // Yes, this could just be independent AND'ed conditions on start/end date,
        // which would reduce code complexity, but you wanted to see BETWEEN, so... :)
        Set conditions = conditions_$ListBuild("SomeDate BETWEEN ? and ?")
        Set parameters($i(parameters)) = startDateFilter
        Set parameters($i(parameters)) = endDateFilter
    } ElseIf (startDateFilter '= "") {
        Set conditions = conditions_$ListBuild("SomeDate >= ?")
        Set parameters($i(parameters)) = startDateFilter
    } ElseIf (endDateFilter '= "") {
        Set conditions = conditions_$ListBuild("SomeDate <= ?")
        Set parameters($i(parameters)) = endDateFilter
    }
    If (conditions '= "") {
        Set sql = sql _ " where "_$ListToString(conditions," and ")
    }
    Set pSC = query.%Prepare(sql)
    If $$$ISERR(pSC) {
        Quit $$$NULLOREF
    }
    
    //Important: Reduce to only the parameters specified/used.
    Kill pInfo.parms
    Merge pInfo.parms = parameters
    Quit query
}

ClassMethod Populate() [ ZenMethod ]
{
    Do ##class(DC.Demo.SampleData).%KillExtent()
    Do ##class(DC.Demo.SampleData).Populate(20,,,,0)
    &js<zen('myTable').executeQuery();>
}

}

Note - I got something slightly wrong in the original example. The responsibility of OnCreateResultSet is just to create the ResultSet, not to execute the query. Most importantly, the parameters in the QueryInfo object need to be reset to just the subset used in our method of generating the query; otherwise, the original parms array would be used to execute the query, which could produce incorrect results (but didn't happen to in the simpler example). An alternative approach would be adding a server-side callback for ExecuteResultSet as well, but in this case it's simpler to just manipulate the parms array.

score 0 · Answer 4 · 2021-01-09T11:22:29-05:00

ED Coder · Jan 9, 2021

Thank you for this Timothy. Goes a long way.. so clear and able to understand well.

0 0