Announcement

**Blama** · 24 May 2015, 06:12

Hi zaj,

please try the following:

Code:

<field name="prfk" title="Production Line" type="integer" foreignKey="productionlineDS.id" displayField="[B]bize[/B]" editorType="ComboBoxItem"/>
<field name="bize" title="Batch Size" includeFrom="productionlineDS.bize" [B]hidden="true"[/B] />

Only one field displayed and should solve your case when displaying as well as when editing.

Best regards
Blama

**Blama** · 24 May 2015, 06:14

Also, as productionlinedetailDS.prfk seems not to be required="true", you might(?) wanna add joinType="outer" to it (please read the docs for it carefully if your result is no different (=no outer join generated)).

Best regards
Blama

**zaj** · 24 May 2015, 20:47

Thanks Blama for your feedback.

The thing is when i add the field hidden=true for bize then it is not getting displayed in the listgrid,which is not what i intend.

i have these 2 tables proine and proined. the proine.id is called prfk in table proined.
Now I have created a listgrid which is set to datasource productionlinedetailDS. so the listgrid shows all those cols which i defined in the DS file productionlinedetailDS. SO far so good.
Now I want to show an additional col called Batch Size(bize) from the DS file productionlineD which is also getting achieved by doing this

Code:

<field name="bize" title="Batch Size" includeFrom="productionlineDS.bize"/>

The problem i have is the Production Line col which is the proine.id in the proined table is not getting displayed in the listgrid and the other issue is when i select a data from the ComboBox i want its respective Batch size to be shown in the Batch size column. I have also attached screenshot to try to explain what my issue is and how to resovle it.
Appreciate your help.

Attached Files

**zaj** · 24 May 2015, 21:05

OK, I managed to get one step ahead. That is I managed to display the PRoduction Lien and Batch number col. But the problem now I have is when I hit the NEW button to enter a new record, the production Line col is no more active i.e. it does not display the Combo box which I had before. The thing I did is in my productionlinedetailDS,i made some changes to the field Production Line,like so.you can compare to my previous version in my original post. In the below DS I just copied the field which we need to concentrate on

Code:

<DataSource ID="productionlinedetailDS" serverType="sql" tableName="proined">
	<fields>
		<field name="id" type="sequence" hidden="true" primaryKey="true" />
		<field name="name" nativeName ="prfk" title="Production Line" type="integer" foreignKey="productionlineDS.id" includeFrom="productionlineDS.name" editorType="ComboBoxItem"/>
		<field name="bize" title="Batch Size" includeFrom="productionlineDS.bize"/>

Attached Files

**Blama** · 25 May 2015, 00:36

Hi zaj,

please try:

Your problem was that an includeFrom-field is always readOnly, therefore no editor was shown.
So you need to have a normal foreignKey field, but add the displayField attribute.

Also check your need for an outerJoin-foreignKey.

Best regards
Blama

**Blama** · 25 May 2015, 00:38

Note that if you set up you ComboBoxItem manually, you can also show the resulting batch size in the dropdown before leaving the row. See the picklist-methods in ComboBoxItem.

**zaj** · 25 May 2015, 00:54

Thanks Blama for your help. I get this error.By the way my database is MSSQL 2008.

