Foreign key conundrum (4)

[RickBollinger]

I have set up the problem from scratch. Attached are the files to reproduce: the SQL database definition and data loading files, the application, and two data source definitions made from the SQL database.

At the top is a list of Automakers. At the bottom is a list of vehicles. When you click on an Automaker, the bottom list displays the related data. It is related by virtue of each vehicle having a foreign key to the automaker table. The app implements the scheme for using an ID field to fetch a NAME field as best as I can figure how it is supposed to work.

The Automaker name is blank in the bottom list when I run the app. Since I started from scratch I really thought this might work. But, there are no errors. Just no fetches.

I saw a recent spike in the number of reviews. I hope it indicates it is being seriously looked at. That would be good news since it was posted back on March 6th, about 10 days ago.

I have gotten really good help, even for my knot-headed attempts. I hope this is the last show-stopper.

Thanks,

Rick

P.S. I am running SmartClient Version: SmartClient_SC_SNAPSHOT-2012-03-05_v82p/Pro Development Only (built 2012-03-05) on Mozilla Firefox 3.6.18 with Firebug using Windows XP Pro 32 bit.

[Isomorphic]

In AutoMakerApp.xml, <valueField> has been set to blank for AUTOMAKERID, which obviously breaks the field.

Sorry, we can't continue to jump in and troubleshoot stuff like this for you unless you purpose support. This is the third time we've been asked to look into something as though it might be a product issue, and it wasn't - in fact it was a simple misconfiguration all 3 times.

If you need this kind of help, you'll need to purchase support, or have Isomorphic build the app for you.

[RickBollinger]

I appreciate the support I have been getting. And, I agree that the problem has been misconfiguration. But, I am trying to do the best that I can. I am trying to get funding for support. The lack of being able to demonstrate a working application is a barrier.

A blank (null) <valueField> for AUTOMAKERID is not what is breaking the field in my case. I tried that configuration and it did not work. The attached application version has AUTOMAKERID in the <valueField> and the <displayField> is not displayed.

In the attached example I have done what is called for in the configuration stated in the documentation. I don't believe it is a product issue. I do believe it is a legitimate usage issue.

I tried a number of configurations. And, my recent mistake in the previous post was leaving it in the wrong one.

I will continue to make mistakes, so I will apologize for them now. And, I will apologize for them later, as well. Your software does a lot. And, you are to be congratulated for its complexity and elegance at the same time. I have to confess it is a lot to learn.

Thanks,

Rick

[Isomorphic]

Rick, this is also now the second time you've asked us to analyze your code for mistakes, only to tell us you already knew about the mistakes we found!

Sorry, we appreciate your tenacity, and we appreciate that you are trying to find funds to buy support, but we just cannot justify investing this level of effort in helping you replicate the function of introductory product samples. Best of luck.

[RickBollinger]

Thanks for your good wishes. It is the tenacity of optimism. I wish this post was just a case of replicating the function of introductory product samples. It is not.

I looked for, but did not find, an example of using and ID and name fields for a very large value map to display a simple listgrid field as described in

Unlike the similar use of PickList.optionDataSource for pickLists used during editing or filtering, listGridField.optionDataSource causes the entire set of records from the optionDataSource to be fetched, without paging. Hence listGridField.optionDataSource is appropriate only for smaller valueMaps. For very large valueMap situations, such as an accountId field that should be displayed as an accountName where there are thousands of accounts, the recommended approach is:

* do not set listGridField.optionDataSource
* declare two fields in the DataSource, eg "accountId" and "accountName".
* Set the ListGridField.displayField attribute on the data field to the name of the display field.
* When fetching records for display in a grid, have your server send back values for both fields, but show only the data field ("accountId") in the grid.

In this case the cells in the accountId field will show the record value from the accountName field. This approach means the valueMap will never be loaded in its entirety, instead, each loaded record contains the valueMapping for that one record, as a pair of fields within the record. This sample illustrates this approach achieved via a server-side SQL join.

But, the example does not really match the specification. The example is for a combo box. And, instead of having no optionDataSource, the example sets filterEditorProperties: { optionDataSource: "supplyItem" }.

 largeValueMap.js
