Zen Datagrid save to table

Primary tabs

Hello all,

I have created a simple Datagrid and using jsonSQLProvider with a SQL statement as the datasource. Please see below.

<jsonSQLProvider id="json" OnSubmitContent="SubmitContent"
sql="SELECT PatchClassName,Environment,ModuleName,ModuleClass,TargetConfig,BusinessRule,MessageSchemaCategory FROM ProjectInventory.TablePatchClass order by PatchClassName" />
 <dataGrid pageSize="20" id="dataGrid" pagingMode="client" controllerId="json" sortMode="client"
  selectMode="cells" >
 <columnDescriptor caption="ID" type="string" readOnly="false"/>
 <columnDescriptor caption="PatchClassName" type="string" readOnly="false"/>
 <columnDescriptor caption="Environment" type="string" readOnly="false"/>
 <columnDescriptor caption="ModuleName" type="string" readOnly="false"/>
 <columnDescriptor caption="ModuleClass" type="string" readOnly="false"/>
 <columnDescriptor caption="TargetConfig" type="string" readOnly="false"/>
 <columnDescriptor caption="BusinessRule" type="string" readOnly="false"/>
 <columnDescriptor caption="MessageSchemaCategory" type="string" readOnly="false" />

I can see the Datagrid in the webpage as well as the data but,

1) The cell value does not change on doubleclick.

2) i am able to modify the cell when i press "F2". But the data is not stored in the table.

How can i save the modified value in the table?



  • 0
  • 0
  • 154
  • 7
  • 1


<button caption="AddLine" onclick="zenPage.btAddLine()"/>
<button caption="Del Line" onclick="zenPage.btDelete()"/>
<button caption="Save" onclick="zenPage.btSave()"/>

<jsonSQLProvider id="json" sql="select nvl(ArticleCost->Aka,'')||nvl(AddServiceLine->AddService->Service->Aka,''),IsNal,RubSum,Comment,round(nvl(AddServiceLine->StoimFact,0)+nvl(TakeSum,0),2),Id 
from doc.AddCost "  >
<dataGrid pageSize="20"  id="dataGrid" pagingMode="client" controllerId="json" 
sortMode="client" selectMode="cells"  valueColumn="5" >
 <columnDescriptor caption="Article" type="lookup" readOnly="false" width="250px" ongetlookupspec="return zenPage.fireLookupDataState();"/>
 <columnDescriptor caption="Nal" type="checkbox" readOnly="false" width="50px"/>
 <columnDescriptor caption="Cost" type="string" readOnly="false" width="150px"/>
 <columnDescriptor caption="Comment" type="string" readOnly="false" width="250px"/>
 <columnDescriptor caption="Dop" type="string" readOnly="false" width="250px"/>
 <columnDescriptor caption="Code" type="string" readOnly="true"  />