java.sql.SQLException: The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1311)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at com.isomorphic.sql.SQLDriver.getTransformedResults(SQLDriver.java:695)
at com.isomorphic.sql.SQLDriver.getTransformedResults(SQLDriver.java:550)
at com.isomorphic.sql.SQLDriver.getTransformedResults(SQLDriver.java:543)
at com.isomorphic.sql.SQLDriver.getScalarResult(SQLDriver.java:729)
at com.isomorphic.sql.SQLDriver.executeScalar(SQLDriver.java:1020)
at com.isomorphic.sql.SQLDataSource.runRowCountQuery(SQLDataSource.java:2957)
at com.isomorphic.sql.SQLDataSource.executeWindowedSelect(SQLDataSource.java:2361)
at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1908)
at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:443)
at com.isomorphic.sql.SQLDataSource.executeFetch(SQLDataSource.java:388)
at com.isomorphic.datasource.DataSource.execute(DataSource.java:1922)
at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:726)
at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:658)
at com.isomorphic.application.AppBase.execute(AppBase.java:491)
at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:2548)
at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:220)
at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:185)
at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:152)
at com.isomorphic.servlet.IDACall._processRequest(IDACall.java:117)
at com.isomorphic.servlet.IDACall.doPost(IDACall.java:76)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:755)
at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:848)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:686)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1494)
at com.isomorphic.servlet.CompressionFilter._doFilter(CompressionFilter.java:260)
at com.isomorphic.servlet.BaseFilter.doFilter(BaseFilter.java:83)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1482)
at com.behsa.server.login.AppAuthenticationFilter.doFilter(AppAuthenticationFilter.java:46)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1474)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:499)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:137)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:557)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:231)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1086)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:428)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:193)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1020)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:135)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116)
at org.eclipse.jetty.server.handler.RequestLogHandler.handle(RequestLogHandler.java:68)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116)
at org.eclipse.jetty.server.Server.handle(Server.java:370)
at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:489)
at org.eclipse.jetty.server.AbstractHttpConnection.content(AbstractHttpConnection.java:960)
at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.content(AbstractHttpConnection.java:1021)
at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:865)
at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:240)
at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:82)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:668)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:52)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:608)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:543)
at java.lang.Thread.run(Thread.java:745)

**Blama** · 25 May 2015, 00:56

Hi zaj,

please read the docs for the outerJoin attribute and set useAnsiJoins either globally or for your .ds.xml one by one.

Best regards
Blama

**zaj** · 25 May 2015, 01:10

Blama, I got rid of that joinType and I got near to my end goal. Only one thing which is not working is that when I want to enter a new record i select the Production Line from the combobox its corresponding Batch Lien is not showing. what could be going wrong here. But when I fetch the listgrid it is showing the saved records Batch line. Please help. My datasource looks like this now

Code:

<DataSource ID="productionlinedetailDS" serverType="sql" tableName="proined">
	<fields>
		<field name="id" type="sequence" hidden="true" primaryKey="true" />
		<field name="prfk" title="Production Line" type="integer" foreignKey="productionlineDS.id" displayField="productionlineName" editorType="ComboBoxItem"/>
		<field name="productionlineName" includeFrom="productionlineDS.name" hidden="true" />
		<field name="productionlineBize" includeFrom="productionlineDS.bize"/>

**Blama** · 25 May 2015, 01:24

Hi zaj,

please rethink the outerJoin-issue. You need to either have the outerJoin or required="true" for prfk because otherwise records will get "lost" in the UI because they do not have a join-partner.

