Foreign key conundrum (3)

[RickBollinger]

I am now 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.

I am so far unable to get foreign key references to resolve and display non-key values. In the case at hand, I have a TASK. It has two key fields that point to the same ISSUE using two different key fields: ISSUEID and ISSUEID2. With the first I am trying to display the integer ISSUENUMBER. And, with the second I am trying to display the ISSUETITLE. There are other FK references to fetch text fields. None of them work either.

The method I am using is described in the Reference for ListGridField.optionDataSource.

ListGridField.optionDataSource [IRW] type:String, defaultValue: null

Derive a ValueMap by fetching records from another DataSource and extracting the valueField and displayField in the loaded records, to derive one valueMap entry per record loaded from the optionDataSource.

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.

You can see in the attached files that the TASK DS has the proper foreign key references. This matches the DDL:

CREATE TABLE Task(
		TaskID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		ProjectID BIGINT NOT NULL,
		CommunityID BIGINT NOT NULL,
		OwnerID BIGINT,
		IssueID BIGINT NOT NULL,
		IssueID2 BIGINT NOT NULL,
		/*****************************************/
		/** Meant to hold copy of ISSUEID to    **/
		/** invoke SmartClient FK ID/NAME fetch **/
		/** for second column in FK table.      **/
		/*****************************************/
		TaskNumber INT,
		TaskStatus VARCHAR(16),
		TaskTitle VARCHAR(64),
		TaskDueDate DATE,
		CompletedDate DATE,
		OriginalDueDate DATE,
		StartDate DATE,
		Desc VARCHAR,
		Log VARCHAR,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	
	CONSTRAINT IDX_TaskNumber UNIQUE (ProjectID, TaskNumber),
	CONSTRAINT IDX_Task_Owner FOREIGN KEY (OwnerID) REFERENCES TeamMember (TeamMemberID),
	CONSTRAINT IDX_Task_Issue FOREIGN KEY (IssueID) REFERENCES Issue (IssueID),
	CONSTRAINT IDX_Task_Issue2 FOREIGN KEY (IssueID2) REFERENCES Issue (IssueID),
	CONSTRAINT IDX_Task_Project FOREIGN KEY (ProjectID) REFERENCES Project (ProjectID),
	CONSTRAINT IDX_Task_Community FOREIGN KEY (CommunityID) REFERENCES Community (CommunityID)
);

going for ISSUE:

CREATE TABLE Issue(
		IssueID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		ProjectID BIGINT NOT NULL,
		CommunityID BIGINT NOT NULL,
		IssueNumber INT NOT NULL,
		IssueTitle VARCHAR(64) NOT NULL,
		CategoryID BIGINT,
		ImpactID BIGINT NOT NULL,
		LikelihoodID BIGINT,
		OwnerID BIGINT,
		IssueStatus VARCHAR(16),
		IssueDueDate DATE,
		ClosedDate DATE,
		IssueTypeID BIGINT NOT NULL,
		OriginatorID BIGINT,
		OriginalDueDate DATE,
		Resolution LONGVARCHAR,
		Private BOOLEAN DEFAULT FALSE,
		Desc VARCHAR,
		Log VARCHAR,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	
	CONSTRAINT IDX_IssueNumber UNIQUE (ProjectID, IssueNumber),
	CONSTRAINT IDX_IssueTitle UNIQUE (ProjectID, IssueTitle),
	CONSTRAINT IDX_Issue_Project FOREIGN KEY (ProjectID) REFERENCES Project (ProjectID),
	CONSTRAINT IDX_Issue_Category FOREIGN KEY (CategoryID) REFERENCES Category (CategoryID),
	CONSTRAINT IDX_Issue_Impact FOREIGN KEY (ImpactID) REFERENCES IssueImpact (ImpactID),
	CONSTRAINT IDX_Issue_Likelihood FOREIGN KEY (LikelihoodID) REFERENCES IssueLikelihood (LikelihoodID),
	CONSTRAINT IDX_Issue_IssueType FOREIGN KEY (IssueTypeID) REFERENCES IssueType (IssueTypeID),
	CONSTRAINT IDX_Issue_Community FOREIGN KEY (CommunityID) REFERENCES Community (CommunityID)
);

The stack is empty. No related errors are reported as can be seen in the console image at end. VB just shows in an attached image the listgrid with blank fields for the fields trying to be displayed.

There just seems not to be any fetch or request for the FK fields, as can be seen in the attached NET tab image.

The optionDataSource is null for each of the attempts. It does not matter if I enter a ValueField or not for either field. VB starts by showing the correct key values and then they go blank when I add the DisplayField. The application xml is attached.

I am trying my best to duplicate what the method calls for to invoke the behavior I need. It is not mentioned if a ValueField is required.

I suspect that some feature in the environment is incorrect for proper execution of this maneuver. I remember from just a month ago that I had one field where this worked. I am just am unable to reproduce it.

This might be a tough one. It is an error of omission rather than one of commission. I may need another insight from the experts to diagnose this.

Thanks in advance for your help. I hope this is the last big hurdle to get over.

Rick