Zen Datagrid save to table

Question

Question

Jimmy Christian · Nov 16, 2018

#Beginner #Object Data Model #ZEN #Caché

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" />
</dataGrid>
.......

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?

Thanks,

Jimmy.

Discussion (8)0

Log in or sign up to continue

Vasiliy Bondar · Nov 17, 2018

<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 "  >
</jsonSQLProvider>
<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"  />
</dataGrid>

ClientMethod btSave() [ Language = javascript ]
{
var dataGrid = zen('dataGrid');
var rowCount = dataGrid.getRowCount();
for(var row=0;row<rowCount;row++) {
zenPage.SaveRow(dataGrid.getCellValue(row,0),dataGrid.getCellValue(row,1),dataGrid.getCellValue(row,2),dataGrid.getCellValue(row,3),dataGrid.getCellValue(row,5));
}
window.location.reload();
}

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 ]
{
zenPage.btSave();
zenPage.AddLine();
window.location.reload();
}

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?")){
zenPage.DeleteRow(dataGrid.getCellValue(cRow-1,5));
}
window.location.reload();
}

Method DeleteRow(Id) As %Status [ ZenMethod ]
{

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

1 0

score 0 · Answer 1 · 2018-11-18T16:16:40-05:00

Jimmy Christian Nov 18, 2018 to Vasiliy Bondar

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

0 0

score 0 · Answer 2 · 2018-11-19T08:39:02-05:00

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.

score 0 · Answer 3 · 2018-11-19T16:39:29-05:00

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.

onchange="zenPage.rowSelected(zenThis.getValue());"

<dataGrid

,......

</dataGrid>

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..

score 0 · Answer 4 · 2018-11-19T20:53:37-05:00

Evgeny Shvarov Nov 19, 2018 to Jimmy Christian

Hi Jimmy!

Do you mind to introduce it as another question?

0 0

score 0 · Answer 5 · 2018-11-20T08:54:28-05:00

Jimmy Christian Nov 20, 2018 to Evgeny Shvarov

Hello Evgeny.

Yes Sure !

Thank you.

Regards.

0 0

score 0 · Answer 6 · 2018-11-20T12:44:29-05:00

Thank you very much Vladimir .

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

Regards,

Jimmy

score 0 · Answer 7 · 2018-11-20T05:50:05-05:00

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 {

width: 100%;

height: 500px;

}

</style>
}

///
/// This XML block defines the contents of this page.
XData Contents [ XMLNamespace = "http://www.intersystems.com/zen" ]
{
<page xmlns="http://www.intersystems.com/zen" xmlns:demo="http://www.intersystems.com/zendemo" title="">
<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" />
</locatorBar>
<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);"/>
</hgroup>
<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"/>
</dataGrid>
<hgroup enclosingStyle="background:#F0F0F0;">
<label value="Show all Names starting with : "/>
<combobox id="combobox" label="combobox"
onchange="zenPage.changeQuery(zenThis.getValue());"
editable="false">
<option value="" text="" />
<option value="A" text="A" />
<option value="B" text="B"/>
<option value="C" text="C" />
<option value="Z" text="Z" />
</combobox>
<spacer width="10"/>
<button caption="Show All" onclick="zenPage.changeQuery('');"/>
</hgroup>
</vgroup>
</page>
}

ClientMethod SetFilter(pTextBox As %ZEN.Component.text) [ Language = javascript ]
{
zen('dataGrid').setProperty('filterKey',pTextBox.getValue());
zen('dataGrid').renderContents();
}

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);
zen('json').reloadContents();
}

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
}

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