The issue with the not filled productionlineBize is expected - the record is not saved, so the SELECT for the new values after UPDATE/ADD was not executed.
If you care only for the information and not for the design issue, you could do what I suggested earlier with a custom ComboBoxItem.
Other options include change handlers (I did not do this, yet), or, if you are OK with a display of "linename (linesize)" a combination of custom ComboBoxItem for the editing, two hidden includeFrom fields (needed!) and another field (used as new displayField for prfk with a customSelectExpression that creates the "linename (linesize)".

Best regards
Blama

**zaj** · 25 May 2015, 21:02

Hi Blama,

Appreciate your helpful feedbacks.
I was able to achieve the requirement using the change handlers in my java class(I have provided the code for anyone to refer). But i am very eager and still wondering how could I have achieved this purely using datasource file only.
I also added the join and required to my foreign key field but still no luck!!Below is my datasource file and the Query which smartgwt generates. Can you please help me to achieve this.I will appreciate it.

Code:

 
<DataSource ID="productionlinedetailDS" serverType="sql" tableName="proined">
	<fields>
		<field name="id" type="sequence" hidden="true" primaryKey="true" />		
		<field name="prfk" title="Production Line" type="integer" foreignKey="productionlineDS.id" joinType="outer" displayField="productionlineName" required="true" editorType="ComboBoxItem"  />
		<field name="productionlineName" includeFrom="productionlineDS.name" hidden="true" />
		<field name="productionlineBize" includeFrom="productionlineDS.bize"/>
		
		<field name="pu" title="Unit" type="text" length="5">
		<valueMap>
                <value>Kg</value>
                <value>Liter</value>
                <value>Pcs</value>
                <value>Other</value>
            </valueMap>
        </field>
		 <field name="up" title="Single Unit Package / Batch" type="integer"/>
		 <field name="ups" title="Multiple Unit Package / Batch" type="integer"/>
		 <field name="amc" title="Multiple Unit Package Content" type="integer"/>
        <field name="was" title="Production Staff" type="integer"/>
	</fields>
</DataSource>

Code:

SELECT
    *
FROM
    (
        SELECT
            *,
            ROW_NUMBER() OVER (ORDER BY id) AS rowID
        FROM
            (
                SELECT
                    TOP 100 PERCENT proined.id,
                    proined.prfk,
                    proined.pu,
                    proined.up,
                    proined.ups,
                    proined.amc,
                    proined.was,
                    proine.name AS productionlineName,
                    proine.bize AS productionlineBize
                FROM
                    proined
                LEFT OUTER JOIN
                    proine
                ON
                    proined.prfk = proine.id
                WHERE
                    (
                        '1'='1')) x) y
WHERE
    y.rowID BETWEEN 1 AND 75
SELECT
    COUNT(*)
FROM
    proined
LEFT OUTER JOIN
    proine
ON
    proined.prfk = proine.id


What I am trying to do is very similar to this showcase example where I select the Country from the Combo box and the corresponding code is apdated in the Code Coulumn.
http://www.smartclient.com/smartgwtee/showcase/#sqlIncludeFromDynamic.

Just to give you and others an idea how i managed to achieve this using handler is below

Code:

if (prfk != null)
		{
			prfk.addChangedHandler(new ChangedHandler()
			{
				@Override
				public void onChanged(ChangedEvent event)
				{
					final int rowNum = event.getRowNum();
					if (event.getValue() != null)
					{
						updateData(prfkDS, new Criteria("id", event.getValue().toString()), rowNum);
					}
				}
			});
		}

private void updateData(DataSource ds, Criteria criteria, final int rowNum)
	{
		ds.fetchData(criteria, new DSCallback()
		{
			@Override
			public void execute(DSResponse dsResponse, Object data, DSRequest dsRequest)
			{
				Record[] recdata = dsResponse.getData();
				if (recdata != null && recdata.length > 0)
				{
					
					listgrid.setEditValue(rowNum, "bize", recdata[0].getAttribute("bize"));
					listgrid.refreshRow(rowNum);
				}
				else
				{
					
				}
			}
		});
	}

**Blama** · 26 May 2015, 00:07

Hi zaj,

this is the "change handler"-way I mentioned in my last post. Using that you should also be able get rid of the fetch by having the bize-value as PickList-ListGridField in the ComboBoxItem and accessing it in the changed-handler via ((ComboBoxItem)event.getItem()).... (as enhancement). See ComboBoxItem.setPickListFields() for details on how to make the information available in the ComboBoxItem.

The other way I described (one field with: linename (bize)) involves a similar setup for the ComboBoxItem (two ListGridFields, one hidden, one with a setCellFormatter() creating the linename (bize)) for the edit-part.
The display-part is .ds.xml-only with a customSelectExpression (mentioned previously, two hidden="true"-includeFrom fields, another field with customSelectExpression used as displayField for prfk. You might want to set relatedTableAlias on the prfk-field in order to be sure that your customSelectExpression will always work).

I'm pretty sure a .ds.xml-only solution is NOT possible because by default the new row data after ADD/UPDATE comes from the cacheSync and is therefore not available until the row is successfully saved.

Best regards
Blama

**Blama** · 26 May 2015, 00:16

FYI: The sample you mentioned in the code does also NOT show the data after just selecting an entry, but only after leaving the row and therefore triggering the cacheSync-SELECT statement of the changed row. The difference to your setup is only that the includeFrom-field is defined in Java rather than in the .ds.xml.

**zaj** · 26 May 2015, 00:18

cheers mate!!
-- but only after leaving the row
But I was aalso expecting that to work in my case,but did not

Announcement

IncludeFrom field not populating data

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment