· Mar 14, 2017

How do you use text box filters in tablePanes that have complex SQL behind them?

I have a tablePane ZEN Component and I am trying to get a filter running on the Specimen Id / Lab Number. The SQL is fairly complex with 3 UNION ALL statements joining 4 tables and a couple of lookup tables.

How would I get the filter on SpecId to work for my tablePane? It's not automatically applying the filter for me so I think I need code something.

                    <parameter id="p1"/>
                    <parameter id="p2"/>
                    <parameter id="p3"/>
                    <parameter id="p4"/>
                    <column colName="ID" hidden="true" width="5%"/> 
                    <column header="Request Date" width="20%" colName="RequestDate"/>
                    <column header="Lab Number" width="20%" colName="SpecId" filterType="text" filterOp="=" /> 
                    <column header="Profile" width="9%" colName="Worksheet" hidden="true"/>
                    <column header="Profile Description" width="30%" colName="ProfileDescription" />
                    <column header="Site" width="9%" colName="Site"/>
                    <column header="Record Type" width="20%" colName="RecordType"/>
                    <column header="Discipline" colName="Discipline" width="20%"/>
                    <column colName="DateTimeDeleted" hidden="true"/>
                    <column colName="Username" hidden="true"/>


I have managed to get the filter working using the OnCreateResultSet event that takes a %ZEN.Auxiliary.QueryInfo object as a parameter. I just need to find the event detects a filter value change and re-applies the filter to make some final adjustments hiding other ZEN components on my page. I have some sample code below but I have hidden most of the SQL as it is over 2000 characters long.

 Method CreateWorkRS(Output tSC As %Status, pInfo As %ZEN.Auxiliary.QueryInfo) As %ResultSet

     Set tRS = ""
    Set tSC = $$$OK

    Set:pInfo.filters("SpecId")'="" filterValue=pInfo.filters("SpecId")

   // Modified SQL with filter value
    If $GET(filterValue)'=""
     Set filterValue=pInfo.filters("SpecId")
     Set sql1=sql1_"and workbio.SpecId='"_filterValue_"' "
     Set sql2=sql2_"and workbioDel.SpecId='"_filterValue_"' "
     Set sql3=sql3_"and workhae.SpecId='"_filterValue_"' "
     Set sql4=sql4_"and workhaeDel.SpecId='"_filterValue_"' "


// Always do this
Set sql=sql1_"UNION ALL "
      _sql2_"UNION ALL "
      _sql3_"UNION ALL "

    Set tRS = ##class(%ResultSet).%New()
    Set tRS.RuntimeMode = 2 ; DISPLAY

     Set tSC = tRS.Prepare(sql)
    Set pInfo.queryText = sql

    Quit tRS

Any advice on the ZEN events relating to filters would be most welcome!

Discussion (1)0
Log in or sign up to continue

To summarise, the answer is twofold:

1) Use the OnCreateResultSet event of the tablePane to get your filter value and pass it into your custom SQL. I have appended any filter values onto the end of my WHERE clause in each SQL fragment.

2) Use the 'onrefresh' event of the tablePane to call JavaScript to hide other ZEN components when the table is updated.