isc.ListGrid.create({
    ID: "orderItemList",
    width:700, height:224, alternateRecordStyles:true, 
    dataSource: largeValueMap_orderItem,
    fields:[
        {name: "orderID"},
        {name: "itemID", title: "Item Name", align: "left", width: "50%",  
         displayField: "itemName", editorType: "SelectItem", filterEditorType: "ComboBoxItem",
         filterEditorProperties: { optionDataSource: "supplyItem" } },
        {name: "quantity"},
        {name: "unitPrice"}
    ],
    autoFetchData: true,
    canEdit: true,
    showFilterEditor: true
});

If you have such an example, maybe you could point it out to me.

What I also realized was that, for my immediate purposes, I don't need to use this largeValueMap technique. My database will not be large enough to really notice the full population of a ValueMap.

I will need it eventually. You offered to build my app. I appreciate your interest in my project. But, I don't have a large enough budget for that. I might be able to fund the building of a vanilla listgridfield display large value map example.

So, for the moment I am trying to get autoFetchDisplayMap to work.

Thanks,

Rick

[Isomorphic]

The example you refer to (Large Value Map) indeed illustrates this exact technique, it is correct that it sets optionDataSource *on the FormItem, not on the ListGridField* (just as the docs say) and there is no difference in settings if you use a SelectItem vs a ComboBoxItem.

[RickBollinger]

I must conclude then that the technique will not do what I need: a simple fetch that displays a NAME field in place of an ID field that has a foreign key to the record on which it sits.

The only ray of hope seems to be in the last paragraph describing the technique:

For very advanced usage a developer can use ListGridField.displayValueFromRecord to explicitly tell the grid whether or not to display the display field value for the record in this field when a displayField is specified. See documentation on that property for more information.

I am not a very advanced user. But, I took a peek anyway:

displayValueFromRecord [IRW] [Advanced] type:Boolean, defaultValue: null

If a ListGridField.displayField is set, should this field show record values from the displayField?

If ListGridField.displayField is specified, and there is no separate ListGridField.optionDataSource, by default we will show display-field values from the same record. Setting this property to false would disable this behavior.

Alternatively, if there is a ListGridField.optionDataSource (and ListGridField.autoFetchDisplayMap is false), the displayField would be ignored for the field and the underlying record[fieldName] value would be displayed to the user. This property may be set to true to override this behavior and pick up values from the displayField for display in this field even when there is an optionDataSource set.

Note that this property has no effect on fields with an explicitly specified valueMap, or with an optionDataSource where ListGridField.autoFetchDisplayMap is true.

This should be just what I need. Except my fetch daemon is still at work. In this AutoMakerApp version that tries to use it, the name fields are blank. Not even the foreign key values are displayed.

<DataSource>
    <loadID>AutoMaker</loadID>
</DataSource>


<ListGrid dataSource="AutoMaker" ID="AutoMakerGrid" autoDraw="false">
    <fields>
        <ListGridField name="AUTOMAKERNAME" title="Automakername"/>
    </fields>
    <autoFetchData>true</autoFetchData>
    <recordClick>
            <Action>
                <target>VehicleGrid</target>
                <name>fetchRelatedData</name>
                <title>Fetch Related Data</title>
                <mapping>
<value>record</value>
                <value>viewer</value>
                <value>null</value>
                <value>null</value>
                
                </mapping>

            </Action>
    </recordClick>
</ListGrid>


<DataSource>
    <loadID>Vehicle</loadID>
</DataSource>


<ListGrid dataSource="Vehicle" ID="VehicleGrid" autoDraw="false">
    <fields>
        <ListGridField name="VEHICLENAME" title="Vehiclename"/>
        <ListGridField name="AUTOMAKERID" title="Auto Maker">
            <type>text</type>
            <optionDataSource>AutoMaker</optionDataSource>
            <valueField>AUTOMAKERID</valueField>
            <displayField>AUTOMAKERNAME</displayField>
            <autoFetchDisplayMap>false</autoFetchDisplayMap>
            <displayValueFromRecord>true</displayValueFromRecord>
        </ListGridField>
        <ListGridField name="COLORID" title="Color">
            <type>text</type>
            <optionDataSource>Color</optionDataSource>
            <valueField>COLORID</valueField>
            <displayField>COLORNAME</displayField>
            <autoFetchDisplayMap>false</autoFetchDisplayMap>
            <displayValueFromRecord>true</displayValueFromRecord>
        </ListGridField>
    </fields>
    <autoFetchData>false</autoFetchData>
