Regression with pickListCriteria

[jay.l.fisher]

A field in one of my data sources has an optionDataSource specified with pick list criteria to limit the records that appear in the drop-down. This was working until I installed the nightly build from May 20th. The additional criteria is no longer being appended to the select statement. Here is the way the field is defined in the data source.

<field name="DRSN" type="text" title="Reason" length="3" detail="true"
optionDataSource="IPATTDS" valueField="ATTDDSI" displayField="ATTDDSD">
	<pickListCriteria>
		<ATTDTPI>007</ATTDTPI>
		<ATTDCTI>001</ATTDCTI>
	</pickListCriteria>
</field>

[Isomorphic]

The ability to set pickListCriteria in a DataSource definition is working OK in that build, so this must be something more subtle. When you say "additional criteria", does this imply that you are setting extra pickListCriteria programatically, that you expect to be applied in addition to the two criterions specified in the DataSource?

[jay.l.fisher]

Something odd is going on, but I'm not sure exactly what it is. The Canvas in question has two components linked to the same data source; a ListGrid and a DynamicForm. This is the ds.xml.

<DataSource ID="IPDIVSN" dataFormat="iscServer"
	serverType="sql" dbName="as400" tableName="IPDIVSN"	
	serverConstructor="com.islandpacific.gui.server.customDataSource.IpDivsnDS">
	<checkRelatedDS>
		<relatedDS dsName="IPDEPTS" parentKeyField="DDIV" childKeyField="DDIV" required="true"/>
	</checkRelatedDS>
	<fields>
		<field name="DDIV" type="integer" title="Division Code" length="2" primaryKey="true" required="true"/>
		<field name="DNAM" type="text" title="Division Name" length="25" required="true"/>
		<field name="DUPP" type="integer" title="GTIN Prefix" length="8" detail="true" required="false"
				optionDataSource="IPUPPFX" valueField="UPCPPFX" displayField="UPCPDES"/> 
		<field name="DNMH" type="text" title="Non-merch division" length="1" detail="true"
					editorType="checkbox" labelAsTitle="true">
			<valueMap>
				<value ID="Y">true</value>
				<value ID="N">false</value>
			</valueMap>
		</field>
		<field name="DRSN" type="integer" title="Reason" detail="true"
		optionDataSource="IPATTDS" valueField="ATTDDSI" displayField="ATTDDSD" align="left">
			<pickListCriteria>
				<ATTDTPI>007</ATTDTPI>
				<ATTDCTI>001</ATTDCTI>
			</pickListCriteria>
		</field>
	</fields>
</DataSource>

When the Canvas is drawn the following DSRequests show up in the console. Notice there are two requests (why 2?) for each of the fields that use an optionDataSource and the pickListCriteria is missing from the fetches for the DRSN field.

=== 2010-05-26 08:12:05,827 [0-22] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
    },
    operationConfig:{
        dataSource:"IPDIVSN",
        operationType:"fetch",
        textMatchStyle:"substring"
    },
    startRow:0,
    endRow:75,
    componentId:"isc_IpListGrid_13",
    appID:"builtinApplication",
    operation:"IPDIVSN_fetch",
    oldValues:{
    }
}
=== 2010-05-26 08:12:05,827 [0-22] DEBUG RPCManager - Request #2 (DSRequest) payload: {
    criteria:{
    },
    operationConfig:{
        dataSource:"IPUPPFX",
        operationType:"fetch"
    },
    appID:"builtinApplication",
    operation:"IPUPPFX_fetch",
    oldValues:null
}
=== 2010-05-26 08:12:05,827 [0-22] DEBUG RPCManager - Request #3 (DSRequest) payload: {
    criteria:{
    },
    operationConfig:{
        dataSource:"IPATTDS",
        operationType:"fetch"
    },
    appID:"builtinApplication",
    operation:"IPATTDS_fetch",
    oldValues:null
}
=== 2010-05-26 08:12:05,827 [0-22] DEBUG RPCManager - Request #4 (DSRequest) payload: {
    criteria:{
    },
    operationConfig:{
        dataSource:"IPUPPFX",
        operationType:"fetch"
    },
    appID:"builtinApplication",
    operation:"IPUPPFX_fetch",
    oldValues:null
}
=== 2010-05-26 08:12:05,827 [0-22] DEBUG RPCManager - Request #5 (DSRequest) payload: {
    criteria:{
    },
    operationConfig:{
        dataSource:"IPATTDS",
        operationType:"fetch"
    },
    appID:"builtinApplication",
    operation:"IPATTDS_fetch",
    oldValues:null
}

