Need help with lookup table in ds.xml for listGrid values and dropdown.

[cschaffer]

Versions: Firefox 3.6.13, gwt-2.1.0, smartgwtpower-2.3, SmartClient Version: SC_SNAPSHOT-2010-09-04/PowerEdition Deployment

I'm creating a ListGrid that is associated with a table named ALARM. The ALARM table has a column that is a foreign key to another table, SEVERITY. I want to display the name of the severity, not the id associated with it, so I need to do a lookup on the SEVERITY table. I also want the ListGrid to be editable and want a dropdown of all existing severity values for the user to select.

I define the fields in two ds.xml files.

ALARM.ds.xml

<DataSource schema="VROOM" dbName="Oracle" tableName="ALARM"
      ID="ALARM" serverType="sql">
      <fields>
            <field sequenceName="ALARM_SEQ" primaryKey="true" name="alarm_id"
                  type="sequence" hidden="true"></field>
            <field name="alarm_name" length="51" type="text">
                  <validators>
                        <validator type="isUnique" requiresServer="true" />
                  </validators>
            </field>
            <field name="alarm_other" length="21" type="text">
            </field>
            <field name="severity_id" type="number"></field>
            <field name="severity_name" type="text" customSQL="true"
                  tableName="severity" />
      </fields>

      <operationBindings>
            <operationBinding operationType="fetch"
                  customValueFields="severity_name" customCriteriaFields="severity_name">
                  <tableClause>alarm, severity</tableClause>
                  <whereClause>alarm.severity_id = severity.severity_id AND
                        ($defaultWhereClause)</whereClause>
            </operationBinding>
      </operationBindings>
</DataSource>

SEVERITY.ds.xml

<DataSource schema="VROOM" dbName="Oracle" tableName="SEVERITY"
      ID="SEVERITY" serverType="sql">
      <fields>
            <field sequenceName="SEVERITY_SEQ" primaryKey="true" name="severity_id"
                  type="sequence" hidden="true"></field>
            <field name="severity_name" length="21" type="text">
                  <validators>
                        <validator type="isUnique" requiresServer="true" />
                  </validators>
            </field>
            <field name="severity_rank" type="number">
                  <validators>
                        <validator type="isUnique" requiresServer="true" />
                  </validators>
            </field>

      </fields>

</DataSource>

The Java code:

package com.smartgwt.sample.client;


import com.google.gwt.core.client.EntryPoint;
import com.smartgwt.client.data.DataSource;
import com.smartgwt.client.widgets.Canvas;
import com.smartgwt.client.widgets.IButton;
import com.smartgwt.client.widgets.events.ClickEvent;
import com.smartgwt.client.widgets.events.ClickHandler;
import com.smartgwt.client.widgets.grid.ListGrid;
import com.smartgwt.client.widgets.grid.events.EditorEnterEvent;
import com.smartgwt.client.widgets.grid.events.EditorEnterHandler;
import com.smartgwt.client.widgets.layout.HLayout;
import com.smartgwt.client.widgets.layout.VStack;

public class Vroom implements EntryPoint 
{
      public void onModuleLoad() 
      {
            final Canvas mainCanvas = new Canvas();
            mainCanvas.setHeight100();
            mainCanvas.setWidth100();
            mainCanvas.setBackgroundColor("blue");

            final ListGrid grid = new ListGrid();
            grid.setDataSource(DataSource.get("ALARM"));
            grid.setWidth100();
            grid.setHeight100();
            grid.setCanEdit(true);
            grid.setCanRemoveRecords(true);
            grid.fetchData();

            final IButton newBtn = new IButton("new");
            newBtn.enable();
            newBtn.addClickHandler(new ClickHandler() {
                  public void onClick(ClickEvent event) {
                        grid.startEditingNew();
                  }
            });

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

            final VStack vstack = new VStack();
            vstack.setLeft(10);
            vstack.setTop(10);
            vstack.setWidth("80%");
            vstack.setHeight("80%");
            vstack.setMembersMargin(10);
            vstack.addMember(grid);
            vstack.addMember(hLayout);

            mainCanvas.addChild(vstack);
            mainCanvas.draw();
      }
}

I can get the name of the severity to display in the grid if I have already entered a number value for severity_id. I want to be able to select a valid severity_name from the grid and use the associated severity_id on the insert. I have tried doing this a variety of ways, including defining all of the fields in the java code. I realize I could hardcode a <valueMap> in the ds.xml, but I need more flexibility than that.
So, I have two questions-
1. How do I display the associated name for the severity_id in the current record to display, while also being able to get a dropdown of the values from a table lookup to appear for editing purposes?
2. How do I get the insert to use the severity_id associated with the severity name from the dropdown?

Thanks

[Isomorphic]

You seem to have pretty much copied this sample, which was the right thing to do, and which shows this entire view/edit/save interaction working. What specifically is going wrong for you?

[cschaffer]

What's not working for me-

1. The only severity_name entries in the dropdown are severity_names whose associated severity_id already exist in the ALARM table.

2. If there are four of the same severity_ids in the ALARM table, there will be four of the associated severity_names in the dropdown.

[Isomorphic]

You can set an optionOperationId on the EditorType for the grid so that you can customize the SQL to do "select distinct" and whatever else you need to do to return appropriate dropdown entries. This is separate from your join to have each Record for the grid include the severity_name.

[cschaffer]

The current definition of severityIdField in the source code:

ListGridField severityIdField = new ListGridField("severity_id", "Severity");
severityIdField.setEditorType(new SelectItem());  
severityIdField.setFilterEditorType(new ComboBoxItem());  
severityIdField.setOptionDataSource(DataSource.get("SEVERITY"));
severityIdField.setOptionOperationId("selectDistinct");
severityIdField.setDisplayField("severity_name");

I have added the following operationBinding to the ds.xml

 <operationBinding operationType="fetch" operationId="distinctSeverity" customValueFields="severity_name" customCriteriaFields="severity_name">  
        <selectClause>DISTINCT severity_name</selectClause>
        <tableClause>SEVERITY</tableClause> 
        <orderClause>severity_name</orderClause> 
 </operationBinding>

This works, but I thought I had read somewhere in the forum that the optionDataSource should not be set on a ListGridField, because it load the entire dataSource. In this instance the lookup table is very small, but in other lookup tables could be larger.

Is there a problem with my current implementation?

[Isomorphic]

Take a look at the docs for ListGridField.optionDataSource - what we suggest for a larger dataset is still to set optionDataSource, but set it on the editor for the field only. This is also the approach used in the "Large Value Map" sample you were following.

[cschaffer]

Thanks for your help.