New post
Question
· May 10, 2019

Adding Parameter to FilterQuery

#ZEN #Caché

In our table, we have a column cities,  which has a filter which displays the cities of a state depending on the state

For simplicity let's assume we have a page

Property State As %String =  "CA"

Our filter should run this query to show all cities of state ca

"SELECT Name FROM City WHERE State= "_%page.State

<tablePane>

<column colName="City"
filterQuery="SELECT Name FROM City WHERE State= <This is where I am stuck>"
filterType="query"
header="City" 
width="10%">
</column>

 

I am not able to pass any parameter to filterQuery. 

Discussion (9)0
Log in or sign up to continue
Fabian Haupt · May 12, 2019

Hi,

please make sure to never pass unchecked client supplied parameters to a query, that is a huge security risk.
For general documentation on how to use filters with tablepane, have a look at : https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY...

If you have an instance with the SAMPLES namespace, have a look at ZENTest.TableTest.cls for an example on how to do filtering.

Best,
Fabian

0 0
Vladimir Iliychev · May 13, 2019

Hi Neerav,

Here is an example:

=====================================================================================

Class ZENMVC.MVCMasterDetail1 Extends %ZEN.Component.page
{

Property State As %ZEN.Datatype.string(ZENURL = "Home_State") [ InitialExpression = "CA" ];

XData Contents [ XMLNamespace = "http://www.intersystems.com/zen" ]
{
<page xmlns="http://www.intersystems.com/zen">
<hgroup>
<spacer width="10"/>
<vgroup valign="top">
<combobox id="combobox" label="State" width="150" dropdownHeight="650"
  valueList="AL,AK,AZ,AR,CA,CO,CT,DE,FL,GA,HI,ID,IL,IN,IA,KS,KY,LA,ME,MD,MA,MI,MN,MS,MO,MT,NE,NV,NH,NJ,NM,NY,NC,ND,OH,OK,OR,PA,RI,SC,SD,TN,TX,UT,VT,VA,WA,WV,WI,WY"
  onchange="zenPage.notifyOnChange(zenThis.getValue());">
</combobox>
</vgroup>
<spacer width="30"/>
<dataController id="source" modelClass="ZENMVC.FormDataModel" modelId="#(%url.ID)#"/>
<vgroup>
<tableNavigatorBar tablePaneId="table" showPageSize="true"/>
<tablePane id="table" width="700"
tableName="ZENDemo_Data.Patient"
whereClause="Home_State=?"
valueColumn="ID"
value="#(%url.ID)#"
maxRows="1000">
<column colName="ID" width="25"/>
<column colName="Name" width="180" filterType="text"/>
<column colName="SSN" width="100" filterType="text"/>
<column colName="MaritalStatus" width="20" filterType="text"/>
<column colName="City" colExpression="Home_City" width="100" filterType="text"/>
<column colName="State" colExpression="Home_State" width="25" filterType="text" filterOp="="/>
<parameter value="#(%page.State)#"/>
</tablePane>
</vgroup>
</hgroup>
</page>
}

ClientMethod notifyOnChange(value) [ Language = javascript ]
{
this.State=value;
zen('table').executeQuery();
}

}
=====================================================================================

0 0
Vladimir Iliychev · May 14, 2019

Class ZENMVC.MVCMasterDetail2 Extends %ZEN.Component.page
{

XData Contents [ XMLNamespace = "http://www.intersystems.com/zen" ]
{
<page xmlns="http://www.intersystems.com/zen">
<tableNavigator tablePaneId="table"/>
<tablePane id="table" width="700" tableName="ZENDemo_Data.Patient"
valueColumn="ID" maxRows="1000">
<column colName="ID" width="5%"/>
<column colName="Name" width="30%" filterType="text"/>
<column colName="SSN" width="17%" filterType="text"/>
<column colName="MaritalStatus" width="18%" filterType="text"/>
<column colName="City" colExpression="Home_City" width="25%" filterType="text"/>
<column colName="State" colExpression="Home_State" width="10%" filterType="query"
filterQuery="SELECT DISTINCT Home_State FROM ZENDemo_Data.Patient ORDER BY Home_State"
 filterOp="="/>
</tablePane>
</page>
}

}
 

