Setting type of child-element in widget to a dropdown

[Blama]

Hi everyone,

I'm getting started with SmartGWT EE and I'm stuck now for quite a time on an widget-design problem and looking for a best practice advice.

System:
- SmartGWT EE 2.4 evaluation version
- Eclipse Indigo
- GWT 2.3
- SQL Server 2005, Windows 2008 Server
- sqljdbc_3.0.1301.101 from http://www.microsoft.com/downloads/de-de/details.aspx?familyid=a737000d-68d0-4531-b65d-da0f2a735707&displaylang=de
- Tomcat 7.0 (nothing deployed there yet, only hosted mode so far)
- FF 5.01

What I'm trying to do is to get a dropdown control for Foreign-Key data.
I have two DS (mapped to a SQL DB), where one has the following field in the ds.xml:

<field name="user_id" foreignKey="users.id" type="integer" required="true"></field>

If I now bind that DS to the widgets in the build-in-ds example, I get a textbox for user_id and have to enter numbers there.

What I'd like is a dropdown-control that offers all the possible parent elements as text for both com.smartgwt.client.widgets.form.DynamicForm and com.smartgwt.client.widgets.grid.ListGrid.

I'm 100% sure that this is easily archived, but I don't know how.
Is there some tag in the .ds.xml file?
Do I have to something like this: boundForm.getField("user_id").setType(xxx)?
Or boundList.getField("user_id").setType(xxx)?

Thanks in advance,
Blama

[Isomorphic]

Take a look at optionDataSource on SelectItem, ComboBoxItem (FormItem in general) and ListGridField.

[Blama]

Hi Isomorphic,

thank you. I was able to solve my problem with the help of optionDataSource and this showcase example: http://www.smartclient.com/smartgwte...e_valuemap_sql.

For users with the same problem:

ds.xml for parent table:

<DataSource schema="dbo" dbName="SQLServer" tableName="users"
	ID="users" dataSourceVersion="1"
	generatedBy="SC_SNAPSHOT-2011-05-03/EVAL Deployment 2011-05-03"
	serverType="sql">
	<fields>
		<field primaryKey="true" name="id" title="ID" type="sequence"
			hidden="true"></field>
		<field name="givenname" title="Vorname" length="50" type="text" required="true"></field>
		<field name="lastname" title="Nachname" length="50" type="text" required="true"></field>
		<field name="gender" title="M/W" length="1" type="text" required="true">
			<valueMap>
				<value>M</value>
				<value>F</value>
			</valueMap>
		</field>
		<field name="email" title="eMail" length="50" type="text">
			<validators>
				<validator type="regexp" expression=".+@.+\.[a-z]+"
					errorMessage="Dies ist keine gueltige eMail-Adresse." />
				<validator type="doesntContain" substring=" "
					errorMessage="Die eMail-Adresse darf kein Leerzeichen enthalten." />
			</validators>
		</field>
	</fields>
</DataSource>

ds.xml for child table:

<DataSource schema="dbo" dbName="SQLServer" tableName="auto"
	ID="auto" dataSourceVersion="1" serverType="sql">
	<fields>
		<field primaryKey="true" name="id" title="ID" type="sequence"
			hidden="true"></field>
		<field name="user_id" foreignKey="users.id" type="integer"
			required="true"></field>
		<field name="name" length="20" type="text" required="true"></field>
		<field name="kennzeichen" length="11" type="text" required="true"></field>
		<field name="lastname" type="text" tableName="users"
			nativeName="lastname" />
	</fields>

	<operationBindings>
		<operationBinding operationType="fetch">
			<tableClause>auto, users</tableClause>
			<whereClause>auto.user_id = users.id AND	($defaultWhereClause)</whereClause>
		</operationBinding>
	</operationBindings>
</DataSource>

.java excerpt for parent table:

public class BenutzerTab extends VStack {

