ListGrid does not fetch related data automatically

Technical Q&A
1

I have a ListGrid with two fields that are in related tables. When the data is displayed, if I double click on each row it fills in the data properly from the related tables, but I cannot seem to get it to do this automatically.

I’ve tried adding a “dataArrived(beginrow, endrow)” function, and then looping through and doing a “fetchRelatedData()” on each record between beginrow and endrow, but that seems to hang the application with the message:

“Finding records that match your criteria”…

See fields “templateID” and “phaseID”.

isc.ListGrid.create({
	ID:"itemList",
	dataSource:"genesysRule",
	useAllDataSourceFields:true,
	fields:[
		{name:"name", title:"Name", showHover:true, canEdit:true},
		{name:"description", showHover:true, canEdit:true},
	        {name:"templateID", tile:"Phase", optionDataSource:"genesysRuleTemplate", displayField:"name", valueField:"templateID", canEdit:false},
		{name:"category", canEdit:false},
	        {name:"phaseID", tile:"Phase", editorType:"select", optionDataSource:"genesysRuleTemplatePhase", displayField:"phase", valueField:"templatePhaseID", canEdit:true},
	        {name:"fromDate", canEdit:true},
	        {name:"toDate", canEdit:true}
	],
	recordClick:"this.updateDetails()",
	canEdit:true,
        modalEditing:true,
	cellChanged:"this.updateDetails()",
	alternateRecordStyles:true,
	canDragRecordsOut:true,
	canRemoveRecords:true,
	leaveScrollbarGap: false,
	hoverWidth:200,
        hoverHeight:20,
	selectionType:"single",

... (continues)

Thanks for any assistance!

2

Fetching related data once the rows are loaded is too late, you’re then looking at least one additional network turnaround (or the way you attempted, one per row). This sample shows the correct approach.

3

Thank you, that example showing how to use <operationBindings> worked! However, when trying to add a second field to my table related to another table, it doesn’t work. If I comment out one or the other, they work fine, but when both are in play only the first works.

I can reproduce the problem by simply using the Admin Console. See example below… the two fields using custom SQL are templateName and phase.

If I code only “templateName”, import the data set, the admin console properly maps the template ID to the templateName field in the genesysRuleTemplate table.

If I code only “phase”, import the data set, the admin console properly maps the phase ID to the phase field in the genesysRuleTemplatePhase table.

But, with both coded (as shown below), only the “templateName” gets mapped. Phase shows blank.

Is there a reason I cannot specify two custom SQL fields in the same datasource definition?

<DataSource
    ID="genesysRule"
    serverType="sql"
    tableName="genesysRule"
    testFileName="/shared/ds/test_data/genesysRule.data.xml"
>
    <fields>
        <field name="ruleID"      type="sequence" hidden="true"       primaryKey="true"/>
        <field name="name"    	  type="text"     title="Name"        length="128"       required="true"/>
        <field name="description" type="text"     title="Description" length="2000"/>
    	<field name="templateID"  type="integer"/>
	<field name="templateName" type="text"    customSQL="true"    tableName="genesysRuleTemplate"/>
        <field name="category"    type="text"     title="Node"        length="128"       required="true"
               foreignKey="genesysHierarchy.categoryName"/>
        <field name="phaseID"     type="integer"/>
	<field name="phase"       type="text"    customSQL="true"    tableName="genesysRuleTemplatePhase"/>
        <field name="fromDate"    type="date"    title="From Date"   required="false"/>
        <field name="toDate"      type="date"    title="To Date"     required="false"/>
    </fields>

    <operationBindings>
        <operationBinding operationType="fetch" customValueFields="phase" customCriteriaFields="phase">
           <tableClause>genesysRule, genesysRuleTemplatePhase</tableClause>
           <whereClause>genesysRule.phaseID = genesysRuleTemplatePhase.templatePhaseID AND ($defaultWhereClause)</whereClause>
        </operationBinding>
        <operationBinding operationType="fetch" customValueFields="templateName" customCriteriaFields="templateName">
           <tableClause>genesysRule, genesysRuleTemplate</tableClause>
           <whereClause>genesysRule.templateID = genesysRuleTemplate.templateID AND ($defaultWhereClause)</whereClause>
        </operationBinding>
    </operationBindings>
    
</DataSource>
4

Only one operationBinding is used per operation. Combine both customizations into one operationBinding.