BDC List column picker filter - search/filter not working and exact entry says not found?

Question

I'm working with MOSS 2007.

Rephrasing the question. I have a BDC column to an external data source in a custom list. Today searching and filtering are not in place and actually exact matches are not working either.

If I enter an exact entry, it comes back with "no Exact match was found"

If I select the picker, I get an error "more than 200 entries were found please narrow your search". If I select an entry it populates it, but If I try to search or filter, nothing happens.

How can fix this so exact matches work and search/filter work too?

These guides look good, but not sure how to implement. My table is very simply an ID (int) and a Department varchar(100).

How to: Define Filters for External Item Picker Controls

BDC Filtered Scriptor

My BDC xml code:

<Entities>
<Entity EstimatedInstanceCount="0" Name="dbo.UMDepartments">
  <Identifiers>
    <Identifier TypeName="System.Int32" Name="[id]" />
  </Identifiers>
  <Methods>
    <Method Name="Getdbo.[UMDepartments]">
      <Properties>
        <Property Name="RdbCommandText" Type="System.String">Select [id],[Department] From dbo.[UMDepartments]</Property>
        <Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>
      </Properties>
      <Parameters>
        <Parameter Direction="Return" Name="dbo.[UMDepartments]">
          <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="dbo.[UMDepartments]DataReader">
            <TypeDescriptors>
              <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.[UMDepartments]DataRecord">
                <TypeDescriptors>
                  <TypeDescriptor TypeName="System.Int32" Name="id" IdentifierName="[id]" />
                  <TypeDescriptor TypeName="System.String" Name="Department" />
                </TypeDescriptors>
              </TypeDescriptor>
            </TypeDescriptors>
          </TypeDescriptor>
        </Parameter>
      </Parameters>
      <MethodInstances>
        <MethodInstance Name="dbo.[UMDepartments]Finder" Type="Finder" ReturnParameterName="dbo.[UMDepartments]" ReturnTypeDescriptorName="dbo.[UMDepartments]DataReader" ReturnTypeDescriptorLevel="0" />
      </MethodInstances>
    </Method>
    <Method Name="dbo.[UMDepartments]SpecificFinder">
      <Properties>
        <Property Name="RdbCommandText" Type="System.String">Select [id],[Department] From dbo.[UMDepartments] Where ([id]=@id)</Property>
        <Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>
      </Properties>
      <Parameters>
        <Parameter Direction="In" Name="@id">
          <TypeDescriptor TypeName="System.Int32" Name="[id]" IdentifierName="[id]" />
        </Parameter>
        <Parameter Direction="Return" Name="dbo.[UMDepartments]">
          <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="dbo.[UMDepartments]DataReader">
            <TypeDescriptors>
              <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.[UMDepartments]DataRecord">
                <TypeDescriptors>
                  <TypeDescriptor TypeName="System.Int32" Name="id" IdentifierName="[id]" />
                  <TypeDescriptor TypeName="System.String" Name="Department" />
                </TypeDescriptors>
              </TypeDescriptor>
            </TypeDescriptors>
          </TypeDescriptor>
        </Parameter>
      </Parameters>
      <MethodInstances>
        <MethodInstance Name="dbo.[UMDepartments]SpecificFinder" Type="SpecificFinder" ReturnParameterName="dbo.[UMDepartments]" ReturnTypeDescriptorName="dbo.[UMDepartments]DataReader" ReturnTypeDescriptorLevel="0" />
      </MethodInstances>
    </Method>
    <Method Name="dbo.[UMDepartments]IdEnumerator">
      <Properties>
        <Property Name="RdbCommandType" Type="System.String">Text</Property>
        <Property Name="RdbCommandText" Type="System.String">Select [id] from dbo.[UMDepartments]</Property>
      </Properties>
      <Parameters>
        <Parameter Direction="Return" Name="dbo.[UMDepartments]IDs">
          <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="dbo.[UMDepartments]IDs">
            <TypeDescriptors>
              <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.[UMDepartments]">
                <TypeDescriptors>
                  <TypeDescriptor TypeName="System.Int32" Name="id" IdentifierName="[id]" />
                </TypeDescriptors>
              </TypeDescriptor>
            </TypeDescriptors>
          </TypeDescriptor>
        </Parameter>
      </Parameters>
      <MethodInstances>
        <MethodInstance Name="dbo.[UMDepartments]EnumeratorInstance" Type="IdEnumerator" ReturnParameterName="dbo.[UMDepartments]IDs" />
      </MethodInstances>
    </Method>
  </Methods>
</Entity>

Answer 1

The key things you are missing on your Finder method are:

1. FilterDescriptors
2. Input Paramaters
3. WHERE clause in you SQL Query

Have a look at this example from MSDN and then look at the Business Data Catalog: Metadata Model for details on each of the elements.

I haven't tested this but your Finder method will probably be similar to this when you are done

<Method Name="Getdbo.[UMDepartments]"> 
  <Properties> 
    <Property Name="RdbCommandText" Type="System.String">Select [id],[Department] From dbo.[UMDepartments] Where [Department] LIKE @Department</Property> 
    <Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property> 
  </Properties>
  <FilterDescriptors>
     <FilterDescriptor Type="Wildcard" Name="Department">
          <Properties>
            <Property Name="UsedForDisambiguation" Type="System.Boolean">true</Property>
          </Properties>
        </FilterDescriptor>
  </FilterDescriptors> 
  <Parameters>
    <Parameter Direction="In" Name="@Department">
          <TypeDescriptor TypeName="System.String" AssociatedFilter="Department" Name="Department">
            <DefaultValues>
              <DefaultValue MethodInstanceName="dbo.[UMDepartments]Finder" Type="System.String">%</DefaultValue>
            </DefaultValues>
          </TypeDescriptor>
        </Parameter>
    <Parameter Direction="Return" Name="dbo.[UMDepartments]"> 
      <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="dbo.[UMDepartments]DataReader"> 
        <TypeDescriptors> 
          <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.[UMDepartments]DataRecord"> 
            <TypeDescriptors> 
              <TypeDescriptor TypeName="System.Int32" Name="id" IdentifierName="[id]" /> 
              <TypeDescriptor TypeName="System.String" Name="Department" /> 
            </TypeDescriptors> 
          </TypeDescriptor> 
        </TypeDescriptors> 
      </TypeDescriptor> 
    </Parameter> 
  </Parameters> 
  <MethodInstances> 
    <MethodInstance Name="dbo.[UMDepartments]Finder" Type="Finder" ReturnParameterName="dbo.[UMDepartments]" ReturnTypeDescriptorName="dbo.[UMDepartments]DataReader" ReturnTypeDescriptorLevel="0" /> 
  </MethodInstances> 
</Method>