	public BenutzerTab() {
		super();
		setLeft(175);
		setTop(75);
		setWidth100();
		setMembersMargin(20);

		final DetailViewer boundViewer = new DetailViewer();
		final ListGrid boundList = new ListGrid(); // Liste oben
		final DynamicForm boundForm = new DynamicForm();
		final IButton saveBtn = new IButton("Speichern");
		final IButton newBtn = new IButton("Neuer Benutzer");

		DataSource ds = DataSource.get("users");
		boundList.setDataSource(ds);
		boundViewer.setDataSource(ds);
		boundForm.setDataSource(ds);
		boundList.fetchData();

		boundList.setHeight(200);
		boundList.setCanEdit(true);
		boundList.addRecordClickHandler(new RecordClickHandler() {
			public void onRecordClick(RecordClickEvent event) {
				Record record = event.getRecord();
				boundForm.editRecord(record);
				saveBtn.enable();
				boundViewer.viewSelectedData(boundList);
			}
		});
		addMember(boundList);

		boundForm.setNumCols(6);
		boundForm.setAutoFocus(false);

		boundForm
				.getField("email")
				.addClickHandler(
						new com.smartgwt.client.widgets.form.fields.events.ClickHandler() {
							public void onClick(
									com.smartgwt.client.widgets.form.fields.events.ClickEvent event) {
								Object email = boundForm.getField("email")
										.getValue();
								Object name = boundForm.getField("lastname")
										.getValue();
								Object givenname = boundForm.getField(
										"givenname").getValue();
								if (email == null
										|| email.toString().equals("")) {
									if (givenname != null && name != null
											&& !givenname.toString().equals("")
											&& !name.toString().equals("")) {
										String mailSuggestion = givenname
												.toString();
										mailSuggestion += "." + name.toString();
										mailSuggestion += "@4cgroup.com";
										boundForm.getField("email").setValue(
												mailSuggestion);
									}
								}
							}
						});
		addMember(boundForm);

		HLayout hLayout = new HLayout(10);
		hLayout.setMembersMargin(10);
		hLayout.setHeight(22);

		saveBtn.addClickHandler(new com.smartgwt.client.widgets.events.ClickHandler() {

			public void onClick(
					com.smartgwt.client.widgets.events.ClickEvent event) {
				boundForm.saveData();
				if (!boundForm.hasErrors()) {
					boundForm.clearValues();
					saveBtn.disable();
				}
			}
		});
		hLayout.addMember(saveBtn);

		newBtn.addClickHandler(new ClickHandler() {
			public void onClick(ClickEvent event) {
				boundForm.editNewRecord();
				saveBtn.enable();
			}
		});
		hLayout.addMember(newBtn);

		IButton clearBtn = new IButton("Zuruecksetzen");
		clearBtn.addClickHandler(new ClickHandler() {
			public void onClick(ClickEvent event) {
				boundForm.clearValues();
				boundList.fetchData();
				saveBtn.disable();
			}
		});
		hLayout.addMember(clearBtn);

		IButton filterBtn = new IButton("Filtern/Laden");
		filterBtn.addClickHandler(new ClickHandler() {
			public void onClick(ClickEvent event) {
				boundList.filterData(boundForm.getValuesAsCriteria());
				saveBtn.disable();
			}
		});
		hLayout.addMember(filterBtn);
		addMember(hLayout);
		addMember(boundViewer);
	}
}

.java excerpt for child table:

public class AutoTab extends VStack {

	public AutoTab() {
		super();
		setLeft(175);
		setTop(75);
		setWidth("50%");
		setMembersMargin(20);

		final DetailViewer boundViewer = new DetailViewer();
		final ListGrid boundList = new ListGrid(); // Liste oben
		final DynamicForm boundForm = new DynamicForm();
		final IButton saveBtn = new IButton("Speichern");
		final IButton newBtn = new IButton("Neues Auto");

		DataSource ds = DataSource.get("auto");
		boundList.setDataSource(ds);
		boundViewer.setDataSource(ds);
		boundForm.setDataSource(ds);
		// boundList.fetchData();

		boundList.setHeight(200);
		boundList.setCanEdit(true);
		boundList.setAutoFetchData(true);
		// boundList.setShowFilterEditor(true);
		boundList.addRecordClickHandler(new RecordClickHandler() {
			public void onRecordClick(RecordClickEvent event) {
				Record record = event.getRecord();
				boundForm.editRecord(record);
				saveBtn.enable();
				boundViewer.viewSelectedData(boundList);
			}
		});

		ListGridField userIdField = new ListGridField("user_id", "Besitzer");
		FormItem feProps = new FormItem() {
			{
				setOptionDataSource(DataSource.get("users"));
			}
		};
		userIdField.setFilterEditorProperties(feProps);
		userIdField.setWidth("50%");
		userIdField.setEditorType(new SelectItem());
		userIdField.setFilterEditorType(new ComboBoxItem());
		userIdField.setDisplayField("lastname");
		userIdField.setAlign(Alignment.LEFT);

		ListGridField nameField = new ListGridField("name");
		ListGridField kennzeichenField = new ListGridField("kennzeichen");

		boundList.setFields(nameField, userIdField, kennzeichenField);
		addMember(boundList);

		boundForm.setNumCols(6);
		boundForm.setAutoFocus(false);

		TextItem nameFormItem = new TextItem("name");
		TextItem kennzeichenFormItem = new TextItem("kennzeichen");
		kennzeichenFormItem.setMask(">???-??-0000");
		FormItem userIdFormItem = new FormItem("user_id") {
			{
				setOptionDataSource(DataSource.get("users"));
			}
		};
		userIdFormItem.setType("comboBox");
		userIdFormItem.setDisplayField("lastname");
		userIdFormItem.setTitle("Besitzer");
		userIdFormItem.setAlign(Alignment.LEFT);
		userIdFormItem.setRequired(true);
		boundForm.setFields(nameFormItem, userIdFormItem, kennzeichenFormItem);

		addMember(boundForm);

		HLayout hLayout = new HLayout(10);
		hLayout.setMembersMargin(10);
		hLayout.setHeight(22);

		saveBtn.addClickHandler(new com.smartgwt.client.widgets.events.ClickHandler() {

			public void onClick(
					com.smartgwt.client.widgets.events.ClickEvent event) {
				boundForm.saveData();
				if (!boundForm.hasErrors()) {
					boundForm.clearValues();
					saveBtn.disable();
				}
			}
		});
		hLayout.addMember(saveBtn);

		newBtn.addClickHandler(new ClickHandler() {
			public void onClick(ClickEvent event) {
				boundForm.editNewRecord();
				saveBtn.enable();
			}
		});
		hLayout.addMember(newBtn);

		IButton clearBtn = new IButton("Zuruecksetzen");
		clearBtn.addClickHandler(new ClickHandler() {
			public void onClick(ClickEvent event) {
				boundForm.clearValues();
				boundList.fetchData();
				saveBtn.disable();
			}
		});
		hLayout.addMember(clearBtn);

		IButton filterBtn = new IButton("Filtern/Laden");
		filterBtn.addClickHandler(new ClickHandler() {
			public void onClick(ClickEvent event) {
				boundList.filterData(boundForm.getValuesAsCriteria());
				saveBtn.disable();
			}
		});
		hLayout.addMember(filterBtn);
		addMember(hLayout);
		addMember(boundViewer);
	}
}