</ListGrid>


<SectionStack ID="SectionStack0" autoDraw="false">
    <sections>
        <SectionStackSection>
            <ID>SectionStackSection0</ID>
            <title>SectionStackSection0</title>
            <autoShow>true</autoShow>
            <items><Canvas ref="AutoMakerGrid"/>
            </items>
        </SectionStackSection>
        <SectionStackSection>
            <ID>SectionStackSection1</ID>
            <title>SectionStackSection1</title>
            <autoShow>true</autoShow>
            <items><Canvas ref="VehicleGrid"/>
            </items>
        </SectionStackSection>
    </sections>
    <visibilityMode>multiple</visibilityMode>
</SectionStack>


<DataView ID="DataView0" overflow="hidden" autoDraw="true">
    <members><Canvas ref="SectionStack0"/>
    </members>
    <width>100%</width>
    <height>100%</height>
</DataView>

I don't see any errors in the log for bad fetches to my datasources:

DataSource ID="AutoMaker" serverType="sql" dataSourceVersion="1" dbName="PlanetProjectDB" schema="PUBLIC" autoDeriveSchema="true">
    <fields>
        <field name="AUTOMAKERID" type="sequence" primaryKey="true"/>
        <field name="AUTOMAKERNAME" type="text" length="64"/>
    </fields>
    <title>Auto Maker</title>
    <titleField>AUTOMAKERNAME</titleField>
    <pluralTitle>Auto Makers</pluralTitle>
    <generatedBy>SC_SNAPSHOT-2012-03-05_v8.2p/Pro Deployment 2012-03-05</generatedBy>
</DataSource>


<DataSource ID="Color" dataFormat="iscServer" serverType="sql" dataSourceVersion="1" dbName="PlanetProjectDB" schema="PUBLIC" tableName="COLOR" autoDeriveSchema="true">
    <fields>
        <field name="COLORID" type="sequence" primaryKey="true"/>
        <field name="COLORNAME" type="text" required="true" length="64" title="Color"/>
    </fields>
    <generatedBy>SC_SNAPSHOT-2012-03-05_v8.2p/Pro Deployment 2012-03-05</generatedBy>
</DataSource>


<DataSource ID="Vehicle" dataFormat="iscServer" serverType="sql" dataSourceVersion="1" dbName="PlanetProjectDB" schema="PUBLIC" autoDeriveSchema="true">
    <fields>
        <field name="VEHICLEID" type="sequence" primaryKey="true"/>
        <field name="VEHICLENAME" type="text" required="true" length="64"/>
        <field name="AUTOMAKERID" type="integer" required="true" foreignKey="AutoMaker.AUTOMAKERID">
            <length></length>
        </field>
        <field name="COLORID" type="integer" required="true" title="Color" foreignKey="Color.COLORID">
            <length></length>
        </field>
    </fields>
    <title>Vehicle</title>
    <titleField>VEHICLENAME</titleField>
    <pluralTitle>Vehicles</pluralTitle>
    <generatedBy>SC_SNAPSHOT-2012-03-05_v8.2p/Pro Deployment 2012-03-05</generatedBy>
</DataSource>

I tried to find an example using <displayValueFromRecord>, but could not. I even tried to make a version of demoApp that used it. But, I could not remove enough things that would let VB process it where it would still work. I had hoped to get demoApp to display parentID in place of category in the supply item list.

Thanks for sticking with me on this. I hope that a Very Advanced User can spot the obvious misconfiguration on my part to get this feature to work for me or clue me in to diagnose the problem. I mean, should I switch database systems and replace HSQLDB? Are dataSource field names case sensitive?

A screen shot, console log and Firebug NET tab image are attached.

Thanks,

Rick

[Isomorphic]

The Large Value Map sample shows the correct approach, ignore this other property, which is for something else.

[RickBollinger]

Can you tell me what the Custom SQL Feature is all about? I could not find a description on your website or in the SC Reference. I got a popup message it was required to run my AutoMakerApp:

<DataSource>
    <loadID>AutoMaker</loadID>
</DataSource>


<ListGrid dataSource="AutoMaker" ID="AutoMakerGrid" autoDraw="false">
    <fields>
        <ListGridField name="AUTOMAKERNAME" title="Automakername"/>
    </fields>
    <autoFetchData>true</autoFetchData>
    <recordClick>
            <Action>
                <target>VehicleGrid</target>
                <name>fetchRelatedData</name>
                <title>Fetch Related Data</title>
                <mapping>