The fetch for the DRSN field's pick list returns 185 rows yet the pick list says "No items to show". I think that is because, without the pickListCriteria being included, the fetch results in multiple records with the same value for the "valueField". Here is the ds.xml for the optionDataSource

<DataSource ID="IPATTDS" dataFormat="iscServer"
	serverType="sql" dbName="as400" tableName="IPATTDS"
	serverConstructor="com.islandpacific.gui.server.customDataSource.IpDataSource">
	<fields>
	 <field name="ATTDTPI" title="Attribute Type Index" type="integer" length="3"/>
	 <field name="ATTDCTI" title="Attribute Category Index" type="integer" length="3"/>
	 <field name="ATTDDSI" title="Attribute Value Index" type="integer" length="3"/>
	 <field name="ATTDDSD" title="Attribute Value Name" type="text" length="25"/>
	 <field name="ATTDDSP" title="Protect Name Y,n" type="text" length="1"/>
	 <field name="ATTDVRQ" title="Vendor Required Y,n" type="text" length="1"/>
	 <field name="ATTDXREF" title="Item Upload Xref" type="text" length="9"/>
	 <field name="ATTDVLF" title="Selection Value File" type="text" length="10"/>
	 <field name="ATTDDSU" title="Uppercase Name" type="text" length="25"/>
	 <field name="ATTDDSC" title="Attribute Value Code" type="text" length="2"/>
	</fields>
</DataSource>

One thing to note is that there is no "primaryKey" specified since it is a multi-part key. The two criteria fields in my pickListCriteria are needed to get a subset of records that pertain to the DRSN field choices.

If I select a record in the ListGrid, which causes the related form to be populated with that record. I get a long series of these messages in the console.

08:25:29.887 [ERROR] [ipgui] 08:25:29.886:XRP8:WARN:SelectItem:isc_SelectItem_43:Deriving valueMap for 'ATTDDSI' from dataSource based on displayField 'ATTDDSD'. This dataSource contains more than one record with ATTDDSI set to 2 with differing ATTDDSD values. Derived valueMap is therefore unpredictable.

[Isomorphic]

It isn't obvious what's wrong here. To prove that DS-specified pickListCriteria were working in that build, I changed the "employees" dataSource in the built-in-ds sample as follows:

<field name="OrgUnit" title="Favorite Protected Animal" type="text" length="128"
optionDataSource="animals" valueField="scientificName" displayField="commonName">
    <pickListCriteria>
        <status>Protected</status>
    </pickListCriteria>
</field>

and then ran the sample and saw that the optionDataSource query does include that criterion. Can you try making the same change and verify that this example of the technique works for you?

[jay.l.fisher]

I'm looking into this again with the latest build (Nov 5). I now see the SELECT statements in the console that are being issued to fill the pick list. But they are treating the integer fields as text. The initial SELECT to get the count looks like this.

SELECT COUNT(*) FROM IPATTDS WHERE (LOWER(IPATTDS.ATTDTPI) LIKE '007%' {ESCAPE '\'} AND LOWER(IPATTDS.ATTDCTI) LIKE '001%' {ESCAPE '\'})

That results in a count of zero so the SelectItem shows nothing. If the SELECT treated the fields as the integers that they are and looked like this

SELECT COUNT(*) FROM IPATTDS WHERE IPATTDS.ATTDTPI = 007 AND IPATTDS.ATTDCTI = 001

it would work.

Is there something I need to add to the field definitions or the pickListCriteria to make this work?

[Isomorphic]

As long as the pickListCriteria remain just Criteria and not AdvancedCriteria, they will have a single setting for textMatchStyle. So, either client-side (getPickListFilterCriteria) or server-side (in a DMI) you need to form AdvancedCriteria that uses the EQUALS operator for the integer fields and the ICONTAINS operator for the text field.

[jay.l.fisher]

Is there no way to accomplish that in the ds.xml? We're using a reusable component as an editor for this data source and many others and I was hoping to avoid having to have a custom editor just for this. In this case at least there is only one textMatchStyle="equals" needed for both fields. I tried putting that on the pickListCriteria tag but it doesn't effect the generated SQL.

[Isomorphic]

You would need to set textMatchStyle on the ComboBoxItem or set it via setEditorType().

One approach to defining the pickListCriteria with specific operators per field would be to express it as a customized <whereClause> instead.

[jay.l.fisher]

Putting textMatchStyle="equals" on the field itself (not on the pickListCriteria) works for me in this particular case. If it gets more complex than that I think I'll try the customized whereClause. But in that case, how do I direct the pickList fetch to a specific operationBinding? Where would I specify the operationId?

[Isomorphic]

setOptionOperationId() on ComboBoxItem and SelectItem.