0 0
Neerav Verma · May 14, 2019

<column colName="State" colExpression="Home_State" width="10%" filterType="query"
filterQuery="SELECT DISTINCT Home_State FROM ZENDemo_Data.Patient ORDER BY Home_State"
 filterOp="="/>

Just in this we need to add a where clause which takes a page property which is not working

<column colName="City" colExpression="Home_City" width="10%" filterType="query"
filterQuery="SELECT DISTINCT Home_City FROM ZENDemo_Data.Patient  Where Home_State = #(%page.State)# ORDER BY Home_City"
 filterOp="="/>

0 0
Vladimir Iliychev · May 15, 2019

Hey Neerav,

From the very beginning I understand what you want, but I'm not sure that tablePane  is designed to use where clause with runtime expression  in the filters . That's why I pointed to you two works around. Here is the third one.

=====================================================================================

Class ZENMVC.MVCMasterDetail3 Extends %ZEN.Component.page
{

Property State As %ZEN.Datatype.string(ZENURL = "Home_State");

XData Contents [ XMLNamespace = "http://www.intersystems.com/zen" ]
{
<page xmlns="http://www.intersystems.com/zen">
<tableNavigator tablePaneId="table"/>
<tablePane id="table" width="700" tableName="ZENDemo_Data.Patient"
valueColumn="ID" maxRows="25">
<column colName="ID" width="5%"/>
<column colName="Name" width="30%" filterType="text"/>
<column colName="SSN" width="17%" filterType="text"/>
<column colName="MaritalStatus" width="18%" filterType="text"/>
<column colName="City" colExpression="Home_City" width="25%" filterType="text"/>
<column colName="State" colExpression="Home_State" width="10%" filterType="query"
filterQuery="SELECT DISTINCT Home_State FROM ZENDemo_Data.Patient ORDER BY Home_State"
 filterOp="="/>
</tablePane>
<button caption="Filter Value" onclick="zenPage.changeFilterValues();" />
</page>
}

ClientMethod changeFilterValues() [ Language = javascript ]
{
this.getFilterValues();
this.restoreFilters();
zen('table').executeQuery();
}

ClientMethod getFilterValues() [ Language = javascript ]
{
var table = this.getComponentById('table');
var state = table.getColumnFilters();
if (state == null) {
alert('Unable to get filter values!');
}
else {
// save filter state
this.filterState = state;
var p='State'
state[p]='CA'
///state[p]=this.State;
}
}

ClientMethod restoreFilters() [ Language = javascript ]
{
var table = this.getComponentById('table');

if (null == this.filterState) {
alert('No saved filter state found.');
}
else {
table.setColumnFilters(this.filterState);
}
}

}
=====================================================================================

Just replace 

state[p]='CA' 

with

state[p]=this.State;

Vlado

0 0
Neerav Verma · May 21, 2019

Hey Vladimir,

Apology for a late reply, was tied up in something else.

Pls pardon me if I am wrong , from what I can tell this code and all other codes are filtering the table by state which is like saying Select * from Patient where State = 'CA' . Correct?

My problem is quiet the opposite. 
I have picked up this snippet from your own code

<column colName="City" colExpression="Home_City" width="25%" filterType="text"/>
<column colName="State" colExpression="Home_State" width="10%" filterType="query"
filterQuery="SELECT DISTINCT Home_State FROM ZENDemo_Data.Patient ORDER BY Home_State"
 filterOp="="/>

City needs to be "filterQuery" and query should say

Select Distinct city from Patient where State = this.state
So basically this city filter should only show cities of the state we are working on.

0 0