PickTreeItem question

[jay.l.fisher]

I have a set of 4 separate tables that represent a hierarchy; Division->Department->SubDepartment->Class. Each table has a primary key which is a number. The top level has a 2-digit primary key, the next level's key is 3-digits and the last two levels have 4-digit keys.

I've joined the tables together in a SQL UNION with a calculated ID field so they can be represented as a tree, with a unique ID and ParentID. This is the data source.

<DataSource ID="IPMerchHierarchyDS" dataFormat="iscServer"
	serverType="sql" dbName="as400" sparseUpdates="true" tableName="IPMerchHierarchy"
	cacheAllData="true"
	serverConstructor="com.islandpacific.gui.server.customDataSource.IpDataSource">
	<!-- Full merchandise hierarchy in a tree view. -->
	<fields>
		<field name="Name" type="text" canFilter="true">
			<filterEditorProperties operator="iContains" />
		</field>
		<field name="ID" type="integer" primaryKey="true" canFilter="false" hidden="true"/>
		<field name="ParentID" type="integer" rootValue="0" canFilter="false" hidden="true"
			foreignKey="IPMerchHierarchy.ID" />
		<field name="Type" type="text" length="3" canFilter="true" detail="true">
			<filterEditorProperties operator="equals" />
			<valueMap>
				<value ID="DIV">Division</value>
				<value ID="DPT">Department</value>
				<value ID="SUB">Sub-department</value>
				<value ID="CLS">Class</value>
			</valueMap>
		</field>
		<field name="Code" type="integer" length="4" canFilter="true">
			<filterEditorProperties operator="equals" />
		</field>
	</fields>
	<operationBindings>
		<operationBinding operationType="fetch">
			<customSQL>
				<![CDATA[
				WITH IpMerchHierarchy AS (
					select 100+DDIV ID, DNAM Name, 0 ParentID, 'DIV'  Type, DDIV Code from ipdivsn               
				union select 1000+DDPT ID, DNAM Name, 100+DDIV ParentID, 'DPT' Type, DDPT Code from ipdepts   
				union select 10000+SSBD ID, SNAM Name, 1000+SDPT ParentID, 'SUB' Type, SSBD Code from ipsbdpt   
				union select 100000+CCLS ID, CLNM Name, 10000+CSBD ParentID, 'CLS' Type, CCLS Code from ipclass )
		  		SELECT * FROM IpMerchHierarchy
		  		WHERE $defaultWhereClause
				]]>
			</customSQL>		
		</operationBinding>
	</operationBindings>				
</DataSource>

I've used this successfully in a TreeGrid and now I'm trying to use it in a PickTreeItem. The PickTreeItem is the editor for the "Class" field in a related data source. I want the users to be able to navigate the tree to pick a leaf "Class". I'm setting up the PickTreeItem like this.

DataSource merchHierarchyDS = DataSource.get(IslandPacificDSConstants.DATASOURCE_IPMerchHierarchyDS);
setDataSource(merchHierarchyDS);
setValueField("Code");
setLoadDataOnDemand(false);
setCanSelectParentItems(false);

The tree displays correctly and I can navigate down the tree to pick a Class. The problem is, when the form is populated with an existing record, the tree does not show the correct leaf in the tree based on the value for Class in the record.

I know it is because of the calculated ID field which doesn't correspond to the value for the Class. But how do I translate the Class value to the calculated ID field and vice versa when populating the form or getting values back from the form?

I've tried overriding the PickTreeItem's setValue() and getValue() methods, but those don't seem to be called when the form is populated with data or submitted.

Is there a better way to handle multiple tables that represent a tree like this?

[jay.l.fisher]

Any advice? Is there a better way to represent data in related tables as a tree? Seems to be a pretty common requirement. I've looked at the Tree samples but can't find one that addresses that scenario.

[Isomorphic]

We've been sitting here trying to parse out what the problem is. Is it that your trying to save a record where the value you want to save is just a Class ID (the leaf ID value) whereas the tree is managing things with a compound ID? But if you're editing a record where only a Class ID is allowed, why aren't you just using an optionDataSource that returns only Class records?

[jay.l.fisher]

I am editing a record where only Class IDs are allowed, but the user wants to navigate down the hierarchy to find their Class, not scroll through the list of all classes.