Thanks,
Blama (with another question on this already in mind :)

[Blama]

Hi Isomorphic,

I have a question regarding the design of PK-FK relations, performance and optionDataSource.

Relevant child ds.xml excerpt:

		<field name="lastname" type="text" tableName="users"
			nativeName="lastname" />
	</fields>

	<operationBindings>
		<operationBinding operationType="fetch">
			<tableClause>auto, users</tableClause>
			<whereClause>auto.user_id = users.id AND	($defaultWhereClause)</whereClause>
		</operationBinding>
	</operationBindings>

Relevant java except:

		ListGridField userIdField = new ListGridField("user_id", "Besitzer");
		FormItem feProps = new FormItem() {
			{
				setOptionDataSource(DataSource.get("users"));
			}
		};
		userIdField.setFilterEditorProperties(feProps);
		userIdField.setWidth("50%");
		userIdField.setEditorType(new SelectItem());
		userIdField.setFilterEditorType(new ComboBoxItem());
		userIdField.setDisplayField("lastname");
		userIdField.setAlign(Alignment.LEFT);

As you can see, the child ds.xml contains the join in order to get the parent-field "lastname" to display.
If I remove the field from the DS, no name is displayed in the ListGrid (but the Dropdown in doubleclick-edit-mode shows names).

So I have to code it the way I did. This means a join where I'd think I do not need one, as the Dropdown has to read the whole table "users" anyway.
Is there something like (a not yet existing) method setOptionDataSourceDisplayField(String fieldnameInOptionDatasource)?

That way I could save the join in DB. This is not important for my use-case (only very little data expected) but might be important for other users.

The only problem I can see so far is that you'd need two DS-requests in order to display all ListGrid data - one for child table only and one for translation of child.parent_id to parent.id.
If they are not the same http request (don't know if this is the case) you could get a listgrid only displaying child data as long as the parent-DS request answer is not received.

Thanks,
Blama

[Isomorphic]

Except for extreme edge cases, a join is more efficient than loading the entire related table.

[Blama]

Hi,

generally speaking, thats true.
But as far as I unterstand it, we're not comparing

get join (DS auto) vs 2x get full-table
but
get join (DS auto) vs get join (DS auto) + get full table (DS users).

This is because of the setOptionDataSource. The app has to load the full users table anyway in order to present all possible parents for selection in the dropdown.

Thanks,
Blama

[Isomorphic]

No, drop-downs automatically use load-on-demand for large datasets.

[Blama]

Thanks for the fast answer and clearing things up!

I know I read about this feature (lazy load?) in the showcase.
Out of curiosity: Is there a way I can influcence the number of records read in advance / cached for a dropdown?

Blama

[Isomorphic]

See ListGrid.dataPageSize, drawAheadRatio and related properties, and note that the PickList used by a SelectItem/ComboBoxItem is a ListGrid, with properties settable via setPickListProperties().