5.1d No join generated for type="text"-field

[Blama]

Hi Isomorphic,

please see this SNAPSHOT_v10.1d_2015-10-13/PowerEdition BuiltInDS based testcase.
I wanted to show something else and needed a textfield-join, which I was not able to produce with this setup:
supplyItem.ds.xml:

<DataSource 
    ID="supplyItem"
    serverType="sql"
    tableName="supplyItem"
    titleField="itemName"
>
    <fields>
        <field name="itemID"      type="sequence" hidden="true"       primaryKey="true"/>
        <field name="itemName"    type="text"     title="Item"        length="128"       required="true"/>
        <field name="SKU"         type="text"     title="SKU"         length="10"        required="true"/>
        <field name="description" type="text"     title="Description" length="2000"/>
        <field name="category"    type="text"     title="Category"    required="true" foreignKey="animals.scientificName"  />
        <field name="animalsCommonName" includeFrom="animals.CommonName"  type="text"     title="Animals Commonname"/>           
        <field name="units"       type="enum"     title="Units"       length="5">
            <valueMap>
                <value>Roll</value>
                <value>Ea</value>
                <value>Pkt</value>
                <value>Set</value>
                <value>Tube</value>
                <value>Pad</value>
                <value>Ream</value>
                <value>Tin</value>
                <value>Bag</value>
                <value>Ctn</value>
                <value>Box</value>
            </valueMap>
        </field>
        <field name="unitCost"    type="float"    title="Unit Cost"   required="true">
            <validators>
                <validator type="floatRange" min="0" errorMessage="Please enter a valid (positive) cost"/>
                <validator type="floatPrecision" precision="2" errorMessage="The maximum allowed precision is 2"/>
            </validators>
        </field>
        <field name="inStock"   type="boolean"  title="In Stock"/>
        <field name="nextShipment"  type="date" title="Next Shipment"/>
    </fields>
</DataSource>

BuiltInDS.java:

package com.smartgwt.sample.client;

import com.google.gwt.core.client.EntryPoint;
import com.smartgwt.client.core.KeyIdentifier;
import com.smartgwt.client.data.DataSource;
import com.smartgwt.client.data.SortSpecifier;
import com.smartgwt.client.types.SortDirection;
import com.smartgwt.client.util.Page;
import com.smartgwt.client.util.PageKeyHandler;
import com.smartgwt.client.util.SC;
import com.smartgwt.client.widgets.IButton;
import com.smartgwt.client.widgets.Window;
import com.smartgwt.client.widgets.events.ClickEvent;
import com.smartgwt.client.widgets.events.ClickHandler;
import com.smartgwt.client.widgets.grid.ListGrid;
import com.smartgwt.client.widgets.grid.ListGridField;

public class BuiltInDS implements EntryPoint {
    private IButton recreateBtn;
    private DataSource supplyItemDS = DataSource.get("supplyItem");

    public void onModuleLoad() {
        KeyIdentifier debugKey = new KeyIdentifier();
        debugKey.setCtrlKey(true);
        debugKey.setKeyName("D");

        Page.registerKey(debugKey, new PageKeyHandler() {
            public void execute(String keyName) {
                SC.showConsole();
            }
        });

        recreateBtn = new IButton("Recreate");
        recreateBtn.addClickHandler(new ClickHandler() {
            @Override
            public void onClick(ClickEvent event) {
                recreate();
            }
        });
        recreate();
        recreateBtn.draw();
    }

    private void recreate() {
        Window w = new Window();
        w.setWidth("95%");
        w.setHeight("95%");
        w.setMembersMargin(0);
        w.setModalMaskOpacity(70);
        w.setTitle("Grouping test");
        w.setShowMinimizeButton(false);
        w.setIsModal(true);
        w.setShowModalMask(true);
        w.centerInPage();

        final ListGrid lg = new ListGrid(supplyItemDS);
        lg.setCanEdit(true);
        lg.setAutoFetchData(false);

        ListGridField itemIdLGF = new ListGridField("itemID");
        ListGridField itemNameLGF = new ListGridField("itemName");
        ListGridField skuLGF = new ListGridField("SKU");
        ListGridField descriptionLGF = new ListGridField("description");
        ListGridField categoryLGF = new ListGridField("category");
        ListGridField dummyLGF = new ListGridField("animalsCommonName");
        ListGridField unitsLGF = new ListGridField("units");

        lg.setFields(itemIdLGF, itemNameLGF, skuLGF, descriptionLGF, categoryLGF, unitsLGF, dummyLGF);

        lg.setSort(new SortSpecifier[] { new SortSpecifier(itemIdLGF.getName(), SortDirection.ASCENDING),
                new SortSpecifier(itemNameLGF.getName(), SortDirection.ASCENDING) });

        lg.fetchData();
        IButton addNewBtn = new IButton("Add new row");
        addNewBtn.addClickHandler(new ClickHandler() {
            @Override
            public void onClick(ClickEvent event) {
                lg.startEditingNew();
            }
        });

        w.addItem(lg);
        w.addItem(addNewBtn);
        w.show();
    }
}

The resulting SQL is:

SELECT LIMIT 0 75  supplyItem.itemID, supplyItem.itemName, supplyItem.SKU, supplyItem.description, supplyItem.category, supplyItem.animalsCommonName, supplyItem.units, supplyItem.unitCost, supplyItem.inStock, supplyItem.nextShipment FROM supplyItem WHERE ('1'='1') ORDER BY supplyItem.itemID, supplyItem.itemName

...
java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: SUPPLYITEM.ANIMALSCOMMONNAME

The framework does not try to access the animals table here. I tried also on Oracle, but got the same result.

SELECT supplyItem.itemID, supplyItem.itemName, supplyItem.SKU, supplyItem.description, supplyItem.category, supplyItem.animalsCommonName, supplyItem.units, supplyItem.unitCost, supplyItem.inStock, supplyItem.nextShipment FROM supplyItem WHERE ('1'='1') ORDER BY supplyItem.itemID, supplyItem.itemName

...
java.sql.SQLSyntaxErrorException: ORA-00904: "SUPPLYITEM"."ANIMALSCOMMONNAME": invalid identifier

I'm not sure where the error is here, but I assume that a join on text fields should be possible as well.

Best regards
Blama

[Isomorphic]

The problem here is likely just a typo. The includeFrom specifies "animals.CommonName", but the field you are trying to include is actually called "commonName" (note the initial lower-case "c"). I would expect to see a message to this effect somewhere in the log.

[Blama]

Hi Isomorphic,

that was the reason indeed.

Thank you & Best regards
Blama