[Isomorphic]

Ah. In that case, one approach is to have the IPickTreeItem have a field name other than the field that stores a Class ID. When something is chosen in the IPickTreeItem, you can extract the Class ID and store it via df.setValue().

[jay.l.fisher]

That's the easy part, and it works pretty much automatically using setCanSelectParentItems(false). The problem is that when I edit a record which already contains a Class ID the IPickTreeItem doesn't match it up and display that tree node as the current selection.

Somehow I need to "translate" the existing Class ID into a compound ID for the tree node.

[Isomorphic]

Since the full tree is presumably not loaded, that's not possible without the server's help, right? Could create a custom "fetch" operation (with a distinct operationId) that will return a record from the hierarchy DataSource based on just a Class ID, then you'd use the compound ID in that record to populate the IPickTreeItem.

[jay.l.fisher]

The full tree is loaded. And I don't need another call to the server. I can easily derive the compound ID from any given Class ID by just adding 100000.

I think I can see how to do it using a 2nd field (say compoundClassId) in place of the actual classId field and put the IPickTreeItem on that field. Then I would populate compoundClassId when the record is fetched and add a selection handler on the IPickTreeItem or some DMI code to populate the real classId field from compoundClassId.

But I have a number of different data sources with Class ID fields so I was hoping to find a way to do this by just attaching a subclass of IPickTreeItem as the EditorType on the actual classId fields and let the subclass handle the compound ID logic.

My first thought was that there was a getter/setter of the IPickTreeItem's value that I could override to do that translation. That's why I tried overriding the IPickTreeItem's setValue() and getValue() methods, but I never see those being called.

[Isomorphic]

Munging the value stored by the IPickTreeItem is just going to create a problem where the IPickTreeItem has a value that's not valid for it's optionDataSource. Leaving it as a separate field makes sense, and you should be able to capture the pattern of having it save it's value to a second, related field as a subclass.

[jay.l.fisher]

The munged value would always be valid. The optionDataSource in this case has as it's primary key the compondId.

I guess I'm not getting it. Isn't there some way to create a custom FormItem subclass (maybe a CanvasItem?) that takes a given value, represents it in some way for editing and returns the value after editing?

I'm trying a similar thing in a completely different context. In that case I have a String field in a record that will hold an encoded AdvancedCriteria object. I want to create a "FilterBuilderItem" that will take the String value, parse it and load it into a FilterBuilder and then translate the FilterBuilder criteria back into a String when the form want's the edited value.

The encoding/decoding of the String is easy, but I can't figure out how to hook into the getValue/setValue part of the FormItem framework. Is that not possible?

[Isomorphic]

Possible in multiple ways (SimpleTypes, EditorValueParser) but it's not going to solve this problem. You aren't mapping between a stored value and displayed value, you're expecting a FormItem to store two different logical values. Instead of trying to do that, store the second logical value under a separate fieldName.

[jay.l.fisher]

I'm not trying to be dense (or argumentative, honest), but I don't see how this isn't exactly the same as mapping a stored value to a displayed value and back again.

A DateItem, for example, takes a date and displays it in a variety of ways, in one case as three separate dropdowns. The incoming Date value is used to select the appropriate choices in the dropdowns, and then the dropdown choices are used to create a Date value to store away.

I just want to do the same. Take my classId value and display it as a Tree (it doesn't really matter where the tree data comes from). The incoming classId value would be used to selected the appropriate node in the tree, and then the selected node in the tree would be converted back into a classId when it comes time to store the new value.

[Isomorphic]

If you want to think of it in the same way, you can do what the DateItem does - creates a sub-form and uses change events to propagate values up from the SelectItems used to edit each part of the date value. The analogous thing would be an IPickTreeItem embedded as a DynamicForm inside a CanvasItem - this would eliminate the need for a separate fieldName to be used in the main form (by essentially creating a sub-form to manage the logical value of the IPickTreeItem).

[jay.l.fisher]

That is what I was thinking of doing, and I can see how to use change events to put the selected tree node's logical value back into the actual classId value. But how do I tap in to the event the sets the CanvasItem with a classId value from a Record (or with a default value, etc). That's where I need to put the code that translates the classId value into a Tree node ID.

[jay.l.fisher]

It looks like addShowValueHandler() is what I was looking for.