4

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>

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> 
  • You might also add a top N to your finder to avoid to many results as a cheap fix for this issue – Heiko Hatzfeld_MSFT Mar 22 '18 at 6:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.