<value>record</value>
                <value>viewer</value>
                <value>null</value>
                <value>null</value>
                
                </mapping>

            </Action>
    </recordClick>
</ListGrid>


<DataSource>
    <loadID>Vehicle</loadID>
</DataSource>


<ListGrid dataSource="Vehicle" ID="VehicleGrid" autoDraw="false">
    <fields>
        <ListGridField name="VEHICLENAME" title="Vehiclename"/>
        <ListGridField name="AUTOMAKERID" title="Auto Maker">
	    <type>text</type>
        </ListGridField>
        <ListGridField name="COLORID" title="Color">
            <type>text</type>
            <valueField>COLORID</valueField>
            <displayField>COLORNAME</displayField>
 	    <editorType>SelectItem</editorType>
 	    <filterEditorType>ComboBoxItem</filterEditorType>
            <filterEditorProperties>
            	<optionDataSource>Color</optionDataSource> 
            </filterEditorProperties>
        </ListGridField>
    </fields>
    <autoFetchData>false</autoFetchData>
    <canEdit>true</canEdit>
    <showFilterEditor>true</showFilterEditor>
</ListGrid>


<SectionStack ID="SectionStack0" autoDraw="false">
    <sections>
        <SectionStackSection>
            <ID>SectionStackSection0</ID>
            <title>SectionStackSection0</title>
            <autoShow>true</autoShow>
            <items><Canvas ref="AutoMakerGrid"/>
            </items>
        </SectionStackSection>
        <SectionStackSection>
            <ID>SectionStackSection1</ID>
            <title>SectionStackSection1</title>
            <autoShow>true</autoShow>
            <items><Canvas ref="VehicleGrid"/>
            </items>
        </SectionStackSection>
    </sections>
    <visibilityMode>multiple</visibilityMode>
</SectionStack>


<DataView ID="DataView0" overflow="hidden" autoDraw="true">
    <members><Canvas ref="SectionStack0"/>
    </members>
    <width>100%</width>
    <height>100%</height>
</DataView>

Could it have something with my attempt to follow your largeValueMap example:

<DataSource ID="Vehicle" dataFormat="iscServer" serverType="sql" dataSourceVersion="1" dbName="PlanetProjectDB" schema="PUBLIC" autoDeriveSchema="true">
    <fields>
        <field name="VEHICLEID" type="sequence" primaryKey="true"/>
        <field name="VEHICLENAME" type="text" required="true" length="64"/>
        <field name="AUTOMAKERID" type="integer" required="true" length="" foreignKey="AutoMaker.AUTOMAKERID"/>
        <field name="COLORID" type="integer" required="true" length="" foreignKey="Color.COLORID"/>
        <field name="COLORNAME" type="text" title="Color" tableName="Color"/>
    </fields>
    <title>Vehicle</title>
    <titleField>VEHICLENAME</titleField>
    <pluralTitle>Vehicles</pluralTitle>
    <generatedBy>SC_SNAPSHOT-2012-03-05_v8.2p/Pro Deployment 2012-03-05</generatedBy>
    
    <operationBindings>
        <operationBinding operationType="fetch">
            <tableClause>Vehicle, Color</tableClause>
            <whereClause>Vehicle.COLORID = Color.COLORID AND ($defaultWhereClause)</whereClause>
        </operationBinding>
    </operationBindings>
    
</DataSource>

I only have the Pro Edition.

Thanks,

Rick

[RickBollinger]

I had a chance over the weekend to do some research. I think the Custom SQL Feature is now called SQL/HQL Templating. This is not available with the Professional Edition. It requires the Power Edition, which would require an upgrade. I guess my application does not qualify as a simpler workgroup application.

This puts a kink into my plans. If get funding it would now have to be applied to the upgrade first. It also places more importance on the other thread posted: http://forums.smartclient.com/showthread.php?t=21448 (AutoFetchDisplayMap not fetching).

I can still demonstrate the full functionality, even though a full ValueMap will be built. The database will be small for a while and the performance hit should not be noticed.

This should be the last hurdle to get over. Boy, are my shins sore.

Happy Spring!

Rick