ClientMethod btSave() [ Language = javascript ]
var dataGrid zen('dataGrid');
var rowCount dataGrid.getRowCount();
for(var row=0;row<rowCount;row++) {

Method SaveRow(aArticleCostAka = "", aIsNal = "", aRubSum = "", aComment = "", aId) As %Status [ ZenMethod ]
set articleCostId=""
&sql(select Id into :articleCostId from ent.ArticleCost where Aka =:aArticleCostAka)

&sql(update doc.AddCost(ArticleCost,IsNal,RubSum,Comment)
values(:articleCostId,:aIsNal,:aRubSum,:aComment) where Id=:aId)

quit $$$OK

ClientMethod btAddLine() [ Language = javascript ]

Method AddLine() As %Status [ ZenMethod ]
&sql(insert doc.AddCost (ArticleCost) values (null) )
quit $$$OK

ClientMethod btDelete() [ Language = javascript ]
var dataGrid zen('dataGrid');
var cRow=dataGrid.getProperty('currRow');

if (confirm("You sure?")){

Method DeleteRow(Id) As %Status [ ZenMethod ]

&sql(delete from doc.AddCost where Id=:Id)
quit $$$OK

Thank you very much  ! I appreciate your time. I will apply it to my code and validate.

Thank you very much  Vasiliy Bondar.

I tweaked the code as per my tables and fields and is working as expected. I appreciate your help.

So i was successful creating the datagrid and some combo box. Making some progress. But....Got another hurdle. Help will be appreciated.

I want the datagrid to reflect values based on the value i select in the Combobox.  How to pass value from the combobox to the

altJSONSQLProvider  parameter and reload the datagrid ?

My combobox defines below method on change.


<altJSONSQLProvider id="PatchClassJsonId" OnGetSQL="GetSQL" >
<parameter paramName="1" value="C"/>




Method GetSQL(ByRef pParm As %String, ByRef pSQL As %String, pCriteria As %ZEN.proxyObject, ByRef pPagingInfo As %String) As %Status
Set pParm= "zenPage.getComponentById('DataComboId').getValue()"

Set pSQL = "SELECT ID,PatchClassName,Environment,ModuleName,ModuleClass,TargetConfig,BusinessRule,MessageSchemaCategory FROM ProjectInventory.TablePatchClass where PatchClassName %STARTSWITH ? " 
    Quit $$$OK

Thank you..

Hi Jimmy!

Do you mind to introduce it as another question? 

One modified example from "Samples" names. 


Class ZENTest.altDataGridCopmboBoxTest Extends %ZEN.Component.page

Parameter APPLICATION = "ZENTest.TestApplication";

Parameter PAGENAME = "DataGrid Test";

Parameter DOMAIN = "ZENTEST";

XData Style
<style type="text/css">

#dataGrid {





/// This XML block defines the contents of this page.
XData Contents [ XMLNamespace = "http://www.intersystems.com/zen]
<page xmlns="http://www.intersystems.com/zenxmlns:demo="http://www.intersystems.com/zendemotitle="">
<demo:demoTitle id="title" title="Zen DataGrid Test Page Using The Alternate JSON SQL Provider widget" category="Zen Test Suite" />
<locatorBar id="locator">
<locatorLink caption="Home" title="Home page" href="ZENDemo.Home.cls"/>
<locatorLink caption="Test Suite" title="Test Suite" href="ZENTest.HomePage.cls"/>
<locatorLink caption="DataGridTest" title="DataGrid Test Page" />
<titleBox title="Zen DataGrid Test Page" 
subtitle="Use this page to test the various DataGrid box controls." />
<spacer height="25"/>
<altJSONSQLProvider id="json" />
<vgroup width="100%" height="100%">
<hgroup enclosingStyle="background:#F0F0F0;">
<spacer width="600"/>
<label value="Filter current results : "/>
<image src="deepsee/ds2_magnify_18.png" onclick="zenPage.SetFilter(zen('txtFilter'));"/>
<text id="txtFilter" onchange="zenPage.SetFilter(zenThis);"/>
 <dataGrid pageSize="20"  id="dataGrid" pagingMode="client" controllerId="json" sortMode="client" selectMode="cells" onaction="zenPage.fireAction(row,name,value);onchangecell="return zenPage.fireChangeCell(value);ongetlookupdata="return zenPage.fireLookupData();>
 <columnDescriptor caption="ID" type="string" readOnly="false"/>
 <columnDescriptor caption="Age" type="string" readOnly="false"/>
 <columnDescriptor caption="DOB" type="string" readOnly="false"/>
 <columnDescriptor caption="FavoriteColors" type="lookup" readOnly="false" ongetlookupspec="return zenPage.fireLookupDataFavColors();"/>
 <columnDescriptor caption="Name" type="string" readOnly="false"/>
 <columnDescriptor caption="SSN" type="string" readOnly="false"/>
 <columnDescriptor caption="Spouse" type="lookup" readOnly="false"/>
 <columnDescriptor caption="Home_City" type="string" readOnly="false"/>
 <columnDescriptor caption="Home_State" type="lookup" readOnly="false" ongetlookupspec="return zenPage.fireLookupDataState('Home');"/>
 <columnDescriptor caption="Home_Street" type="string" readOnly="false"/>
 <columnDescriptor caption="Home_Zip" type="string" readOnly="false"/>
 <columnDescriptor caption="Office_City" type="string" readOnly="false"/>
 <columnDescriptor caption="Office_State" type="lookup" readOnly="false" ongetlookupspec="return zenPage.fireLookupDataState('Office');"/>
 <columnDescriptor caption="Office_Street" type="string" readOnly="false"/>
 <columnDescriptor caption="Office_Zip" type="string" readOnly="false"/>
 <hgroup enclosingStyle="background:#F0F0F0;">
 <label value="Show all Names starting with : "/>
 <combobox id="combobox" label="combobox"
<option value="" text="" />
<option value="A" text="A" />
<option value="B" text="B"/>
<option value="C" text="C" />
<option value="Z" text="Z" />
 <spacer width="10"/>
 <button caption="Show All" onclick="zenPage.changeQuery('');"/>

ClientMethod SetFilter(pTextBox As %ZEN.Component.text) [ Language = javascript ]

ClientMethod fireChangeCell(value) [ Language = javascript ]
var cCol=zen('dataGrid').getProperty('currColumn');
var cRow=zen('dataGrid').getProperty('currRow');
var cPage=zen('dataGrid').getProperty('currPage');
return value;

ClientMethod fireLookupData() [ Language = javascript ]
var cCol=zen('dataGrid').getProperty('currColumn');
if (cCol==4) return zenPage.fireLookupDataFavColors(); //Favorite Colors
if (cCol==9) return zenPage.fireLookupDataState('Home'); //Home_State
if (cCol==13) return zenPage.fireLookupDataState('Office'); //Office_State

ClientMethod fireLookupDataFavColors() [ Language = javascript ]
//var values = ['Red','Orange','Yellow','Green','Blue','Purple','Black','White'];
var FavColString zenPage.GetFavoriteColors();
var values FavColString.split(',');
return values

Method GetFavoriteColors() As %String [ ZenMethod ]
Quit "Red,Orange,Yellow,Green,Blue,Purple,Black,White"

ClientMethod fireLookupDataState(what) [ Language = javascript ]
var StatesString;
if (what=='Home') StatesString zenPage.GetStates(true);
else  StatesString zenPage.GetStates(false);
var values StatesString.split(',');
//var values = ['MA','NY','OH'];
return values

Method GetStates(Home As %Boolean = 1) As %String [ ZenMethod ]
set sql="select distinct("_$s(Home=1:"Home",1:"Office")_"_State) as State from Sample.Person order by "_$s(Home=1:"Home",1:"Office")_"_State "_$s(Home=1:"asc",1:"desc")
Set rs=##class(%ResultSet).%New()
do rs.Prepare(sql)
do rs.Execute()
Set StatesString=""
While (rs.Next()) {
Set StatesString=StatesString_rs.Get("State")_","
do rs.Close()
Set:$e(StatesString,$l(StatesString))="," StatesString=$e(StatesString,1,$l(StatesString)-1)
Quit StatesString

ClientMethod fireAction(row, name, value) [ Language = javascript ]
alert(row ' : ' name ' : ' value ' : ' zen('dataGrid').getCurrPage());

ClientMethod changeQuery(namestartswith) [ Language = javascript ]
var ret zenPage.ChangeQueryOnServer(namestartswith);

Method ChangeQueryOnServer(namestartswith As %String = "") As %Boolean [ ZenMethod ]
set %page.%GetComponentById("json").sql = "select * from sample.person "_$s(namestartswith'="":"where name %startswith '"_namestartswith_"'",1:"")_" order by name"
quit 1

/// This callback is called after the server-side page
/// object and all of its children are created.<br/>
/// Subclasses can override this to add, remove, or modify
/// items within the page object model, or to provide values
/// for controls.
Method %OnAfterCreatePage() As %Status
Set provider = ..%GetComponentById("json")
Set provider.sql = "select * from sample.person order by name"
Quit $$$OK


Thank you very much  Vladimir .

This was very helpful. I used the ChangeQueryOnServer as a reference and my code is working good.